persistence/sqlite

persistence@1 on top of Node's built-in node:sqlite (no native dependency). One table per collection, id TEXT PRIMARY KEY, columns from the schema (boolean as 0/1, json as text). ensureCollection adds missing columns to existing tables; it never drops or retypes them. A field declared as { type, unique: true } gets a UNIQUE INDEX (<collection>_<field>_unique; NULL stays repeatable, as in SQLite); the index is dropped again when the declaration goes. Existing rows that already share a value make ensureCollection throw (collection, field, value, count) – boot fails instead of running on data the index cannot cover. Config: { file: "./data/kestrel.db", busyTimeoutMs?: 5000 } or file: ":memory:". Directories are created on setup.

Pragmas set on every connection: journal_mode = WAL (readers never block the writer), busy_timeout = <busyTimeoutMs> (default 5000 – a locked database makes the caller wait that long before the call answers Err(TRANSIENT); 0 restores the SQLite default of giving up at once), foreign_keys = ON and synchronous = NORMAL. NORMAL is the WAL-safe choice: a process crash cannot corrupt the database, only the last transactions can be lost if the machine loses power – pair it with backup/blobstore or replication/sqlite rather than with FULL.

One process, one writer. The module assumes it is the only process that opens file. replication/sqlite opens a second connection to the same file inside the same process (it holds a read transaction and runs its own checkpoints); that is supported. A second process on the same file – a standalone kestrel CLI next to a running server, a second dev server, two containers on a shared volume – is not: writes are only serialised inside one process, and restores swap the file before it is opened. Point each process at its own file, or route all writes through one of them.

Every persistence@1 method returns a Result. A SQLITE_BUSY/SQLITE_LOCKED after the busy timeout is Err(TRANSIENT) (503, retryable, details.retryAfterSeconds: 1), a UNIQUE constraint failed is Err(CONFLICT) (409) – createOne with an id that already exists included; for a unique field the error carries details: { collection, field } and applies to createOne, createMany, updateOne and updateMany alike. updateOne on a missing id is Err(NOT_FOUND), deleteOne on one is Ok. Everything else – unknown collection, unknown field, id in a schema, a non-string for a string column – stays a throw, because it is a wiring bug. node:sqlite reports the extended result code in errcode (a primary-key collision is 1555), so the mapping masks it with 0xff to get the primary code: 5 SQLITE_BUSY, 6 SQLITE_LOCKED, 19 SQLITE_CONSTRAINT.

persistence.snapshot:<file> writes a transactionally consistent copy via VACUUM INTO (safe while writes are in flight) – back that file up, not the live database. persistence.checkpoint folds the WAL into the main file. Both are maintenance calls outside persistence@1, so they signal a locked database by throwing; the steps turn that into the same TRANSIENT the contract methods return.

| Step | reads | writes | errors | |---|---|---|---| | persistence.checkpoint | – | – | TRANSIENT | | persistence.snapshot:<file> | – | result | TRANSIENT | | persistence.createOne:<c> | – | result | CONFLICT, TRANSIENT | | persistence.findOne:<c> | params.id | result | NOT_FOUND, TRANSIENT | | persistence.findMany:<c> | – | result | TRANSIENT | | persistence.updateOne:<c> | params.id | result | VALIDATION, NOT_FOUND, CONFLICT, TRANSIENT | | persistence.deleteOne:<c> | params.id | result | VALIDATION, TRANSIENT |

Not included: migrations beyond adding columns, transactions across calls, full-text search.

Generated from the manifest

@michaelthielemann/kestrel-persistence-sqlite – module persistence/sqlite: provides persistence@1.

| Config | Type | Required | Default | |---|---|---|---| | file | string | yes | – | | busyTimeoutMs | integer | no | – |

| Step | Summary | Reads | Writes | Input | Output | Errors | |---|---|---|---|---|---|---| | persistence.checkpoint | Fold the write-ahead log into the main database file | – | – | – | – | – | | persistence.snapshot:<arg> | Write a transactionally consistent copy of the database to | – | result | – | { file: string, … } | – | | persistence.createOne:<arg> | Create one document from the payload | – | result | object | – | 409 a document with that id or a unique value already exists | | persistence.findOne:<arg> | Read the document named by params.id | params.id | result | – | – | 404 no such document | | persistence.findMany:<arg> | List every document | – | result | – | { items: object[], total: number, … } | – | | persistence.updateOne:<arg> | Patch the document named by params.id from the payload | params.id | result | object | – | 400 missing id; 404 no such document; 409 a unique value already exists | | persistence.deleteOne:<arg> | Delete the document named by params.id | params.id | result | – | { ok: true, … } | 400 missing id |