Schema migrations (shared SQLite DB)

The shared DB's schema lives in src/lib/db/schema.ts; bun run db:generate (drizzle-kit) writes a SQL migration to drizzle/, and scripts/migrate.ts applies pending ones on deploy and in CI.

Never ship a rebuild of a referenced table

Some changes SQLite can't do in place: a column's default, NOT NULL, a check or foreign key. For those, drizzle-kit generates a table rebuild:

PRAGMA foreign_keys=OFF;
CREATE TABLE `__new_users` (...);
INSERT INTO `__new_users`(...) SELECT ... FROM `users`;
DROP TABLE `users`;
ALTER TABLE `__new_users` RENAME TO `users`;
PRAGMA foreign_keys=ON;

That looks safe, and it isn't here. Our migrator runs every pending migration inside one transaction, on a connection opened by src/lib/db/index.ts, which sets PRAGMA foreign_keys = ON. SQLite ignores PRAGMA foreign_keys inside a transaction, so the OFF does nothing, and DROP TABLE users performs an implicit DELETE FROM users that fires every ON DELETE CASCADE pointing at it: sessions, accounts, workspaces, everything. In production, on deploy.

make check-migration-cascade (part of make ci) fails on any migration that drops a table some other table references. Rebuilding a table nothing references (a leaf like a join table) is fine and passes.

What to write instead

Hand-write the migration, keep the snapshot drizzle-kit generated:

Change schema.ts, run bun run db:generate.
If the SQL is a rebuild of a referenced table, replace the SQL file's contents (keep the file name and the meta/ snapshot, which describes the schema you want) with what you actually need, usually ALTER TABLE ... ADD ... plus an UPDATE to backfill. Separate statements with --> statement-breakpoint. Say in a comment why it's hand-written.
Run bun run db:generate again: it must report no changes, which proves the snapshot still matches schema.ts.
Run the migration against a copy of a populated database (the e2e DB works) and compare row counts of the parent and its children before and after.

Changing a default without a rebuild

The SQL default can't change, so set it in the schema instead. Use $defaultFn, not .default(): when a column has both, Drizzle writes the static .default() value into every insert and $defaultFn never runs. Remove .default() from the column and add .$defaultFn(() => value). Every Drizzle insert then writes the value explicitly, including better-auth's adapter (db.insert(table).values(...)); only raw SQL that omits the column still gets the old SQL default. Note that in a comment on the column.

Worked example: runmist migration 0029_marketing_email_opt_in.sql and the marketingEmailsEnabled column in its schema.ts (2026-10-16, where the generated rebuild of users was caught before it shipped).

Genuinely dropping a referenced table

If wiping the children is the point, add a line to the migration:

-- migration-cascade-ok: <why losing the child rows is intended>

The check skips files with that marker.