@sdxc/data-table-sqlstorage

A remix/data-table database driver backed by a Cloudflare Durable Object's SQL storage.

remix/data-table models reach a database through a driver. This one runs their queries against the SqlStorage handle a Durable Object owns, so a model's rows live inside the object that serves them, with JSON and boolean columns round-tripped on the way through.

Installation

npm add @sdxc/data-table-sqlstorage

The driver plugs into the data-table models of remix, which installs alongside this package. The SqlStorage handle it executes against comes from the Durable Objects runtime, so a TypeScript project also wants @cloudflare/workers-types for that type.

Usage

Build A Driver From A Durable Object's SQL Handle

import { createSQLStorageDatabaseAdapter } from "@sdxc/data-table-sqlstorage";
import { DurableObject } from "cloudflare:workers";
import { Database } from "remix/data-table";

export class Tenant extends DurableObject {
	#db = new Database(createSQLStorageDatabaseAdapter(this.ctx.storage.sql));

	async listUsers() {
		return this.#db.findMany(users, { orderBy: ["id"] });
	}
}

The handle belongs to the object and outlives every request, so the driver is built once per instance rather than per call.

Read And Write Through Models

Models are declared the way every remix/data-table model is, and the driver compiles them to SQLite text:

import { createSQLStorageDatabaseAdapter } from "@sdxc/data-table-sqlstorage";
import { column as c, Database, table } from "remix/data-table";

let users = table({
	name: "users",
	columns: {
		id: c.integer().primaryKey(),
		email: c.varchar(255),
		settings: c.json(),
		active: c.boolean(),
	},
});

let db = new Database(createSQLStorageDatabaseAdapter(sql));

let created = await db.create(users, {
	id: 1,
	email: "user@example.com",
	settings: { theme: "dark" },
	active: true,
});

SQLite stores neither JSON nor booleans natively, so the driver bridges both: a c.json() value is serialized on the way in and parsed back into an object on the way out, and a c.boolean() column reads back as true or false instead of the integers SQLite holds. A nullable column keeps null as its own third state.

Atomicity Without A Transaction Scope

A Durable Object's SQL storage rejects BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE and ROLLBACK TO, so the driver issues none of them and db.transaction() rejects:

await db.transaction(async (tx) => {
	await tx.create(users, { id: 1, email: "first@example.com" });
}); // rejects: Durable Object SQL storage has no transaction statements

Atomicity comes from the object itself instead. Every write an object makes within one turn of its event loop is coalesced into a single atomic commit, so a sequence of writes that stays inside a turn either lands whole or not at all:

async register(email: string) {
	await this.#db.create(users, { id: 1, email });
	await this.#db.create(profiles, { userId: 1, email });
}

The limit is the turn. Awaiting network I/O — a fetch, an RPC call to another object, anything that yields to the runtime — ends the turn, and the writes made before the await commit separately from the ones made after. A scope that spans an await is therefore no longer atomic, so gather what a write needs before the first write rather than between two of them.

Where a scope must also cover key-value storage, the runtime's own ctx.storage.transactionSync(callback) runs synchronous work atomically across both. It is synchronous by design, so the callback cannot await; work that must await belongs outside it.

capabilities.savepoints and capabilities.transactionalDdl both report false, which is what remix/data-table reads to decide what it attempts: a nested transaction() is refused by name, and the migration runner applies each migration without opening a scope.

Run Raw SQL

let result = await db.exec("SELECT email FROM users WHERE id = ?", [2]);
result.rows; // [{ email: "second@example.com" }]

let deleted = await db.exec("DELETE FROM users WHERE id = ?", [1]);
deleted.affectedRows; // 1

A raw statement carries no read/write signal of its own, so the leading keyword decides: SELECT, WITH and PRAGMA come back with rows, and anything else reports how many rows it wrote.

API

createSQLStorageDatabaseAdapter(db: SqlStorage, options?): DatabaseDriver

