D1 dev/prod parity

Valcraven runs on better-sqlite3 in local dev and Docker, but deploys to Cloudflare D1 in production (with Postgres as a third supported target). Drizzle ORM papers over most of the difference, so typed queries via getDb() behave the same everywhere. The gaps are in the raw-SQL layer and in driver-specific result shapes — and because your dev database is SQLite, these gaps produce bugs that never appear locally and only surface once you deploy to D1. This is the single most important parity class to understand before shipping.

The meta-lesson: a bug that runs fine against better-sqlite3 in dev can be silently broken on D1 in production. Any code path that touches raw SQL, reads an affected-row count, or lists tables deserves a check against D1 behavior — ideally an integration test that covers all three drivers. Batching many days of changes into one production deploy is exactly how these latent parity bugs surface all at once.

The dialect is selected at runtime by the DATABASE_DRIVER environment variable (sqlite | pg | d1), resolved in lib/db-dialect.ts via getDialect() / isPg() / isD1(). See Cloudflare Workers architecture and Data layer for the surrounding picture.

getRawDb() throws on D1 — use getRawAdapter()

getRawDb() returns a synchronous better-sqlite3 handle. That API does not exist on Workers/D1 (D1 prepared statements are async), so getRawDb() throws on D1:

getRawDb() is not available when using D1. D1 prepared statements are async —
use the D1 binding directly via getD1Binding().

Because dev is SQLite, getRawDb() works fine locally — then 500s in production on every raw-SQL path (admin user lists, the database browser, analytics, audit log, search, waitlist, …). The core app is unaffected because it goes through Drizzle getDb(), which D1 handles natively.

The fix is to never call getRawDb() on a path that runs in production. For raw SQL that must work across all three dialects, use the dialect-agnostic adapter:

import { getRawAdapter } from "@/lib/db-raw";

const adapter = getRawAdapter();          // sqlite | pg | d1 impl, chosen at runtime
const rows = await adapter.queryAll(sql, ...params);

getRawAdapter() (lib/db-raw.ts) dispatches to createSqliteRawAdapter, createPgRawAdapter, or createD1RawAdapter based on the dialect and exposes an async interface (queryAll, queryFirst, run, listTables, getTableColumns, searchItems, transaction, …). If you genuinely need the D1 binding itself, it's exposed via getD1Binding() / resolveD1Binding() in lib/db.ts. Reserve getRawDb() for genuinely SQLite-only, dev-only tooling.

Affected-row counts live in a different place on each driver

This one is subtle and it broke every compare-and-swap in the app when it hit production. The count of rows affected by an UPDATE/DELETE is at a different property on each driver:

• better-sqlite3 (dev/Docker) → result.changes
• Cloudflare D1 (prod) → result.meta.changes (the raw D1Result)
• node-postgres → result.rowCount

Code that only read result.changes returned 0 for every mutation on D1 — even when rows were changed. Any logic gated on "did this update affect a row?" (optimistic locking / compare-and-swap, "claim this job", "only transition from draft") then silently failed: the update succeeded in the database but the code believed nothing changed.

The template centralizes this in executeChanges() (lib/db-helpers.ts), which checks all three shapes:

import { executeChanges } from "@/lib/db-helpers";

// Returns the affected-row count correctly on sqlite, D1, AND pg.
const changed = await executeChanges(
  db.update(campaigns).set({ status: "sending" }).where(/* CAS condition */)
);
if (changed === 0) {
  // the CAS lost the race — handle it
}

Whenever you rely on an affected-row count, go through executeChanges() rather than reading .changes directly — otherwise your feature works in dev and breaks the moment it runs on D1. There's a regression test at apps/web/tests/db-helpers.test.ts.

D1 restricts its internal _cf_* tables

D1 databases carry internal bookkeeping tables named _cf_* (e.g. _cf_KV, _cf_METADATA). Querying them raises an authorization error:

D1_ERROR: not authorized (SQLITE_AUTH)

So any table-enumeration code — a "list all tables" admin view, a schema browser, a per-table row-count loop — must exclude both sqlite_% and _cf_%. The template's D1 adapter (lib/db-raw-d1.ts) does exactly this:

SELECT name FROM sqlite_master WHERE type='table'
  AND name NOT LIKE 'sqlite_%'
  AND name NOT LIKE '_cf_%'
ORDER BY name

Notes for anything that walks the D1 schema:

• The d1_migrations table is accessible (it's not a _cf_* table).
• PRAGMA table_info("<table>") works on D1 via .prepare().all().
• On plain SQLite in dev, _cf_* tables don't exist, so this exclusion is a no-op — which is exactly why it's easy to forget and only fails on D1.

The multi-dialect schema

The schema is defined per dialect and selected at runtime. lib/schema.ts is a barrel that re-exports from schema.sqlite.ts or schema.pg.ts based on getDialect():

// Consumers never change their imports:
import { user, items } from "@/lib/schema";

The TypeScript types come from the SQLite schema (the primary dialect), but the actual runtime objects carry dialect-specific metadata so Drizzle generates dialect-correct SQL. D1 uses the SQLite schema definitions (D1 is SQLite-compatible), so the same column/table names apply. Practical implications:

• When you add a column or table, add it to both schema.sqlite.ts and schema.pg.ts (keep names identical) so every deploy target stays in sync.
• Author migrations so they apply cleanly on your production target. See Database migrations and Deployment → migrations.

How to catch parity bugs before production

• Prefer getDb() (Drizzle) and executeChanges() — the abstractions that already handle all three dialects — over raw driver access.
• Write integration tests that assert the count logic, not just the happy path (the db-helpers regression test is the model).
• Deploy incrementally. A large batch release surfaces every latent parity bug at once and makes the culprit hard to isolate; smaller releases keep the blast radius small.
• When a raw-SQL admin feature works in dev, treat that as unverified for prod until it's run against D1. Dev's better-sqlite3 will not catch any of the gotchas above.