Skip to main content

Migrations

Schema-first. Generate SQL artifacts, review, commit, apply.

The cardinal rule​

Never run drizzle-kit push against any non-local DB.

push compares the schema to the target DB and applies the diff with no SQL artifact. It's the canonical horror story for prod data loss. Always go through generate → review → migrate.

Workflow​

# 1. Edit schema
$EDITOR src/db/schema/business.ts

# 2. Generate SQL
pnpm db:generate --name add_my_column
# → drizzle/0017_add_my_column.sql

# 3. REVIEW THE GENERATED FILE
$EDITOR drizzle/0017_add_my_column.sql

# 4. Commit
git add src/db/schema/ drizzle/
git commit -m "Add my_column to agents"

# 5. Apply
pnpm db:migrate            # dev (uses ts-node)
pnpm db:migrate:prod       # prod (compiled, in Docker)

Auto-generated auth schema​

Don't hand-edit src/db/schema/auth.ts. After upgrading better-auth or a plugin:

pnpm db:auth-schema       # runs @better-auth/cli generate
pnpm db:generate --name auth_upgrade
# review + migrate

The auth.ts file has a frontmatter comment saying this. If a PR touches it by hand, reject.

What generate emits​

drizzle-kit generate produces:

  • drizzle/NNNN_<slug>.sql — the migration SQL.
  • drizzle/meta/_journal.json — registry of applied migrations.
  • drizzle/meta/NNNN_snapshot.json — schema snapshot for diffing.

Each statement is separated by --> statement-breakpoint so the migrator can run them one at a time.

Rare patterns​

Drizzle handles most cases automatically. For the rare cases (partial indexes, CHECK constraints, GENERATED columns), express them in TypeScript with sql\...``:

.where(sql`status IN ('active', 'trialing')`)

These come through generate correctly.

Standalone migrator​

src/db/migrate.ts is a standalone Node script:

  • Loads .env itself.
  • Resolves drizzle/ via process.cwd().
  • Works under both ts-node (dev) and node dist/src/db/migrate.js (prod).

docker-entrypoint.sh runs it on every container start. Migrations are tracked in __drizzle_migrations; re-running an applied migration is a no-op.

Rollback​

There is no automatic down migration. If you need to undo:

  1. Write a new migration that reverses the change.
  2. Generate and apply it like any forward migration.

This is intentional. Drizzle's stance is "down migrations are dangerous in production"; just write a fix-forward migration.

Common patterns​

Add a non-null column with default​

isArchived: boolean("is_archived").notNull().default(false)

Generates ALTER TABLE ... ADD COLUMN is_archived BOOLEAN NOT NULL DEFAULT false; — safe on existing rows.

Add a non-null column with no default​

Two-step migration:

  1. Add as nullable.
  2. Backfill in a follow-up migration.
  3. ALTER to set NOT NULL once backfill is verified.

Rename a column​

Drizzle treats column renames as DROP + ADD by default. Use .rename or write a manual ALTER TABLE ... RENAME COLUMN ... migration to preserve data.

Add a unique constraint​

uniqueIndex("agent_pricing_tiers_unique_active")
  .on(table.agentId, table.tierName)

Verify before deploy​

pnpm db:migrate:dry         # if you've added a dry-run flag
psql -1 -f drizzle/0017_add_my_column.sql $DATABASE_URL   # one-shot transaction

Drizzle Studio​

pnpm db:studio              # opens at https://local.drizzle.studio

Useful for inspecting rows and verifying migration effects without dropping into psql.