record-adapter-do-sqlite — a Workers story that fits the sync SqlExecutor and the ownership rule

[cuibonobo]

Problem

With record-adapter-sqljs removed, there is no adapter for non-Node runtimes. sql.js was meant to serve that case and could not: Workers forbid compiling WASM from bytes at runtime, and its whole-database-serialize-per-write persist model has no single-writer guarantee, so a Worker fleet would silently lose writes.

Cloudflare Durable Objects with SQLite storage is the shape that actually fits — and it fits this codebase specifically, not just the runtime:

• ctx.storage.sql.exec() is synchronous. SqlExecutor (packages/sqlite-shared/src/executor.ts) is a synchronous interface and all of SharedSqlRecordLogic is built on it. A DO adapter drops onto it as-is. D1 and libsql-over-HTTP are both async and would force an async refactor of the entire shared layer — that cost is the reason to prefer this over either.
• DO supports FTS5, the same module record-adapter-sqlite uses, so this rejoins the shared FTS path with no pluggable seam.
• A DO instance is a single owning writer. docs/spec/adapters.md § Concurrency & storage ownership requires exactly one owning process per stack's storage; a Durable Object implements that constraint rather than delegating it to the host.

Work items

• record-adapter-do-sqlite — StackRecordAdapter with a SqlExecutor over ctx.storage.sql, reusing SharedSqlRecordLogic, the FTS5 schema and strategy, the query builder, cursor codec, and row mappers
• Engine-specific surface only: construction from the DO's ctx, SqlStorageCursor → row-object normalization, lifecycle (no lock file — the DO is the lock; no persist callback — storage is durable by construction)
• Confirm the error-message probes in sqlite-shared/src/executor.ts (isForeignKeyViolation, isUniqueConstraintViolation) match what DO's SQLite build reports; they currently pin exact strings that happen to agree across sql.js and node:sqlite
• Spec: a bullet in docs/spec/adapters.md § Concurrency & storage ownership for how this adapter honors single-writer
• Tests against the Workers runtime (@cloudflare/vitest-pool-workers) rather than under environment: 'node' — the sql.js package's tests only ever exercised Node, which is how a browser/Worker adapter shipped without ever running in a browser or a Worker

Notes

Not urgent, and worth confirming there's real demand before building it — it's listed as the answer if a Workers story is wanted, not a commitment to one. Explicitly not libsql: @libsql/client on Workers is a remote HTTP client to Turso, which is closer in kind to adapter-api than to an embedded storage adapter, and is async.

Cross-refs: #93 (optional-capability discovery). Blob storage in this topology would be R2 via #160.

[cuibonobo]

On "worth confirming there's real demand before building it"

Here's a demand signal, offered as information rather than a request.

cuibonobo.com is a Cloudflare Pages + D1 + R2 site currently being migrated onto a stack. The working assumption for that migration has been a VPS — not because anything about the site wants one, but because there is no Workers record adapter, so leaving Cloudflare is the only way to run the current stack at all.

This issue plus #160 is a route to not leaving: Durable Object SQLite for records, R2 for blobs. That would keep an existing Cloudflare deployment on Cloudflare instead of relocating it to satisfy a runtime constraint.

Why it's worth deciding before the migration rather than after

Very little of the deployment work transfers between the two targets. A VPS path means Docker, a process manager, TLS termination, the storage-ownership lock file, disk-backed blobs, and a backup story for the SQLite file. A Workers path means none of those and a different set of unknowns instead. Building one and then switching is close to a full redo of that layer, so the fork is better taken up front — which makes this a decision with a deadline attached, even though the issue itself is "not urgent."

Marking priority: medium on that basis. Genuinely undecided from this side: the VPS path is well-trodden and the DO adapter doesn't exist yet, so "migrate to a VPS now, revisit if this lands" remains a legitimate answer — just one worth choosing deliberately rather than by default.

One note from the issue's own work items that argues in its favour if it does go ahead: the sync SqlExecutor fit is a real advantage, and the observation that the removed sql.js package "shipped without ever running in a browser or a Worker" is the kind of thing worth pinning with @cloudflare/vitest-pool-workers from the first commit rather than adding later.

---

Generated by Claude Code