23. 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:

  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 10).

23.1 Editions concerned#

The REST endpoint exists in all editions, Express included.

23.2 Activation and configuration#

23.2.1 Variables#

Like the main port: default value, then miraj_config.xml, then command line (the command line takes precedence, see 11.2). Disabled by default.

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

23.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 (REST GET /clients/42 -> 200, token mobile, account app@%, 3 ms, from 10.0.0.5).

23.2.3 Startup and configuration errors#

With a certificate, all connections to the endpoint are encrypted (HTTPS).

23.3 Authentication#

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

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

23.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, User, Host, Access, Databases, Created, Expires

CREATE API TOKEN returns the token's secret (mja_…) only once: only its fingerprint is kept, in the accounts 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 'partenaire'@'%' ACCESS ENDPOINTS (ventes.catalogue, ventes.stock) EXPIRE INTERVAL 90 DAY;
-- Internal tool: tables and free-form SQL on one database
CREATE API TOKEN outil FOR 'dev'@'%' ACCESS TABLES, SQL DATABASES (recette);

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

Creating a token for your own account is free; for another account, CREATE USER is required.

23.3.2 Order of checks#

23.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;

23.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 parameter
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
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'@'%';
CREATE API TOKEN mobile FOR 'app'@'%';

GET  /clients/42                     → 200 {"id": 42, "nom": "Ali", "ville": "Oran"}
GET  /clients?ville=Oran&limite=10   → 200 [{"id": 42, …}, …]
POST /clients   {"nom": "Lina", "ville": "Alger"}   → 201 {"affected_rows": 1, "last_insert_id": 43}
POST /factures/7/solder              → 200 {"facture": 7, "regle": 120.50}

23.4.2 Path and method#

• The path starts with /; its segments are fixed (letters, digits, -, _, ., ~) or {name} parameters. Paths under /api and /mcp are reserved. Error 9045 otherwise.
• 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. A fixed segment takes precedence over a parameter (/clients/nouveaux before /clients/{id}).
• A known path called with another method: 405 with the Allow header.

23.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)"}).
• A parameter without a value or DEFAULT, or a value for an unknown parameter: 400, error 9046. The same name given twice (path and query string, for example): 400.

23.4.4 Response (RETURNS)#

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

23.4.5 Rights#

• Creating an endpoint: CREATE ROUTINE on the database; dropping it: ALTER ROUTINE.
• 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 (1143 for a denied column).
• SHOW GRANTS writes GRANT EXECUTE ON ENDPOINT `gestion`.`fiche_client` TO …; information_schema.ENDPOINT_PRIVILEGES lists them.

Endpoints are neither procedures nor functions: CALL does not find them, information_schema.ROUTINES does not show them; they appear in SHOW ENDPOINTS and information_schema.ENDPOINTS. They are backed up with the database (BACKUP, miraj-dump) and replicated in a cluster like procedures.

23.5 Tables: /api/{database}/{table}#

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

Read parameters: select=a,b; order=a.desc,b; 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). Example: GET /api/gestion/clients?ville=Oran&nom.like=A%25&order=nom&limit=20.

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.

23.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. Response:

{ "results": [ { "columns": [{"name": "id", "type": "int"}, {"name": "nom", "type": "varchar(40)"}],
                 "rows": [[42, "Ali"]], "row_count": 1, "truncated": false,
                 "affected_rows": null, "last_insert_id": null } ],
  "warnings": [] }

Several statements separated by ; produce several results. On error, the response carries error and the results of the statements already executed. Each request is a new session: a transaction does not span requests (use a statement block, or an endpoint).

23.7 Other paths#

23.8 Errors#

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

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

See also#

• Chapter 10: Accounts and privileges: column privileges, EXECUTE per endpoint.
• Chapter 20: MCP server: same HTTP transport, for AI assistants.
• Chapter 15: Error codes: 1143, 9044 to 9047.