@sdxc/data-table-d1

A remix/data-table DatabaseDriver backed by Cloudflare D1.

remix/data-table models reach a database through a DatabaseDriver. This package implements that interface over a Cloudflare D1 binding, compiling each operation to SQLite-dialect SQL and normalizing the response shapes D1 returns, so models, queries and raw statements run inside a Worker.

Installation

npm add @sdxc/data-table-d1

The driver is built for remix, which supplies the Database, table and column values every example here uses, and it installs alongside this package.

Usage

Query A Table

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

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

export default {
	async fetch(request, env) {
		let db = new Database(createD1DatabaseAdapter(env.DB));
		return Response.json(await db.findMany(users));
	},
} satisfies ExportedHandler<Env>;

c.json() columns are stringified on the way in and parsed on the way back, and c.boolean() columns read back as true, false or null rather than SQLite's 1 and 0 — on a select and on a RETURNING row alike.

Run A Raw Statement

let db = new Database(createD1DatabaseAdapter(env.DB));

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

let write = await db.exec("DELETE FROM users WHERE id = ? RETURNING id, email", [2]);
write.rows; // the deleted rows
write.affectedRows; // 1

The SQL text decides how the statement is read back: one that starts with SELECT, WITH or PRAGMA yields rows, and so does any statement carrying a RETURNING clause. Every other write reports affectedRows alone.

Observe What Each Statement Costs

let db = new Database(
	createD1DatabaseAdapter(env.DB, {
		onStatement({ kind, table, rowsRead, rowsWritten, durationMs }) {
			console.log(kind, table, rowsRead, rowsWritten, durationMs);
		},
	}),
);

The numbers come from the meta D1 already returns with every response, so observing them costs no extra statement and no extra billable operation.

API

createD1DatabaseAdapter(db: D1Database, options?): DatabaseDriver

Builds the driver you hand to new Database(...). db is the D1 binding to execute against, such as env.DB.

options:

• capabilities: Overrides for the driver's feature flags, as a Partial<DatabaseCapabilities> from remix/data-table. By default returning, upsert and transactionalDdl are true, and savepoints and migrationLock are false.

• onStatement: A D1StatementObserver called once per executed statement.

D1StatementObserver

The function onStatement takes: it receives one D1StatementObservation and returns nothing. It runs on the hot path, once per statement, so keep it cheap; anything it throws is swallowed rather than failing the statement it was measuring.

D1StatementObservation

What one executed statement cost, as D1 itself reported it.

interface D1StatementObservation {
	/** Operation kind the statement came from (`select`, `insert`, `raw`, …). */
	kind: DataManipulationOperation["kind"];
	/** Table the operation targets, or `undefined` for a raw statement. */
	table: string | undefined;
	/** Rows D1 read from tables and indexes, 0 when unreported. */
	rowsRead: number;
	/** Rows D1 wrote to tables and indexes, 0 when unreported. */
	rowsWritten: number;
	/** Milliseconds D1 reports for the statement, 0 when unreported. */
	durationMs: number;
}

A statement that throws is not reported, since D1 returns no meta for it, and neither are the driver's own schema probes. A count is 0 whenever D1 omits the matching meta field, rather than being estimated.

Transactions

D1 commits each statement on its own, the moment it runs. db.transaction(...) therefore gives a scope rather than atomicity: a statement that succeeded before a later one threw stays in the database. That immediacy is also what makes create and update able to return their RETURNING row inside the callback.

Express an all-or-nothing write as a single SQL statement — createMany(), one UPDATE/DELETE, or an upsert — and see Pattern: Making A Multi-Row Write Atomic. For cross-statement atomicity, a Durable Object's SQLite storage supports it, through @sdxc/data-table-sqlstorage.

Schema And Unsupported Operations

executeScript(sql) runs a DDL script, which covers applying a schema at boot; wrangler d1 migrations apply owns versioned schema changes. Savepoints and wipe() throw, and close() is a no-op, since the Worker runtime owns the binding's lifetime.

Pattern: Making A Multi-Row Write Atomic

One statement is the unit D1 commits, so a write that must land completely is written as one statement. createMany() compiles to a single multi-row INSERT:

await db.createMany(members, [
	{ id: 1, team: "core" },
	{ id: 2, team: "core" },
]);

A raw write with RETURNING extends that to work a queue claims, because the same statement both moves the rows and reports exactly which ones this caller won, with no read-then-write race:

let claimed = await db.exec("UPDATE jobs SET run_at = run_at + ? WHERE run_at <= ? RETURNING id", [
	interval,
	now,
]);

The typed builder cannot express this, since its changes are bound values rather than SQL expressions. affectedRows reads the same with the clause as without it.

Pattern: Attributing Rows Read And Written Per Query

Cloudflare's analytics report usage per database. onStatement reports it per statement, so an app can attribute row counts to the query or unit of work that caused them:

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

let usage = { statements: 0, rowsRead: 0, rowsWritten: 0 };

let db = new Database(
	createD1DatabaseAdapter(env.DB, {
		onStatement({ rowsRead, rowsWritten }) {
			usage.statements += 1;
			usage.rowsRead += rowsRead;
			usage.rowsWritten += rowsWritten;
		},
	}),
);

Accumulating into a per-request object and logging it once at the end turns the same hook into a cost breakdown per endpoint, which is what a cost regression needs to be found.

Pattern: Reusing The Driver Across Requests

The binding is stable for the lifetime of an isolate, so build the driver once and let every request on that isolate share it:

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

let db: Database | null = null;

export default {
	async fetch(request, env) {
		db ??= new Database(createD1DatabaseAdapter(env.DB));
		return Response.json(await db.findMany(users));
	},
} satisfies ExportedHandler<Env>;

Pattern: Handing The Driver To A Host-Agnostic Library

A library that takes a DatabaseDriver rather than opening its own connection runs wherever the host can supply one. The host builds the driver and injects it, and the library sees only the interface:

import { createD1DatabaseAdapter } from "@sdxc/data-table-d1";

let engine = createEngine({ database: createD1DatabaseAdapter(env.DB) });

Swapping the host means swapping that one line, so the same library code runs on D1 in a Worker and on another SQLite-backed driver elsewhere.