The SQLite extension

Last updated 29 October 2026

Read this when you need to know what the package does on your machine, beyond what Guarantees promises: how @tonbo/sqlite-ursula runs every statement on a SQLite file on your host and turns each commit into a record it can send to the database’s stream.

A VFS in a loadable extension

SQLite reaches files only through its OS interface, the VFS. The package ships a loadable extension, sqlite-ursula-vfs, prebuilt for each supported platform, and open() loads it into node:sqlite with loadExtension().

The extension registers a VFS named ursula as the default one. It starts from a copy of SQLite’s unix VFS, replaces how files are opened, deleted and looked up, and implements every file method itself, so it sees each page SQLite writes and reads.

The extension calls SQLite only through the host’s own routines, so it runs on the SQLite that node:sqlite links, and refuses to load into one older than 3.50.4 or built without WAL. Statements are plain SQLite: the extension changes where the bytes go, not how SQL runs.

What a commit writes

The database runs in WAL mode, so a transaction first appends its pages to the WAL. The extension watches those writes, and at the commit point it turns the transaction into one record of the log before SQLite makes the commit visible:

  db.prepare("INSERT ...").run()
          |
          v
  SQLite writes the transaction's pages as WAL frames -----> db-uwal
          |
          |  commit point: the frame that ends the transaction
          v
  extension reads the pages back, checks them,
  compresses them into one record (zstd)
          |
          v
  log/<position>.usl   one write, no fsync, the commit is "logged"
          |
          v
  SQLite publishes the commit, and the statement returns
          |
          :  later, in the background
          v
  the replicator sends the record to the stream

Because the log write comes first, every commit SQLite could recover from its WAL is also in the log. Nothing is sent over the network and nothing waits for the stream; that is the job of the replicator (see Replication and synced()). A commit whose pages exceed the commit limit fails alone, before anything is logged (see Limits).

Nothing is fsynced per commit. The extension syncs at three points instead:

So a process crash loses no commit: the next open on the host replays the log and sends what the stream lacks. A power loss or a lost disk can lose the commits logged since the last log sync that the stream had not yet acknowledged. PRAGMA synchronous changes nothing here: the extension makes SQLite’s sync of a database file a no-op, and synced() is what makes a commit durable.

The cache root and generations

The extension owns its files. Each database gets a directory in the cache root (the dir option of open()), named after the database and the first 32 hex digits of a BLAKE3 hash of its stream’s path:

<cache root>/                        default <user cache>/sqlite-ursula/0.8
  <name>-<hash>/                     one directory per database (stream)
    lock                             held while a process has it open
    gen-<id>/                        the current generation
      db                             the SQLite file, page 1 marked
      db-uwal                        its WAL, under a name SQLite never looks for
      db-shm                         the WAL index
      log/<position>.usl             the log, in 16 MiB segments
    gen-<id>.tmp/                    a generation being built
    gen-<id>.trash/                  a replaced generation being deleted
    salvage/<time>-<reason>/         generations moved aside, never read again

A generation is one local copy of the database with its WAL and log. A new one is built in a .tmp directory and published by one rename, so a crash leaves either the old generation or the new one. An open picks what to do with the generation it finds, and db.status().attach.local says which it did:

The lock file keeps a second process from using the same directory while one has the database open. Keep the cache root on a disk that outlives the process, so a restart resumes instead of rebuilding (see Connect).

Bound to one stream

A generation belongs to one stream of one deployment, and the files say so in three places:

• The first 16 bytes of the database file, where SQLite keeps its magic string SQLite format 3, hold the marker Ursula08 and part of the copy’s random lineage id. The extension shows SQLite the magic string only for its own lineage.
• The WAL is db-uwal, not db-wal, so SQLite without the extension never finds or checkpoints it.
• Every log segment’s header records the deployment’s cluster id, the stream’s path and its incarnation. An open compares them with what the stream answers, and refuses a host name that leads to another deployment with wrong_cluster.

So the sqlite3 shell, node:sqlite without the extension, or a connection opened before the extension loaded fails with SQLITE_NOTADB (file is not a database) and changes nothing. To read a database with plain SQLite, export it with VACUUM INTO (see Backups and salvage).

WAL mode and 4 KiB pages

The extension captures a commit from its WAL frames, so every database it creates is in WAL mode with 4096-byte pages, and it holds them there:

Only that transaction fails; the database stays usable. Connections in one process share the files, as WAL mode allows.