SQL over the API

Four operations run SQL for a caller holding an app key, over HTTP and MCP. They use the same engine as db.sql in a function (MariaDB 12.3 semantics), one connection per app. Parameters are bound, never spliced into the text.

SQL tables are part of the app's data: a backup (backup.create), its restore (backup.restore), a sandbox (sandbox.create, sandbox.reset) and an archive (app.archive) copy every table whole, its rows, its indexes and its empty tables, and the copy answers the same SELECT the app does.

POST /v1/_sql {"sql":"INSERT INTO prices VALUES (1, 'tea', 2.50)"}
POST /v1/_backups {"description":"before the change"}
  -> 202 {"job_id":"job_...","status":"running"}
POST /v1/_backups/snap_.../restore
POST /v1/_sql {"sql":"SELECT id, label, amount FROM prices"}
  -> {"rows":[{"id":1,"label":"tea","amount":"2.50"}],...}

Examples send Authorization: Bearer $SUBSTRIO_KEY (the app key; the app is the host or X-Substrio-Tenant). Error bodies are the standard envelope (error, code, status, retryable, next_action, then detail, field, limit and current when there are any).

Parameters and types on the wire

params is an array bound to ? in order. A value is a string, a number, a boolean or null; an object or array is INVALID_ARGUMENT. Integers up to 2^64 bind exactly, a number past 64 bits is refused (send it as a string). Decimals, dates and times go as strings ("9.99", "2026-10-01 12:00:00").

Result cells: integers are JSON integers, DOUBLE a number, DECIMAL, DATE, TIME and DATETIME their exact SQL text as strings, TEXT strings, BOOLEAN true/false, NULL null. Each result lists columns as {"name","type"}; type is one of null, boolean, integer, real, decimal, text, binary, time, date, datetime, mixed.

A TEXT/BLOB column holds 65535 bytes; a longer value is refused with error 1406 (Data too long) under the default strict sql_mode, never cut.

query.sql: one statement

curl -s -X POST $SUBSTRIO_URL/v1/_sql -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT id, owner FROM ledger WHERE id = ?","params":[1]}'

{"rows":[[1,"ann"]],"columns":[{"name":"id","type":"integer"},{"name":"owner","type":"text"}],
 "returns_rows":true,"offset":0,"total_rows":1,"next_cursor":null,"changes":0,"last_insert_id":0}

Body fields: sql (required), params, table (the table a SELECT without a FROM clause reads), limit (page size, default 1000), cursor. A statement that is not a SELECT answers "rows":[],"columns":[] with changes and last_insert_id. A result longer than limit answers its first page and a next_cursor; the statement runs once and the cursor reads the next page of that same result (it names its app and statement, so it never resumes another). CURSOR_INVALIDATED means the held result was released: run the statement again. A statement that does not parse or run answers SQL_FAILED (400) with the engine's message in detail. Transaction statements are refused here: use sql.batch.

sql.batch: statements in one transaction

curl -s -X POST $SUBSTRIO_URL/v1/_sql/batch -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" -d '{"statements":[
    {"sql":"INSERT INTO ledger (id, owner) VALUES (?, ?)","params":[1,"ann"]},
    {"sql":"INSERT INTO ledger (id, owner) VALUES (?, ?)","params":[2,"bo"]},
    {"sql":"SELECT id, owner FROM ledger ORDER BY id"}]}'

{"results":[
   {"index":0,"returns_rows":false,"changes":1,"last_insert_id":0},
   {"index":1,"returns_rows":false,"changes":1,"last_insert_id":0},
   {"index":2,"returns_rows":true,"rows":[[1,"ann"],[2,"bo"]],
    "columns":[{"name":"id","type":"integer"},{"name":"owner","type":"text"}],
    "changes":0,"last_insert_id":0}],
 "count":3}

Each statement is {"sql", "params"?, "table"?}; no limit or cursor (a batch answers whole, every row of every statement). At least one statement.

Atomicity rule. The batch is one writer on the one connection, in order. The first statement that fails undoes the row writes of every earlier statement and its own, ends the batch, and nothing of it stays. Schema changes (CREATE, ALTER, DROP) are not rolled back, as MariaDB does not roll them back. A transaction statement inside a batch (BEGIN, COMMIT) is refused as a failure: the batch is the transaction.

A failed batch answers SQL_FAILED (400) with detail the message and failed_statement beside it:

{"error":"The SQL statement did not parse or run","code":"SQL_FAILED","status":400,
 "retryable":false,"next_action":"correct_request",
 "detail":"Duplicate entry '1' for key 'PRIMARY'",
 "failed_statement":{"index":1,"errno":1062,"sqlstate":"23000",
                     "message":"Duplicate entry '1' for key 'PRIMARY'"}}

index is the statement's position (from 0); errno and sqlstate are MariaDB's (0 and HY000 when the failure carried none, as a wrong parameter count). Unknown members, an empty statements, and limit/cursor on a statement are INVALID_ARGUMENT (400).

sql.schema

curl -s "$SUBSTRIO_URL/v1/_sql/schema?table=ledger" -H "Authorization: Bearer $SUBSTRIO_KEY"

{"tables":[{"name":"ledger","kind":"table","row_count":2,"view_definition":null,
  "columns":[
    {"name":"id","type":"int(11)","data_type":"int","nullable":false,"default":null,
     "key":"primary","generated":false,"generation_expression":null,"extra":"","comment":""},
    {"name":"owner","type":"varchar(20)","data_type":"varchar","nullable":false,
     "default":"'nobody'","key":"none","generated":false,"generation_expression":null,
     "extra":"","comment":""}],
  "indexes":[{"name":"PRIMARY","primary":true,"unique":true,"fulltext":false,
              "columns":[{"name":"id","descending":false,"prefix_length":null}]}],
  "foreign_keys":[]}],
 "routines":[]}