Builds the DatabaseDriver that new Database(...) takes. db is the SqlStorage handle to execute against, which a Durable Object exposes as ctx.storage.sql once its class is SQLite-backed. The driver reports a dialect of "sqlite".

options.capabilities overrides the feature flags the driver advertises. returning and upsert are on by default and migrationLock is off; those three are what it accepts. savepoints and transactionalDdl are fixed at false and are not overridable, because the platform rejects the statements either one would need.

The returned driver carries the full DatabaseDriver surface. Three members behave in a way worth knowing:

• beginTransaction(), and every commit, rollback and savepoint method, always rejects. The error names ctx.storage.transactionSync() as the API the platform offers instead.

• executeScript(sql) runs a multi-statement script one statement at a time, which is what SQL storage's one-statement-per-call exec needs. It cuts the script on the semicolons that terminate a statement; see below for what that covers.

• wipe() always rejects. The Durable Object owns its database's lifecycle, so a clean slate comes from migrating down or deleting the object's storage.

Reads after a write are best served by a RETURNING clause, which returning enables: insertId falls back to last_insert_rowid(), and it is reported only for a table with a single-column primary key.

Pattern: Running Migrations When A Durable Object Boots

An object's database is created the first time the object runs, so the schema is applied from the constructor or from the first call that needs it. executeScript takes the whole script:

import { createSQLStorageDatabaseAdapter } from "@sdxc/data-table-sqlstorage";
import { DurableObject } from "cloudflare:workers";

export class Tenant extends DurableObject {
	#adapter = createSQLStorageDatabaseAdapter(this.ctx.storage.sql);

	async migrate() {
		await this.#adapter.executeScript(`
			CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, email TEXT NOT NULL);
			CREATE UNIQUE INDEX IF NOT EXISTS users_email ON users (email);
		`);
	}
}

transactionalDdl is off, so the migration runner applies each script as it stands rather than wrapping it. A migration declared as requiring transactional DDL is refused by name before anything runs, which is what keeps a half-applied schema from being mistaken for a rolled-back one.

Where A Script Is Cut

A semicolon ends a statement everywhere except inside a string literal, a quoted identifier — double-quoted, backtick-quoted or [bracketed], each with its doubled-delimiter escape — a -- line comment, a /* */ block comment, and a CREATE TRIGGER body. So all of this runs as its author wrote it, and a comment explaining a migration is free to use prose punctuation:

-- Pages by keyset on (created_at, id); the seek is a seek only while an index carries both.
CREATE INDEX feeds_subscription_idx ON feeds (created_at, id);
INSERT INTO settings (label) VALUES ('first; second');
CREATE TRIGGER counted AFTER INSERT ON events BEGIN
	UPDATE totals SET n = CASE WHEN n < 10 THEN n + 1 ELSE n END;
END;

A trigger arrives at exec whole, so its body semicolons stay inside it and the BEGIN that opens it reads as the trigger's own rather than as the transaction statement the platform refuses. CASE … END inside a body nests, so the trigger ends at the END that matches its BEGIN.

Fragments holding only whitespace or comments yield no statement, so a doubled ;; and a closing -- done are both fine, and a final statement needs no trailing semicolon.

A script whose string literal, quoted identifier, block comment or trigger body never closes is refused before any statement runs, with an error naming what is open and the line it opened on — a truncated script fails whole rather than applying the part that parsed.

Pattern: Hosting A Storage-Agnostic Library Inside A Durable Object

A library that takes a DatabaseDriver rather than opening its own connection runs wherever a driver can be built. Constructing one here is what lets such a library live inside a Durable Object, keeping each object's data in the object itself:

import { createSQLStorageDatabaseAdapter } from "@sdxc/data-table-sqlstorage";
import { DurableObject } from "cloudflare:workers";

export class Tenant extends DurableObject {
	#engine = createEngine({
		database: createSQLStorageDatabaseAdapter(this.ctx.storage.sql),
	});
}

The same library moves to Cloudflare D1 by swapping in the driver from @sdxc/data-table-d1; the models, queries and migrations above it are unchanged.