SQLite on Ursula

@tonbo/sqlite-ursula gives a Node.js application a SQLite database that an Ursula stream replicates. Statements run on a local SQLite file. Each commit goes to a log on the same host and then to the stream in the background, and await db.synced() tells you when the stream holds it.

Start a single node in a terminal of its own. It keeps nothing across restarts.

docker run --rm -p 4437:4437 ghcr.io/tonbo-io/ursula:0.10.1

Install the package, which needs Node.js 22.19 or later, and run:

npm install @tonbo/sqlite-ursula

import { open } from "@tonbo/sqlite-ursula";

const db = await open("http://127.0.0.1:4437/demo/notes");
db.exec("CREATE TABLE IF NOT EXISTS notes(id INTEGER PRIMARY KEY, body TEXT)");
db.prepare("INSERT INTO notes(body) VALUES (?)").run("hello"); // committed on this host
await db.synced(); // in the stream, and this process still owns it
console.log(db.prepare("SELECT id, body FROM notes").all());
db.close();

The one rule: before anything outside the process acts on what you wrote or read (a response, a queue acknowledgement, a call to another service), await db.synced().

you	what happens
run a statement or COMMIT	local work: SQLite, plus one write to the log
await `synced()`	one append round trip while writing, one leader read when idle
lose the stream	commits continue, `synced()` waits, past `maxPendingBytes` writes fail with `backlog_full`
are taken over	statements and `synced()` throw `UrsulaResetError` (`fenced`), and `reset` listeners run
crash	commits the stream never acknowledged come back from the log at the next open and are resent

The local cache outlives the node. The quick-start node keeps nothing across restarts and sets no server.cluster_id, so a restarted node is a new deployment with a new random cluster id, and open refuses the old cache with wrong_cluster. Open with a fresh dir, or delete the stream's directory in the cache root (demo-notes-* in ~/Library/Caches/sqlite-ursula/0.8, ~/.cache/sqlite-ursula/0.8 on Linux). For anything beyond a throwaway quick start, give the server a server.cluster_id and a Raft WAL path (see Configuration), so a restart keeps both the stream and its deployment.

How it works

open loads the sqlite-ursula-vfs extension into node:sqlite, brings the local cache up to date from the stream, claims the stream for this process and returns the database. Any host can rebuild the database from the stream: the same code on another host opens the same database, and fences this one.

A commit is local. At SQLite's commit point the extension appends the transaction's final pages to a log it owns, with no fsync and no network. Statements never wait on the stream.
Durable means acknowledged. One replicator per database sends logged commits in order, one append at a time, batching what accumulates. An append is resent unchanged until the stream answers it, however long an outage lasts.
synced() is the output gate. It resolves once the stream holds every commit made before the call, and a round trip that started after the call found this process still the owner. It never blocks the event loop.
Ownership is explicit. The last open() that reaches the stream owns it. Claims the extension makes on its own, after an outage for example, never take the stream from an owner that an explicit open installed.

The design, with the test behind each guarantee, is in docs/architecture/sqlite-vfs.md.

Gating outputs

gate(db, work, { signal }) applies the one rule for you. It runs work and settles with its outcome only once a synced() call made after work settled resolved, on every database given (db may be an array).

import { gate, open } from "@tonbo/sqlite-ursula";

const db = await open("http://127.0.0.1:4437/demo/notes");

// A fetch-style handler: the status, the headers and every body chunk wait.
const handler = (request: Request) => gate(db, () => router.fetch(request), { signal: request.signal });

// A job processor whose promise a queue framework acknowledges.
const processor = (job: Job) => gate(db, () => applyJob(db, job));

what is held	released when	if a reset comes first	if `signal` fires first
the result of `work`, or its error unless that is or wraps an `UrsulaError`	a `synced()` call made after `work` settled resolves on every database	rejects with the `UrsulaResetError`, and the outcome is dropped	rejects with `signal.reason`, the outcome is dropped and a returned body is cancelled
each chunk of a returned `Response` body, and its end	a `synced()` call made after the chunk was read resolves	the body errors	not applied: cancel the body to stop it

