Transactions over WebSocket - Database

Opening a session

Connect a WebSocket to /api/db/transaction. The handshake is an ordinary GET, so authentication works exactly as it does for the rest of the API — a browser sends its session cookie automatically, and other clients can send an Authorization header when Basic authentication is enabled.

const ws = new WebSocket("wss://your-server/api/db/transaction");

Four restrictions apply at this point, before a single command has been sent:

The user needs the USE_TRANSACTION database right. It is separate from the rights the individual commands need, and it is not granted by default — see The right to open a session.
Opening sessions is rate limited per client address, to ten a minute. A client that exceeds it gets an HTTP 429 instead of an upgrade, which a WebSocket client sees as a failed connection.
Connections are accepted from the same origin as the server, from any origin the CORS rules cover, and from clients that send no Origin header at all (which is everything that is not a browser). Everything else is refused with 403 before the upgrade — see Which origins may connect.
Only one transaction session at a time exists per server — see Why only one at a time.

Sending commands

Each message is one JSON object. command says what to do and id is anything you like — it is echoed back so you can match a reply to its command.

Parameters are real JSON here. The HTTP endpoints take filters and values as strings containing JSON, because form encoding has nothing else; over a WebSocket they are nested objects.

query — arbitrary SQL

The body may hold several statements separated by semicolons; they all run, and only the last one can return rows. Requires the custom SQL API to be enabled and the EXEC_SQL right.

{"id": 1, "command": "query", "sql": "ALTER TABLE notes ADD COLUMN pinned INTEGER"}
{"id": 2, "command": "query", "sql": "SELECT id, title FROM notes", "limit": 100, "offset": 0, "includeNames": true}

insert

values is either one object or a non-empty array of them. The answer follows the request: an object yields generated_id, an array yields generated_ids — including an array of one. Requires the table data editing API and the CREATE right.

{"id": 3, "command": "insert", "table": "notes", "values": {"title": "First"}}
{"id": 4, "command": "insert", "table": "notes", "values": [{"title": "A"}, {"title": "B"}]}

update

Requires the table data editing API and the UPDATE right.

{"id": 5, "command": "update", "table": "notes",
 "values": {"pinned": 1},
 "filters": {"clauses": ["id="], "args": [7]}}

delete

Requires the table data editing API and the DELETE right. A delete with no filters removes every row of the table.

{"id": 6, "command": "delete", "table": "notes",
 "filters": {"clauses": ["id∈2"], "args": [7, 8]}}

select

Reads a page of rows. Requires the READ right; there is no setting to switch it off, exactly as for GET /api/db/table.

{"id": 7, "command": "select", "table": "notes",
 "columns": "id,title", "limit": 100, "offset": 0,
 "sort": "id", "sortOrder": "desc",
 "includeRowId": false, "includeTotal": true, "rowsAsObjects": false,
 "filters": {"clauses": ["title?"], "args": ["%draft%"]}}

Only table is required. includeTotal adds a total field holding the number of rows matching the filters, ignoring the page window.

Reading commands always have a row limit. GET /api/db/table may return a whole table because it streams the answer; here the rows are collected in memory while the transaction is open, so a missing limit becomes 100 and any larger value is capped at 1000. Page through a large result with offset, or use the ordinary HTTP endpoint when you do not need a transaction.

commit

The only thing that makes the work permanent. The server replies once the commit has actually succeeded, then closes the connection.

{"id": 8, "command": "commit"}

There is no rollback command: simply closing the connection rolls back, as does letting it time out.

Replies

One reply per command, in order, each echoing the id it answers.

{"id": 3, "ok": true, "result": {"generated_id": 42}}
{"id": 4, "ok": true, "result": {"generated_ids": [43, 44]}}
{"id": 5, "ok": true, "result": {"updated_rows": 1}}
{"id": 6, "ok": true, "result": {"deleted_rows": 2}}
{"id": 7, "ok": true, "result": {"total": 9, "data": [[1, "First"]]}}
{"id": 1, "ok": true, "result": null}
{"id": 8, "ok": true, "committed": true}

result is null when a command produced no rows — an ALTER TABLE, or an INSERT run through query.

Errors

{"id": 2, "ok": false, "error": {"kind": "FORBIDDEN", "message": "Forbidden"}}

`kind`	Meaning
`BAD_REQUEST`	The command itself is malformed — unknown name, missing `table`, unparseable `filters`.
`FORBIDDEN`	This user may not perform the operation, by rights or by an access rule.
`DISABLED`	The setting this command needs is switched off in the server configuration.
`NOT_FOUND`	No such table, row or cell.
`FAILED`	The database refused the statement — a constraint violation, a syntax error.

A failed command ends the session. The error reply is sent, the transaction is rolled back and the connection closes. A client that wants to carry on has to reconnect and start again. This is deliberate: it makes a half-applied change impossible.

How a session ends

The close code says why. Codes in the 4000 range are specific to this endpoint.

