SQL over the API

These 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, with one connection per app. SQLite is the default dialect, and MySQL is also available; see SQL dialect. Parameters are bound rather than spliced into the statement text.

SQL tables are part of the app's data. Backups (backup.create), restores (backup.restore), sandboxes (sandbox.create, sandbox.reset), and archives (app.archive) copy tables, rows, indexes, and empty tables. The copy returns the same results for SELECT as the app.

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, dialect (sqlite or mysql, this statement's SQL dialect; the app's sql.dialect setting when absent, which is sqlite until it is set). Any other dialect returns SQL_DIALECT_UNKNOWN (400). A statement that is not a SELECT returns "rows":[],"columns":[] with changes and last_insert_id. A result longer than limit returns its first page and a next_cursor. The statement runs once, and the cursor reads the next page of that result. It identifies the app and statement, so it cannot resume 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"?}. A batch returns every row of every statement and does not accept limit or cursor. It requires at least one statement. dialect, alongside statements, sets the SQL dialect for every statement in the batch.

Atomicity rule. The batch runs in order as one writer on the connection. If a statement fails, its row writes and those of earlier statements are undone and the batch ends. Schema changes (CREATE, ALTER, DROP) are not rolled back. A transaction statement inside a batch, such as BEGIN or COMMIT, is refused as a failure because the batch is the transaction.

A failed batch returns SQL_FAILED (400), with the message in detail and a failed_statement object:

{"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 requires collection, or returns MISSING_FIELD. With field, it returns whether that field is declared unique or indexed, and the state of its value keys: ready, building, or none with the reason. A unique field is also indexed. No key is kept until the field is declared or the first query builds its keys. Without field, the response lists every declared field in the same shape, with unique fields first. count is the number listed, or 0 when none is declared. An unknown collection returns 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'"}}