Reads wait too: a result may have read another call's pending commit, or state this process is about to lose to a takeover.
An UrsulaError thrown by work is rethrown at once, also when a library wrapped it (as a cause, or in an AggregateError): a refused write committed nothing, and a reset is what the wait would report.
A gate has at most one synced() call in flight, and chunks read meanwhile wait for the next one, so a streamed body pays one round trip per batch, not per chunk. It reads ahead at most 64 KiB plus one chunk, and chunks leave in order.
A node:http response is not gated by itself: write it after the gate, as in res.end(await gate(db, () => save(req))).

The gate does not cover effects inside work before it returns (an outbound call or a manual acknowledgement in the middle of a handler needs await db.synced() first), WebSocket frames, raw sockets, timers, background jobs or child processes. It does not make effects exactly once: a crash after the release but before an acknowledgement runs the work again, so record message ids and idempotency keys in the same transaction as the work. And it promises nothing beyond what synced() means: the stream holds the commit, with the durability the server gives it.

Open options

open(url, options) returns a promise of an UrsulaDatabase, a node:sqlite DatabaseSync subclass, so drizzle, kysely and Pi's driver take it. Options it does not know go to DatabaseSync.

option	default	meaning
`dir`	`<user cache>/sqlite-ursula/0.8`	The cache root, created when missing
`token`	none	The bearer token requests carry. One per process: the last open's applies
`signal`	30 s	Bounds the open
`maxPendingBytes`	64 MiB	Compressed commits that may wait for the stream before writes fail with `backlog_full`
`recreate`	`false`	When the stream is missing and the cache holds data, move the cache aside and create the stream again, instead of `not_found`
`readOnly`	`false`	Claim nothing, see Readers
`timeout`	5000 ms	node:sqlite's busy timeout. The connections of one database share its files

The first open of a stream in the process binds it. Later opens return another connection on the same binding with no network, concurrent opens share one, and worker threads call open too.
db.close() is synchronous. Once a database's last connection closes, replication drains in the background. There is no drain at exit: a script awaits db.synced() before it exits, and the log keeps what the stream lacks for the next open.
A stream is open under one dir at a time in a process. An open under another dir fails with bound_elsewhere while the first has connections open.

