pn-database-migrations

Database migrations

When to use

• Adding, altering, or dropping columns/tables in a production database
• Writing or reviewing a migration file (Prisma, Drizzle, TypeORM, raw SQL)
• Planning zero-downtime deployments that involve schema changes
• Seeding reference data or test fixtures
• Reviewing index coverage or query performance regressions

Core principles

1. Migrations are immutable once committed — never edit a migration file that has been applied to any shared environment; create a new migration instead.
2. Always pair with a rollback — every up migration must have a safe down path; document when down is destructive (irreversible data loss).
3. Expand/contract for zero downtime — break breaking changes into two migrations: (a) add new column/table (expand), (b) backfill + switch app code, (c) drop old column (contract). Never rename or drop in one step.
4. Gate on review — migrations that touch > 10M rows or add a non-concurrent index require explicit review before deploying to production.
5. Never mix data and schema — separate DDL migrations (schema) from DML migrations (data backfills) to allow independent rollback.

Workflow

Prisma

# Generate migration from schema changes
npx prisma migrate dev --name add_user_email_index

# Apply pending migrations in production (CI/CD)
npx prisma migrate deploy

# Inspect current migration status
npx prisma migrate status

# Reset dev database (destructive — dev only)
npx prisma migrate reset

• Store migrations/ directory in version control; commit with the code change that requires it.
• Use prisma db seed with prisma/seed.ts for reference data; keep seed idempotent (upsert, not insert).
• For large table changes, use --create-only flag, then edit the SQL before applying.

Drizzle

# Generate migration
npx drizzle-kit generate:pg   # or :mysql, :sqlite

# Apply migrations
npx drizzle-kit push:pg       # dev: push directly
# Production: use drizzle-orm/node-postgres migrator in startup script