Implementing Database Migration
1. Implementing Schema Versioning
Tool Convention Flyway V1__create_users.sqlLiquibase XML/YAML/JSON changelogs Alembic Python SQLAlchemy Knex/Sequelize JS/TS migrations
2. Implementing Migration Scripts
Example: Flyway migration
-- V20260101_001__create_orders.sql CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY , customer_id UUID NOT NULL , status VARCHAR ( 20 ) NOT NULL , total_cents BIGINT NOT NULL CHECK (total_cents >= 0 ), placed_at TIMESTAMPTZ NOT NULL DEFAULT now () ); CREATE INDEX idx_orders_customer ON orders(customer_id, placed_at DESC );
3. Implementing Rollback Scripts
Approach Detail Forward-only Write a new migration to undo Liquibase rollback Define rollback in changeset Backup + restore For destructive changes
Warning: Rollback scripts often can't restore lost data — prefer forward-fix migrations.
4. Implementing Baseline Migration
Use Detail Existing DB Mark current state as baseline Flyway flyway baseline -baselineVersion=1Effect Skip migrations ≤ baseline
5. Implementing Repeatable Migrations
Naming Run When R__views.sqlChecksum changes Use for Views, functions, procedures
6. Implementing Callback Handlers
Callback When beforeMigratePre-run all migrations afterMigratePost-run all beforeEachMigratePer migration afterEachMigratePer migration
7. Implementing Migration Validation
Check Tool Checksum mismatch Flyway validate Out-of-order detection Flyway config Linting squawk, sqlfluff Dry-run CI on shadow DB
8. Implementing Out-of-Order Migrations
Case Detail Long-running branch Lower-number migration appears later Flyway flag outOfOrder=trueRisk Apply order differs across envs
9. Implementing Placeholder Replacement
Example: Per-env placeholder
-- placeholders: schema=acme CREATE TABLE ${ schema }.orders ( ... );
10. Implementing Migration Testing
Step Tool Spin disposable DB Testcontainers PostgreSQL Run migrations Flyway/Liquibase API Assert schema Query information_schema Test data preserved Seed before, verify after
11. Implementing Data Migrations
Pattern Detail Backfill in batches UPDATE ... LIMIT loopRead replica Avoid impacting production Idempotent Safe to re-run Track progress Cursor table
12. Implementing Zero-Downtime Migrations
Expand-Contract Pattern
Expand: add new column/table (nullable)
Deploy app version writing both old and new
Backfill data into new column
Deploy app version reading from new only
Contract: drop old column in later release
Concern Mitigation Long-locking DDL Use CONCURRENTLY for indexes (PG) Renaming column Add new + sync + drop old Type change New column + backfill + cutover