Database migrations

Valcraven's database is SQLite through Drizzle ORM in local dev and Docker, and Cloudflare D1 in production (the same SQLite dialect, so your schema and queries are identical). Adding a table means two things that stay in lockstep: the Drizzle schema (so getDb() gives you typed queries) and a numbered SQL migration (the DDL that actually creates the table). This page walks that workflow.

For how the DB layer resolves the right dialect at runtime, see Data layer. For applying migrations as part of a deploy, see Deployment → Migrations.

Where the schema lives

The schema is split by dialect and re-exported through a barrel so your imports never change:

• lib/schema.sqlite.ts — the SQLite/D1 table definitions (the primary/canonical dialect).
• lib/schema.pg.ts — the Postgres equivalents (only if you deploy on Postgres).
• lib/schema.ts — the barrel: it re-exports every table so app code does import { items } from "@/lib/schema" regardless of dialect.

Step 1 — Add the table to the schema

Define the table in lib/schema.sqlite.ts, following the example items table. The user_id column references user(id):

export const reports = sqliteTable("reports", {
  id: text("id").primaryKey(),
  user_id: text("user_id").notNull().references(() => user.id),
  title: text("title").notNull(),
  created_at: text("created_at").default(sql`CURRENT_TIMESTAMP`),
}, (table) => [
  index("idx_reports_user_id").on(table.user_id),
]);

Then re-export it from the barrel lib/schema.ts:

export const reports = mod.reports;

If you deploy on Postgres, add the equivalent table to lib/schema.pg.ts too.

Foreign keys reference user(id) — singular. Better Auth names its accounts table user, not users. A FK to users(id) will fail. This is the single most common migration mistake.

Scaffold the DDL (optional)

npm run db:generate runs drizzle-kit generate, which diffs your schema files and emits the SQL under drizzle/. It's a handy way to get the exact CREATE TABLE statement — copy/adapt it into your numbered migration in the next step. You can also browse the database with npm run db:studio (Drizzle Studio).

Step 2 — Write a numbered migration

Migrations are numbered SQL files in migrations/ (SQLite/D1) — and migrations-pg/ for Postgres. Create the next number in sequence:

-- migrations/026_create_reports.sql
-- UP
CREATE TABLE IF NOT EXISTS reports (
  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_reports_user_id ON reports(user_id);

Conventions to follow (they're load-bearing):

• Number sequentially with a zero-padded prefix (026_…) and a descriptive name. The migration runner applies files in filename order and records each in the _migrations tracking table, so a migration runs exactly once.
• Use IF NOT EXISTS on tables and indexes — migrations must be safe to re-run.
• Omit the -- DOWN section. Cloudflare D1 applies each file as raw SQL and does not parse the UP/DOWN marker, so a -- DOWN block containing DROP statements would execute on D1 and immediately undo the migration. New migrations are forward-only; to reverse something, write a new numbered migration.
• Reference user(id), matching the schema.

Step 3 — Apply it locally

npm run db:migrate

This runs scripts/migrate.ts, which applies every pending file in migrations/ (or migrations-pg/ when DATABASE_URL points at Postgres) and records them in _migrations. Re-running is a no-op once everything is applied. In Docker, run it inside the app container.

Step 4 — Ship it to production (Cloudflare D1)

D1 is not migrated by npm run db:migrate. On Workers the database is async and the app cold-starts constantly, so migrations are applied at deploy time with Wrangler:

wrangler d1 migrations apply <DATABASE_NAME>

This is part of the deploy runbook — see Deployment → Migrations for the exact commands and the local-vs-remote flags. If a D1 deploy starts erroring with no such table: …, the migrations weren't applied — re-run the apply against the target database.

Recap

Related

• Data layer — getDb() / getRawDb() and how the dialect is chosen.
• D1 dev/prod parity — SQLite-vs-D1 differences worth knowing before you ship.
• Deployment → Migrations — running migrations against D1 in a deploy.
• Add a feature — where the schema step fits end to end.