Database Migrations

Overview

Database migrations provide version-controlled, repeatable schema changes that evolve alongside application code. In the full-lifecycle pipeline, migration scripts are generated in Phase 5 immediately after schema design, ensuring every DDL change is tracked, reversible, and deployable across environments. Flyway is the default tool for Java/Spring Boot projects.

Key Concepts

Migration Lifecycle

┌──────────┐    ┌──────────┐    ┌──────────┐    ┌──────────┐
│  Author   │───▶│  Review   │───▶│  Test     │───▶│  Deploy   │
│  V3__add  │    │  PR check │    │  CI with  │    │  Flyway   │
│  _column  │    │  + DBA    │    │  Testcont │    │  migrate  │
└──────────┘    └──────────┘    └──────────┘    └──────────┘

Flyway Naming Convention

Pattern	Type	Example
`V{version}__{description}.sql`	Versioned migration	`V001__create_users_table.sql`
`V{version}.{sub}__{desc}.sql`	Sub-versioned	`V001.1__add_email_index.sql`
`R__{description}.sql`	Repeatable (views, functions)	`R__refresh_user_view.sql`
`U{version}__{description}.sql`	Undo (Flyway Teams)	`U001__drop_users_table.sql`

Tool Comparison

Feature	Flyway	Liquibase
Format	Raw SQL	XML/YAML/JSON/SQL
Learning Curve	Low	Medium
Spring Boot	Auto-configured	Auto-configured
Rollback	Teams edition	Built-in
Diff/Generate	No	Yes (changelog diff)
Best For	SQL-first teams	Multi-DB targeting

Best Practices

One migration per change — Never combine unrelated changes in one file
Forward-only in production — Never edit a migration already applied to prod
Use zero-padded versions — V001, V002 for sorted ordering
Test migrations in CI — Run full migration chain against TestContainers
Include rollback script — Even if manual; document the reverse operation
Review migration size — Large table locks under migration can cause downtime
Separate data vs. schema migrations — Data seeding in separate files
Store migrations in version control — Same repo, same branch as code

Code Examples

✅ Good: Zero-Downtime Column Addition

-- V003__add_phone_to_users.sql
-- ZERO DOWNTIME: Adding nullable column does not lock table

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Index added concurrently to avoid table lock
CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone);

-- Application handles NULL phone gracefully (backward compatible)

✅ Good: Safe Column Rename (Multi-Step)

-- V004__add_display_name_column.sql (Deploy 1: add new column)
ALTER TABLE users ADD COLUMN display_name VARCHAR(100);
UPDATE users SET display_name = name WHERE display_name IS NULL;

-- V005__make_display_name_not_null.sql (Deploy 2: after app reads both)
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

-- V006__drop_old_name_column.sql (Deploy 3: after app only uses new)
ALTER TABLE users DROP COLUMN name;

❌ Bad: Dangerous Migration

-- V003__rename_stuff.sql
-- LOCKS TABLE — will cause downtime on large tables
ALTER TABLE users RENAME COLUMN name TO display_name;

-- Multiple unrelated changes in one migration
ALTER TABLE orders ADD COLUMN discount DECIMAL;
DROP TABLE legacy_logs;
CREATE INDEX idx_users_phone ON users (phone);  -- Not CONCURRENTLY

Anti-Patterns

Manual schema changes — “Just run this ALTER in production” (always use migration files)
Editing applied migrations — Checksum mismatch causes deployment failure
Mixing DDL and DML — Schema changes and data changes should be separate files
Missing default values — Adding NOT NULL without DEFAULT locks table for full rewrite
No CI validation — Migrations only tested in dev (use TestContainers in CI)

Testing Strategies

Full migration chain — Apply all migrations V001–VN against empty TestContainers PostgreSQL
Idempotency checks — Run flyway validate after migrate
Rollback verification — Apply V005, undo to V004, re-apply V005
Performance testing — Measure migration time on production-sized data samples