MySQL のテーブルを REST API として公開する方法

INSTALL EXTENSION vsql_rest;

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

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

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

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

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

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"}]

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

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

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

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

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

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'

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

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

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

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     = '...';