Migrations basics

Migrations are versioned schema change scripts applied in order across dev, staging, and production. Frameworks like Django migrations and Laravel/PHP tools generate SQL from model changes.

Why migrations

• Reproducible schema across teammates and CI
• Reviewable DDL in pull requests
• Rollback or forward-fix strategies documented

Up and down

-- up.sql
ALTER TABLE products ADD COLUMN archived INTEGER DEFAULT 0;

-- down.sql (if supported)
-- ALTER TABLE products DROP COLUMN archived;

Not every change reverses cleanly—backup before destructive migrations in production.

Safe migration habits

1. Add nullable columns before backfill
2. Backfill data in batches
3. Add NOT NULL and constraints after data is valid
4. Create indexes concurrently on large Postgres tables (dialect-specific)

Important interview questions and answers

1. Q: Migration vs raw SQL file?
   A: Migrations track applied versions in a metadata table; ad-hoc scripts lack ordering guarantees.
2. Q: Zero-downtime deploy?
   A: Expand-contract pattern: add column, dual-write, backfill, switch reads, remove old.

Self-check

1. Why not ALTER production by hand?
2. What is a common order for adding a NOT NULL column to a populated table?

Tip: Add nullable columns first, backfill, then add NOT NULL in a follow-up migration.