> Fetch the index of every Tonbo docs page as Markdown at https://tonbo.dev/llms.txt

# 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](https://tonbo.dev/docs/databases/how-it-works.md) 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](https://sqlite.org/vfs.html). The package ships a [loadable extension](https://sqlite.org/loadext.html), `sqlite-ursula-vfs`, prebuilt for each supported platform, and `open()` loads it into `node:sqlite` with [loadExtension()](https://nodejs.org/docs/latest-v22.x/api/sqlite.html#databaseloadextensionpath).

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](https://sqlite.org/c3ref/io_methods.html) 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](https://ursula.tonbo.io/docs/examples/sqlite-vfs/#other-hosts) 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](https://sqlite.org/wal.html), 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](https://github.com/tonbo-io/ursula/blob/v0.11.2/docs/architecture/sqlite-vfs.md#commit-path) before SQLite makes the commit visible:

```text
  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()](https://tonbo.dev/docs/databases/internals/replication.md)). A commit whose pages exceed the commit limit fails alone, before anything is logged (see [Limits](https://tonbo.dev/docs/databases/limits.md#transaction)).

Nothing is fsynced per commit. The extension [syncs at three points](https://github.com/tonbo-io/ursula/blob/v0.11.2/docs/architecture/sqlite-vfs.md#fsyncs) instead:

| When | What is synced | Why |
| --- | --- | --- |
| Before a [checkpoint](https://sqlite.org/wal.html#checkpointing) copies pages into the database file | The log | Every page in the database file comes from a durable record |
| Before the WAL starts over | The database file | A base record then names the last commit the file holds |
| Before a claim is sent | The claim record | A claim whose answer was lost is still known to be this process’s |

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`](https://sqlite.org/pragma.html#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](https://docs.rs/blake3/1.8.7/blake3/) hash of its stream’s path:

```text
<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](https://github.com/tonbo-io/ursula/blob/v0.11.2/docs/architecture/sqlite-vfs.md#layout), 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:

| `attach.local` | When |
| --- | --- |
| `resumed` | The generation is current: the open replays its log and continues |
| `caught_up` | Another process wrote to the stream since, and nothing of this copy is pending: the open applies those commits in place |
| `built` | No usable generation: the open builds one from the latest snapshot and the log |
| `rekeyed` | The stream got a new history that kept every commit this copy had acknowledged |
| `salvaged` | The copy cannot be kept, because the stream has another history or the file is not the copy’s own, and it holds data: it moves into `salvage/` and the open builds a new one (see [Backups and salvage](https://tonbo.dev/docs/databases/backups.md#salvage)) |

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](https://tonbo.dev/docs/databases/connect.md)).

## 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](https://sqlite.org/fileformat2.html#the_database_header) `SQLite format 3`, hold the marker `Ursula08` and part of the copy’s random lineage id. The extension [shows SQLite the magic string](https://github.com/tonbo-io/ursula/blob/v0.11.2/docs/architecture/sqlite-vfs.md#marker) 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](https://github.com/tonbo-io/ursula/blob/v0.11.2/docs/architecture/sqlite-vfs.md#identity) with `wrong_cluster`.

So the `sqlite3` shell, `node:sqlite` without the extension, or a connection opened before the extension loaded [fails with `SQLITE_NOTADB`](https://ursula.tonbo.io/docs/examples/sqlite-vfs/#other-hosts) (`file is not a database`) and changes nothing. To read a database with plain SQLite, export it with `VACUUM INTO` (see [Backups and salvage](https://tonbo.dev/docs/databases/backups.md#export)).

## WAL mode and 4 KiB pages

The extension [captures a commit from its WAL frames](https://github.com/tonbo-io/ursula/blob/v0.11.2/docs/architecture/sqlite-vfs.md#capture), so every database it creates is in WAL mode with 4096-byte pages, and it holds them there:

| A transaction that | Fails with |
| --- | --- |
| Leaves WAL mode, such as [`PRAGMA journal_mode = DELETE`](https://sqlite.org/pragma.html#pragma_journal_mode), `MEMORY` or `OFF` | `unsupported`: a rollback journal would bypass the log |
| Changes the [page size](https://sqlite.org/pragma.html#pragma_page_size) | `unsupported` |
| Writes in `EXCLUSIVE` locking mode | `unsupported` |

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