Databases

ctx.db runs SQL from an automation. The engine is node:sqlite, the SQLite driver built into Node itself, so there is no new dependency and no native build. An automation never holds a connection string or a file path: it asks for a named connection and the Robot resolves it (framework/db-types.ts).

#The engine

node:sqlite sits behind the facade as a swappable driver. A provider this engine cannot serve is refused by name, DB_PROVIDER_UNSUPPORTED, listing what this build ships. SQL Server, Oracle, MySQL and PostgreSQL drivers are addable behind the same facade. None is built here, so the message says so rather than failing with a raw driver error.

The driver is loaded lazily, so its absence is a named refusal, DB_DRIVER_UNAVAILABLE, instead of a crash that takes the runner down. The driver is present from Node 22.13 and 23.4 onward; on an older runtime the same automation diagnoses the gap and names the runtime it is on.

SQLite vendor spellings seen in real projects fold to the one driver: System.Data.SQLite, Microsoft.Data.Sqlite, Microsoft.Data.Sqlite.Core and sqlite3 all resolve to sqlite.

#Connections are named

An automation declares its connections in its own definition and the environment can supply one too:

export default defineAutomation({
  id: 'ledger-sync',
  capabilities: ['database'],
  db: { connections: [{ name: 'ledger', provider: 'sqlite', file: 'ledger.sqlite' }] },
  // ...
});

A relative file resolves under the Robot's data directory, the same jail ctx.files uses. An absolute path, or a .. traversal, is refused DB_PATH_REJECTED unless the declaration sets allowAbsolutePath, which is a reviewed line of source rather than an environment variable. The environment route is TS_DB_<NAME>_PROVIDER, _FILE, _CONNECTION_STRING, _BUSY_TIMEOUT_MS and _READ_ONLY. Those two routes are the only ones. config/ is never read.

#The verbs

ctx.db is a DbFacade:

• open(name) and close(name).
• query(name, sql, params) returns every row as an object keyed by result column.
• execute(name, sql, params) returns { changes, lastInsertRowid }.
• scalar(name, sql, params) returns the first column of the first row, or null.
• begin, commit and rollback for explicit transaction control.
• transaction(name, fn) runs fn inside a transaction.

params is a required argument of every statement verb, so concatenated SQL is a shape the compiler refuses rather than the easy path. A positional array binds ? and an object binds :name. A value the driver cannot bind is refused by name: undefined is rejected with its position and a boolean is normalised to 1 or 0, because SQLite has no boolean type.

transaction runs BEGIN, calls fn with a transaction object carrying the same query, execute and scalar, then COMMITs. On a throw it rolls back and rethrows the original error, never a rollback error masking the cause. Nesting is refused with DB_TRANSACTION_ALREADY_OPEN.

#How rows are read back

The read-back is the proof. A row comes back as an object keyed by column name and a scalar comes back as the bare value:

const rows = await ctx.db.query('ledger', 'SELECT id, sku, qty FROM items ORDER BY id', []);
ctx.log.info(`read ${rows.length} rows: ${JSON.stringify(rows)}`);

const count = await ctx.db.scalar('ledger', 'SELECT COUNT(*) FROM items', []);
ctx.log.info(`count = ${String(count)}`);

DB_BUSY is the one retryable database failure and it means another writer holds the lock. Every other failure is a statement or schema problem and is not retried, because retrying it would only spend the retry budget.

#What state it is in

The first capability audit measured database as MISSING: a legal capability token with no engine and no run-time gate. The slice that built the engine closed that row, moving it from MISSING to WORKING when the facade was exercised against a real SQLite file: a committed write, a rolled-back write and four named refusals. A later slice then ran the same capability through a deployed .tspkg, building, installing and running it on the installed artifact, with the rows read back through a second independent handle.

#Things that catch people out

Declare 'database'. An undeclared call throws DB_NOT_DECLARED and a declared call with no engine throws DB_UNAVAILABLE.

scalar returns the first column of the first row, not the whole row. It returns null only when there is no row.

The connection's provider must be one this build serves. A provider with no driver reaches DB_PROVIDER_UNSUPPORTED at open time, never a silent no-op.