Data: SQLite with roots/data

Where the data lives #

<app> is :name from roots.lisp; <name> is what you pass to open(). Every connection has foreign keys on (PRAGMA foreign_keys = ON), does not trust the schema of the file (PRAGMA trusted_schema = OFF) and has a 5 s busy timeout.

On the terminal, a database file or data directory that already exists with looser modes (made by an older version or another tool) and belongs to the user is tightened to 0600 / 0700 when it is opened. On phones the file used to be <name> without an extension (up to 0.2.1); the shells rename such a file the first time it is opened.

API reference #

open(name, options?) → Promise<Database> #

• name: letters, digits, _ and -, at most 64 characters, starting with a letter or digit (D101). It is a name, not a path: where the file lives is the target's business.
• options.memory: an in-memory database (gone when the app closes; on phones and in the browser it uses the WASM build).
• options.driver: a DriverFactory of your own (an adapter for another engine or plugin; see Drivers).
• options.foreignKeys: false turns SQLite's foreign key enforcement off.
• options.trustedSchema: true lets views, triggers, DEFAULTs, CHECKs and generated columns call functions and virtual tables that SQLite does not mark as innocuous. The default is PRAGMA trusted_schema = OFF: a database file someone else produced cannot make the app run such code just by being read. SQLite's ordinary built-in functions, FTS5 and R-Tree are marked innocuous and work either way.
• options.attach: true allows ATTACH, DETACH and VACUUM INTO (D117 otherwise). A database opened with roots/data stays in its own file: on the terminal ATTACH reads or creates any file the user can, so SQL built from a string must not be able to reach it. The native shells' sqlite plugin refuses these statements whatever the option says.
• options.memoryFallback: false: in the browser, fail with D154 where the database cannot be persisted (no OPFS: private browsing, an insecure context, an old browser) instead of keeping it in memory. The default (true) opens it in memory, warns D151 and sets db.persistent to false and db.reason to why: check them before the user types something they expect to keep. A second tab is no such case: it shares the first tab's database (see One database for all tabs).

const db = await open("shop")
if (!db.persistent) showBanner(db.reason === "no-opfs"
  ? "This browser window does not keep data (private browsing?)."
  : "Another tab of an older version of this app is open: close it and reload.")

Opening a name that is already open returns the same Database (one connection per name; not when you pass your own driver).

Database #

Parameters are always bound: ? placeholders and an array. Values are strings, finite numbers, booleans (stored as 1 and 0), null and Uint8Array (BLOB). undefined, NaN, Infinity, Date, objects and bigints outside the safe range are refused with D112 instead of being coerced; named parameters are not supported. The array must have exactly as many values as the statement has ? placeholders (D113): a missing value is never bound as NULL. run, all, get, live and query take one statement (D114): a second one after a ; is refused, not dropped; scripts go through exec(). SQL text with a NUL character in it is refused as well (D111): SQLite stops reading there, so what follows would not run. There is no string-interpolation helper on purpose.

Rows come back as plain objects, one own property per column (a column named __proto__ included). An INTEGER outside JavaScript's safe range (beyond ±(2^53 − 1)) is returned as its decimal string, never as a rounded number and never as an error: two different stored values cannot read as the same one. This holds for node:sqlite, bun:sqlite, the WASM build and the native bridge; the Capacitor plugin hands out rounded numbers. lastInsertRowid is always a number. To store such values on purpose, use TEXT.

Statements on one database run one after another (a promise queue), in the order they were called, so an app never sees interleaved partial work. Each driver keeps a cache of prepared statements (100 per connection, least recently used evicted), cleared when the schema changes.

Transactions #

const id = await db.transaction(async (tx) => {
  const r = await tx.run("INSERT INTO orders (total) VALUES (?)", [total])
  for (const line of lines) await tx.run("INSERT INTO lines (order_id, sku) VALUES (?, ?)", [r.lastInsertRowid, line.sku])
  await tx.transaction(async (inner) => { … })   // a SAVEPOINT: its failure rolls back only itself
  return r.lastInsertRowid
})

• BEGIN IMMEDIATE … COMMIT when the callback's promise resolves; ROLLBACK when it rejects, and the error is rethrown. Nested calls on tx are savepoints.
• While a transaction runs it holds the database's queue: calls on db wait until it finishes. Inside the callback use tx: a db call there would wait for the transaction itself; roots/data warns D122 after 2 s.
• Using tx after the callback finished is D121 (await every call inside it).
• Let an error of a tx statement reject the callback. A statement written INSERT OR ROLLBACK …, or a trigger that does RAISE(ROLLBACK, …), has SQLite end the whole transaction when it fails: a callback that catches that error and goes on writes in autocommit, those writes stay, and the call then fails at COMMIT ("no transaction is active").

Live queries #

const notes = db.live<NotesRow>("SELECT * FROM notes WHERE pinned = ? ORDER BY id DESC", [1])
notes.value      // the rows (a ReadonlyRef: effects, computeds and templates track it)
notes.loading    // true until the first result
notes.error      // the last error (also reported to the console as D140), else null
await notes.ready          // resolves after the first run
notes.tables     // the tables it reads: ["notes"]
notes.refresh(); notes.stop()

