vsql_rest extension for MySQL

vsql_rest serves your tables over HTTP from inside the database process. A client reads rows with a URL instead of a connection, filters them with query parameters, and calls stored functions under /rpc/. There is no application to write and no second process to run.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-rest
License	GPL-2.0
Capability	Preview

This extension uses a preview capability, so vsql_allow_preview_extensions must be ON. Start the server with --vsql_allow_preview_extensions=ON, or run SET PERSIST vsql_allow_preview_extensions = ON. INSTALL EXTENSION is refused otherwise.

Install

vsql_rest.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_rest;

Confirm it is there:

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

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_rest      |
+----------------+

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

What it adds

It adds no SQL functions. You configure it with SET GLOBAL, and every request it answers arrives over HTTP.

Setting	Default	What it controls
`vsql_rest.vsql_rest_enabled`	`OFF`	Whether the listener runs
`vsql_rest.schema`	empty	The database it serves; every table and RPC request gets `503` (code `VSQL0007`) until this is set
`vsql_rest.allowed_tables`	empty	Which tables are reachable; empty means all of them
`vsql_rest.table_methods`	empty	Which HTTP methods each table accepts, written `table:GET,POST\|table2:GET`; empty means `GET`, `POST`, `PATCH` and `DELETE` on every table
`vsql_rest.allowed_routines`	empty	Which functions are callable under `/rpc/`; empty means all of them
`vsql_rest.port`	`3000`	The HTTP port
`vsql_rest.ssl_port`	`3443`	The HTTPS port
`vsql_rest.ssl_cert`, `vsql_rest.ssl_key`	empty	The certificate and key for HTTPS
`vsql_rest.require_auth`	`OFF`	Whether a request must carry a JWT
`vsql_rest.jwt_secret`, `vsql_rest.jwt_public_key`	empty	How that JWT is verified
`vsql_rest.max_rows`	`1000`	How many rows come back when a request carries no `?limit`
`vsql_rest.schema_ttl`	`60`	How long the cached table layout is reused, in seconds; changing `vsql_rest.schema`, or turning `vsql_rest_enabled` off and on, rebuilds it on the next request

Example

Create the table first, because the listener caches the database layout when it starts and holds it for schema_ttl seconds:

CREATE DATABASE extdocs;
USE extdocs;

CREATE TABLE people (id INT PRIMARY KEY, name VARCHAR(32));

INSERT INTO people VALUES
    (1, 'Jonathan Smith'),
    (2, 'John Smyth'),
    (3, 'Joan Smithe'),
    (4, 'Peter Brown');

Then name the database and the table and switch the listener on:

SET GLOBAL vsql_rest.schema = 'extdocs';
SET GLOBAL vsql_rest.allowed_tables = 'people';
SET GLOBAL vsql_rest.vsql_rest_enabled = ON;

Read the table over HTTP:

curl http://127.0.0.1:3000/people

[{"id":1,"name":"Jonathan Smith"},{"id":2,"name":"John Smyth"},{"id":3,"name":"Joan Smithe"},{"id":4,"name":"Peter Brown"}]

Filter it with a query parameter:

curl "http://127.0.0.1:3000/people?id=eq.2"

[{"id":2,"name":"John Smyth"}]

Switching the setting off closes the port:

SET GLOBAL vsql_rest.vsql_rest_enabled = OFF;

The defaults leave authentication off, every table served, and every supported HTTP method (GET, POST, PATCH, DELETE) allowed, and the listener binds every interface from the moment it starts. Anything that can reach the host can then read and write every table in the schema. Set allowed_tables, table_methods and require_auth, configure a JWT secret or public key, and put the port behind a firewall before you switch the listener on. There is no bind-address setting.

A setting written with SET PERSIST survives a restart, but UNINSTALL EXTENSION deletes it. After an uninstall and reinstall every setting is back at its default, and no restart brings the old value back.

Every string setting is read back through the server, which returns at most 1022 bytes. A longer schema, allowed_tables, allowed_routines, table_methods, jwt_secret or jwt_public_key makes every request fail with 500; a longer ssl_cert or ssl_key keeps the listener from starting.

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