Database: the HTTP endpoints and their filters

The endpoints at a glance

“Editing enabled” and “custom SQL enabled” are the two database switches in the app’s settings; with one off, its endpoints answer 403 regardless of the user’s rights. The rights themselves come from the user or their role — see Users & Auth.

USE_TRANSACTION is the odd one out: every other right above says what you may do, while that one says whether you may open a transaction session at all. It is not granted by default, because such a session holds the database's single writer while it lives — long enough to hold up every other client, which is not something a plain reader should be able to do by accident.

How parameters are sent

Parameters are ordinary form fields (application/x-www-form-urlencoded), or query-string parameters on a GET. Values that are themselves JSON — filters and values — are sent as strings containing JSON, because a form field has nothing else to offer.

curl -X PUT https://your-server/api/db/update \
     --data-urlencode 'table=notes' \
     --data-urlencode 'values={"pinned":1}' \
     --data-urlencode 'filters={"clauses":["id="],"args":[7]}'

Table and column names must look like identifiers — a letter or underscore followed by letters, digits or underscores. Anything else is rejected before it reaches the database, which is what keeps these endpoints safe from injection through a name.

Filters

Every endpoint that acts on “the rows matching something” takes the same filters parameter. It is a JSON object with two arrays:

{"clauses": ["dept=", "salary]"], "args": ["eng", 50000]}

clauses holds column names each with an operator suffix, and args holds the values they compare against, in order. The values never become part of the SQL text — they are bound as parameters — which is why the operator has to be part of the clause rather than written out by you.

Clauses are combined with AND. There is no OR and no grouping; when you need those, use /api/db/query and write the SQL yourself. Or use the SQL Views feature.

Operators

The two set operators carry their own count: id∈3 means “id is one of the next three arguments”. Those characters are U+2208 (∈) and U+2209 (∉) — remember to URL-encode them, as you would any non-ASCII character in a request.

Examples

// engineers earning at least 50000
{"clauses": ["dept=", "salary]"], "args": ["eng", 50000]}

// anyone whose name contains "smith"
{"clauses": ["name?"], "args": ["%smith%"]}

// three specific rows, by the hidden rowid column
{"clauses": ["rowid∈3"], "args": [12, 13, 14]}

// everything except two departments, created this year
{"clauses": ["dept∉2", "created>"], "args": ["sales", "ops", "2026-01-01"]}

// no filter at all - every row
{"clauses": [], "args": []}

The last one is worth pausing on: a delete with no filters removes every row of the table. The parameter may also be omitted entirely, which means the same thing.

When a filter is wrong

Two different things can go wrong, and they answer differently:

• The JSON is not the expected shape — not an object, or missing clauses or args, or one of them is not an array. That is a 400 Bad Request.
• A clause does not parse — an unknown operator, a column name that is not an identifier, a ∈ without a count. That is refused as a 403, because a clause that does not match the grammar is treated as an attempt to inject SQL rather than as a typo.

Count your arguments carefully — the number of args must match what the clauses ask for, one per simple operator and n for a ∈n or ∉n. A mismatch is not checked before the query is built, and the two directions fail differently: supplying too few silently matches nothing and returns an empty result, while supplying too many fails the request outright. Neither tells you clearly what you got wrong, so when a filter returns nothing you expected, check the counts first.

Reading rows

/api/db/table takes, besides table and filters:

• columns — a comma-separated list; all columns when omitted.
• limit and offset — paging. With no limit the whole table is returned, so pass one for anything large.
• sort and sort-order — a column name, and asc (the default) or desc. Note the hyphen in sort-order.
• includeRowId — prepend SQLite’s hidden rowid to each row. Useful as a stable handle for tables without a primary key.
• rowsAsObjects — return each row as {"column": value} instead of a positional array.
• includeTotal — add a total field with the number of rows matching the filters, ignoring the page window. Handy for a pager.

{"total": 42, "data": [[1, "john", "eng"], [2, "jane", "sales"]]}

Writing rows

values is a JSON object of column names to values. For /api/db/insert it may instead be an array of such objects, which inserts several rows in one transaction — if any row is rejected, none of them is inserted. The answer follows the request: an object yields {"generated_id": n}, an array yields {"generated_ids": [...]}, even for an array of one. Rows in an array need not share the same columns.

Binary values are written as a small wrapper object rather than a bare string:

{"avatar": {"type": "blob", "value": "<base64-encoded bytes>"}}

Reading them back is what /api/db/cell-data is for: it streams one cell as raw bytes with the appropriate content type, answers 204 No Content when the cell is empty, and 404 when no row matches the filters. Only text and binary cells can be streamed.

Arbitrary SQL

POST /api/db/query takes the SQL as the request body, as text/plain. The body may hold several statements separated by semicolons; they all run in one transaction, and only the last one returns rows. limit, offset and includeNames are query-string parameters controlling the answer.

curl -X POST 'https://your-server/api/db/query?limit=50&includeNames=true' \
     -H 'Content-Type: text/plain' \
     --data 'SELECT id, name FROM notes ORDER BY id DESC'

{"offset": 0, "limit": 50, "columns": ["id", "name"], "data": [[7, "Latest"]]}

A statement the database rejects comes back as 420 with the database’s own message — an unusual status code, but the one this endpoint has always used, and it lets a client tell a bad query from a server fault.

See also

• API Documentation — every parameter and response, and the downloadable specification.
• Transactions over WebSocket — when several of these operations have to succeed or fail together.
• Security: Rules — allowing or denying individual operations per table, optionally with a SQL expression.
• Users & Auth — where the rights named above come from.