The House Renovation Analogy
Building a house over time:
Without records: "Did we add the third bedroom before or after the garage?" Nobody remembers. Chaos when contractors change.
With renovation permits: Every change documented with before/after states. Any contractor can see the full history.
Database migrations are renovation permits for your database. Each migration documents a schema change with instructions to apply it (up) and reverse it (down).
Why Migrations Matter
The Problem Without Migrations
Developer 1: "I added a 'phone' column"
Developer 2: "Error! 'phone' column doesn't exist"
Developer 1: "Oh, you need to run this SQL..."
Developer 2: "Which SQL? In what order?"
Production: Even scarier!
The Solution With Migrations
Developer 1: Commits migration file 003_add_phone.py
Developer 2: Runs 'migrate' → Database updates automatically
Production: Same migrations run during deployment
Everyone's database stays aligned more reliably.
How Migrations Work
Migration History
─────────────────────────────────────────
001_create_users.py ✓ Applied
002_add_email.py ✓ Applied
003_add_phone.py ✓ Applied
004_create_orders.py ⟳ Pending
─────────────────────────────────────────
'migrate up' → Apply pending migrations
'migrate down' → Reverse last migration
Each Migration Has Two Parts
UP → Apply the change
"Add the 'phone' column"
DOWN → Reverse the change
"Remove the 'phone' column"
This makes rollbacks possible if something goes wrong.
Migration File Structure
migrations/
├── 001_create_users.sql (2024-01-01)
├── 002_add_email_to_users.sql (2024-01-15)
├── 003_add_phone_to_users.sql (2024-02-01)
├── 004_create_orders.sql (2024-02-15)
└── 005_add_user_foreign_key.sql (2024-03-01)
Numbered order → Run in sequence
Descriptive names → Know what each does
Timestamps → Track when created
Types of Schema Changes
Adding (Usually Low Risk)
✓ Add new table
✓ Add new column (nullable)
✓ Add new index
These don't break existing code.
Modifying (Careful)
⚠ Rename column → Old code still uses old name
⚠ Change data type → May lose data
⚠ Add NOT NULL → Existing nulls fail
Requires coordination with application code.
Removing (Dangerous)
⚠ Drop column → Code referencing it breaks
⚠ Drop table → All data gone
⚠ Drop index → Queries may slow down
Usually done in stages:
1. Deploy code that doesn't use column
2. Wait
3. Drop column in migration
The Migration Table
Databases track which migrations have run:
_migrations (or schema_migrations)
┌──────────────┬─────────────────────┐
│ version │ applied_at │
├──────────────┼─────────────────────┤
│ 001 │ 2024-01-01 10:00:00 │
│ 002 │ 2024-01-15 14:30:00 │
│ 003 │ 2024-02-01 09:15:00 │
└──────────────┴─────────────────────┘
Before running migration:
Check if version exists in table.
Already applied? Skip it.
Migration in Different Environments
Development → Production Pipeline
Local DB │ CI DB │ Staging DB │ Prod DB
───────────────────────────────────────────────────────────
Run migrations │ Run in tests │ Verify works │ Deploy
quickly │ Catch errors │ Final check │ Same migrations
───────────────────────────────────────────────────────────
Same migration files run everywhere.
Environments stay in sync.
Popular Migration Tools
| Language | Tool | Notable Feature |
|---|---|---|
| Python | Alembic | Auto-generates changes |
| Python | Django Migrations | ORM-integrated |
| JavaScript | Prisma Migrate | TypeScript-first |
| JavaScript | Knex | Query builder + migrate |
| Java | Flyway | SQL-first approach |
| Java | Liquibase | XML/YAML/JSON/SQL |
| Ruby | Rails Migrations | Convention over config |
| Go | golang-migrate | Database-agnostic |
Best Practices
1. Avoid Editing Past Migrations
Migration 003 already ran in production.
Found a bug in it?
Wrong: Edit migration 003
Right: Create migration 004 to fix
Past migrations are history. Don't rewrite history.
2. One Change Per Migration
Bad:
"003_add_phone_and_create_orders_and_modify_users.sql"
Good:
"003_add_phone_to_users.sql"
"004_create_orders_table.sql"
"005_modify_user_status.sql"
Smaller = easier to debug, review, rollback.
3. Test Down Migrations
up → down → up should result in same state.
If you can't go down:
- Rollback isn't possible
- Deployments are scarier
4. Make Changes Backward Compatible
Deploying new code and migrations together?
Old code might run during transition.
Add columns as nullable first.
Keep old columns until code is updated.
Common Mistakes
1. Running Migrations Out of Order
Developer A: Created migration 005
Developer B: Created migration 005 (conflict!)
Solution: Use timestamps as prefixes
20240301_120000_add_phone.sql
2. Forgetting Down Migrations
Deploy breaks something.
Need to rollback.
No down migration!
Manual fix required.
Write both up and down.
3. Large Data Migrations in Schema Migrations
Schema: Change column type
Data: Transform 1 million rows
Both in one migration → Long lock on table!
Split: Schema change first, then background data job.
4. Not Testing in Staging First
"Works in development" ≠ Works in production
Staging has production-like data.
Find issues before they hit users.
FAQ
Q: Can I modify data in migrations?
Yes, but keep schema and data migrations separate. Data migrations can be slow and might need different strategies (batching, background jobs).
Q: What if a migration fails halfway?
Most tools support transactions. Either the whole migration succeeds or it rolls back. For non-transactional databases, plan carefully.
Q: How do I handle migration conflicts in branches?
Merge the migrations, possibly renumbering. Some teams use timestamps instead of sequential numbers to reduce conflicts.
Q: Should migrations be reviewable?
Absolutely! Migrations should go through code review. A bad migration can cause data loss or outages.
Q: How do I migrate a production database with zero downtime?
Use blue-green deployments, or design migrations to be backward compatible. Avoid table locks on large tables.
Summary
Database migrations version-control your schema changes, making database evolution predictable, repeatable, and reversible.
Key Takeaways:
- Migrations are version-controlled schema change scripts
- Each has UP (apply) and DOWN (reverse) operations
- Run same migrations in all environments
- Avoid editing past migrations - create new ones
- Small, focused, one-concern-per-migration
- Test DOWN migrations to ensure rollback works
- Review migrations like code - they're critical
Migrations bring the same discipline to databases that Git brings to code!
Leave a Comment
Comments (0)
Be the first to comment on this concept.
Comments are approved automatically.