• A live query re-runs only when a table it reads is written through this database (in the browser: by any tab of the app), and its ref changes only when the rows really changed.
• A transaction reports its writes once, when it commits; a rolled back transaction reports nothing. A write that changed no row reports nothing, unless a trigger could have written on its behalf (see below).
• A statement that fails is reported too (as the tables its plan writes; a failing exec() script as "any table"): INSERT OR FAIL keeps the rows before the conflict, and the statements of a script before the failing one have run.
• Writing .value throws D143.

Parameters that change: db.live(() => [sql, params]). Give live a function instead of the SQL. It is tracked like a computed: when a ref (or a prop, a route parameter…) it read changes, the query runs again with the new text and parameters. The rows of before stay until the new result arrives, so a list does not flash empty while the user types; loading is true in between. If the function is called again before a result arrived, that older result is dropped: the rows always belong to the latest query, or to the one shown before it.

const search = ref("")
const page = ref(0)
const found = db.live<NotesRow>(() => [
  "SELECT * FROM notes WHERE title LIKE ? ORDER BY id DESC LIMIT 20 OFFSET ?",
  [`%${search.value}%`, page.value * 20],
])
// <Input v-model="search" />  <p v-if="found.loading">…</p>
// <NoteItem v-for="note in found" :key="note.id" :note="note" />

The function returns [sql, params], [sql] or just the SQL. It may return null (or undefined) for "no query now": the rows are then empty at once, loading is false, and nothing runs until it returns a query again.

const order = db.live<OrdersRow>(() => orderId.value == null ? null : ["SELECT * FROM orders WHERE id = ?", [orderId.value]])

The table helper takes a function the same way: notes.live(() => ({ pinned: onlyPinned.value ? 1 : 0 }), { orderBy: "id" }). What the function returns is checked like the arguments of db.live(sql, params); a wrong value, or a function that throws, is the query's error (below), not an exception.

Where a live query lives, and when it stops. A live query made while an effect scope is current stops with that scope: in a component's setup that is when the component unmounts; inside effectScope().run(() => …) when the scope stops. One made anywhere else — at module level, in an event handler, after an await in setup (the scope is no longer current there) — lives until you call stop() or the database closes. So a query for the whole app can simply sit in a module, and a query made in a handler needs its stop():

// state/notes.ts: made once, lives as long as the app
export const pinned = db.live<NotesRow>("SELECT * FROM notes WHERE pinned = 1")

// a component: stops when the component unmounts
const mine = db.live<NotesRow>(() => ["SELECT * FROM notes WHERE author = ?", [props.author]])

// made in a handler: stop it yourself
function preview(id: number) { const q = db.live("SELECT * FROM notes WHERE id = ?", [id]); /* … */ q.stop() }

A failed live query is not an empty result. When the query fails (a misspelt table, a wrong number of values, a function that throws) error is set, the rows of before stay (none, if it never ran) and [D140] is logged with console.error, with SQLite's message and the query. A page that only renders the rows therefore shows "no data" for a query that failed. Render error next to the rows:

<p v-if="cart.error" role="alert">The cart could not be read: {{ cart.error.message }}</p>
<p v-else-if="!cart.loading && cart.length === 0">Your cart is empty.</p>
<CartLine v-for="line in cart" :key="line.id" :line="line" />

In development the D140 report says so itself when nothing has ever read the query's .error: Nothing has read this query's .error: its rows are empty, so the page shows "no data" where it should show a failure. The next successful run clears error.

await db.settled(): the live queries show my write. The promise of db.run(), table.update() or db.transaction() resolves when the write is done; the live queries re-run after that, a moment later. db.settled() resolves when every statement called so far has run and no live query is running or waiting to run again, so after it the rows of every live query include the write:

await notes.update({ id }, { title })
await db.settled()                 // notes.value (and every other live query) now has the new title

It runs nothing itself and resolves at once when there is nothing to wait for. Do not await it inside db.transaction(async (tx) => …): it would wait for the transaction. It is about this tab's statements: a write another tab makes right now arrives when it arrives (see One database for all tabs).

Recipe: an optimistic edit. Show the new value at once, write, and drop the overlay only when the live rows have caught up (dropping it when the write resolves shows the old value for a few frames):

const stock = db.live<BooksRow>("SELECT id, stock FROM books ORDER BY id")
const patch = ref<Record<number, number>>({})                        // id → the value being saved
const shown = computed(() => stock.value.map((b) => b.id in patch.value ? { ...b, stock: patch.value[b.id] } : b))

async function setStock(id: number, value: number) {
  patch.value = { ...patch.value, [id]: value }                      // 1. show it
  try {
    await db.table<BooksRow>("books").update({ id }, { stock: value })   // 2. write it
    await db.settled()                                                // 3. the live rows have it
  } finally {
    const { [id]: _, ...rest } = patch.value; patch.value = rest      // 4. drop the overlay (also on failure:
  }                                                                   //    the old value shows again, the error propagates)
}

