@statewalker/db-sqlite-do
v0.1.4
Published
Durable Object SQLite (ctx.storage) driver for @statewalker/db-api, with transactions through @statewalker/db-sqlite and a node:sqlite fake for tests.
Readme
@statewalker/db-sqlite-do
What it is
A driver that implements Db from @statewalker/db-api on a Durable Object's SQLite storage
(ctx.storage), on Cloudflare or on celld. Its Db works with inTransaction from
@statewalker/db-sqlite. The ./fake subpath gives a Node.js stand-in for ctx.storage with
the same limits, for unit tests.
Why it exists
Code written against Db (search indexes, file stores) must run inside a Durable Object as it
does on Node.js and in the browser. DO SQLite differs in ways that break such code silently: SQL
BEGIN is rejected, transactionSync commits at the first await, BLOBs come back as
ArrayBuffer. This driver hides those differences behind the same Db and transaction call.
How to use
pnpm add @statewalker/db-sqlite-do| Import | Gives | Environment |
| --- | --- | --- |
| @statewalker/db-sqlite-do | newDoSqliteDb; types DoSqlStorage, DoSqlCursor, Db, DbEntry | Durable Object (Cloudflare, celld) |
| @statewalker/db-sqlite-do/fake | newFakeDoStorage; type FakeDoStorage | Node.js 22.13 or later (tests) |
newDoSqliteDb(storage) returns a Db synchronously, so it can be created in the DO
constructor. Use ? placeholders.
Examples
import { DurableObject } from "cloudflare:workers";
import { inTransaction } from "@statewalker/db-sqlite";
import { newDoSqliteDb } from "@statewalker/db-sqlite-do";
export class Notes extends DurableObject {
db = newDoSqliteDb(this.ctx.storage);
async add(body: string) {
await this.db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
return inTransaction(this.db, (tx) =>
tx.query<{ id: number }>("INSERT INTO notes (body) VALUES (?) RETURNING id", [body]),
);
}
}In a Node.js test:
import { newDoSqliteDb } from "@statewalker/db-sqlite-do";
import { newFakeDoStorage } from "@statewalker/db-sqlite-do/fake";
const db = newDoSqliteDb(newFakeDoStorage());How it is tested against a real Durable Object
pnpm test:do (from the repository root) builds the workspace, starts celld dev on
packages/db-sqlite-do/test-do/ with a clean state on a free port, and runs, inside a real
Durable Object, the shared Db conformance suite (through a small HTTP bridge), every scenario
listed in test-do/scenarios.ts (the SQLite transaction scenarios a DO can meet), and a platform probe whose expectations the ./fake must also meet. It
needs celld 0.6.x on PATH; with another version it stops at once with
test:do needs celld 0.6.x on PATH, found "<version>". A celld that prints no ready line within 60 seconds fails
the run with celld dev was not ready within 60 s. It stops only the celld process it started. It is not part of pnpm test: run it before a release. The same root pnpm test:do also runs
@statewalker/db-sqlite-index's smoke scenarios in its own test-do/.
Internals
- Transactions use
storage.transaction(async fn). It rolls back on a throw even afterawaits on timers orfetch.transactionSyncis not used: given an async function it commits at the firstawait, so a later throw rolls nothing back. The platform can run the callback again, so it must be idempotent and do no external I/O. - Limits of DO SQLite, and what they look like: more than 100 bound parameters rejects with
too many SQL variables(pass id lists as one JSON string tojson_each(?)); a BLOB or string over about 2 MB rejects withstring or blob too big(celld 0.6.2 accepts 1,900,000 bytes and rejects 2,500,000; the fake rejects above 2,000,000 bytes, measuring a string in UTF-8);BEGIN,SAVEPOINTandPRAGMAreject withnot authorized. ON CONFLICT ROLLBACKandRAISE(ROLLBACK)fail the whole DO call. InsideinTransactionon a Durable Object, a statement that makes SQLite itself end the transaction rolls the data back, but the call in flight rejects witha transaction rollback failed: … cannot rollback - no transaction is activeinstead of the constraint error, and the DO instance is reset (observed on celld 0.6.2; Cloudflare unverified). Use the defaultABORTconflict resolution inside transactions. The fake rejects with the same message, soinTransactioncallers see it in unit tests too. An operation that runs past the platform deadline (15 s on celld) is retried rather than failed, so keep each write step small.- BLOBs are converted both ways. Typed-array parameters are bound as
ArrayBuffer;ArrayBuffervalues read back becomeUint8Array, as on every other SQLite driver. - Input handling comes from
@statewalker/db-sqlite.queryrejects a second statement and empty SQL beforesql.execsees it, andtrue/false,undefinedandDateparameters bind as1/0,NULLand ISO 8601 text, as on every SQLite driver. How DO SQLite itself binds abigintand reads an integer above 2^53 is not probed yet: the fake reads such an integer as abigint(as the other drivers do), andtest:doskips that scenario (DO_UNPROBED_SCENARIOSintest-do/unsupported-scenarios.ts). The real-DO run ofrunDbConformancealso skips the scenarios its harness cannot carry, since each call is a separate HTTP request with a JSON body (call order, close during a call, BLOB and bigint round trips:DO_REMOTE_UNSUPPORTED_DB_SCENARIOS); they run on the fake. close()releases nothing. The storage belongs to the DO. It only makes later calls on this Db reject withdb-sqlite: the database is closed.- Use one Db per DO. Calls are queued inside one Db; direct
ctx.storage.sqlcalls made elsewhere in the DO bypass that queue. - The fake is strict where the platform is unknown. It rejects every
PRAGMA(the platform allows a few) and typed-array BLOB parameters, so code that passes on the fake does not depend on them. Its limits are checked against real DO SQLite bypnpm test:do, which runs the same probe in acelld devDurable Object. - Dependencies:
@statewalker/db-sqlite(queue and transactions),@statewalker/db-api(types)../fakeuses Node.js'snode:sqlite.
License
MIT