UrsulaDatabase adds name (the stream's path, such as /acme/notes), synced({ signal }), status(), and on("reset", listener) and off("reset", listener).

Errors

Every error the package throws for a failure of the extension has a kind, an action and a cause. Branch on action. Every other error, such as a constraint violation, is node:sqlite's own. Network failures never throw from statements or synced(): they wait.

`action`	what to do
`retry`	Nothing was lost and the database stays usable. For `backlog_full`, await `synced()` and run the transaction again. For a failed `open`, call it again later
`reopen`	Close every connection of the database and `open` it again
`stop`	Another owner took the stream over. Stop using the database, and never reopen it in a loop: opening it takes the stream back
`fix`	It cannot succeed as asked. Fix the URL, the setup, the stream or the transaction

class	thrown by	kinds
`UrsulaWriteRefusedError`	a write or COMMIT, nothing committed (`errcode` 7178)	`backlog_full`, `unsupported`, `too_large`
`UrsulaResetError`	statements after a reset, `synced()`, `reset` listeners	`fenced`, `stale`, `rewound`, `deleted`, `local_damage`, `stream_state`, `internal`
`UrsulaUnavailableError`	`open()` when its signal fires first, and `importSqlite()` when it cannot finish (action `fix`, since a failed import leaves the stream refusing opens)	the last condition (`unreachable`, `unavailable`, `rate_limited`, `unauthorized`, `payment_required`, `forbidden`), or `cancelled`
`UrsulaSetupError`	`open()`, `importSqlite()`, and `synced()` or `status()` of a retired database	`invalid_url`, `invalid_argument`, `not_found`, `in_use`, `bound_elsewhere`, `local_setup`, `stream_format`, `no_server_identity`, `protocol_mismatch`, `wrong_cluster`, `stream_state`, `claim_races`, `not_bound`, `internal`

unsupported is a transaction the extension cannot capture: EXCLUSIVE locking mode, or a commit that leaves WAL mode or 4096-byte pages. Only that transaction fails.

too_large is a commit whose pages take more than the commit limit, URSULA_VFS_MAX_COMMIT_BYTES (128 MiB by default). SQLite rolls the whole transaction back, nothing of it is logged or sent, and the database stays usable. Split the transaction, or raise the limit on every host that may own the database. A VACUUM or a rewrite of a large table writes most of the database in one transaction: raise the limit for it, run PRAGMA incremental_vacuum(N) in steps on a database created with auto_vacuum = INCREMENTAL, or VACUUM INTO a plain file and import that into a new stream with importSqlite().

Resets

A reset ends a database. Pending synced() calls reject first, then the reset listeners run once, and every statement throws the reset, reads included (errcode 266 for a read, 778 for a write). A read transaction already open keeps its snapshot. open the stream again, or stop when the action is stop. The new open need not wait for the old database to close: it works on a copy of the cache, which carries every commit the old one made.

kind	cause	action	commits the stream never acknowledged
`fenced`	another owner claimed the stream	`stop`	kept, and sent if this process opens the stream again
`stale`	the stream moved on while this process could not see it, such as an owner idle past producer expiry	`reopen`	discarded
`rewound`	a restore or an accepted loss removed commits the stream had acknowledged	`reopen`	moved aside into `salvage/` by the reopen
`deleted`	the stream was deleted	`fix`	kept: `open` with `recreate`
`local_damage`	a local fsync failed, a WAL page read back wrong, or SQLite ended a commit without publishing it	`reopen`	kept, and the reopen replays them
`stream_state`	another writer appended to the stream, the stream was closed, or the server lost acknowledged state	`fix`	kept
`internal`	a bug inside the extension	`reopen`	kept

Provisional opens

When the stream is unreachable (the name lookup or the connect fails, or takes more than 5 s), an open with a trusted local cache that claimed the stream before resolves at once, provisional in status(). Reads come from the cache, commits are local and pending, and synced() waits. Once the stream answers, the extension reads it from the cache's acknowledged state to its tail and claims it only when no other owner wrote or claimed there. The database is then live.

If another owner took the stream meanwhile, the database resets (fenced or stale) with nothing sent, and no synced() ever resolved on the provisional state. Without a cache, the open waits for the stream until its signal fires.

History changes and salvage

A restore, an accepted loss, or a delete and recreate gives the stream a new history, and Stream-Incarnation changes.

When the stream kept every commit the cache acknowledged, the extension keeps the cache under the new history, with no reset (attach.local: "rekeyed" at an open). This covers a restart of a single node that lost nothing.
Otherwise it moves the cache aside and builds a new one from the stream. A bound database first resets rewound.
A cache is also moved aside when its db file is not its own (replaced), and with recreate (recreated).

A moved cache goes into salvage/ in the stream's cache directory as a plain SQLite database with its log and a manifest.json, and status().salvage lists it. The extension never reads or deletes it. Open the db file with any SQLite to look at the commits the new history lacks, and decide what to write again.

Readers

readOnly: true claims nothing and never fences the owner. Writes fail with SQLITE_READONLY (8).

In a process that owns the stream it returns a read-only connection on the owner's database, which sees the owner's latest commits.
In any other process it builds or catches up a reader cache of its own (role: "reader", reader-<n>/ in the stream's cache directory) to the stream's tail at the open. It reads the stream as of then, and its synced() resolves at once. Close its connections and open again to read newer commits.
A closed stream opens. On an unreachable stream it opens an earlier reader cache as it is, and otherwise waits until the signal fires. A missing stream fails with not_found.

Status

db.status() returns one JSON object. The fields to watch:

state: provisional, live, retiring, retired or reset. role: owner or reader.
commits: logged, acknowledged, pending and pending_bytes. pending_bytes reaching maxPendingBytes means writes fail with backlog_full.
replication.condition: null, or why requests are retried: retrying (no definite answer yet), unauthorized, forbidden, payment_required, request_too_large, redirected, wrong_cluster, no_server_identity, protocol_mismatch or rejected. Commits continue under any condition until the backlog fills.
attach.local: where the open got the database: resumed, rekeyed, built, salvaged or caught_up.
snapshot: offset, age_ms, failures, error, log_bytes and due_bytes. A snapshot is due once log_bytes passes due_bytes, and a fresh host builds from the latest snapshot and the log after it.
refused, salvage and reset.

The extension logs one logfmt line per event on stderr, such as sqlite-ursula-vfs level=warn event=reset ....

Alerts worth setting:

replication.condition other than retrying that stays: something in front of the stream refuses the writes. The condition says what.
pending_bytes growing for minutes: the stream is unreachable or slow.
Any reset, and any entry in salvage.
snapshot.log_bytes staying above a few times due_bytes, or snapshot.failures growing: snapshots are not published, and snapshot.error says why. Every rebuild then reads the whole log.

Pi Durable

Install Pi Durable next to the package:

npm install @tonbo/sqlite-ursula @earendil-works/pi-durable

import { openUrsulaPiStorage } from "@tonbo/sqlite-ursula/pi";

const storage = await openUrsulaPiStorage("https://ursula.example.com/pi/harness-1", { closeTimeoutMs: 10_000 });
// hand `storage` to the Pi harness, and close it on shutdown

openUrsulaPiStorage opens the stream with open and runs Pi's own node driver on it. Every transaction and statement resolves only after synced(), so a Pi commit resolves once the stream holds it.

A commit refused with backlog_full commits nothing. From Pi 1.2, it waits for synced() and runs again, since Pi expects a storage to retry its own transient failures. On Pi 1.0 and 1.1 it throws Pi's StorageRejected, and the session continues.
A reset throws the UrsulaResetError itself, so the session stops. Open the storage again, or stop when its action is stop.
close waits up to closeTimeoutMs for synced(), then closes. On Pi 1.3, Session.close first waits for the storage calls in flight, which wait for the stream, so closing a session during an outage waits until the stream answers. Bound the shutdown in your host if it must end sooner.

@earendil-works/pi-durable is an optional peer dependency in the range >=1.0.0 <1.4.0, and npm refuses the install with ERESOLVE outside it.

Other hosts

Any SQLite host can load the extension and call its SQL functions on the connection loadUrsulaVfs() returns: ursula_open, ursula_synced, ursula_events, ursula_cancel, ursula_status, ursula_stats, ursula_set_token and ursula_import. ursula_open(url, dir, options_json, timeout_ms) returns the path of the local file, which connections then open with plain SQLite. A host opens a reader's path with SQLITE_OPEN_READONLY. Errors read <function>: <kind>: <message>. The extension refuses to load into SQLite older than 3.50.4.

A cache's files are not SQLite files outside the extension. The db file holds a marker in place of SQLite's header magic, and its WAL is db-uwal. The sqlite3 shell, node:sqlite without the extension and a connection opened before the extension loaded fail with SQLITE_NOTADB (file is not a database) and change nothing. To read a database with plain SQLite, export it from a connection open returned, with VACUUM INTO.

Import

importSqlite(url, file, { token, timeoutMs }) imports a plain SQLite database file into a new stream, as the stream's first snapshot, and returns { offset, digest, pages, bytes }. Then open(url) it as any other stream.

import { importSqlite, open } from "@tonbo/sqlite-ursula";

importSqlite("https://ursula.example.com/acme/notes", "/tmp/notes.db");
const db = await open("https://ursula.example.com/acme/notes");

The file must have 4096-byte pages, no -wal beside it and no hot journal. VACUUM INTO writes such a copy (after PRAGMA page_size = 4096 when the database has other pages). The file is only read.
The stream must be missing, or empty. A stream of another content type fails with stream_format, anything else with stream_state, and nothing is published. A server of another release line fails with protocol_mismatch, and an answer without Ursula-Protocol and Ursula-Cluster-Id with no_server_identity. While the import runs, opens of the stream fail with stream_state. An import that fails after it claimed the stream leaves a stream that refuses opens and imports with stream_state until you delete or recreate it.
A file whose compressed snapshot is above the request limit goes up in parts, which needs a cold backend on the server. Without one it fails with invalid_argument, naming the limit.
The call blocks its thread until the import is done, for at most timeoutMs (10 minutes by default).

Moving from 0.7

0.8 changes every format. A 0.8 server reads no 0.7 data, the 0.8 extension refuses 0.7 streams (stream_format) and 0.7 servers, and it never reads 0.7 caches. Move each database by export and import:

Export with 0.7. Close the owner cleanly, or open the database read-only, and export it with VACUUM INTO. 0.7 caches already use 4096-byte pages.

// @tonbo/sqlite-ursula 0.7.x
const db = open("https://old.example.com/acme/notes", { readOnly: true });
db.exec("VACUUM INTO '/tmp/notes.db'");
db.close();

Import with 0.8 into a new stream on the 0.8 cluster, then open it:
```
// @tonbo/sqlite-ursula 0.8.x
importSqlite("https://new.example.com/acme/notes", "/tmp/notes.db");
const db = await open("https://new.example.com/acme/notes");
```
From another host, call SELECT ursula_import(url, file, options_json, timeout_ms) with a positive timeout_ms.
Keep the 0.7 cache until you have checked the imported database. 0.7 appended every commit to the stream before the local WAL, so the cache holds nothing the 0.7 stream lacks, but once the 0.7 cluster is gone it may be the only copy.

Code changes:

open() is asynchronous: await open(url, options). A commit returns before the stream holds it, so add await db.synced() before every external effect.
The extension owns its files. The path option, attach, setToken, status(file), drainStats and instrument are gone. Pass token to open, or use URSULA_VFS_TOKEN_FILE.
Six error classes become four. UrsulaReattachError, UrsulaReplicationError and UrsulaFencedError become UrsulaResetError, whose action includes reopen. A 402 is a replication condition in status() instead of UrsulaPaymentRequiredError. Network failures no longer throw. There is no poisoned state and no outcome_unknown.
openUrsulaPiStorage(url, options) takes no file.
status() has new fields: see Status.

Operations changes:

URSULA_VFS_RETRY_MS is gone: appends are resent until a definite answer. URSULA_VFS_MAX_FRAME_BYTES becomes URSULA_VFS_MAX_REQUEST_BYTES. Commits up to the commit limit (URSULA_VFS_MAX_COMMIT_BYTES) go out, in parts above the request limit. URSULA_VFS_SNAPSHOT_MIN_BYTES is gone: a snapshot is due past the database's size or 8 MiB, whichever is larger.
The default cache root is <user cache>/sqlite-ursula/0.8, and the sqlite3 shell cannot open a cache.
The extension sends User-Agent: sqlite-ursula-vfs/0.8.x. Update any proxy, WAF or log rule keyed on it.
Multi-node clusters need server.cluster_id, and snapshots above 32 MiB compressed need a cold backend (see What the cluster needs).

What the cluster needs

A server of the extension's release line. HEAD answers carry the server's release line as Ursula-Protocol (11 for 0.11.x) and Ursula-Cluster-Id. The extension refuses a server of another line with protocol_mismatch at open and at import, and an answer without them with no_server_identity. A running session checks them only at its next HEAD, which it sends to confirm an answer such as a 412 or a 404. Upgrade the extension with the server, and stop every SQLite session before you move a cluster to another release line.
A stream URL that reaches the stream's leader by itself: a gateway, or a single node. The extension never follows a redirect.
A cluster id per deployment (server.cluster_id). The extension remembers the id each cache was built on, and refuses (wrong_cluster) a host name that routes it to another deployment.
For databases whose compressed snapshots exceed 32 MiB, a cold backend, so snapshots go up in parts, up to 1 GiB. Without one, snapshot.error reports the limit, the log grows, and every rebuild reads it all.
Appends are at most 4 MiB, and snapshots up to 32 MiB go in one request. Clusters keep per-group admission caps of at least 64 MiB (standard or large preset), so such a publish is admitted.

Layers in front of the stream

Every append the extension sends, a claim included, carries a producer key and a digest of its body. A proxy or a gateway that sends an append again does no harm: the stream answers the repeat as a duplicate of the same write. A snapshot publish or a parts commit sent again is answered from its offset and digest. The extension never needs to know that a write applied nothing: it sends the same bytes again until the stream answers them. 0.8 servers and gateways send no x-ursula-not-applied header.

Other clients on a shared deployment may send writes without producer headers, which a replayed request applies twice. Keep proxy retries off for stream writes on such a deployment (proxy-next-upstream: "off" in the chart's TLS ingress example).

A layer that limits request bodies should send Ursula-Max-Request-Bytes with its limit on HEAD, and name it on its 413, as ursula gateway does. The extension then cuts its requests to fit. A bare 413 makes it ask the stream's HEAD for the limit.

TLS and tokens

https:// stream URLs work with the certificate authorities bundled with the extension (Mozilla's). For a private CA, point URSULA_VFS_CA_FILE at a PEM bundle: the extension then trusts only the CAs in it.

Behind ursula gateway with access control (see Security), every request needs a bearer token. Pass token to open, or name a file that holds it, which the extension reads again whenever it changes and after a 401 (a Kubernetes projected token, or a file another process refreshes):

export URSULA_VFS_TOKEN_FILE=/var/run/secrets/ursula/token

The token is process-wide in 0.8, and its audience must not depend on the host name, since every host name of a deployment shares one cache. A refused token is reread and the request sent again. A token refused twice blocks replication as unauthorized until it changes, and commits continue until the backlog fills. When the file holds no token as it is read, the extension keeps the last token it read and logs token_file_unreadable.

Over http:// the token crosses the network in clear. Use https:// for any endpoint on another host. The extension logs token_in_clear once when it sends a token over http:// to a host that is not a loopback address.

Settings

The extension reads these environment variables once per process:

variable	default	meaning
`URSULA_VFS_TOKEN_FILE`	none	A file holding the bearer token, read again when it changes
`URSULA_VFS_CA_FILE`	the bundled CAs	A PEM bundle of the certificate authorities to trust
`URSULA_VFS_MAX_REQUEST_BYTES`	the server's	A request limit below the one `HEAD` advertises, for a layer in front of the stream that does not advertise its own
`URSULA_VFS_MAX_COMMIT_BYTES`	134217728 (128 MiB)	The most bytes of pages one commit may hold, from 4096 to 1 GiB. A larger commit fails as `too_large`. Writing a commit takes about three times the limit in memory for pages that do not compress, and readers need memory for the largest commit any owner logs
`URSULA_VFS_IDLE_MS`	90000	How long a connection is kept after its last request. Keep it below the idle timeout of any NAT, load balancer or firewall on the path
`URSULA_VFS_MIN_UPLOAD_KBPS`	256	The slowest upload, in KiB/s, an attempt's time limit allows: a request body may take 5 s plus its size at this rate

Platforms

npm installs the prebuilt extension for your platform, @tonbo/sqlite-ursula-<platform>, as an optional dependency. It is prebuilt for Linux x64 and arm64 with glibc 2.28 or later (Debian 11, Ubuntu 20.04, Amazon Linux 2023 and newer), Linux x64 with musl (Alpine), and macOS 11 or later on Apple silicon.

On other platforms, build the extension and point SQLITE_URSULA_VFS at it. The variable also overrides the prebuilt extension.

cd clients/sqlite-vfs
cargo build --release   # target/release/libsqlite_ursula_vfs.so (.dylib on macOS)
export SQLITE_URSULA_VFS=$PWD/target/release/libsqlite_ursula_vfs.so

Within 0.8.x the formats the extension writes to the stream and to its files do not change, so owners upgrade one at a time. Upgrade @tonbo/sqlite-ursula together with the extension. The release notes list the known limits.

Snapshots
Exactly-once writes
The design: docs/architecture/sqlite-vfs.md