Data layer (D1 + Drizzle)

Valcraven's data layer is Drizzle ORM over SQLite, and in production that SQLite is Cloudflare D1. The clever part is that the same application code runs against three dialects — D1 (production on Workers), local SQLite (better-sqlite3, for dev), and PostgreSQL — chosen at runtime by environment. All of this lives in apps/web/lib (db.ts, db-dialect.ts, db-raw.ts, schema.ts).

Dialect selection

lib/db-dialect.ts picks the dialect from the environment, in this order:

D1 is SQLite-shaped at the schema layer but exposed via a Workers binding, not a connection string — so it gets its own opt-in DATABASE_DRIVER env var rather than a URL. isPg() and isD1() are the helpers the rest of the code branches on.

getDb() — the Drizzle instance (use this)

getDb() from @/lib/db returns a Drizzle ORM instance and is the primary way to read and write. It works on all three dialects — Drizzle generates dialect-correct SQL at runtime — so your query code is portable:

import { getDb } from "@/lib/db";
import { user } from "@/lib/schema";
import { eq } from "drizzle-orm";

const db = getDb();
const rows = await db.select().from(user).where(eq(user.id, userId));

Import table objects from the schema barrel (@/lib/schema); scope every query to the session user (see Request lifecycle).

getRawDb() — raw SQLite (dev/SQLite only)

getRawDb() returns the raw better-sqlite3 connection for SQLite-specific things Drizzle can't express — PRAGMA, sqlite_master, FTS5. It throws on Postgres and D1, because those drivers don't expose the synchronous better-sqlite3 API (D1's prepared statements are async). If you need raw SQL that must also work in production on D1, use getRawAdapter() instead.

getRawAdapter() — dialect-agnostic raw SQL

getRawAdapter() from @/lib/db-raw returns a RawDbAdapter — a small async interface (queryAll, queryFirst, run, listTables, getTableColumns, searchItems, transaction, …) with a concrete implementation per dialect (db-raw-sqlite.ts, db-raw-pg.ts, db-raw-d1.ts). This is what powers the admin database browser and search in production on D1, where getRawDb() can't run:

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

const adapter = getRawAdapter();
const rows = await adapter.queryAll("SELECT * FROM items WHERE user_id = ?", userId);

D1 gotcha: D1 keeps its own internal _cf_* tables (and the usual sqlite_* ones). The D1 adapter excludes them from the table browser because counting rows in them raises SQLITE_AUTH (not authorized). If you write your own raw D1 queries against system tables, expect the same restriction.

Quick reference:

The schema

lib/schema.ts is a barrel that re-exports every table (user, session, account, items, notifications, apiKeys, …). It conditionally sources from schema.sqlite.ts or schema.pg.ts based on the active dialect; the D1 schema is structurally identical to SQLite and is re-exported via schema.d1.ts. TypeScript types come from the SQLite schema (the canonical dialect) — consumer imports never change:

import { items, user, notifications } from "@/lib/schema";

Add new tables to the dialect schema files (and to the barrel's re-exports), then create a migration.

Migrations

Schema changes are numbered SQL migration files in apps/web/migrations/ (e.g. 001_create_items.sql, 025_create_api_keys.sql), each with -- UP and -- DOWN sections. The canonical shape (from AGENTS.md):

-- migrations/026_create_widgets.sql
-- UP
CREATE TABLE IF NOT EXISTS widgets (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_widgets_user_id ON widgets(user_id);

-- DOWN
DROP TABLE IF EXISTS widgets;

Commands (run from the repo root):

npm run db:migrate     # apply pending migrations (dev / local SQLite)
npm run db:generate    # drizzle-kit generate (Drizzle migration artifacts)

• Local dev applies migrations via db:migrate (and lib/db.ts also bootstraps the Better Auth schema on first SQLite start).
• On D1, migrations are not applied at runtime — Workers cold-start too often and D1 is async-only. They are pre-applied at deploy time via wrangler d1 migrations apply (the migrations_dir = "migrations" in wrangler.toml points wrangler at the same files). If a D1 deploy starts erroring with "no such table", the migrations weren't applied. See Database migrations and Add a migration.

Foreign keys reference user(id) — singular. Better Auth names its users table user, not users. Every user-owned table foreign-keys to user(id), which is what makes the schema multi-tenant by default.

How the D1 binding is resolved

On Workers the D1 database is a request-scoped binding (env.DB), not a process-level pool. lib/db.ts resolves it via resolveD1Binding(), which reads getCloudflareContext().env.DB (from @opennextjs/cloudflare) — no manual setup needed; the adapter populates the context on every Worker request. setD1Binding() exists as a test seam for injecting a mock binding. Because the binding is per request, the Drizzle instance (and the Better Auth instance) are built lazily on first use rather than at module load — see Request lifecycle for why auth is a Proxy for the same reason.