24. REST API: Miraj over HTTP and HTTPS

The REST endpoint of miraj-server opens the databases to any application that speaks HTTP: web or mobile application, script, automation tool, other service. It requires neither a driver nor a client library: just HTTP requests and JSON.

It serves three access families, opened separately on the server and for each client:

Family	Paths	Use
Endpoints	paths that you define (`GET /clients/{id}`)	APIs written in SQL (`CREATE ENDPOINT`): the application calls only what you have planned, with typed parameters
Tables	`/api/{database}/{table}[/{key}]`	Read and write the rows of a table (filters, sorting, pagination)
SQL	`POST /api/sql`	Free-form SQL, for a trusted internal tool

  Application (web, mobile, script)
         │   HTTP or HTTPS: GET https://host:7009/clients/42
         │   Authorization: Bearer mja_…   (or Basic account:password)
         ▼
  ┌─────────────────────────────── miraj-server ───────────────────────────────┐
  │  port 7007: Miraj network protocol                                         │
  │  port 7009: REST endpoint (--rest ON)                                      │
  │     family open on the server? ─► client authenticated?                    │
  │     family allowed for the token? ─► SQL rights of the account (table,     │
  │     column, EXECUTE on the endpoint) ─► SQL statement ─► locks, triggers,  │
  │     foreign keys, journal, transactions, replication                       │
  └────────────────────────────────────────────────────────────────────────────┘

Each request opens a Miraj session under the client's account, executes an ordinary SQL statement, then closes the session: nothing is ever written directly to the data files, and the account's SQL rights always apply, down to the column (chapter 15). Each request takes one max_connections slot for the duration of its execution.

24.1 Editions concerned#

The REST endpoint exists in all editions, Express included.

24.2 Activation and configuration#

24.2.1 Variables#

Like the main port: default value, then miraj_config.xml, then command line (the command line takes precedence, see 16.2). Disabled by default. The ON|OFF variables also accept true, false, 1, 0.

Variable (`miraj_config.xml`)	Argument	Default	Purpose
`rest`	`--rest ON\|OFF`	`OFF`	Opens the endpoint or not
`rest_port`	`--rest-port <port>`	`7009`	TCP port (0 to 65535), distinct from `port` and `mcp_port`; `0`: chosen by the system, displayed at startup
`rest_bind`	`--rest-bind <address>`	`127.0.0.1`	Listening address; outside the loopback interface (`127.0.0.1`, `localhost`, `::1`), TLS is mandatory
`rest_endpoints`	`--rest-endpoints ON\|OFF`	`ON`	Endpoints family open on the server
`rest_tables`	`--rest-tables ON\|OFF`	`OFF`	Tables family open on the server
`rest_sql`	`--rest-sql ON\|OFF`	`OFF`	SQL family open on the server
`rest_basic`	`--rest-basic ON\|OFF`	`ON`	Login by account and password (`Authorization: Basic`) accepted
`rest_basic_access`	`--rest-basic-access <families>`	`ENDPOINTS`	Families allowed for password logins: `ALL`, `NONE`, or `ENDPOINTS`, `TABLES`, `SQL` separated by commas
`rest_idle_timeout`	`--rest-idle-timeout <s>`	`60`	Persistent HTTP connection closed after this delay without a request (1 to 31,536,000 seconds)
`rest_statement_timeout`	`--rest-statement-timeout <s>`	`30`	Maximum duration of a statement (0 to 31,536,000 seconds, fraction allowed; `0`: no limit)
`rest_max_rows`	`--rest-max-rows <n>`	`1000`	Maximum rows returned by a response (1 to 1,000,000)
`rest_cors_origins`	`--rest-cors-origins <origins>`	empty	Origins allowed by CORS: empty (none), `*`, or `https://app.example.com,https://…` (each origin starts with `http://` or `https://`)

A family that is closed on the server does not exist for clients: its paths answer 404, even to an ACCESS ALL token.

24.2.2 Examples#

<miraj>
    <rest>ON</rest>
    <rest_bind>0.0.0.0</rest_bind>
    <tls_cert>C:\miraj\cert.pem</tls_cert>
    <tls_key>C:\miraj\key.pem</tls_key>
    <rest_cors_origins>https://app.exemple.com</rest_cors_origins>
</miraj>

miraj-server.exe --root D:\miraj\data --log --rest ON
miraj-server.exe --root D:\miraj\data --log --rest ON --rest-tables ON --rest-sql ON --rest-port 8443 --rest-bind 0.0.0.0 --tls-cert cert.pem --tls-key key.pem

