vsql_http extension for MySQL

vsql_http makes HTTP requests from inside a query. A trigger can call a webhook, a SELECT can enrich rows from an API, and a scheduled event can push a summary somewhere, with no application layer in between. It is built on libcurl and returns every response as JSON.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-http
License	GPL-2.0

Install

vsql_http.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_http;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_http';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_http      |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

Every request function returns one JSON object with four fields: status, content_type, headers, and content.

Function	Returns	What it does
`http_get(url)`	text	A GET request, with the response as JSON
`http_post(url, content_type, body)`	text	A POST request with a body
`http_put(url, content_type, body)`	text	A PUT request with a body
`http_patch(url, content_type, body)`	text	A PATCH request with a body
`http_delete(url)`	text	A DELETE request
`http_request(method, url, headers, body, content_type, options)`	text	Any method, with your own headers and options as JSON
`url_encode(s)`	text	Percent-encodes a string for use in a URL
`url_decode(s)`	text	Reverses `url_encode`

Example

Fetch a URL and read two fields out of the response:

SET @r = CONVERT(http_get('https://villagesql.com/robots.txt') USING utf8mb4);

SELECT JSON_EXTRACT(@r, '$.status') AS status,
       JSON_UNQUOTE(JSON_EXTRACT(@r, '$.content_type')) AS content_type;

+--------+---------------------------+
| status | content_type              |
+--------+---------------------------+
| 200    | text/plain; charset=utf-8 |
+--------+---------------------------+

Build a query string safely:

SELECT url_encode('hello world & more') AS encoded;

+----------------------------+
| encoded                    |
+----------------------------+
| hello%20world%20%26%20more |
+----------------------------+

On VillageSQL 0.0.6 and earlier the response carries the binary character set. A JSON function or a JSON column rejects it, so the example above without its CONVERT fails there with ERROR 3144 (22032): Cannot create a JSON value from a string with CHARACTER SET 'binary'. Inside JSON_OBJECT() or JSON_ARRAY() it is accepted but base64-encoded instead. Keep the CONVERT(... USING utf8mb4) on those versions; it is unnecessary on 0.0.7 and later, and harmless there.

A request blocks the statement that made it, for up to 30 seconds by default. Only http_request can shorten that, through its options argument, as in '{"timeout": 5}'. One call per row turns a table scan into a series of network round trips, so keep these calls off large result sets.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

Example

See also

​Install

​What it adds

​Example

​See also

Install

What it adds

Example

See also