How invalidation works. roots/data does not guess table names from SQL text. It asks the engine: EXPLAIN <sql> (run with NULL for every placeholder) lists the statement's bytecode, whose cursors name b-tree root pages (OpenRead/OpenWrite p2, database p3; Clear for DELETE without WHERE), and sqlite_master maps root pages to tables (indexes map to their table). Views, CTEs, subqueries and joins are therefore exact, and it works through every driver because it is just another query. The plan is cached per SQL text and recomputed when the schema changes. What a plan cannot pin down is reported as "any table" ("*"): trigger programs and foreign key actions (Program), virtual tables, scripts run with exec(), schema changes. Where the driver offers an update hook (the WASM build, Bun), the tables it saw replace that "*", but only when the hook accounted for every changed row (total_changes() before and after; SQLite skips the hook for the truncate optimization and WITHOUT ROWID tables). Three cases need more than the plan and the row count:

• sqlite3_changes() is 0 for an INSERT into a view with an INSTEAD OF trigger and for a statement whose trigger ends in RAISE(IGNORE), although the trigger wrote rows. A statement whose plan has a trigger program is therefore reported even when it "changed no row", unless the update hook saw everything and saw nothing.
• After every exec(), successful or not, the schema versions of main and temp are compared (PRAGMA schema_version covers main only, and a temp trigger changes what a write to a main table does); a change forgets every plan.
• A failing write is reported like a successful one (above).

Invalidation is conservative: it may re-run a query for nothing, it never misses a write made through roots/data, in this tab or (in the browser) another one. Writes made by other connections to a file (another process, roots db query) are not seen; reopen or refresh().

The table helper #

import type { NotesRow, NotesInsert } from "./db-types"     // generated: roots db types
const notes = db.table<NotesRow, NotesInsert>("notes")
await notes.insert({ title: "Hello", created_at: now })   // INSERT INTO "notes" ("title", "created_at") VALUES (?, ?)
await notes.update({ id: 3 }, { pinned: 1 })              // where first, then the values
await notes.delete({ id: 3 })
await notes.find({ pinned: 1 }, { orderBy: "id", desc: true, limit: 20 })
await notes.get({ id: 3 }); await notes.count({ pinned: 0 })
const pinnedNotes = notes.live({ pinned: 1 }, { orderBy: "id" })
const shown = notes.live(() => ({ pinned: onlyPinned.value ? 1 : 0 }))   // follows the ref, see Live queries

where is an object of column → value: null means IS NULL, an array means IN (…). undefined is refused (D115): tokens.get({ token: request.token }) with a missing token must not find the rows whose token is NULL. Table and column names must be plain identifiers ([A-Za-z_][A-Za-z0-9_]*, D115) and are double-quoted; values are always bound. update and delete need a non-empty where (D116): to change every row, write the SQL. A where or values object taken straight from untrusted JSON still lets its sender pick any column of the table and say null or an array: build the object yourself.

Errors #

Every error is a DataError (a RootsError): code is the D-code, sqlite the SQLite result code name when the engine reported one ("SQLITE_CONSTRAINT_UNIQUE"), sql the statement. The message names both and shows the statement on one line:

[D110] SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed: users.email
  in: INSERT INTO users (email) VALUES (?)

Migrations #

Migrations are SQL files in the project's migrations/ folder:

migrations/
  001_create_notes.sql
  002_add_pinned.sql

• The name is NNN_description.sql: a positive number (the version; leading zeros are only for sorting), _, then letters, digits, _ or - (D160). Two files with the same version are D161. Other files in the folder are ignored.
• The build turns them into the module that roots/data exports as migrations ({ version, name, sql }[]); db.migrate() without an argument uses it. A list of your own works the same way.
• Each migration runs once, in version order, in its own transaction together with its row in _roots_migrations (version, name, checksum, applied_at). A failing migration is rolled back and stops the run (D135); the ones before it stay applied.
• A migration must not contain BEGIN, COMMIT, END or ROLLBACK (ROLLBACK TO a savepoint of its own is fine): it would end the transaction it runs in, and the migration would be applied without being recorded, or recorded while reported as rolled back. Such a migration is refused before anything is applied (D136), by the app and by roots db.
• Migrations are code. db.migrate() and roots db migrate | types execute the SQL in migrations/; review a migration in a pull request like any other code. What that SQL cannot do: ATTACH another file (D117 in the app; in roots db the connection has no ATTACH at all, so roots db types and migrate --check really change no file).
• The checksum is the SHA-256 of the SQL with \n line endings (the toolchain and the runtime compute the same digest). Before anything is applied, every applied migration must still exist with the same checksum: an edited applied migration is refused (D132), and so is a database that has a migration the app does not know (D133: a newer version of the app ran here). A new migration must be numbered above the newest applied one (D134).
• Running migrate() again applies nothing. Never edit a migration that was applied somewhere: add a new one.
• While the first schema is still taking shape you will edit 001_….sql anyway, and meet D132 on the next start. When the data does not matter, start the database again: roots db reset for the terminal target's file (it deletes the database and applies migrations/ to a new one); in the browser, clear the site's data (developer tools: Application, Storage, "Clear site data") and reload. In development the D132 message says which of the two applies and where the database is; a production build's message does not suggest deleting anything.
• What the checksums are for: they detect an edited migration file against an honest database. They are stored in the database they describe, so whoever can write the database can rewrite them (or delete a row, which makes that migration run again): they are no protection against a tampered database.

