feat: impl `node:sqlite` {Database,Statement}Sync with Durable Objects

[LoneRifle]

[NOTE: Issue text generated by Devin using the @CognitionAI SWE 1.7 model]

Summary

Node.js ships node:sqlite as a stable, synchronous SQLite API. workerd has had the module stubbed for some time (src/workerd/api/node/sqlite.{h,c++} and src/node/sqlite.ts), but DatabaseSync and StatementSync are not constructible and backup is unimplemented.

This is a request to implement a node:sqlite shim that uses workerd's existing Durable Object SQLite engine. That would let node:sqlite code run on Cloudflare Workers inside a Durable Object, and also support a transient in-memory mode for stateless Workers.

Motivation

• If workerd supported node:sqlite, projects that depend on it could be deployed to Cloudflare Workers with fewer changes by running the SQLite logic inside a Durable Object.
• workerd already has all the necessary pieces: SqliteDatabase, ActorSqlite, DurableObjectStorage, and SqlStorage. The main gap is the JavaScript API surface.

Feasibility assessment

What already exists

• src/workerd/api/node/sqlite.{h,c++}: the node:sqlite stub, registered as node-internal:sqlite and exposed as node:sqlite when enable_nodejs_sqlite_module is enabled.
• src/node/sqlite.ts: public module wrapper that exports DatabaseSync, StatementSync, backup, and constants.
• src/workerd/util/sqlite.{h,c++}: SqliteDatabase with Query, Statement, Regulator, Vfs, reset(), serialize/deserialize support, etc.
• src/workerd/io/actor-sqlite.{h,c++}: ActorSqlite implements ActorCacheInterface and exposes getSqliteDatabase().
• src/workerd/api/actor-state.{h,c++}: DurableObjectStorage exposes getSqliteDb() and getSql().
• src/workerd/api/sql.{h,c++}: SqlStorage/Cursor/Statement, demonstrating how to wrap SqliteDatabase for JavaScript.

Why this is feasible

• SqliteDatabase, Query, and Statement are synchronous, matching node:sqlite DatabaseSync/StatementSync.
• A DatabaseSync implementation can access the current actor via IoContext::current().getActor() and then ActorCacheInterface::getSqliteDatabase() (or DurableObjectStorage::getSqliteDb()).
• For :memory: databases, a new SqliteDatabase can be constructed with a SqliteDatabase::Vfs backed by a kj::Directory (e.g. kj::newInMemoryDirectory()). SqliteDatabase already supports this.
• SqlStorage/SqlStorageRegulator provide a working reference design for prepared-statement caching and regulator behavior.

Key constraints

• node:sqlite is synchronous. This means DatabaseSync cannot be backed by a remote Durable Object from a regular Worker. The only way to get a persistent, synchronous DO-backed SQLite is to run the node:sqlite code inside the Durable Object itself, so the database is the current actor's SqliteDatabase.
• Durable Objects already disallow explicit BEGIN/SAVEPOINT statements via SqlStorageRegulator because DOs manage transactions through ctx.storage.transaction()/transactionSync(). A node:sqlite DatabaseSync built on top of ActorSqlite must either preserve this restriction or carefully integrate isTransaction/exec transactions with the DO's automatic transaction wrapping.
• Some node:sqlite features (e.g. loadExtension, ATTACH DATABASE, user-defined functions, Session/applyChangeset, SQLTagStore, backup to a real file path) require additional work or may not be safe in a multi-tenant environment. They can be progressively enabled or explicitly disabled.

Proposed design

1. node:sqlite DatabaseSync

Add a real constructor to workerd::api::node::SqliteUtil::DatabaseSync.

new DatabaseSync(path, options):

• If path === ':memory:' (or URL equivalent) or if the constructor is not running inside an actor, create a private SqliteDatabase using a SqliteDatabase::Vfs on a temporary/in-memory directory. This gives a non-persistent, per-connection SQLite database.
• If the constructor is running inside a Durable Object and enableSql is true, use the current actor's SqliteDatabase via IoContext::current().getActor()->getPersistent()->getSqliteDatabase() (or DurableObjectStorage::getSqliteDb()).
• In a regular Worker, a non-:memory: path should throw, because file-backed databases are not accessible outside a DO.

Implement the DatabaseSync methods:
open(), close(), isOpen, isTransaction, exec(), prepare(), location(), serialize(), deserialize(), function(), aggregate(), setAuthorizer(), enableDefensive(), enableLoadExtension()/loadExtension(), limits, createSession(), applyChangeset(), createTagStore(), and [Symbol.dispose].

DatabaseSync should be a ResetListener on the underlying SqliteDatabase so it can survive reset() / deleteAll().

2. node:sqlite StatementSync

