> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_rest extension for MySQL

> The vsql_rest extension serves MySQL tables as HTTP endpoints from inside the server process, with URL filters, stored function calls, JWT auth, and row caps.

`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](https://github.com/villagesql/vsql-rest) |
| **License** | GPL-2.0 |
| **Capability** | Preview |

> [!WARNING]
>
> 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:

```sql theme={null}
INSTALL EXTENSION vsql_rest;
```

Confirm it is there:

```sql theme={null}
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](https://github.com/villagesql/vsql-rest).

## 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:

```sql theme={null}
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:

```sql theme={null}
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:

```bash theme={null}
curl http://127.0.0.1:3000/people
```

```json theme={null}
[{"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:

```bash theme={null}
curl "http://127.0.0.1:3000/people?id=eq.2"
```

```json theme={null}
[{"id":2,"name":"John Smyth"}]
```

Switching the setting off closes the port:

```sql theme={null}
SET GLOBAL vsql_rest.vsql_rest_enabled = OFF;
```

> [!WARNING]
>
> 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.

> [!NOTE]
>
> 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.

> [!NOTE]
>
> 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.

## See also

* [Serving Tables as a REST API](https://villagesql.com/docs/guides/rest-api-from-tables.md) — filters, per-table methods, and the defaults to change first
* [Managing extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/managing.md) — how extension settings are read and written
* [Preview capabilities](https://villagesql.com/docs/mysql-8.4/0.0.6/preview-capabilities.md) — what the preview flag turns on and what it promises
* [Available extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/extensions.md) — the full catalog
* [villagesql/vsql-rest](https://github.com/villagesql/vsql-rest) — source, the full route syntax, and the known limitations


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.