Types from the schema #

roots db types            # applies migrations/ to an in-memory database → src/db-types.ts

For each table, <Name>Row (what queries return) and <Name>Insert (what insert() needs: columns with a default, NULL allowed or the INTEGER PRIMARY KEY are optional); for each view, <Name>Row; and Tables, every row type by name. Declared column types map with SQLite's affinity rules: INT → number; CHAR, CLOB, TEXT → string; BLOB → Uint8Array; REAL, FLOA, DOUB → number; no type → SqlValue; anything else (NUMERIC) → number, except names with DATE, TIME or JSON → string. Nullable columns get | null. Commit the file; the test suite checks that the example's is current.

Views. SQLite records no NOT NULL for a view's columns, so roots db types works it out: a column that the view reads straight from a NOT NULL column (or the rowid) through inner joins is not nullable. Everything else keeps | null: a nullable column, the optional side of a LEFT, RIGHT or FULL JOIN, an expression (a || b, coalesce(…), count(*)), a GROUP BY, UNION, a subquery SQLite does not merge into the view, a table with generated columns. The answer comes from the engine's plan for SELECT * FROM <view> (EXPLAIN), not from reading the SQL text, and every doubt is decided for | null: a type never promises a value that can be missing.

CREATE VIEW book_list AS
  SELECT b.id, b.title, b.subtitle, a.name AS author, s.name AS series
  FROM books b JOIN authors a ON a.id = b.author_id LEFT JOIN series s ON s.id = b.series_id;

export interface BookListRow {
  id: number              // the rowid
  title: string           // books.title is NOT NULL
  subtitle: string | null // nullable in books
  author: string          // NOT NULL, through an inner join
  series: string | null   // NOT NULL in series, but the join is a LEFT JOIN
}

Table and column names are any text SQLite accepts: in the generated file they appear as quoted property keys and, in comments, as JSON strings with */ broken up, so a name cannot put code into db-types.ts.

The toolchain: roots db #

roots db migrate [dir] [--db name|file] [--check]
roots db types [dir] [--out src/db-types.ts]
roots db query "<sql>" [dir] [--db name|file] [--json]
roots db reset [dir] [--db name|file]

They work on the database the terminal target uses ($XDG_DATA_HOME/<app>/<app>.sqlite3; --db notes2 names another database, --db ./x.sqlite3 a file), so roots db query shows what the terminal app wrote and roots db migrate migrates it with exactly the app's rules and checksums. migrate --check changes nothing: the migrations must apply cleanly, in order, to a fresh in-memory database, and the local database (if any) must agree with them; it reports how many are pending (use it in CI). query prints a table (or JSON) for statements that return rows and the number of changed rows otherwise.

reset is for development: it deletes the database (with its -journal, -wal and -shm files) and applies migrations/ to a new one, which is the way out of D132 while the first schema is still changing. The migrations are checked first (a migration that fails stops the command before anything is deleted), and a file that is not an SQLite database is never deleted (D173):

$ roots db reset
deleted and created again ~/.local/share/shop/shop.sqlite3: applied 001, 002 (version 2)