Always start the server with --log: every request is then recorded, with the token or the account, the status, and the duration; an SQL error is also recorded with its code and the endpoint called:

REST GET /clients/42 -> 200, jeton mobile, compte app@%, 0 ms, depuis 127.0.0.1
REST GET /noms -> 200, compte lecteur@%, 0 ms, depuis 127.0.0.1
[509] REST ! 1143 (42000): SELECT command denied to user 'lecteur'@'localhost' for column 'ville' in table 'clients' -- ENDPOINT gestion.villes
REST GET /villes -> 403, compte lecteur@%, 0 ms, depuis 127.0.0.1

Errors and authentication failures are also recorded in <root>\miraj\server.log.

24.2.3 Startup and configuration errors#

Situation	Message or effect
Endpoint open	`MIRAJ <version> <edition> - REST endpoint on 127.0.0.1:7009, access ENDPOINTS.` (`, TLS` with a certificate; `accès` lists the open families, for example `ENDPOINTS,TABLES,SQL`)
`rest = OFF` and a `rest_*` variable given	`Note : variables rest_* ignorées, le point d'accès REST est désactivé (rest = OFF ; --rest ON pour l'ouvrir).` Server started, REST port closed
`rest_port` equal to `port`	`Point d'accès REST : rest_port (7007) est aussi le port principal ; choisissez un autre port (--rest-port) ; serveur arrêté.`
`rest_port` equal to `mcp_port`	`Point d'accès REST : rest_port (7008) est aussi le port du point d'accès MCP ; choisissez un autre port (--rest-port) ; serveur arrêté.`
`rest_bind` outside the loopback interface without a certificate	`Point d'accès REST : écoute sur 0.0.0.0 hors de la boucle locale sans TLS refusée ; donnez un certificat (--tls-cert, --tls-key) ou gardez rest_bind sur 127.0.0.1 ; serveur arrêté.`
Port already taken	`point d'accès REST : impossible d'écouter sur 127.0.0.1:7009 : … ; serveur arrêté.`
Value out of bounds	`Valeur invalide pour rest_max_rows : 0 (de 1 à 1000000)`, `Valeur invalide pour rest_basic_access : … (ALL, NONE, ou ENDPOINTS, TABLES, SQL séparés par des virgules)`, `--rest-tables : « peut-être » inconnu (ON, OFF)`…

In every "server stopped" case, the exit code is 2. The certificate is the same as the one of the main port (tls_cert, tls_key); with a certificate, all connections to the endpoint are encrypted (HTTPS).

24.3 Authentication#

Every request (except GET /api/health) presents one of the two forms:

Form	Header	When to use it
API token	`Authorization: Bearer mja_…`	Applications and services: revocable, limited to families, endpoints and databases
Account and password	`Authorization: Basic base64(account:password)`	Administration tools, one-off scripts. Accepted over HTTPS, or over HTTP from the loopback interface only; families limited by `rest_basic_access`

The account of a password login is chosen as on the main port, from the client's address: from the server machine, 'app'@'localhost' takes precedence over 'app'@'%'.

Refusals (status 401, headers WWW-Authenticate: Bearer realm="miraj" and, if rest_basic = ON, WWW-Authenticate: Basic realm="miraj", charset="UTF-8"):

Case	Message
No `Authorization` header	`Unauthorized: an API token (Authorization: Bearer mja_...) or Basic credentials is required`
Unknown, expired, or revoked token, or account that does not accept the client's host	`Unauthorized: unknown, expired or revoked API token, or account not allowed`
Wrong account or password	`Unauthorized: wrong account or password`
Basic with `rest_basic = OFF`	`Unauthorized: Basic authentication is disabled (rest_basic = OFF); use an API token`
Basic over plain HTTP outside the loopback interface	`Unauthorized: Basic authentication requires HTTPS`

