cf-dosql

Real SQLite implementation of the Cloudflare Durable Object SqlStorage API for testing.

Durable Object logic that touches SQLite is normally locked inside the CF Workers runtime. By providing the same SqlStorage interface backed by real SQLite, cf-dosql lets you extract that logic into plain functions that accept SqlStorage as a parameter, then test them in Node.js or the browser without deploying to Cloudflare:

// src/schema.ts -- pure logic, no CF runtime dependency
export function initSchema(sql: SqlStorage) {
  sql.exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)");
}

export function addUser(sql: SqlStorage, name: string): number {
  return sql.exec<{ id: number }>(
    "INSERT INTO users (name) VALUES (?) RETURNING id", name
  ).one().id;
}

// tests/schema.test.ts -- runs in Node.js with real SQLite
import { createDurableObjectState } from "@inferal-oss/cf-dosql";
import { initSchema, addUser } from "../src/schema";

const { ctx, close } = createDurableObjectState();
initSchema(ctx.storage.sql);
const id = addUser(ctx.storage.sql, "Alice");
assert(id === 1);
close();

The same functions work in your Durable Object constructor with the real ctx.storage.sql and in tests with cf-dosql's implementation. No mocks, no regex SQL parsing, full SQL fidelity: JOINs, subqueries, CTEs, window functions, and more.

Implements the exact SqlStorage and SqlStorageCursor interfaces from @cloudflare/workers-types, verified by tsc --noEmit:

interface SqlStorage {
  exec<T extends Record<string, SqlStorageValue>>(
    query: string,
    ...bindings: any[]
  ): SqlStorageCursor<T>;
  get databaseSize(): number;
  Cursor: typeof SqlStorageCursor;
  Statement: typeof SqlStorageStatement;
}

declare abstract class SqlStorageCursor<
  T extends Record<string, SqlStorageValue>,
> {
  next():
    | { done?: false; value: T }
    | { done: true; value?: never };
  toArray(): T[];
  one(): T;
  raw<U extends SqlStorageValue[]>(): IterableIterator<U>;
  columnNames: string[];
  get rowsRead(): number;
  get rowsWritten(): number;
  [Symbol.iterator](): IterableIterator<T>;
}

type SqlStorageValue = ArrayBuffer | string | number | null;

Backends

| Backend | Runtime | SQLite Version | Init | |---------|---------|---------------|------| | better-sqlite3 | Node.js | 3.51.2 | Synchronous | | sql.js | Browser (WASM) | 3.49 | Async (WASM load), then sync |

Usage

Node.js (better-sqlite3)

import { createDurableObjectState } from "@inferal/cf-lib/dosql";

const { ctx, close } = createDurableObjectState();

// Use exactly like a real DurableObject context
ctx.storage.sql.exec("CREATE TABLE t (id INTEGER PRIMARY KEY)");
ctx.storage.transactionSync(() => {
  ctx.storage.sql.exec("INSERT INTO t (id) VALUES (?)", 1);
  ctx.storage.sql.exec("INSERT INTO t (id) VALUES (?)", 2);
});
ctx.blockConcurrencyWhile(async () => {
  // Runs synchronously in tests (no real concurrency control needed)
});

close();

DurableObject Testing (Browser)

import { createDurableObjectStateAsync } from "@inferal/cf-lib/dosql";

const { ctx, close } = await createDurableObjectStateAsync();

ctx.storage.sql.exec("CREATE TABLE t (id INTEGER PRIMARY KEY)");
ctx.storage.transactionSync(() => {
  ctx.storage.sql.exec("INSERT INTO t (id) VALUES (?)", 1);
});

close();

Extension Support

| Extension | better-sqlite3 | sql.js | CF DO | |-----------|---------------|--------|-------| | JSON1 | Yes (built-in) | No | Yes | | FTS5 | Yes (built-in) | No | Yes | | Math functions | Yes (built-in) | Yes (JS polyfill) | Yes | | R-Tree | Yes (built-in) | No | No | | GeoPoly | Yes (built-in) | No | No |

Caveats

R-Tree and GeoPoly: These are compiled into better-sqlite3 but are NOT available in CF Durable Object SQLite. Tests using these extensions will pass locally but fail in production. There is no PRAGMA to disable them at runtime. Future iterations may add runtime blocking.

sql.js lacks FTS5 and JSON1: The standard sql.js WASM build does not include FTS5 or JSON1. Tests that require these should be skipped in browser test suites.

Math functions in sql.js: Registered via JavaScript (registerMathFunctions), not compiled natively. Behavior should match but minor floating-point differences are possible.

SQLite version delta: better-sqlite3 bundles 3.51.2 while sql.js ships 3.49. Features added between these versions may not be available in the browser backend.

API

createDurableObjectState(options?)

Creates a DurableObjectState-compatible context backed by better-sqlite3 (Node.js):

• ctx.storage.sql -- SqlStorage
• ctx.storage.transactionSync(fn) -- wraps in SQLite IMMEDIATE transaction
• ctx.blockConcurrencyWhile(fn) -- executes fn (no-op concurrency control in tests)

Options:

• path?: string -- database file path (default: ":memory:")
• sizeLimit?: number -- max DB size in bytes (default: 10 GB)

Returns { ctx, close: () => void }.

createDurableObjectStateAsync(options?)

Same shape as createDurableObjectState but backed by sql.js (browser).

Same options. Returns a Promise.