Code	Meaning	Committed?
`1000`	Normal close. Either the commit succeeded, or the client closed without committing.	Only if you received `"committed": true` first
`4400`	A command was malformed — same situation as an error frame of kind `BAD_REQUEST`.	No
`4403`	Not authenticated, or lacking `USE_TRANSACTION` so the session itself was refused, or a command was refused — `FORBIDDEN` or `DISABLED`. The close reason says which.	No
`4404`	No database is open on the server, or a command found nothing — `NOT_FOUND`.	No
`4408`	Inactivity timeout — no command arrived in time.	No
`4409`	Another transaction session is already in progress.	No
`4410`	The session reached its maximum lifetime.	No
`4500`	The database refused a statement (`FAILED`), or the commit itself failed.	No

The close code alone does not tell a 1000 commit from a 1000 walk-away, so treat the "committed": true reply as the only proof that the work was kept.

When a command ends the session, the close code says the same thing its error frame did — a client that watches only the socket still learns whether its request was wrong, refused, or the database's own objection.

The right to open a session

Opening a transaction needs the USE_TRANSACTION database right, which is not granted by default — grant it in the app under the user's or the role's Database access rights, alongside Read, Create and the rest.

It exists because a session is expensive in a way the ordinary endpoints are not: it takes the database's single writer for its whole life, so anyone able to open one can hold up every other client. That is worth granting deliberately, to the clients that genuinely batch work, rather than falling out of being able to read a table.

It is a separate question from what the session may then do. A user who holds it still needs CREATE to insert, EXEC_SQL to run arbitrary SQL, and so on — the right opens the door, it does not decide what is behind it. A user without it is closed with 4403 at the handshake, before a transaction is started.

Which origins may connect

A WebSocket handshake is not subject to CORS in the browser. There is no preflight, and the browser does not check Access-Control-Allow-Origin before letting the connection through — a page on any origin can open a socket to any server, and the user's cookies for that server go with it. That is the opposite of what happens to an XHR, and it is why this endpoint decides for itself who may connect.

The decision uses the same CORS rules you configure for ordinary requests, enforced server-side. A handshake is upgraded when:

it comes from the same origin as the server, or
it carries no Origin header — command-line clients, scripts and libraries send none, only browsers do, or
its origin is covered by a CORS rule: one naming it exactly, or a * rule.

Anything else is answered with 403 Forbidden instead of an upgrade. The rules are read at each handshake, so adding or removing one takes effect immediately, without restarting the server.

Note the weight a * rule carries here. For ordinary requests a browser refuses to combine Access-Control-Allow-Origin: * with credentials, so a wildcard cannot by itself expose an authenticated endpoint. No such protection exists for a WebSocket. A * rule lets any page open a transaction session, and if the server uses session authentication that page connects as whoever is logged in. Prefer naming the origins you actually serve.

Why only one at a time, and why the timeouts

The server serialises database writes through a single worker. An open transaction holds that worker for its whole life, so while a session is running, every other write — and every read that runs in a transaction of its own, which includes /api/db/schema and /api/db/table — waits behind it.

That is why a transaction session is bounded rather than open-ended:

Setting	Default	What it bounds
`db_transaction_inactivity_timeout_ms`	2000	How long the session may sit without a command before it is rolled back.
`db_transaction_max_lifetime_ms`	5000	How long the session may live in total, however busy the client keeps it.

Both are in milliseconds and are set from the app — the Transaction limits button in the Database section on Android and desktop — or by hand in the server's configuration file. Either way they are clamped to the range 100–60000 ms, so a value outside it never takes effect as written. A change applies to the next session; the server does not need restarting. A second client that connects while a session is open is refused with 4409 rather than left to queue behind it.

The practical advice: keep a session short. Open it, send the statements that belong together, commit, and let it close. It is a tool for making a handful of related changes atomic, not a place to hold a conversation with the user.

One thing the timeouts cannot cut short is a single statement already running — a slow UPDATE over a large table finishes before the deadline takes effect. The deadline is checked between statements.

A worked example

Renaming a table and adding a column, as a single change that either lands whole or not at all:

const ws = new WebSocket("wss://your-server/api/db/transaction");

const replies = [];
ws.onmessage = e => replies.push(JSON.parse(e.data));

ws.onopen = () => {
    ws.send(JSON.stringify({id: 1, command: "query",
        sql: 'ALTER TABLE "notes" RENAME TO "articles"'}));
    ws.send(JSON.stringify({id: 2, command: "query",
        sql: 'ALTER TABLE "articles" ADD COLUMN "pinned" INTEGER DEFAULT 0'}));
    ws.send(JSON.stringify({id: 3, command: "insert", table: "articles",
        values: {title: "Migrated"}}));
    ws.send(JSON.stringify({id: 4, command: "commit"}));
};

ws.onclose = e => {
    const committed = replies.some(r => r.committed);
    console.log(committed ? "all four steps landed" : "nothing changed: " + e.reason);
};

If the second ALTER fails, the rename is undone too, and the table is exactly as it was. Compare that with four separate HTTP requests, where the rename would already be permanent by the time the failure arrives.

Database: transactions over WebSocket