DatabaseSync.prepare(sql, options) returns a StatementSync that wraps SqliteDatabase::Statement.

Implement StatementSync methods: run(), get(), all(), iterate(), setReadBigInts(), setReturnArrays(), setAllowBareNamedParameters(), setAllowUnknownNamedParameters(), expandedSQL, sourceSQL, and columns().

Type conversion:

• null <-> NULL
• number / bigint <-> INTEGER
• number <-> REAL
• string <-> TEXT
• Uint8Array / Buffer / ArrayBuffer / DataView / TypedArray <-> BLOB

Use the readBigInts and returnArrays options to control output shape.

3. backup

sqlite.backup(sourceDb, path, options) is async. It can be implemented using SQLite's sqlite3_backup_* API to copy from a SqliteDatabase to a new SqliteDatabase (in-memory or, if permitted, a file path). In a Durable Object context it may be most useful to serialize the result as a Uint8Array or to copy to another in-memory database.

4. Session, SQLTagStore, and constants

• constants already exists in src/node/sqlite.ts; ensure it is complete.
• Add Session and SQLTagStore exports to src/node/sqlite.ts.
• Session uses sqlite3session_* APIs and requires the SQLite session extension.
• applyChangeset uses sqlite3changeset_apply with conflict/filter callbacks.
• SQLTagStore is an LRU cache over prepared statements; it can be implemented in TypeScript on top of DatabaseSync.prepare().

5. Regulator / security

Define a NodeSqliteRegulator (or reuse SqlStorageRegulator) that:

• Denies access to names prefixed with _cf_.
• Disallows BEGIN/SAVEPOINT when using DO storage, or maps them to the DO's transaction semantics.
• Reports errors through JSG.
• Enables shouldAddQueryStats for billing when executing user SQL.

For an in-memory :memory: database, the regulator can be more permissive because the database is not shared.

How a user would "bind" a Durable Object

Because DatabaseSync is synchronous, the DO binding is implicit: the user runs the node:sqlite code inside the Durable Object class whose storage they want to use.

export class MyDb extends DurableObject {
  db = new DatabaseSync('my-db'); // ':memory:' for in-memory; any other path selects the DO's persistent DB

  async fetch(req) {
    this.db.exec('CREATE TABLE IF NOT EXISTS kv (k TEXT PRIMARY KEY, v TEXT)');
    const row = this.db.prepare('SELECT v FROM kv WHERE k = ?').get('hello');
    return Response.json(row);
  }
}

new DatabaseSync(...) would detect IoContext::current().getActor() and use the current DO's SqliteDatabase. A regular Worker can only use new DatabaseSync(':memory:'); a file-backed or persistent database must be accessed from within the DO or through an RPC wrapper (which cannot use node:sqlite directly because the API is synchronous).

Open questions

1. Path semantics: Should new DatabaseSync(':memory:') inside a DO create a separate in-memory database, or use the DO's persistent DB? The safest approach is:
   • :memory: -> in-memory, private to the DatabaseSync instance.
   • any other path -> use the current DO's persistent SQLite DB (the path is ignored, because a DO has only one database).
   • outside a DO -> :memory: is supported; other paths throw.
2. Transactions: How should BEGIN/COMMIT/ROLLBACK/SAVEPOINT be handled in a DO-backed DatabaseSync? The DO already wraps each request in an implicit transaction. Options:
   • a) Deny them, like ctx.storage.sql (simplest and consistent).
   • b) Allow them as nested savepoints within the DO's implicit transaction.
   • c) Add a separate DatabaseSync transaction API or transactionSync integration.
3. Scope: Which node:sqlite features should be available initially? A minimal implementation could cover DatabaseSync, StatementSync, backup, and constants; Session, SQLTagStore, and loadExtension could be follow-ups.
4. Enablement: Should node:sqlite be auto-enabled with nodejs_compat/nodejs_compat_v2, or still require the enable_nodejs_sqlite_module compatibility flag?

References

• node:sqlite stub: src/workerd/api/node/sqlite.{h,c++} and src/node/sqlite.ts
• DO SQLite internals: src/workerd/util/sqlite.h, src/workerd/io/actor-sqlite.{h,c++}, src/workerd/api/actor-state.{h,c++}, src/workerd/api/sql.{h,c++}
• add node:sqlite module #5106 (added the stub), add node:sqlite to NODEJS_BUILTINS #3775 (added node:sqlite to NODEJS_BUILTINS)
• Node.js node:sqlite documentation: https://nodejs.org/api/sqlite.html
• Related issue: Bug Report - Segmentation fault on MacOS with workerd and wrangler and vitest #6583 (node:sqlite segfault under vitest-pool, indicating community interest in using this module)