[ Data & storage ]

@sdxc/data-table-sqlstorage

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

Used by: auth-saas, reader
Source: packages/data-table-sqlstorage

View as Markdown Open in GitHub Open in ChatGPT Open in Claude

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

The driver runs remix/data-table 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 remix/data-table from remix. TypeScript projects also install @cloudflare/workers-types for the SqlStorage type.

Usage

Build a database inside a Durable Object

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

import { users } from "./schema.js";

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 build the database once per instance.

Read and write JSON and boolean columns

import { column as c, 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(),
	},
});

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

let user = await db.find(users, 1);
user?.settings; // { theme: "dark" }
user?.active; // true

A c.json() value is stored as JSON text and parsed back into an object; a c.boolean() column reads back as true or false instead of SQLite's 1 and 0. null stays null in both.

Run raw SQL

import { sql } from "remix/data-table";

let result = await db.exec(sql`SELECT email FROM users WHERE id = ${1}`);
result.rows; // [{ email: "user@example.com" }]

let deleted = await db.exec(sql`DELETE FROM users WHERE id = ${1}`);
deleted.affectedRows; // 1

A statement starting with SELECT, WITH or PRAGMA answers rows; any other statement answers the number of rows it wrote.

Write atomically within one turn

Durable Object SQL storage has no transaction statements, so db.transaction() rejects. Instead, every write an object makes within one turn of its event loop commits as one unit:

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

Awaiting network I/O (a fetch, an RPC call to another object) ends the turn, so the writes before it and after it commit separately.

API

`createSQLStorageDatabaseAdapter(db: SqlStorage, options?): DatabaseDriver`

Builds the driver new Database(...) takes, executing against a Durable Object's SqlStorage handle (ctx.storage.sql). The driver reports the "sqlite" dialect.

options.capabilities.returning: compile RETURNING clauses. Default true.
options.capabilities.upsert: compile INSERT … ON CONFLICT. Default true.
options.capabilities.migrationLock: let the migration runner take its lock. Default false.

savepoints and transactionalDdl are always false. On the returned driver:

executeScript(sql) splits a multi-statement script and runs it one statement at a time. A semicolon inside a string literal, a quoted identifier, a comment or a CREATE TRIGGER body stays in its statement, and a script with an unclosed one throws, naming it and its line, before any statement runs.
beginTransaction() and every commit, rollback and savepoint method reject, pointing to ctx.storage.transactionSync().
wipe() rejects; reset an object's data by migrating down or deleting its storage.
insertId is reported for tables with a single-column primary key, from the RETURNING row or else from last_insert_rowid().

Pattern: Apply A Schema When The Object Boots

An object's database starts empty, so the object applies its schema before the first query. executeScript takes the whole script, comments and triggers included.

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

let SCHEMA = `
	-- Keyset pages read (created_at, id); keep both in the index.
	CREATE TABLE IF NOT EXISTS events (id TEXT PRIMARY KEY, label TEXT, created_at TEXT);
	CREATE INDEX IF NOT EXISTS events_created ON events (created_at, id);
	CREATE TABLE IF NOT EXISTS totals (id INTEGER PRIMARY KEY, n INTEGER NOT NULL DEFAULT 0);
	CREATE TRIGGER IF NOT EXISTS count_events AFTER INSERT ON events BEGIN
		UPDATE totals SET n = CASE WHEN n < 1000 THEN n + 1 ELSE n END WHERE id = 1;
	END;
	INSERT OR IGNORE INTO totals (id, n) VALUES (1, 0)
`;

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

	constructor(ctx: DurableObjectState, env: Env) {
		super(ctx, env);
		ctx.blockConcurrencyWhile(() => this.#adapter.executeScript(SCHEMA));
	}
}

With transactionalDdl off, the remix/data-table migration runner applies each migration without a transaction and refuses one that declares it needs transactional DDL.

Pattern: Keep A Multi-Step Write Atomic

Gather everything a write needs before the first write, so no await on network I/O falls between two of them. Use transactionSync when the same unit must also cover key-value storage.

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

import { orders, stock } from "./schema.js";

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

	async order(sku: string, quantity: number) {
		let price = await fetch(`https://prices.example.com/${sku}`).then((r) => r.json());

		await this.#db.create(orders, { sku, quantity, total: price.amount * quantity });
		await this.#db.update(stock, sku, { reserved: quantity });
	}

	markShipped(orderId: string) {
		this.ctx.storage.transactionSync(() => {
			this.ctx.storage.sql.exec("UPDATE orders SET shipped = 1 WHERE id = ?", orderId);
			this.ctx.storage.kv.put(`shipped:${orderId}`, Date.now());
		});
	}
}

transactionSync takes a synchronous callback, so async work runs before or after it.

Pattern: Host A Storage-Agnostic Library In A Durable Object

A library that takes a DatabaseDriver runs wherever a driver can be built, so each object keeps its own data.

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

import { createEngine } from "./engine.js";

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

The same library runs on Cloudflare D1 with the driver from @sdxc/data-table-d1; its models, queries and migrations stay the same.