Query D1 and Durable Object SQL

remix/data-table gives you tables, typed queries and write helpers, and leaves the connection to a driver. Cloudflare has two SQLite databases a Worker can reach: D1, a database bound to the Worker, and the SQL storage inside a SQLite-backed Durable Object. There is a driver for each: @sdxc/data-table-d1 and @sdxc/data-table-sqlstorage.

This guide opens a database over D1, publishes it on the request context, covers the writes D1 can commit safely, moves the same models into a Durable Object, and tests all of it against real SQLite with @sdxc/cloudflare-mocks. Declaring tables and writing queries is remix/data-table's own API, and its documentation covers it; everything here works with whatever tables you already have.

npm add @sdxc/data-table-d1 @sdxc/data-table-sqlstorage remix
npm add -D @sdxc/cloudflare-mocks

What the drivers add to your tables

Your tables and queries stay exactly as remix/data-table defines them. What a driver decides is how values cross into SQLite, which has no native boolean or JSON type. Both drivers bridge them: a c.boolean() column reads back as true or false, and a c.json() value is serialized on the way in and parsed on the way out, on a plain read and on a RETURNING row alike.

Open the database over D1

createD1DatabaseAdapter takes the D1 binding and returns the driver new Database(...) expects:

import { createD1DatabaseAdapter } from "@sdxc/data-table-d1";
import { env } from "cloudflare:workers";
import { Database } from "remix/data-table";

export function openDatabase(): Database {
	return new Database(createD1DatabaseAdapter(env.DB));
}

The adapter also takes an onStatement observer, called once per statement with the rows read, rows written and duration D1 already reports. Summing those per request is how you find the endpoint whose query plan changed.

Publish it on ctx.db

Handlers should read the database off the context rather than open it themselves. That keeps the binding out of your controllers, and it gives a test one place to hand in its own database:

import type { Database as DataTable } from "remix/data-table";
import type { Middleware } from "remix/router";

import { createContextKey } from "remix/router";

export const Database = createContextKey<DataTable>();

declare module "remix/router" {
	interface RequestContext {
		db: DataTable;
	}
}

export default function database(source: () => DataTable): Middleware {
	return (ctx, next) => {
		ctx.set(Database, source(), { property: "db" });
		return next();
	};
}

Register it as database(openDatabase) in the router's middleware list. The property option installs the value as ctx.db, and the declare module block is what types it everywhere. A handler then passes ctx.db to its queries, and a query written against Database runs unchanged on either driver.

Write what D1 can commit

D1 commits every statement on its own, the moment it runs. db.transaction(...) on D1 gives you a scope, not atomicity: if the third statement throws, the first two are already in the database. So write every change that must land completely as a single statement.

The D1 driver compiles createMany() to one multi-row INSERT, updateMany() and deleteMany() to one UPDATE or DELETE, and a query-builder upsert to one INSERT … ON CONFLICT … DO UPDATE, so each is atomic on its own. Reach for an upsert instead of a read followed by a write, which two concurrent requests can both win. When the change is a SQL expression the builder cannot bind, such as moving a timestamp forward, write it raw with a RETURNING clause:

import type { Database } from "remix/data-table";

export async function claimDueReminders(db: Database, intervalMs: number) {
	let claimed = await db.exec(
		"UPDATE reminders SET run_at = run_at + ? WHERE run_at <= ? RETURNING id",
		[intervalMs, Date.now()],
	);
	return claimed.rows;
}

The same statement moves the rows and reports exactly which ones this caller won, with no gap between the read and the write for another request to land in.

Move the models into a Durable Object

A SQLite-backed Durable Object exposes its database as ctx.storage.sql. Build the driver once per instance, since the handle outlives every request the object serves:

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

import { postings } from "~/database/schema";

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

	async listPostings() {
		return await this.#db.findMany(postings);
	}
}

postings is one of your own tables. Your tables and queries do not change, because they only ever saw a Database. What changes is how atomicity works. SQL storage rejects BEGIN and COMMIT, so db.transaction() rejects here. Instead, every write an object makes within one turn of its event loop commits together. Two create calls in a row are atomic; the same two calls with a fetch between them are not, because awaiting I/O ends the turn. Gather what a write needs before the first write.

Apply the schema from the object itself, since its database exists only once the object runs. The adapter's executeScript takes a whole migration file and runs it one statement at a time.

Test against real SQL

createD1Database() from @sdxc/cloudflare-mocks is a D1 binding over an in-memory SQLite database. It executes real SQL and autocommits each statement the way D1 does, so a malformed query or an unbound value fails in the test instead of after a deploy. Apply the migration you ship and hand the database to the router through the same source the middleware takes:

import { readFileSync } from "node:fs";

import { createD1Database } from "@sdxc/cloudflare-mocks";
import { createD1DatabaseAdapter } from "@sdxc/data-table-d1";
import { Database } from "remix/data-table";

const MIGRATION = readFileSync(
	new URL("../../database/migrations/0001-init.sql", import.meta.url),
	"utf8",
);

export async function createTestDatabase(): Promise<Database> {
	let binding = createD1Database();
	await binding.exec(MIGRATION.replaceAll("\n", " "));
	return new Database(createD1DatabaseAdapter(binding));
}

D1's own exec reads each line as a statement, so flattening the file keeps one migration valid for both. A Durable Object's database is tested the same way with createSqlStorage(), which also throws on BEGIN exactly as the platform does. Both run on Bun and Node: the package picks whichever built-in SQLite module the runtime has.

Where to go next

• Wire the router: middleware, context and services — how ctx.db sits among the rest of the middleware.

• Background jobs and cron — run a nightly sweep against the same database.

• Cache on Cloudflare Workers — keep a hot query out of D1 entirely.

• Test Workers apps — drive the whole router against the test database.

• remix/data-table — declaring tables, queries, upserts and migrations.