The commands need roots.lisp (for the app's :name) and migrations/, nothing else: a project whose entry component does not exist yet (schema first, UI later) can run all of them.

What these commands read is not trusted. migrate and types run the project's SQL on a connection that cannot ATTACH (so not VACUUM INTO either) and does not trust the schema; query runs what you typed and may. query prints what the app's users stored: control characters (ESC, the C1 range, line separators, bidirectional overrides) are shown as \e, \n, \u{9b} instead of being sent to the terminal, in values and in column names; --json escapes them as \u…. Bytes that are not UTF-8 print as U+FFFD; Inf and NaN print as words (null in JSON); a double keeps all its digits in JSON. SQLite's own error messages quote names from the SQL (no such table: …), so they are shown the same way, also when a migration fails in migrate or types.

The commands use roots-data (src/data/), a small sb-alien binding to the system's libsqlite3.so.0 (macOS: libsqlite3.dylib): open, prepare, bind, step, column, finalize, error messages, busy timeout, limits, transactions. It is loaded on first use, so the toolchain starts without it (D170 says what to install). A library is code and roots db runs inside checkouts nobody has read, so it is never taken from the working directory: on macOS only absolute paths are tried (/usr/lib, then Homebrew and MacPorts), and a library search path variable with an empty or relative element stops the load (D170). New files are created 0600 (before SQLite opens them, whatever the umask) in a 0700 directory; the app's own database and data directory are tightened when they exist with looser modes, a file named with --db keeps its modes.

Drivers #

A driver is a thin adapter (Driver in runtime/data/types.lisp): exec(sql), run(sql, params), query(sql, params) → { columns, rows }, close(), an optional reset() (the schema changed: drop cached statements), and name, persistent, location. Rows travel as arrays; parameters are already checked. Everything else is target-neutral. The build picks the driver with per-target files: runtime/data/driver.term.lisp, driver.browser.lisp (web, pwa, ext), driver.mobile.lisp (ios, android). To bring your own engine, pass open(name, { driver: async ({ name, app, memory, setup }) => myDriver }).

Three more members are optional. reason says why persistent is false (db.reason reads it). A driver whose connection is shared with other parties has notify(tables) (roots/data calls it after each write, with the tables the write changed) and subscribe(listener) → remove (the driver calls listener(tables) when another party wrote): that is how the browser driver carries changes between tabs. setup in the factory's options is the SQL roots/data runs on a new connection (PRAGMA foreign_keys, PRAGMA trusted_schema); roots/data runs it itself after the driver opened, so only a driver that opens the connection again later needs it.

node:sqlite (node.lisp). Node 22 prints an ExperimentalWarning when the module loads; that one warning is filtered while it loads (it would garble a terminal UI), every other warning still reaches stderr. Integers are bound as integers. After a schema change made by another connection, a cached statement is re-prepared. Where Node has it (24+), the connection is in SQLite's defensive mode. node:sqlite throws on an INTEGER beyond 2^53; a read that hits that runs once more with bigints (reads only: a statement that writes, like INSERT … RETURNING, always reads bigints, because it cannot run twice), so ordinary reads pay nothing for it.

bun:sqlite (bun.lisp). The terminal output started with bun app.mjs uses Bun's SQLite with the same files. Bun's onInsert/onUpdate/onDelete hooks, when present, are the update hook. Bun rounds an INTEGER beyond 2^53 silently; a read whose result holds a number that could be such a value runs once more with safeIntegers. Bun keeps an EXPLAIN statement active until it is finalized (a later COMMIT then fails), so those are prepared, read and finalized, never cached. Bun's own exec() goes on after a statement that fails while it runs and reports only the last statement's error, so the driver splits a script (as SQLite's sqlite3_complete reads it) and runs it statement by statement: the first error ends the script, as on the other targets.

The WASM build (wasm.lisp on the page, worker.lisp in a dedicated module Worker). The worker loads runtime/data/sqlite-wasm/sqlite3.mjs, fetches sqlite3.wasm with the SRI hash the build recorded (fetch(…, { integrity }); a file that does not match is refused, D152; so is a page without a hash) and installs the opfs-sahpool VFS, which needs no COOP/COEP headers. The pool lives in .roots-data/<app>/ and is never deleted on a failed start (preserveOnInitFailure). A reload can briefly find the previous page's worker still holding the files: installing is retried for about 3 s. When OPFS cannot be used (an insecure context, an old browser, private browsing) the database is opened in memory, roots/data warns D151, db.persistent is false and db.reason is "no-opfs" (the notes example shows it); open(name, { memoryFallback: false }) fails with D154 instead. Temporary tables stay in memory (temp_store = MEMORY), the VFS has a fixed set of file slots. The worker checks the database and app names again before they become OPFS paths. A Uint8Array parameter that is a view into a larger buffer is copied first: postMessage would clone the whole buffer.

One database for all tabs #

The VFS's file handles are exclusive: one worker can hold an app's files. So the tabs of an app share one database: a second tab opens it at once (nothing is retried, nothing falls back to memory), reads what the first tab wrote, and each tab's live queries follow the other tabs' writes. For the app nothing changes: open(), db.persistent === true, the same API.

// tab 1                                         // tab 2, opened later
const db = await open("shop")                    const db = await open("shop")      // the same database
const cart = db.live("SELECT * FROM cart")       await db.run("INSERT INTO cart (book_id) VALUES (?)", [7])
// cart.value gets the new row; db.onChange((tables) => …) is called with ["cart"]

How it works, and what follows from it:

• One tab leads. Each tab's worker asks for the Web Lock roots-data:<app>. The one that has it (the leader) installs the pool and opens one connection per database; the others send it their statements over BroadcastChannel and get the results back. Statements of all tabs run on that connection one at a time, in the order they arrive.
• Transactions keep to themselves. While a tab's db.transaction() is open, the statements of the other tabs wait until it commits or rolls back: they neither see half of it nor become part of it (the same rule as inside one tab, where calls on db wait for the transaction). A transaction that awaits something slow therefore holds up every tab; keep transactions short.
• Changes travel. After a write (a transaction: when it commits) the tab that made it tells the others which tables changed; their live queries re-run and their onChange listeners are called, exactly as for a write of their own. A schema change arrives as "*". Should a tab close between its write and that message, the leader sends "*" on its behalf: a query may re-run for nothing, no write is missed.
• A tab that closes inside a transaction is rolled back by the leader, and the tabs that waited go on.
• When the leading tab closes (or reloads), another tab takes the lock and the files, opens the databases again and carries on; tabs opened later join it. For the other tabs this takes a moment (usually milliseconds; up to about 3 s when the browser is slow to release the files), during which their statements wait. What was on its way to the closed tab at that instant: a read is simply asked again; a write, or any statement of an open transaction, fails with D155, because nobody can tell whether a lone write was applied (an open transaction was certainly not: SQLite rolls it back). Treat D155 like a lost connection: read the row again, then repeat the write if it is missing.
  Copy

  try { await db.run("UPDATE cart SET qty = ? WHERE id = ?", [qty, id]) }
  catch (e) { if (e instanceof DataError && e.code === "D155") await reloadCart(); else throw e }

• What belongs to the connection does not survive such a hand-over, and is shared by all tabs while it lasts: TEMP tables and triggers, ATTACHed databases, PRAGMAs set by hand. roots/data's own setup (foreign_keys, trusted_schema) is applied again to the new connection. Open the database with the same options in every tab (it is one app).
• In-memory databases ({ memory: true }) are never shared: each tab has its own.
• When sharing is not possible: a browser without Web Locks or BroadcastChannel, or a tab that still runs a build made with an older Roots and holds the files. The tab that cannot have the files then gets an in-memory database as before: D151, db.persistent === false, db.reason === "another-tab" (D154 with { memoryFallback: false }). db.reason tells this from "no-opfs", where no tab can persist anything.
• Other tabs are other pages of the same origin and app: there is no boundary between them (each could open the OPFS files itself), and the messages between the workers are not a security mechanism. The leader checks database names like the worker always did.

The Roots native bridge (native.lisp), contract shared with the native shells:

globalThis.RootsNative = { platform: "ios" | "android", version: "1",
                           call(plugin, method, args?): Promise<unknown>, on(event, handler): () => void }
plugin "sqlite":
  open  {name, adopt?}       → {db: number}
  exec  {db, sql}            → {}
  run   {db, sql, params}    → {changes, lastInsertRowid}
  query {db, sql, params}    → {columns: string[], rows: unknown[][]}
  close {db}                 → {}

Only JSON crosses the bridge: values are strings, numbers (integral ones bind as INTEGER), booleans, null, and blobs as {"$bytes": "<base64>"}; an INTEGER beyond ±(2^53 − 1) comes back as its decimal string (JSON.parse would round it). Errors reject with {code: "SQLITE_…", message}. The suite runs against a mock bridge backed by node:sqlite that round-trips every call through JSON, and (with swiftc) against the iOS shell's real plugin in another process.

roots/data opens <name>.sqlite3 and passes adopt: "<name>": a file an older version created without the extension is renamed (with its -journal, -wal, -shm) when the new one does not exist yet. Without the extension, the database x-journal would have been the rollback journal of the database x. The plugin is the boundary for script in the page (an XSS can call the bridge directly, past roots/data), so it enforces on its own side:

• name is a file name in the app's database directory: no path, and not a name ending in -journal, -wal or -shm (SQLITE_CANTOPEN).
• ATTACH of a file and VACUUM INTO are refused with SQLITE_AUTH (iOS: an authorizer, so SQLite itself decides; Android has no authorizer API, the plugin reads each statement's first word). The page cannot read the WebView's cookie store or another library's database through SQL, nor write a database file elsewhere. Plain VACUUM works.
• iOS binds text with its byte length (a NUL inside a string is stored, not a cut-off point), refuses SQL that contains a NUL, and requires as many params as placeholders (SQLITE_RANGE). On Android a missing parameter is still bound as NULL by the platform's API when the bridge is called directly; roots/data refuses it before (D113).

Capacitor (capacitor.lisp): @capacitor-community/sqlite 8.1.1 through window.Capacitor.Plugins.CapacitorSQLite. Add it to :plugins of the ios/android target in roots.lisp (it is pinned like the other plugins). Every call passes transaction: false (roots/data runs its own transactions); iOS's ios_columns first row gives the column order. Alpha: checked against a mock of the plugin, not on devices. The plugin returns integers as JavaScript numbers (beyond 2^53: rounded) and applies none of the native plugin's restrictions on its side.

What the build does #

• import … from "roots/data" resolves to runtime/data/index.lisp; the driver files for other targets never ship (a DOM target has no node: code, the terminal no WASM).

• migrations/*.sql → runtime/data/migrations.js (generated, checked: D160–D162).

• DOM targets of an app that imports roots/data: the build looks for @sqlite.org/sqlite-wasm in the nearest node_modules at or above the project (D163 if none; install it with roots add @sqlite.org/sqlite-wasm@3.54.1-build1, see below), copies dist/index.mjs and dist/sqlite3.wasm to runtime/data/sqlite-wasm/ (with a NOTICE) and compiles the worker to runtime/data/worker.js. These two files are code that runs in your app's origin, so the build ships them only when their SHA-256 is the pinned one for 3.54.1-build1, wherever in the node_modules chain the package was found: other bytes are D166. Another installed version is an error (D164) unless roots.lisp pins it, version and both hashes together: :data (:sqlite-version "…" :sqlite-mjs-sha256 "…" :sqlite-wasm-sha256 "…") (shasum -a 256 dist/index.mjs dist/sqlite3.wasm in the package you reviewed).

• Installing the package. One instruction, in the project directory:

  Copy

  roots add @sqlite.org/sqlite-wasm@3.54.1-build1

  It writes package.json (creating it in a project that has none, like a fresh roots new) with the exact version and roots.lock with the tarball's integrity hash, and runs no install script. The version is part of the instruction: the build ships only the pinned build, and roots add without a version would record the newest release as a ^ range, which D164 then refuses. The hints of D163 and D164 print this command with the version the build accepts (the toolchain's pin, or the one :data in roots.lisp names). A checkout that already has package.json and roots.lock (the examples) needs roots install. npm works too (npm install --ignore-scripts --save-exact …): the build checks the two files by their hash, whoever put them into node_modules.

• Bundled (the default of roots build): roots/data, its driver for the target and the generated modules (migrations, the .wasm's SRI hash) are part of the page's bundle, app.js. The worker is a bundle of its own at runtime/data/worker.js: nothing imports it (wasm.js creates it with new Worker(new URL("./worker.js", import.meta.url))), so the build walks its module graph separately and bundles it as a self-contained worker entry next to sqlite-wasm/; its new URL("./sqlite-wasm/…", import.meta.url) still points at the copied files (sqlite3.mjs is loaded with a computed import() and stays verbatim). The unbundled layout (roots dev, --no-bundle) ships the modules one by one, the worker's imports checked against the files next to it (D165). On the terminal nothing changes: node:sqlite / bun:sqlite stay imports of the bundle.

• The CSP of those pages gets exactly one more source, 'wasm-unsafe-eval' in script-src (compiling WebAssembly needs it); nothing else changes, and apps that do not import roots/data keep the strict policy byte for byte. The same applies to the MV3 manifest's extension_pages (Chrome enforces it in the worker too) and to the dev server's header.

• PWA: the service worker precaches the worker, sqlite3.mjs and sqlite3.wasm like every other built file, so the database opens offline.

• Dependency record: @sqlite.org/sqlite-wasm 3.54.1-build1 (SQLite 3.54.1), published 2026-10-19, chosen 2026-11-08: past the 7-day dependency cooldown, the newest release, pinned exactly (roots.build::*sqlite-wasm-version*, the example's package.json and package-lock.json with its integrity hash). It is the only new third-party dependency; no Capacitor types are used.

Security notes #

The model in one paragraph. roots/data never builds SQL from values. Raw SQL strings are the developer's, and roots/data is not a sandbox around them: it refuses the statements that leave the database's own file, and nothing more. The boundaries around the data are the user account (terminal), the origin (browser) and the app sandbox (phones); nothing is encrypted.

• Bound parameters only. No API splices values into SQL; identifiers in the table helper are checked against a strict pattern and quoted. db.run("… " + input) is still possible, so review string-built SQL like any other injection risk.
• One statement, every value. run/all/get/live refuse a second statement (D114) and a wrong number of values (D113) before the engine sees them, the same on every target: an injected ; DROP … is an error, not something one engine runs and another drops, and a missing value is never NULL.
• A database stays in its file. ATTACH, DETACH and VACUUM INTO are refused (D117) unless the database was opened with { attach: true }: on the terminal ATTACH can read or create any file the user can. The check reads each statement's first word (they cannot appear inside another statement or a trigger); it is a guard for SQL that came from a string, not a sandbox: with attach: true, or with a driver of your own, raw SQL has the engine's full reach. The native shells' plugin enforces the same on its side of the bridge, where script in the page cannot skip it; the toolchain's connections for migrations cannot attach at all.
• The schema of a file is not trusted (PRAGMA trusted_schema = OFF): a database someone else produced cannot run non-innocuous functions through its views, triggers or defaults. Extension loading is never enabled. node:sqlite connections are in defensive mode where Node offers it.
• Names, not paths. open() takes a name (D101); the location is fixed by the target.
• Private files. Terminal databases are 0600 in a 0700 directory, also when they existed with looser modes; the toolchain creates them the same way. A relative XDG_DATA_HOME is ignored.
• Data at rest is plaintext, on every target. What protects it: file modes on the terminal (another process of the same user reads it); origin isolation in the browser (any script on the origin, so any XSS, reads all of it through OPFS); the app sandbox on phones, where the platform's backup defaults apply (device backups include the database: set android:allowBackup / data-extraction rules or iOS file protection and backup exclusion in the native project if that matters). Do not keep credentials, tokens or keys in roots/data; use the platform's keychain/keystore. For an encrypted database, bring an engine with SQLCipher through open(name, { driver }).
• CSP. Only 'wasm-unsafe-eval' is added, only for apps that use roots/data, and 'unsafe-eval' never. The SQLite build is shipped from the app's own origin (no CDN), its .wasm checked against the SRI hash computed at build time. No shipped file uses eval or new Function (tested). The SRI check detects a broken or mixed-up deploy; it is no defence against someone who can replace files on the origin, who can replace the script that checks as well (sqlite3.mjs and worker.js are ordinary same-origin scripts under the page's CSP).
• Migrations are code, and tamper-evident only one way. db.migrate() and roots db execute migrations/. An applied migration whose file changed is refused, not silently re-run or skipped; a migration cannot end its own transaction (D136). The checksums do not protect against someone who can write the database itself.
• Integers are exact or text. A value beyond 2^53 is never a rounded number: one stored value cannot make a row unreadable (node:sqlite threw on it for every later read), and two identifiers cannot collapse into one.
• Live queries never miss a write made through roots/data: also one made by a trigger that reports no changed row, by a temp trigger, or by a statement that then failed. A UI that shows roles, shares or revocations from a live query shows what is stored.
• roots db output is safe to print: control characters from the database, and from the names SQLite quotes in an error about a migration, are shown, not sent to the terminal.
• Errors are never swallowed. A failing live query reports D140, sets error and keeps its last rows; an onChange listener that throws is rethrown on its own, without breaking the write. Messages carry the SQL text and SQLite's message, never parameter values.
• Browser storage is the origin's. OPFS data can be evicted under storage pressure unless the app calls storage.persist(); on iOS Safari, unless the PWA is installed. Where the database cannot be persisted it is kept in memory (D151, db.reason) unless the app asks for D154 (memoryFallback: false). A second tab shares the first tab's database; the tabs talk over BroadcastChannel, which any script of the origin can use too: that is the same boundary as OPFS itself (see One database for all tabs).
• No bound on result sets. A query without LIMIT over a table users can grow returns all of it (in the browser twice: in the worker and on the page), and a live query re-reads its whole result on every matching write. Write the LIMIT; Table.find() takes { limit }.

Error codes #

Tests #

bin/test-system roots-data-tests                  # everything (about 90 s)
bin/test-system roots-data-tests data-runtime     # tsc --strict, no eval, the driver conformance suite
bin/test-system roots-data-tests data-e2e         # examples/notes end to end

• Conformance (runtime/data/tests/conformance.lisp, ~35 tests) runs on node:sqlite, bun:sqlite, the WASM build in Chromium (in memory and on OPFS, under the CSP, with a reload in between), a mock RootsNative bridge and a mock Capacitor plugin: values, errors, statement cache, transactions and savepoints, persistence, live queries (only affected tables re-run, a commit updates once, a rollback not at all, joins, views, triggers, DELETE without WHERE, schema changes, scopes, queries that follow a function, failures, settled()), migrations and the helpers; and the hostile or careless input of Security notes: parameter counts and stacked statements, integers beyond 2^53, NUL in text, __proto__ columns, writes hidden by triggers or made by failing statements, undefined in where, migrations that end their transaction, ATTACH, and the native plugin's own rules asked through the bridge.
• Several tabs (runtime/data/tests/browser-suite.lisp, in Chromium, real tabs of one browser context): the second tab opens the same database without waiting; live queries and onChange follow the other tab; a schema change crosses; a transaction of one tab makes the other wait and is never half seen; a tab closed inside a transaction is rolled back; the leading tab closes and another takes over (data, live queries, connection setup; a follower's open transaction fails with D155 and applies nothing); a reload; and db.reason with OPFS or Web Locks taken away from the worker.
• Toolchain: the binding, migration files and runner, generated types (compiled with tsc; view columns through inner and outer joins), roots db (also reset, and without an entry component), and databases migrated by the toolchain accepted by the app and back; migrations that try to write another file or end their transaction, hostile table names in the generated types, hostile text and odd values in roots db query, file modes.
• Build: every target of the notes example (drivers, files, SRI, CSP, precache, manifest, no eval), the strict CSP untouched for apps without roots/data, the errors.
• End to end: notes on the web and as a PWA (offline) in Chromium with zero console errors, as an MV3 extension, under roots dev, in a pseudo-terminal (the file is written, read by the toolchain, and read back by the app), and a tampered .wasm refused.

Missing prerequisites (libsqlite3, bun, Playwright, python3, the npm package: run bin/roots install at the repository root and in examples/notes) make a test print PENDING; ROOTS_E2E_REQUIRE=1 makes it fail instead.

Limits (0.1.x) #

• Positional parameters only; no named parameters, no iterate()/streaming of huge results and no row limit of roots/data's own (write LIMIT).
• Live queries see writes made through roots/data in this app (in the browser: in any of its tabs), not writes other connections make to the file.
• In the browser one tab holds the OPFS files and runs every tab's statements; a transaction that stays open holds up all tabs, and a write on its way to a tab that closes fails with D155. Tabs of a build made with an older Roots cannot join (D151 there). Sharing is tested in Chromium (real tabs); Safari and Firefox have the APIs it uses (Web Locks, BroadcastChannel) but were not run. Not examined: a leading tab that the browser suspends in the background while it holds the lock; the other tabs would wait for it.
• Integers beyond 2^53 are read as decimal strings (as rounded numbers through the Capacitor plugin); lastInsertRowid beyond 2^53 is a rounded number.
• No encryption at rest (bring a driver), no defensive mode on Bun, in the WASM build or in the toolchain's binding.
• The Capacitor adapter is untested on devices; the native bridge is implemented by the native-shells stream.
• No sync or replication (PLAN, Phase 6).