Implementing Database Migrations
1. Choosing Migration Tools
| Tool | Ecosystem |
|---|---|
| Flyway | JVM, multi-DB, SQL-first |
| Liquibase | XML/YAML/SQL changelogs |
| Alembic | Python / SQLAlchemy |
| Knex / Prisma | Node.js |
| Rails Active Record | Ruby |
| Sqitch | Dependency-based, no ORM |
| Atlas | Declarative schema mgmt |
2. Writing Forward Migrations
Example: Flyway SQL Migration
-- V20260315.1430__add_user_status.sql
ALTER TABLE users ADD COLUMN status TEXT;
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_status_chk
CHECK (status IN ('active','suspended','deleted'));| Rule | Detail |
|---|---|
| Idempotent where possible | IF NOT EXISTS guards |
| Small, atomic | One logical change per file |
| Never edit shipped migration | Add new one instead |
3. Implementing Reversible Migrations
| Aspect | Detail |
|---|---|
| Up + Down | Each change has inverse |
| Data loss warning | DROP TABLE down is destructive |
| Soft rollback | Prefer forward fix in production |
| Test down locally | Verify the inverse works |
4. Managing Migration Versions
| Storage | Detail |
|---|---|
| schema_history table | Tracks applied versions + checksum |
| Timestamp-based names | Avoid merge conflicts on numeric IDs |
| Lock during apply | Prevent concurrent runs |
| Repair command | Fix corrupted history (rare) |
5. Running Migrations in CI/CD
| Stage | Action |
|---|---|
| PR | Lint migration + apply on ephemeral DB |
| Pre-deploy | Apply to prod before app rollout |
| Init container / Job | k8s pre-install hook |
| Lock + timeout | Fail fast on conflicts |
| Logs + status | Track in deployment pipeline |
6. Handling Migration Failures
| Failure | Response |
|---|---|
| Syntax error | Wrap in transaction; auto-rollback |
| Timeout / lock | Retry off-hours; use lock_timeout |
| Constraint violation on backfill | Fix data first, re-run |
| DDL outside tx (MySQL) | Manual cleanup required |
7. Implementing Zero-Downtime Migrations
| Pattern | Detail |
|---|---|
| Expand / contract | Add new shape; migrate; remove old |
| Online DDL | CONCURRENTLY / ALGORITHM=INPLACE |
| App tolerance | Read both old & new during transition |
| Background backfill | Batched UPDATE jobs |
| Feature flag | Toggle code paths to new schema |
8. Coordinating Schema and Code Changes
Coordinated Release
- Deploy app supporting both old & new schemas
- Run schema migration (expand)
- Run data migration (backfill)
- Deploy app reading/writing new only
- Run schema migration (contract: drop old)
9. Testing Migrations in Lower Environments
| Env | Test |
|---|---|
| Dev | Apply against empty + seeded DB |
| CI | Apply against fresh image; run app tests |
| Staging | Apply against prod-snapshot; measure timing |
| Canary | Optional dark-launch with shadow traffic |
10. Documenting Migration Procedures
| Doc | Detail |
|---|---|
| Per-migration README | Risk, rollback, expected timing |
| Runbook | How to apply / abort / roll forward |
| Pre-flight checklist | Backups, replication health, lock budget |
| Post-migration validation | Smoke queries, metrics check |