npm package: @inferal-oss/cf-dosql
Description: Cloudflare Durable Object SqlStorage backed by SQLite for Node.js and browser
Latest version: 0.1.0

# 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:

```typescript
// 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;
}
```

```typescript
// 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`:

```typescript
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)

```typescript
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)

```typescript
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.