Without table, every table and view, plus routines ({"name","kind": "function"|"procedure","parameters":[...]}). type is the declared type as SHOW COLUMNS spells it; default is the default's SQL spelling, null for no default; key is none, primary, unique or multiple. A view has "kind":"view" and its SELECT in view_definition. row_count is the kept live row count, never a scan. A table that does not exist is SQL_FAILED (400). (The exact spelling of type and default follows the engine; read them from the response, do not assume.)

index.list, index.create, index.drop

These manage indexes of SQL tables, and (with kind=collection) the declared fields of a collection: unique fields and indexed fields (below). An index.list that names a collection and no kind lists that collection's declared fields, as kind=collection does: the SQL listing reads no collection.

curl -s "$SUBSTRIO_URL/v1/_indexes?table=ledger" -H "Authorization: Bearer $SUBSTRIO_KEY"

{"indexes":[{"kind":"sql","table":"ledger","name":"PRIMARY","primary":true,"unique":true,"fulltext":false,
             "columns":[{"name":"id","descending":false,"prefix_length":null}],
             "state":"ready","reason":null}],"count":1}

Every index carries a state, the value-key state of its columns in the engine (information_schema.substrio_value_keys): ready (every row has its key and every write keeps it), building (a first query is posting the keys now) or none (no key is kept; reason says why). A primary key is always ready; a full-text index keeps no value keys and reports none with that reason. A key build that fails (a full disk, an I/O error) leaves the column none, and the engine retries it: there is no separate failed state to read.

curl -s "$SUBSTRIO_URL/v1/_indexes?kind=collection&collection=notes&field=n" -H "Authorization: Bearer $SUBSTRIO_KEY"

{"indexes":[{"kind":"collection","collection":"notes","field":"n","unique":false,"indexed":false,
             "state":"none","reason":"no key is kept: index.create declares the field, ..."}],"count":1}

kind=collection needs collection (MISSING_FIELD otherwise). With field it answers whether that field is declared unique or indexed (a unique field is also indexed: its keys are kept), and the state of its value keys: ready, building, or none with the reason (no key is kept until the field is declared or a first query on it builds the keys). Without field it lists every field the collection declares, in the same shape (unique ones first; count is the number listed, 0 when none is declared). An unknown collection is 404. collection.stats (GET /v1/{collection}/_stats) names the same indexes in indexes ({"kind":"field","field","unique"} for a declared field, {"kind":"vector"|"text"|"sparse"} for a search index); index_count is the number of entries in that list.

curl -s -X POST $SUBSTRIO_URL/v1/_indexes -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"kind":"collection","collection":"users","field":"email","unique":true,"if_not_exists":true}'
# {"success":true,"kind":"collection","collection":"users","field":"email","unique":true,"state":"ready"}

index.create with kind: "collection" declares the field: the keys are built before the answer, so the answer is the readiness signal. unique: true makes every write refuse a second document with the same value (UNIQUE, 409; a missing field, null and "" never conflict); without it the field is only kept keyed. The refusal names the declaration that refused in field, so with two unique fields a collision on the second names the second; a compound names its members in declaration order and adds fields and values:

{"error":"A unique field already holds the value","code":"UNIQUE","status":409,"retryable":false,
 "next_action":"inspect_detail","detail":"'users' already holds a document with handle = kris","field":"handle"}

The same field is on each refused row of data.import's errors. The declaration lasts across restarts. A field declared already is 409 unless if_not_exists. If two documents already share a value, a unique declaration is refused with INDEX_DUPLICATES (409: conflicts.count, conflicts.first with its _id, value and the declaration's field) and nothing is stored. The documents that hold a shared value are listed a page at a time:

curl -s "$SUBSTRIO_URL/v1/_indexes?kind=conflicts&collection=users&field=email&limit=100" -H "Authorization: Bearer $SUBSTRIO_KEY"

{"conflicts":[{"_id":"3","value":"[email protected]"},{"_id":"9","value":"[email protected]"}],"count":2,"next_cursor":null}

next_cursor goes back as cursor for the next page. DELETE /v1/_indexes/email?kind=collection&collection=users takes the declaration away (404 KEY_NOT_FOUND when there is none); the field's keys stay.

curl -s -X POST $SUBSTRIO_URL/v1/_indexes -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"table":"ledger","name":"by_owner","columns":[{"name":"owner","descending":true}],"unique":false,"if_not_exists":false}'
# {"success":true,"table":"ledger","name":"by_owner"}

curl -s -X DELETE "$SUBSTRIO_URL/v1/_indexes/by_owner?table=ledger" -H "Authorization: Bearer $SUBSTRIO_KEY"
# {"success":true,"table":"ledger","name":"by_owner"}

A column may carry prefix_length (1 to 65535, index only the first characters of a text column). table, name and a non-empty columns are required (MISSING_FIELD); index.drop needs table as a query option.

A table index made or dropped this way acts as CREATE INDEX and DROP INDEX do: it takes effect at once and is not rolled back. A refusal (duplicate name, unknown column, no such index) is SQL_FAILED (400) with the engine's message in detail and MariaDB's numbers beside it:

{"error":"The SQL statement did not parse or run","code":"SQL_FAILED","status":400,
 "retryable":false,"next_action":"correct_request","detail":"Duplicate key name 'by_owner'",
 "sql_error":{"errno":1061,"sqlstate":"42000","message":"Duplicate key name 'by_owner'"}}