Database SDK

microhost/sql and microhost/db (alias db) give you parameterized SQL fragments, object rows, and table objects generated from your migrations. They run on top of ctx.db: the same database, the same grant, the same broker rules (Context). The SDK adds no permission of its own. Request the modules with sdk: ["db"] (see SDK modules).

Status. Works on the Wasm, native and Linux engines. The behaviour below is taken from the shared module source (sdk-js/runtime/v1/sql.js, db.js). The cross-engine conformance suite does not yet contain database-module scenarios, and the example tests that exercised the module are stale (see Examples). Two defects and a typing mismatch are listed in Known issues.

sql: fragments

import { sql } from "microhost/sql";

const byState = sql`state = ${"open"}`;
const q = sql`SELECT id, title FROM tasks WHERE ${byState} AND id IN (${[1, 2, 3]}) ORDER BY id`;
// q.text       = "SELECT id, title FROM tasks WHERE state = ? AND id IN (?, ?, ?) ORDER BY id"
// q.parameters = ["open", 1, 2, 3]

• Each interpolated value becomes a ? placeholder and a bound parameter. Nothing is ever spliced into the SQL text.
• An interpolated fragment is inlined with its parameters, so fragments compose.
• A non-empty array expands to ?, ?, .... An empty array throws a TypeError (empty SQL parameter arrays are not supported) instead of producing invalid SQL.
• There is no sql.raw. Fragments are frozen.
• Parameter types are those of ctx.db (null, boolean, number, bigint, string, Uint8Array, ArrayBuffer).

db: running fragments

import { all, one, run, tx, id, now } from "microhost/db";

These entry points accept fragments only. A plain string throws TypeError with code db_use_sql_template. Duplicate column names in a result (for example SELECT a.id, b.id) throw db_duplicate_column: alias them (SELECT a.id AS a_id, b.id AS b_id). If ctx.db is absent or revoked, every call rejects with capability_denied.

const open = await all(sql`SELECT id, title FROM tasks WHERE state = ${"open"} ORDER BY id`);
const task = await one(sql`SELECT * FROM tasks WHERE id = ${taskId}`);
await run(sql`UPDATE tasks SET state = 'done' WHERE id = ${taskId}`);
await tx([
  sql`INSERT INTO audit (at, what) VALUES (${now()}, 'close')`,
  sql`UPDATE tasks SET state = 'done' WHERE id = ${taskId}`,
]);

all and one read through query, which the broker refuses for statements that modify data. For INSERT ... RETURNING use a table object (below) or raw ctx.db.execute.

Table objects

Every user table created by your migrations becomes a table object:

import { tasks, tables } from "microhost/db";   // tasks === tables.tasks

• A table is a named export when its name is a valid ASCII JavaScript identifier (and not a reserved word). Every table, valid identifier or not, is also in the frozen tables map (tables["my-table"]).
• Table objects exist only for a release that has a sealed schema snapshot (Migrations). A table created outside migrations is not importable. Without a snapshot there are no table exports (only the functions above and an empty tables).
• Each table has name, columns (frozen list of column names) and primaryKey (the primary key column name, or null). For a composite primary key only the first key column is used as primaryKey; avoid composite keys for tables you use through these methods, or address rows with filters.

Rules:

• Filters are objects of column names to values, combined with AND: a value means equality, an array means IN (...), null means IS NULL. An unknown column throws db_no_such_column. An empty array throws db_use_sql_template. A value of undefined is bound as NULL and matches nothing, so guard against it. There are no range or LIKE filters: use sql with all for those.
• insert, update and delete check column names against the snapshot (db_no_such_column). insert({}) and update(..., {}) are refused.
• update and delete need a primary key value or a non-empty filter. Otherwise they throw db_where_required, so you cannot update or delete a whole table by accident.
• Identifiers are always quoted from the snapshot and values are always bound.
• The SQLite broker stays the authority: a constraint violation (NOT NULL, UNIQUE, CHECK) surfaces as a rejected call.