Authentication failures are counted per address, as on the main port: increasing delay, then the address is blocked at max_connect_errors (FLUSH HOSTS unblocks it; 403 while it is blocked). The log reports them (SÉCURITÉ : 4 échecs d'authentification REST consécutifs depuis 127.0.0.1).

24.3.1 API tokens#

CREATE API TOKEN name [FOR account]
    [ACCESS { ALL | family [, family …] }]     -- default: ACCESS ENDPOINTS
    [DATABASES (database, …)]
    [EXPIRE { NEVER | INTERVAL n DAY }];
--  family := ENDPOINTS [([database.]name, …)] | TABLES | SQL

ALTER API TOKEN name ACCESS …;        -- families replaced, secret unchanged, effective from the next request
DROP API TOKEN [IF EXISTS] name;
SHOW API TOKENS;

name: identifier, identifier in backticks, or string; unique on the server, MCP tokens included (error 9035 Token '…' already exists).
FOR account: without FOR, the token belongs to the session's account.
The ACCESS, DATABASES and EXPIRE clauses can be written in any order, each once; EXPIRE INTERVAL n DAY requires n ≥ 1.
ENDPOINTS (name, …): a name without a database designates an endpoint of the current database (error 1046 No database selected without a current database).
DATABASES (…): the other databases no longer exist for the token's queries, even for an account that has rights on them.
DROP API TOKEN and ALTER API TOKEN of an unknown token: error 9036 Unknown token '…' (warning 9036 with IF EXISTS).

CREATE API TOKEN returns the token's secret (mja_ followed by 24 characters) only once: only its fingerprint is kept, in the account vault (replicated in a cluster). Examples:

-- Mobile application: only the APIs intended for it
CREATE API TOKEN mobile FOR 'app'@'%' ACCESS ENDPOINTS;
-- Partner: two specific endpoints, 90 days
CREATE API TOKEN partenaire FOR 'app'@'%' ACCESS ENDPOINTS (gestion.fiche_client, gestion.liste_clients) EXPIRE INTERVAL 90 DAY;
-- Internal tool: tables and free-form SQL on one database
CREATE API TOKEN outil FOR 'dev'@'%' ACCESS TABLES, SQL DATABASES (gestion);

+--------+------------------------------+-----------+-----------+---------+
| name   | token                        | account   | access    | expires |
+--------+------------------------------+-----------+-----------+---------+
| mobile | mja_3e3ZwjRCqjj47aCnxpFmUWPy | 'app'@'%' | ENDPOINTS | NULL    |
+--------+------------------------------+-----------+-----------+---------+

SHOW API TOKENS;

+------------+------+------+-----------------------------------------------------------------+-----------+---------------------+---------------------+
| Name       | User | Host | Access                                                          | Databases | Created             | Expires             |
+------------+------+------+-----------------------------------------------------------------+-----------+---------------------+---------------------+
| mobile     | app  | %    | ENDPOINTS                                                       | NULL      | 2026-10-19 02:28:09 | NULL                |
| outil      | dev  | %    | TABLES, SQL                                                     | gestion   | 2026-10-19 02:29:20 | NULL                |
| partenaire | app  | %    | ENDPOINTS (`gestion`.`fiche_client`, `gestion`.`liste_clients`) | NULL      | 2026-10-19 02:29:23 | 2027-01-17 02:29:23 |
+------------+------+------+-----------------------------------------------------------------+-----------+---------------------+---------------------+

Dates in UTC, sorted by name. The secret is never shown. As for MCP tokens (20.4.2), an account that has CREATE USER sees all tokens, the others only their own.

A token grants no rights: it restricts those of the account. Its allowed family, then the account's SQL privileges (EXECUTE on the endpoint, SELECT on the table or column…) are both checked. A session opened by token never manages tokens (9034 token management is not allowed at MCP access level ADMIN). An MCP secret (mjt_…) never opens the REST endpoint, nor does an mja_… secret open the MCP endpoint.

Creating a token for your own account is free; for another account, CREATE USER is required. DROP USER deletes the account's tokens; a locked account sees its tokens refused (401).

24.3.2 Order of checks#

Step	Refusal
Family open on the server (`rest_endpoints`, `rest_tables`, `rest_sql`)	404
Client authenticated (valid, unexpired token; correct password; account not locked)	401
Family allowed for the token (`ACCESS`) or for password logins (`rest_basic_access`)	403, error 9047
Endpoint allowed for the token (`ACCESS ENDPOINTS (…)`)	403, error 9047
SQL rights of the account	403 (1044, 1142, 1143, 1370…)

Messages of error 9047, as rendered:

{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to SQL is not allowed for API token 'mobile'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to tables is not allowed for API token 'mobile'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to endpoints is not allowed for API token 'outil'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to endpoint gestion.nouveau_client is not allowed for API token 'partenaire'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to SQL is not allowed for password authentication"}}

24.4 Endpoints: APIs written in SQL#

An endpoint associates an HTTP method and path with a SQL body (a statement, or a BEGIN … END block like a procedure). This is the recommended way to open up data: the application has access only to the planned operations, with typed parameters, and can hold only EXECUTE on its endpoints.

CREATE [OR REPLACE] [DEFINER = account] ENDPOINT [IF NOT EXISTS] [database.]name
    { GET | POST | PUT | PATCH | DELETE } '/path/{param}/…'
    [PARAMS (name type [DEFAULT value], …)]
    [RETURNS { ROWS | ONE | NONE | AFFECTED }]
    [COMMENT 'text'] [SQL SECURITY { DEFINER | INVOKER }]
    AS statement | BEGIN … END;

DROP ENDPOINT [IF EXISTS] [database.]name;
SHOW ENDPOINTS [FROM database] [LIKE 'pattern'];
SHOW CREATE ENDPOINT [database.]name;

OR REPLACE replaces an endpoint of the same name; without it, a name already taken gives error 1304 (ENDPOINT fiche_client already exists), except with IF NOT EXISTS (nothing is done). OR REPLACE and IF NOT EXISTS together: error 1221.
An endpoint cannot be created in the body of a routine.
DROP ENDPOINT of an unknown endpoint: error 1305 (ENDPOINT gestion.nope does not exist); nothing with IF EXISTS.
Two parameters with the same name in PARAMS: error 1330 (Duplicate parameter: a).

24.4.1 Complete example#

USE gestion;

-- Customer record: one row, 404 if it does not exist
CREATE ENDPOINT fiche_client GET '/clients/{id}' PARAMS (id INT) RETURNS ONE
AS SELECT id, nom, ville FROM clients WHERE id = :id;

-- Filtered list, optional parameters
CREATE ENDPOINT liste_clients GET '/clients' PARAMS (ville VARCHAR(40) DEFAULT NULL, limite INT DEFAULT 50)
AS SELECT id, nom, ville FROM clients WHERE :ville IS NULL OR ville = :ville ORDER BY nom LIMIT :limite;

-- Creation: 201 and inserted rows
CREATE ENDPOINT nouveau_client POST '/clients' PARAMS (nom VARCHAR(80), ville VARCHAR(40)) RETURNS AFFECTED
AS INSERT INTO clients (nom, ville) VALUES (:nom, :ville);

-- Multi-statement processing (with miraj-cli, between DELIMITER // and DELIMITER ;)
CREATE ENDPOINT solder_facture POST '/factures/{id}/solder' PARAMS (id INT) RETURNS ONE
AS BEGIN
    DECLARE reste DECIMAL(12,2);
    SELECT montant - paye INTO reste FROM factures WHERE id = :id FOR UPDATE;
    UPDATE factures SET paye = montant WHERE id = :id;
    INSERT INTO reglements (facture, montant) VALUES (:id, reste);
    SELECT :id AS facture, reste AS regle;
END;

-- The application only has the right to call these endpoints
CREATE USER 'app'@'%' IDENTIFIED BY '…';
GRANT EXECUTE ON ENDPOINT gestion.fiche_client TO 'app'@'%';
GRANT EXECUTE ON ENDPOINT gestion.liste_clients TO 'app'@'%';
GRANT EXECUTE ON ENDPOINT gestion.nouveau_client TO 'app'@'%';
GRANT EXECUTE ON ENDPOINT gestion.solder_facture TO 'app'@'%';
CREATE API TOKEN mobile FOR 'app'@'%';

Calls with curl (the clients table contains Ali and Karim in Oran, Samia in Alger; invoice 7 is 120.50, nothing is paid yet):

curl http://127.0.0.1:7009/clients/42 -H "Authorization: Bearer mja_3e3ZwjRCqjj47aCnxpFmUWPy"
→ 200 {"id":42,"nom":"Ali","ville":"Oran"}

curl "http://127.0.0.1:7009/clients?ville=Oran&limite=10" -H "Authorization: Bearer mja_…"
→ 200 [{"id":42,"nom":"Ali","ville":"Oran"},{"id":12,"nom":"Karim","ville":"Oran"}]

curl -X POST http://127.0.0.1:7009/clients -H "Authorization: Bearer mja_…" \
     -H "Content-Type: application/json" -d '{"nom":"Lina","ville":"Alger"}'
→ 201 {"affected_rows":1,"last_insert_id":43}

curl -X POST http://127.0.0.1:7009/factures/7/solder -H "Authorization: Bearer mja_…"
→ 200 {"facture":7,"regle":120.50}

curl http://127.0.0.1:7009/clients/999 -H "Authorization: Bearer mja_…"
→ 404 {"error":{"code":null,"sqlstate":null,"message":"Not Found: no row"}}

Without GRANT EXECUTE on solder_facture, the last call would have returned:

→ 403 {"error":{"code":1370,"sqlstate":"42000","message":"execute command denied to user 'app'@'localhost' for routine 'gestion.solder_facture'"}}

24.4.2 Path and method#

The path starts with / and is at most 255 characters long; its segments are fixed (letters, digits, -, _, ., ~) or {name} parameters (a letter or _, then letters, digits, _), each once. Paths under /api and /mcp are reserved. Error 9045 otherwise:

ERROR 9045 (HY000): Invalid endpoint path 'clients': the path must start with '/'
ERROR 9045 (HY000): Invalid endpoint path '/api/x': paths under /api and /mcp are reserved
ERROR 9045 (HY000): Invalid endpoint path '/a b': invalid character in segment 'a b'
ERROR 9045 (HY000): Invalid endpoint path '/x/{id}/{id}': parameter 'id' appears twice

Two endpoints cannot answer the same method on a path of the same shape (GET /clients/{id} and GET /clients/{code}), across all databases: error 9044.

ERROR 9044 (HY000): Endpoint path GET /clients/{code} is already used by endpoint gestion.fiche_client

A fixed segment takes precedence over a parameter: with GET '/clients/nouveaux' and GET '/clients/{id}', GET /clients/nouveaux calls the first, GET /clients/12 the second.
A known path called with another method: 405 with the Allow header (DELETE /clients/42 → 405, Allow: GET). Unknown path: 404 (Not Found).

24.4.3 Parameters#

Declared in PARAMS (name type [DEFAULT value]); an undeclared path parameter is a VARCHAR(255).
The body reads a parameter as :name, never by its bare name: WHERE id = :id compares the id column to the id parameter.
Values are taken, by name, from the path ({id}), the query string (?ville=Oran) and, for POST, PUT and PATCH, the keys of a JSON object body. Those from the path and the query string are strings converted to the declared type; those from the body keep their JSON type (null, number, boolean, string; bytes {"hex": "…"} or {"base64": "…"}; geometry {"wkt": "POINT(1 2)"} or {"wkt": "…", "srid": n}).
Errors, all with status 400:

Case	Response
Parameter without a value or `DEFAULT`	`{"error":{"code":9046,"sqlstate":"HY000","message":"Endpoint parameter 'ville': missing value"}}`
Value for an unknown parameter (`?zz=1`, body key)	`{"error":{"code":9046,"sqlstate":"HY000","message":"Endpoint parameter 'zz': unknown parameter"}}`
Same name given twice (path and query string)	`{"error":{"code":null,"sqlstate":null,"message":"Bad Request: parameter 'id' is given twice"}}`
Body that is not a JSON object	`{"error":{"code":null,"sqlstate":null,"message":"Bad Request: the body must be a JSON object"}}`
Value that cannot be converted (`GET /clients/abc`)	`{"error":{"code":1366,"sqlstate":"22007","message":"Incorrect integer value: 'abc' for column 'id' at row 1"}}`

Example of typed values in the body:

CREATE ENDPOINT echo_types PUT '/echo' PARAMS (b VARBINARY(8), n DECIMAL(10,2), d DATE, ok BOOLEAN, g GEOMETRY)
RETURNS ONE AS SELECT HEX(:b) AS hex_b, :n AS n, :d AS d, :ok AS ok, ST_AsText(:g) AS g, :g AS geo;

curl -X PUT http://127.0.0.1:7009/echo -u root:… -H "Content-Type: application/json" \
     -d '{"b":{"hex":"00ff"},"n":12.5,"d":"2026-09-25","ok":true,"g":{"wkt":"POINT(1 2)"}}'
→ 200 {"hex_b":"00FF","n":12.50,"d":"2026-09-25","ok":1,"g":"POINT(1 2)","geo":{"wkt": "POINT(1 2)", "srid": 0}}

24.4.4 Response (`RETURNS`)#

`RETURNS`	Response
`ROWS` (default)	200, array of objects from the last result of the body (`[]` without a result); beyond `rest_max_rows`, rows are truncated and the `X-Miraj-Truncated: true` header is set
`ONE`	200, object of the first row of the last result; 404 without a row (`Not Found: no row`)
`NONE`	204 without a body
`AFFECTED`	`{"affected_rows": n, "last_insert_id": id}`; 201 for `POST`, 200 otherwise

Values are written in JSON as by the MCP endpoint (20.9.1): exact decimals, integers beyond 2^53 as a string, BOOLEAN as 1 or 0, dates and times as SQL text, bytes as a hexadecimal string "0x…", geometries as an object {"wkt": "POINT(1 2)", "srid": 0}.

24.4.5 Rights#

Creating an endpoint (or replacing it with CREATE OR REPLACE): CREATE ROUTINE on the database; dropping it: ALTER ROUTINE (1044 otherwise). The creator receives no right on the endpoint they create.
Calling it: EXECUTE on the endpoint (GRANT EXECUTE ON ENDPOINT database.name TO account) or on its database (GRANT EXECUTE ON database.* …, which opens all its endpoints); otherwise 403, error 1370.
SQL SECURITY DEFINER (default): the body runs with the rights of its definer; the caller needs no rights on the tables. SQL SECURITY INVOKER: with those of the caller, columns included. For an account that reads only id and nom of clients:

CREATE ENDPOINT villes GET '/villes' SQL SECURITY INVOKER AS SELECT DISTINCT ville FROM clients;

→ 403 {"error":{"code":1143,"sqlstate":"42000","message":"SELECT command denied to user 'lecteur'@'localhost' for column 'ville' in table 'clients'"}}

SHOW GRANTS writes the rights per endpoint, and information_schema.ENDPOINT_PRIVILEGES lists them:

SHOW GRANTS FOR 'app'@'%';

+-------------------------------------------------------------------+
| Grants for app@%                                                  |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`%`                                   |
| GRANT EXECUTE ON ENDPOINT `gestion`.`fiche_client` TO `app`@`%`   |
| GRANT EXECUTE ON ENDPOINT `gestion`.`liste_clients` TO `app`@`%`  |
| GRANT EXECUTE ON ENDPOINT `gestion`.`nouveau_client` TO `app`@`%` |
| GRANT EXECUTE ON ENDPOINT `gestion`.`solder_facture` TO `app`@`%` |
+-------------------------------------------------------------------+

SELECT * FROM information_schema.ENDPOINT_PRIVILEGES;

+-----------+------------------+-----------------+----------------+----------------+--------------+
| GRANTEE   | ENDPOINT_CATALOG | ENDPOINT_SCHEMA | ENDPOINT_NAME  | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------+------------------+-----------------+----------------+----------------+--------------+
| 'app'@'%' | def              | gestion         | fiche_client   | EXECUTE        | NO           |
| 'app'@'%' | def              | gestion         | liste_clients  | EXECUTE        | NO           |
| 'app'@'%' | def              | gestion         | nouveau_client | EXECUTE        | NO           |
| 'app'@'%' | def              | gestion         | solder_facture | EXECUTE        | NO           |
+-----------+------------------+-----------------+----------------+----------------+--------------+

24.4.6 Viewing endpoints#

SHOW ENDPOINTS FROM gestion;

+---------+----------------+--------+-----------------------+----------+---------------------------------------------------------+----------------+---------------+---------------------+---------------------+---------+
| Db      | Name           | Method | Path                  | Returns  | Parameters                                              | Definer        | Security_type | Modified            | Created             | Comment |
+---------+----------------+--------+-----------------------+----------+---------------------------------------------------------+----------------+---------------+---------------------+---------------------+---------+
| gestion | fiche_client   | GET    | /clients/{id}         | ONE      | id int(11)                                              | root@localhost | DEFINER       | 2026-10-19 03:28:03 | 2026-10-19 03:28:03 |         |
| gestion | liste_clients  | GET    | /clients              | ROWS     | ville varchar(40) (optional), limite int(11) (optional) | root@localhost | DEFINER       | 2026-10-19 03:28:03 | 2026-10-19 03:28:03 |         |
| gestion | nouveau_client | POST   | /clients              | AFFECTED | nom varchar(80), ville varchar(40)                      | root@localhost | DEFINER       | 2026-10-19 03:28:03 | 2026-10-19 03:28:03 |         |
| gestion | solder_facture | POST   | /factures/{id}/solder | ONE      | id int(11)                                              | root@localhost | DEFINER       | 2026-10-19 03:28:09 | 2026-10-19 03:28:09 |         |
+---------+----------------+--------+-----------------------+----------+---------------------------------------------------------+----------------+---------------+---------------------+---------------------+---------+

Parameters marks a parameter that has a default value as (optional). SHOW CREATE ENDPOINT returns the columns Endpoint, sql_mode, Create Endpoint, character_set_client, collation_connection and Database Collation; the Create Endpoint text reproduces the definition with its definer and its RETURNS clause:

CREATE DEFINER=`root`@`localhost` ENDPOINT `liste_clients` GET '/clients' PARAMS (ville VARCHAR(40) DEFAULT NULL, limite INT DEFAULT 50) RETURNS ROWS
AS SELECT id, nom, ville FROM clients WHERE :ville IS NULL OR ville = :ville ORDER BY nom LIMIT :limite

information_schema.ENDPOINTS gives one row per endpoint, with the columns ENDPOINT_CATALOG, ENDPOINT_SCHEMA, ENDPOINT_NAME, METHOD, PATH, RETURNS, PARAMETERS, ENDPOINT_DEFINITION (the body), SECURITY_TYPE, DEFINER, CREATED, LAST_ALTERED, SQL_MODE and ENDPOINT_COMMENT.

Endpoints are neither procedures nor functions: CALL fiche_client(42) gives error 1305 (PROCEDURE gestion.fiche_client does not exist) and information_schema.ROUTINES does not show them. They are backed up with the database (BACKUP, miraj-dump) and replicated in a cluster like procedures.

24.5 Tables: `/api/{database}/{table}`#

Family opened by rest_tables = ON and by the token (ACCESS TABLES). Each request becomes a parameterized SQL statement (quoted identifiers, bound values) under the account's rights.

Request	Effect
`GET /api/{database}/{table}`	Rows (100 by default, at most `rest_max_rows`); 200 and an array of objects
`GET /api/{database}/{table}/{key}`	The row with primary key `key` (404 without it); multi-column key: values separated by commas, in the order of the table's columns
`POST /api/{database}/{table}`	Inserts a JSON object, or a non-empty array of objects with the same keys; 201 and `{"affected_rows", "last_insert_id"}`
`PATCH /api/{database}/{table}/{key}`	Modifies the columns of the body; 200 and `{"affected_rows", "last_insert_id"}`
`PATCH /api/{database}/{table}?filters`	Modifies the filtered rows (at least one filter); 200
`DELETE /api/{database}/{table}/{key}`	Deletes the row; 204, 404 without it
`DELETE /api/{database}/{table}?filters`	Deletes the filtered rows (at least one filter); 200 and `{"affected_rows", "last_insert_id"}`

Read parameters: select=a,b; order=a.desc,b (.asc optional); limit; offset; filters column[.op]=value, with op among eq (default), ne, gt, ge, lt, le, like, in (comma-separated values), null (true or false). Several filters are combined with AND. Examples, on the clients table of 24.4.1:

GET /api/gestion/clients?ville=Oran&nom.like=A%25&order=nom&limit=20
→ 200 [{"id":42,"nom":"Ali","ville":"Oran"}]

GET /api/gestion/clients?select=id,nom&order=id.desc&limit=2&offset=1
→ 200 [{"id":42,"nom":"Ali"},{"id":12,"nom":"Karim"}]

GET /api/gestion/clients?id.in=7,12&ville.null=false
→ 200 [{"id":7,"nom":"Samia","ville":"Alger"},{"id":12,"nom":"Karim","ville":"Oran"}]

POST /api/gestion/clients   {"nom":"Nadia","ville":"Blida"}
→ 201 {"affected_rows":1,"last_insert_id":44}

POST /api/gestion/clients   [{"nom":"Omar","ville":"Oran"},{"nom":"Rym","ville":null}]
→ 201 {"affected_rows":2,"last_insert_id":45}

PATCH /api/gestion/clients/44   {"ville":"Tlemcen"}
→ 200 {"affected_rows":1,"last_insert_id":0}

DELETE /api/gestion/clients/46
→ 204

Without select, the response contains the columns the account can read: all of them if it has SELECT on the table, otherwise only those granted to it. Requesting, filtering, sorting or writing a denied column: 403, error 1143. For an account that has only SELECT (id, nom) on clients:

GET /api/gestion/clients?limit=2
→ 200 [{"id":42,"nom":"Ali"},{"id":7,"nom":"Samia"}]

GET /api/gestion/clients?ville=Oran
→ 403 {"error":{"code":1143,"sqlstate":"42000","message":"SELECT command denied to user 'lecteur'@'localhost' for column 'ville' in table 'clients'"}}

Refusals specific to this family:

Case	Status and message
`PATCH` or `DELETE` without a key or filter	400 `Bad Request: PATCH needs a key or at least one filter` (`DELETE needs …`)
Unknown operator (`nom.zz=1`)	400 `Bad Request: unknown filter operator 'zz' (eq, ne, gt, ge, lt, le, like, in, null)`
`limit` or `offset` that is not a non-negative integer	400 `Bad Request: 'limit' must be a non-negative integer`
Unknown column in a filter	400, error 1054
Multi-column key badly split	400 `Bad Request: the key has 2 column(s), separated by commas`
Table without a primary key, called by key	404 `Not Found: unknown table, or a table without primary key`
Path of another shape (`/api/gestion/clients/1/2`)	404 `Not Found: /api/{database}/{table}[/{key}] expected`
`POST` on a key	405 `Method Not Allowed: POST to /api/{database}/{table}`, `Allow: GET, PATCH, DELETE`
Other method (`PUT`)	405, `Allow: GET, POST, PATCH, DELETE` (or `GET, PATCH, DELETE` with a key)
Duplicate key	409, error 1062

24.6 Free-form SQL: `POST /api/sql`#

Family opened by rest_sql = ON and by the token (ACCESS SQL).

{ "sql": "SELECT id, nom FROM gestion.clients WHERE ville = :ville", "params": { "ville": "Oran" }, "max_rows": 100 }

params: array for ? placeholders, object for :name ones; values as for the parameters of execute_sql of the MCP endpoint (20.9.2). max_rows (positive integer) is capped at rest_max_rows. Response:

{"results":[{"columns":[{"name":"id","type":"int(11)"},{"name":"nom","type":"varchar(80)"}],"rows":[[42,"Ali"],[12,"Karim"]],"row_count":2,"truncated":false,"affected_rows":null,"last_insert_id":null}],"warnings":[]}

Several statements separated by ; produce several results; a statement that writes produces a result without columns, with affected_rows and last_insert_id:

{"sql": "SELECT id, nom FROM gestion.clients WHERE id > ? ORDER BY id; UPDATE gestion.clients SET ville = ville WHERE id = 7", "params": [10]}

{"results":[{"columns":[{"name":"id","type":"int(11)"},{"name":"nom","type":"varchar(80)"}],"rows":[[12,"Karim"],[42,"Ali"],[43,"Lina"]],"row_count":3,"truncated":false,"affected_rows":null,"last_insert_id":null},{"columns":[],"rows":[],"row_count":0,"truncated":false,"affected_rows":1,"last_insert_id":0}],"warnings":[]}

On error, the response carries error and the results of the statements already executed, with the status of the error (24.8):

{"error":{"code":1142,"sqlstate":"42000","message":"SELECT command denied to user 'dev'@'localhost' for table `ventes`.`article`"},"results":[{"columns":[{"name":"un","type":"bigint"}],"rows":[[1]],"row_count":1,"truncated":false,"affected_rows":null,"last_insert_id":null}],"warnings":[]}

Each request is a new session: a transaction does not span requests (use a statement block, or an endpoint). Refusals specific to this path: missing sql (400 Bad Request: 'sql' (string) is required), params of another type (400), method other than POST (405 Method Not Allowed: use POST).

24.7 Other paths#

Path	Purpose
`GET /api/health`	`{"status":"ok","version":"1.0.0","edition":"Entreprise"}`, without authentication (availability probe); other method: 405
`OPTIONS …`	204 with `Allow: GET, POST, PUT, PATCH, DELETE, OPTIONS`; for an origin in `rest_cors_origins`, also `Access-Control-Allow-Methods`, `Access-Control-Allow-Headers: Authorization, Content-Type` and `Access-Control-Max-Age: 600`

A response to a request whose Origin header is allowed by rest_cors_origins carries Access-Control-Allow-Origin (the origin, or *) and Vary: Origin. Every response carries Cache-Control: no-store.

24.8 Errors#

Body: {"error": {"code": 1143, "sqlstate": "42000", "message": "…"}} (code and sqlstate are null for an HTTP error without a SQL code).

Status	Case
400	Invalid JSON (`Bad Request: invalid JSON body (…)`), missing or unknown parameter (9046), invalid SQL (1064), unknown column (1054), value outside the type (1366…), other error with SQLSTATE `22` or `42`
401	Authentication missing or refused; Basic over plain HTTP outside the loopback interface (24.3)
403	Blocked address, family or endpoint not allowed (9047), SQL rights (1044, 1142, 1143, 1370), token management by token (9034)
404	Family closed on the server, unknown path, row absent (`ONE`, key), unknown database or table (1049, 1146)
405	Method not served on this path (`Allow`)
409	Duplicate key (1062), foreign key (1451, 1452), lock conflict (1205, 1213), other error with SQLSTATE `23`
415	Body sent with a `Content-Type` other than `application/json` (`Unsupported Media Type: application/json expected`)
500	Other engine error
503	`max_connections` reached (1040)
504	Statement timeout exceeded (1969) or statement interrupted (1317)

24.9 Best practices#

Prefer endpoints: the application holds only EXECUTE on its APIs, the SQL stays on the server, and a schema change affects only the endpoint bodies.
One account per application, one token per deployment (ACCESS ENDPOINTS (…) as narrow as possible, EXPIRE), revoked with DROP API TOKEN.
Keep rest_tables and rest_sql closed in production, or reserved for internal-tool tokens limited to one database.
Outside the loopback interface, the endpoint requires TLS; place it behind a firewall and limit rest_cors_origins to the origins of your applications.