How to Expose MySQL Tables as a REST API

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

This guide uses a preview capability. Start the server with --vsql_allow_preview_extensions=ON, or INSTALL EXTENSION is refused.

Putting a read-only JSON endpoint in front of a few tables usually means writing a small service, deploying it, giving it a database account, and keeping it alive. VillageSQL’s vsql_rest extension serves those tables from inside the server process instead, so there is no second process and no new deployment. The defaults are built for a developer machine, not for a network. Read “Before you expose the port” below before you open it to anything.

Turning it on

INSTALL EXTENSION vsql_rest;

Create the database and its tables first. The listener reads the database layout when it starts and caches it for vsql_rest.schema_ttl seconds, 60 by default, so a table created afterwards answers table not found until that cache expires.

CREATE DATABASE shop;
USE shop;

CREATE TABLE products (
    id       INT PRIMARY KEY,
    name     VARCHAR(40),
    category VARCHAR(20),
    price    DECIMAL(8,2),
    in_stock INT
);

INSERT INTO products VALUES
    (1, 'Budget Widget',   'widget', 29.99, 120),
    (2, 'Standard Widget', 'widget', 79.99,  40),
    (3, 'Premium Widget',  'widget', 149.99,  4),
    (4, 'Cable, 2m',       'cable',   9.99, 500),
    (5, 'Cable, 5m',       'cable',  14.99,   0);

Then name the database, name the tables you are willing to serve, and start the listener:

SET GLOBAL vsql_rest.schema = 'shop';
SET GLOBAL vsql_rest.allowed_tables = 'products';
SET GLOBAL vsql_rest.vsql_rest_enabled = ON;

Reading

A table is a path, and the query string does the filtering. Each filter is column=operator.value:

curl 'http://127.0.0.1:3000/products?category=eq.widget&order=price.desc&limit=2'

[{"id":3,"name":"Premium Widget","category":"widget","price":"149.99","in_stock":4},{"id":2,"name":"Standard Widget","category":"widget","price":"79.99","in_stock":40}]

Ask for the columns you want, and nothing else:

curl 'http://127.0.0.1:3000/products?select=name,price&in_stock=eq.0'

[{"name":"Cable, 5m","price":"14.99"}]

Combine conditions with or=(...):

curl 'http://127.0.0.1:3000/products?or=(category.eq.cable,price.gt.100)&select=id,name'

[{"id":3,"name":"Premium Widget"},{"id":4,"name":"Cable, 2m"},{"id":5,"name":"Cable, 5m"}]

Page through with limit and offset:

curl 'http://127.0.0.1:3000/products?order=id.asc&limit=2&offset=2'

[{"id":3,"name":"Premium Widget","category":"widget","price":"149.99","in_stock":4},{"id":4,"name":"Cable, 2m","category":"cable","price":"9.99","in_stock":500}]

The operators are eq, neq, lt, lte, gt, gte, like, cs_like, in and is. in takes a list, as in ?id=in.(1,3), and is takes null, as in ?price=is.null or ?price=is.not.null. A request with no limit returns at most vsql_rest.max_rows rows, which starts at 1000.

The allowlist

A table that is not on allowed_tables does not exist as far as the API is concerned:

curl 'http://127.0.0.1:3000/orders'

{"message":"table not found: orders","details":null,"hint":null,"code":"VSQL0001"}

The response is 404, and it reads the same whether the table is absent or merely withheld.

Leaving allowed_tables empty does not mean no tables. It means all of them. Set it to the tables you intend to publish before you enable the listener.

Restricting methods per table

table_methods says which HTTP methods each table accepts, as table:METHOD,METHOD with a | between tables:

SET GLOBAL vsql_rest.table_methods = 'products:GET';

By default every table accepts every method, so set this before the listener starts. A method the table does not grant is refused:

{"message":"method not allowed: POST","details":null,"hint":null,"code":"VSQL0001"}

Granting writes is the same setting:

SET GLOBAL vsql_rest.table_methods = 'products:GET,POST';

curl -X POST -H 'Content-Type: application/json' \
     -d '{"id":6,"name":"Cable, 10m","category":"cable","price":19.99,"in_stock":10}' \
     'http://127.0.0.1:3000/products'

A successful insert answers 201 with an empty body. A duplicate key comes back as 409:

{"message":"Duplicate entry '6' for key 'products.PRIMARY'","details":null,"hint":null,"code":"VSQL0001"}

Watching it work

Five status counters report on the listener, including the port it actually bound:

SHOW STATUS LIKE 'vsql_rest%';

+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| vsql_rest.connections_total | 0     |
| vsql_rest.http_port         | 3000  |
| vsql_rest.https_port        | 0     |
| vsql_rest.requests_active   | 0     |
| vsql_rest.requests_total    | 16    |
+-----------------------------+-------+

http_port reads 0 when nothing is listening, which is the quickest way to tell whether the listener started. Setting vsql_rest.port to 0 asks the operating system to choose a port, and this counter is where you read which one it chose.

Before you expose the port

The defaults leave the door open, and each one is reasonable alone. require_auth starts at OFF, so no request carries or needs a token. An empty allowed_tables publishes the whole schema. An empty table_methods allows every method, including DELETE, and a DELETE against a listener in that state succeeds:

curl -X DELETE 'http://127.0.0.1:3000/products?id=eq.6'

It answers 204, and the row is gone. Put together, a listener switched on with nothing else configured lets anyone who can reach the port read, insert, update, and delete every table in the schema. On a laptop that is convenient. Anywhere else, change four things before enabling it:

SET GLOBAL vsql_rest.allowed_tables = 'products';
SET GLOBAL vsql_rest.table_methods  = 'products:GET';
SET GLOBAL vsql_rest.require_auth   = ON;
SET GLOBAL vsql_rest.jwt_secret     = '...';

Then serve it over HTTPS by setting ssl_cert and ssl_key. There is no bind-address setting, and the listener binds every interface from the moment it starts, so block the port at the firewall and let only a reverse proxy reach it.

Troubleshooting

Symptom	Fix
`ERROR 3219 ... requires preview capabilities` on install	Start the server with `--vsql_allow_preview_extensions=ON`
Connection refused	The listener is off. Check `vsql_rest.vsql_rest_enabled`, then read `vsql_rest.http_port` in `SHOW STATUS`
`table not found` for a table that exists	It is missing from `allowed_tables`, or `vsql_rest.schema` names a different database
A write returns 405	`table_methods` does not grant that method for the table
Settings vanished after reinstalling the extension	`UNINSTALL EXTENSION` deletes persisted settings. Re-apply them

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Turning it on

Reading

The allowlist

Restricting methods per table

Watching it work

Before you expose the port

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Turning it on

​Reading

​The allowlist

​Restricting methods per table

​Watching it work

​Before you expose the port

​Troubleshooting

​See also

Turning it on

Reading

The allowlist

Restricting methods per table

Watching it work

Before you expose the port

Troubleshooting

See also