runtime:db

Databases, in two tiers. The application tier is connect() and what it returns. The driver tier is what a third party needs to add a backend of their own. Both are exported from runtime:db; the split is in the documentation, not the specifier.

Import

import { connect, sqlite, sql } from "runtime:db";

connect(url, options)

Opens a connection with the driver you pass. sqlite is built in; every other driver is a package export.

const db  = await connect("sqlite:./app.db", { driver: sqlite });
const mem = await connect("sqlite::memory:", { driver: sqlite });
const enc = await connect("sqlite:./secret.db", { driver: sqlite, key });

// Every driver package exports its driver as `driver`, and nothing as a
// default. Two of them in one module are told apart with `as`.
import { driver as postgres } from "@opentf/esrun-postgres";
const pg = await connect("postgres://user@host/app", { driver: postgres });

Everything else in the options object belongs to the driver — key, cipher and readOnly for sqlite, a host and TLS settings for a networked one.

What comes back is that driver's connection, so a driver's own surface is on the object connect returned and needs no second entry point: Redis's commands, PostgreSQL's LISTEN. The URL's scheme is checked against the driver's, so a driver pointed at a URL it does not take is refused at the call.

pool

const pool = await connect("postgres://host/app", { driver: postgres, pool: { max: 20 } });
await pool.query("SELECT 1");            // borrows and returns a connection
await pool.transaction(async (tx) => …); // holds one for the whole of it
await pool.withConnection(async (c) => …);  // for anything stateful across calls

A pooled connection answers the same surface a single one does, plus size, idle and pending. A connection is returned to the pool only if the driver says it is fit for the next caller (reusable), and destroyed otherwise.

sqlite: names a file format and a SQL dialect the way postgres:// names a wire protocol — not a particular implementation, which may be replaced without the URL changing.

sqlite::memory: opens a database that exists only in memory, and each connection gets its own. The named form (:memory:name), which in SQLite means sharing one, is refused rather than quietly not sharing.

Connection

Every one of these is on a single connection and on a pooled one, so code that holds "a connection" never has to ask which kind it has.

q is SQL text, a sql `` template, or a QueryAst. params is an array (bound by position) or an object (bound by name).

Which forms a backend takes is its own declaration — dialect.supports.queryText and supports.queryAst — and the form it does not take is refused with ERR_DB_QUERY_FORM, in either direction. sqlite: and postgres: take SQL; redis: takes command arrays and refuses SQL.

supports.transactions is the matching declaration for transaction(fn): a backend that says false refuses with ERR_DB_UNSUPPORTED rather than emitting a BEGIN it has never heard of, and its executeMany is not atomic, because there is no transaction to wrap it in.

Cancelling

await db.query(sql, params, { signal: AbortSignal.timeout(5_000) });

Aborting asks the backend to cancel and waits for it to answer, so the connection is left in a known state and stays usable — which is the difference between cancelling and hanging up. The rejection carries the signal's own reason rather than the backend's word for a cancelled statement, including when a streaming result is abandoned halfway.

How much can actually be cancelled is a backend question, and the interface does not pretend otherwise: sqlite: interrupts a running statement, postgres: sends a cancel on a second connection, and a backend that implements neither still rejects the caller while the work runs to completion.

await db.execute("INSERT INTO users (name, age) VALUES (?, ?)", ["ada", 36]);
await db.execute("INSERT INTO users (name) VALUES (:name)", { name: "grace" });

executeMany(sql, rows)

One statement, many parameter sets, one crossing and one prepare.

await db.executeMany("INSERT INTO users (name, email) VALUES (?, ?)", [
  ["Ada", "ada@example.com"],
  ["Grace", "grace@example.com"],
]);

The reason to reach for it is arithmetic rather than taste: a crossing costs about the same whatever it carries, so a loop that crosses once per row spends its time on the boundary instead of in the database.

A sql `` template with values describes a single row, so it is refused here rather than silently running the first row's values for every row.

results carries one result per parameter set where the backend reports them — which the default batch path always does, since it ran the sets one at a time. It is absent when the backend answered for the batch as a whole, so check before reading it. Without it, a batch of inserts against a backend that generates keys is a batch whose keys are unreachable: the aggregate can only carry the last.

subscribe(channels, handler?)

Server-pushed messages: PostgreSQL's LISTEN/NOTIFY, Redis's pub/sub, a change stream elsewhere.

await conn.subscribe("orders", (payload, { channel }) => console.log(channel, payload));
conn.onMessage = (payload, context) => …;      // the catch-all
conn.onSubscribeError = (error) => …;          // the delivery loop failed
await conn.unsubscribe("orders");              // omit the name for all of them

It resolves once the backend has confirmed the subscription, so publishing immediately afterwards cannot race it. Subscribing gives the connection over to delivering messages, and on most backends it then refuses ordinary work with ERR_DB_CONNECTION_BUSY — which is the protocol's own rule as often as it is the driver's, and how you would deploy it anyway.

