> ## 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 the server must be started with
  `--vsql_allow_preview_extensions=ON`. `INSTALL EXTENSION` is refused
  otherwise.
</Warning>

## 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                                              |
| `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; empty means every method     |
| `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              |

## 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 HTTP
  method 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.
</Warning>

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

## See also

* [Managing extensions](/docs/mysql-8.4/dev/managing) — how extension settings are read and written
* [Preview capabilities](/docs/mysql-8.4/dev/preview-capabilities) — what the preview flag turns on and what it promises
* [Available extensions](/docs/mysql-8.4/dev/extensions) — the full catalog
* [villagesql/vsql-rest](https://github.com/villagesql/vsql-rest) — source, the full route syntax, and the known limitations