Database Migrations

Database Migrations

A migration is a versioned, ordered script that describes a change to your database schema. Migrations let your team evolve the database schema safely — every change is tracked in version control and can be applied or rolled back deterministically across development, staging, and production environments.

The Core Principle

Instead of making ad-hoc changes to a production database, you write a migration file that describes the change. A migration tool applies pending migrations in order and records which migrations have run in a tracking table (often called schema_migrations or _drizzle_migrations).

• Never alter a production schema by hand in psql. Always write a migration.
• Migrations are append-only — never edit a migration that has already been applied.
• Each migration should be idempotent where possible.
• Include a down migration to reverse the change when the tool supports it.
• Test migrations on a staging environment before running on production.

Popular Migration Tools in the Node Ecosystem

• Drizzle Kit (drizzle-kit push / drizzle-kit generate) — generates SQL migrations from your Drizzle schema. What CPCODELAB uses.
• Prisma Migrate — similar concept, tightly coupled to the Prisma ORM.
• node-pg-migrate — framework-agnostic, plain SQL migration files.
• Flyway / Liquibase — Java-based but polyglot; popular in enterprise environments.

# Drizzle Kit workflow (CPCODELAB standard)

# 1. Define or change your schema in TypeScript (src/db/schema.ts)
# 2. Generate a migration SQL file
npx drizzle-kit generate

# 3. Review the generated SQL in drizzle/migrations/

# 4. Apply migrations
npx drizzle-kit migrate

# Alternatively, push schema changes directly (dev only)
npx drizzle-kit push

Safe Migration Patterns

• Adding a nullable column — safe, no table rewrite needed.
• Adding a NOT NULL column with a DEFAULT — safe in Postgres 11+ (no table rewrite for constant defaults).
• Renaming a column — requires application code to support both names during the rollout.
• Dropping a column — first deploy the app code that stops using it, then drop.
• Adding an index — use CREATE INDEX CONCURRENTLY to avoid locking the table.
• Changing a column type — potentially expensive; may require a multi-step migration.