import { tasks } from "microhost/db";

const created = await tasks.insert({ title: "Write docs", state: "open" });   // note defaults to NULL
const open = await tasks.where({ state: "open" }).orderBy("id").limit(50).all();
await tasks.update(created.id, { state: "done" });
const one = await tasks.find({ title: "Write docs" });
const n = await tasks.count({ note: null });
await tasks.delete({ state: "done", note: null });

Queries and paging

table.where(filter?) returns a lazy query. Nothing runs until all() or iteration.

const rows = await posts.where({ status: "published" })
  .orderBy("published_at", "desc")
  .limit(20)
  .all();

• orderBy(key, direction = "asc"): key is a column name (checked against the snapshot, db_no_such_column) or a SQL fragment (for example sql`lower(title)` ). direction is "asc" or "desc", otherwise db_invalid_direction. Call it repeatedly for secondary keys. An expression key is selected as a hidden column and removed from the returned rows.
• limit(count = 100): an integer from 1; values above 1000 are clamped to 1000. A non-integer or less than 1 throws RangeError.
• offset(count): a non-negative integer, applied to the first page only.
• all() resolves with Rows: a normal array of up to limit row objects plus
  • rows.hasMore: true when more rows exist beyond this page,
  • rows.first(): the first row or null,
  • rows.next(): a promise of the next page, as another Rows.
• Paging is keyset based, not offset based: next() repeats the query with a predicate on the sort keys, using the primary key as a final tiebreak in the direction of the last key. This needs a table with a primary key; without one next() rejects with db_where_required. Call next() only when hasMore is true (otherwise it rejects with db_where_required).
• Always call orderBy before paging. Without orderBy the first page has no ORDER BY. Keyset predicates also do not work across NULL sort values, so page over NOT NULL columns.
• for await (const row of query) yields every matching row, reading pages of 100 (it ignores limit). Iteration needs a primary key from the second page on.
• Each page is a separate ctx.db.query call. Every page re-checks the database grant (so a revocation shows up as capability_denied between pages), counts as a host call (64 per execution by default) and counts its rows against the row budget. A paging loop over a large table can exhaust the host-call budget; page on the client side instead of draining a table inside one request. Pages are not a snapshot: concurrent writes can add or skip rows between calls.

const page1 = await posts.where().orderBy("id", "desc").limit(20).all();
const page2 = page1.hasMore ? await page1.next() : null;

A full pagination guide (cursor in the URL) is in Guides.

Error codes

SDK errors carry a stable, enumerable code. Messages are the code text. db_use_sql_template, db_duplicate_column and others are thrown as Error or TypeError; match on error.code, never the message.

Broker errors from the underlying calls keep their usual codes (QUOTA_EXCEEDED, INVALID_INPUT, HOST_UNAVAILABLE, ...). All codes are summarized in Limits and errors. Operators see the stable code in diagnostics; visitors see only a sanitized HTTP status.

Types

sdk/microhost.d.ts declares sql, the microhost/db functions, DbTable, DbQuery, DbRows and DbFilter generically. Column-level types need a per-release declaration file shaped like sdk/db.d.ts (column types, nullability, CHECK (col IN (...)) string unions).

Status. No tool in this repository generates db.d.ts from a release: sdk/db.d.ts is a hand-written example of the intended shape, and sdk/tests/db-types.ts is a type-check fixture for it. Write the declaration for your tables yourself if you want typed rows.

Known issues

These are defects in the current shared module source, documented so you can avoid them.

1. stmt.insert and stmt.insertOrIgnore used to quote the table name twice. Fixed on 2026-10-25 and covered by a regression test in examples/tasks/test.mjs.
2. Result type of all, update, delete. These return plain arrays at runtime, and microhost.d.ts now types them as T[]. Only table.where(...).all() returns the paged Rows (first, hasMore, next).
3. sql empty-array error has no code. The TypeError for an empty array does not carry db_use_sql_template.