supports.subscriptions is the declaration; a backend without them refuses by name. A pooled connection refuses too: a subscription needs a connection that does not come back, which is the opposite of a pool's premise.

transaction(fn)

Commits when fn returns and rolls back when it throws. Nested calls become savepoints where the backend has them, so a helper that opens a transaction composes with a caller that already did instead of committing the outer one early. A rollback that itself fails never replaces the error that caused it.

await db.transaction(async (tx) => {
  await tx.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100, from]);
  await tx.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100, to]);
});

sql ``

Every interpolation becomes a parameter, never text. A nested fragment splices with its own values, so queries compose without either half knowing the other's placeholder numbering.

const active = sql`AND status = ${"active"}`;
await db.query(sql`SELECT * FROM users WHERE id > ${10} ${active}`);

Fragments and values stay apart until the backend renders them, so one template targets $1, ? and :name backends unchanged — and there is no arrangement of it that puts a value into the text.

Rows

Async-iterable, pulled one batch at a time — never the whole result, so a table larger than memory streams through at the cost of a batch.

Stopping early — break, return, a throw — closes the cursor on the way out and leaves the connection usable.

A result small enough to fit one batch comes back with the query itself, so no cursor is opened and the whole query costs one crossing instead of three.

Row

A lazy view over its batch, with one getter per column, so a query that selects more columns than it reads pays only for the ones it touches. A 64-bit integer arrives as a bigint only where a number would have lost it; a blob arrives as a Uint8Array.

What a column produces is the backend's decision. DbOutput — the union above — is what sqlite produces, and Row<V> is generic so a driver can say what it produces instead: @opentf/esrun-postgres decodes timestamptz into a Temporal.Instant and jsonb into the document, and types its rows accordingly. Held through the portable Connection type, a row's values are unknown, because an unknown backend decodes what it likes.

Errors

A failure is a DbError whose code is layered: the driver's own classification first, then a stable host code, then ERR_DB_BACKEND. A denied capability stays ERR_CAPABILITY_DENIED and a jail escape stays ERR_JAIL_ESCAPE — an application testing for those should not have to know the call went through a database. The backend's own code, where it had one, stays on e.backendCode.

import { DbErrorCode } from "runtime:db";

try {
  await db.execute("INSERT INTO users (email) VALUES (?)", [email]);
} catch (e) {
  if (e.code === DbErrorCode.UniqueViolation) return { taken: true };
  throw e;
}

The driver tier

For adding a backend or building an ORM.

import { defineDriver, BaseConnection, Dialect } from "runtime:db";

const dialect = new Dialect({ name: "mydb", placeholder: (i) => `$${i}` });

class MyConnection extends BaseConnection {
  constructor(socket) {
    super({ dialect, backend: "mydb" });
    this.socket = socket;
  }
  async _query({ text, positional }) { /* … */ }
  async _execute({ text, positional }) { /* … */ }
  async _close() { /* … */ }

  // Optional. The default loops `_execute` — correct, and no faster than the
  // loop it replaces. Override it with whatever the backend does in one round
  // trip: a reused prepared statement, or pipelined protocol messages.
  async _executeMany(query, sets) { /* … */ }
}

export const driver = defineDriver({
  name: "mydb",
  schemes: ["mydb"],
  dialect,
  open: async (url, options) => new MyConnection(await open(url, options)),
});

Nothing is registered, and nothing has to be imported for its side effects: your package's export is the driver, and connect(url, { driver }) is how a caller reaches it. Export it as a named driver and nothing as a default, as the first-party packages do — one import shape for every backend, and a caller with two of them tells them apart with as.

If your rows are not bytes

The batch layout exists because a wire protocol hands over bytes and decoding them lazily is worth the machinery. A backend that never had bytes — a document store answering JSON, a graph or vector service over HTTP, an in-process engine holding objects — hands over records instead, and nothing downstream can tell the difference:

async _query(q) {
  const docs = await this.find(q.ast);       // already objects
  return Rows.fromObjects(docs);             // columns default to their keys
}

For a driver that wants control of batching, a RowSource may answer { records, done } — each record an array of values in column order, paired with defineRecordShape(columns) — instead of { bytes, rows, done }. Batch either way: a caller who breaks out of the loop after three rows should not have paid for three thousand.

A backend that does not speak SQL declares it, and inherits the rest unchanged:

const dialect = new Dialect({
  name: "mydb",
  placeholder: () => { throw new DbError("no placeholders here", { code: DbErrorCode.QueryForm }); },
  supports: { queryText: false, queryAst: true, transactions: false },
});

supports also carries your own capabilities — a vector index, a full-text mode, a graph traversal. They survive on dialect.supports untouched, and they are how an ORM branches on a backend that did not exist when it was written. driver.dialect.supports answers before a connection is opened.

Most conformance checks are written in SQL. Against such a backend they are skipped with a reason rather than failed — a check you cannot express is not a finding — and what runs is the part that holds for every backend.

Adding a networked backend needs no new runtime code — a driver is JS over runtime:net. See Databases for the guide and Internals for why it is built this way.