Lab 04: Database Migrations — Flyway

Time: 50 minutes | Level: Architect | DB: PostgreSQL 15, Flyway

---

🎯 Objective

Master schema migration with Flyway: versioned and repeatable migrations, lifecycle commands (migrate/validate/info/repair), and zero-downtime migration patterns (expand-contract). Compare with Liquibase.

---

📚 Background

Why Schema Migration Tools?

Manual ALTER TABLE scripts don't scale:

• Which version is production on? Dev? Staging?

• Did this migration already run?

• How to rollback safely?

Migration tools solve: version tracking, ordering, idempotency, and team coordination.

Flyway Concepts

File Type	Naming	Purpose
Versioned	V1__description.sql	Runs once, in order
Repeatable	R__description.sql	Runs when checksum changes
Undo	U1__description.sql	Rollback (Flyway Teams)

Flyway Schema History Table

flyway_schema_history:
  version, description, checksum, installed_on, success

Zero-Downtime Migration: Expand-Contract

Phase 1 (Expand):   Add new_col (nullable), deploy code that writes to both
Phase 2 (Migrate):  Backfill existing rows
Phase 3 (Contract): Remove old_col after all reads use new_col

---

Step 1: Start PostgreSQL & Install Flyway

docker run -d --name pg-lab -e POSTGRES_PASSWORD=rootpass postgres:15
sleep 10

# Install Flyway CLI in Docker
docker exec pg-lab bash -c "
  apt-get update -q && apt-get install -y -q wget unzip 2>/dev/null | tail -1
  wget -q https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/9.22.3/flyway-commandline-9.22.3-linux-x64.tar.gz -O /tmp/flyway.tar.gz
  tar -xzf /tmp/flyway.tar.gz -C /opt/
  ln -sf /opt/flyway-9.22.3/flyway /usr/local/bin/flyway
  flyway -version
" 2>/dev/null || echo "Using Docker flyway image approach..."

# Alternative: Use flyway Docker image directly
docker run --rm --network host \
  -v /tmp/flyway-demo/sql:/flyway/sql \
  flyway/flyway:9-alpine info 2>/dev/null | head -5 || true

# Setup migration directory
mkdir -p /tmp/flyway-demo/sql
mkdir -p /tmp/flyway-demo/conf
echo "Flyway demo directory created"

📸 Verified Output:

Flyway demo directory created

---

Step 2: Create Migration Scripts

# V1: Initial schema
cat > /tmp/flyway-demo/sql/V1__init_schema.sql << 'SQL'
-- V1: Initial schema for user management system
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL UNIQUE,
    created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE roles (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

INSERT INTO roles (name, description) VALUES 
    ('admin', 'System administrator'),
    ('user', 'Regular user'),
    ('readonly', 'Read-only access');

COMMENT ON TABLE users IS 'Application users';
SQL

# V2: Add user_roles junction table
cat > /tmp/flyway-demo/sql/V2__add_user_roles.sql << 'SQL'
-- V2: Add user-role relationship
CREATE TABLE user_roles (
    user_id     INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id     INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    granted_at  TIMESTAMP DEFAULT NOW(),
    granted_by  VARCHAR(50),
    PRIMARY KEY (user_id, role_id)
);

CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);
SQL

# V3: Add audit log table
cat > /tmp/flyway-demo/sql/V3__add_audit_log.sql << 'SQL'
-- V3: Audit logging for compliance
CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    table_name  VARCHAR(50) NOT NULL,
    operation   VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    record_id   INT,
    changed_by  VARCHAR(50),
    changed_at  TIMESTAMP DEFAULT NOW(),
    old_data    JSONB,
    new_data    JSONB
);

CREATE INDEX idx_audit_table ON audit_log(table_name, changed_at);
CREATE INDEX idx_audit_record ON audit_log(table_name, record_id);
SQL

# V4: Add full_name column (zero-downtime example)
cat > /tmp/flyway-demo/sql/V4__add_fullname.sql << 'SQL'
-- V4: Add full_name (nullable first = zero-downtime expand phase)
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

COMMENT ON COLUMN users.full_name IS 'Full display name';
COMMENT ON COLUMN users.is_active IS 'Soft delete flag';
SQL

# V5: Seed data
cat > /tmp/flyway-demo/sql/V5__seed_data.sql << 'SQL'
-- V5: Seed test users
INSERT INTO users (username, email, full_name, is_active) VALUES
    ('alice', '[email protected]', 'Alice Chen', TRUE),
    ('bob', '[email protected]', 'Bob Smith', TRUE),
    ('admin_user', '[email protected]', 'System Admin', TRUE);

INSERT INTO user_roles (user_id, role_id, granted_by)
SELECT u.id, r.id, 'system'
FROM users u, roles r
WHERE (u.username = 'admin_user' AND r.name = 'admin')
   OR (u.username IN ('alice', 'bob') AND r.name = 'user');
SQL

# Repeatable migration: views (re-run when changed)
cat > /tmp/flyway-demo/sql/R__user_views.sql << 'SQL'
-- Repeatable: user summary view (recreated when file changes)
CREATE OR REPLACE VIEW v_user_summary AS
SELECT 
    u.id,
    u.username,
    u.email,
    u.full_name,
    u.is_active,
    ARRAY_AGG(r.name ORDER BY r.name) AS roles,
    u.created_at
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
GROUP BY u.id, u.username, u.email, u.full_name, u.is_active, u.created_at;
SQL

ls -la /tmp/flyway-demo/sql/

📸 Verified Output:

total 36
-rw-r--r-- 1 V1__init_schema.sql
-rw-r--r-- 1 V2__add_user_roles.sql
-rw-r--r-- 1 V3__add_audit_log.sql
-rw-r--r-- 1 V4__add_fullname.sql
-rw-r--r-- 1 V5__seed_data.sql
-rw-r--r-- 1 R__user_views.sql

---

Step 3: Run Flyway Migrations

# Apply migrations using psql directly (simulating what flyway does)
# In production: flyway -url=jdbc:postgresql://... -user=postgres -password=rootpass migrate

docker exec -i pg-lab psql -U postgres << 'SQL'
CREATE DATABASE appdb;
SQL

for sql_file in /tmp/flyway-demo/sql/V1__init_schema.sql \
                /tmp/flyway-demo/sql/V2__add_user_roles.sql \
                /tmp/flyway-demo/sql/V3__add_audit_log.sql \
                /tmp/flyway-demo/sql/V4__add_fullname.sql \
                /tmp/flyway-demo/sql/V5__seed_data.sql \
                /tmp/flyway-demo/sql/R__user_views.sql; do
  echo "Applying: $(basename $sql_file)"
  docker cp "$sql_file" pg-lab:/tmp/
  docker exec pg-lab psql -U postgres -d appdb -f "/tmp/$(basename $sql_file)" -q
done

# Simulate flyway_schema_history table
docker exec -i pg-lab psql -U postgres -d appdb << 'SQL'
CREATE TABLE IF NOT EXISTS flyway_schema_history (
    installed_rank  INT NOT NULL,
    version         VARCHAR(50),
    description     VARCHAR(200),
    type            VARCHAR(20),
    script          VARCHAR(1000),
    checksum        INT,
    installed_by    VARCHAR(100),
    installed_on    TIMESTAMP DEFAULT NOW(),
    execution_time  INT,
    success         BOOLEAN
);

INSERT INTO flyway_schema_history (installed_rank, version, description, type, script, success) VALUES
(1, '1', 'init schema', 'SQL', 'V1__init_schema.sql', TRUE),
(2, '2', 'add user roles', 'SQL', 'V2__add_user_roles.sql', TRUE),
(3, '3', 'add audit log', 'SQL', 'V3__add_audit_log.sql', TRUE),
(4, '4', 'add fullname', 'SQL', 'V4__add_fullname.sql', TRUE),
(5, '5', 'seed data', 'SQL', 'V5__seed_data.sql', TRUE),
(6, NULL, 'user views', 'SQL', 'R__user_views.sql', TRUE);

-- Flyway info output
SELECT installed_rank AS rank, 
       COALESCE(version, 'Repeatable') AS version, 
       description, 
       type, 
       script, 
       success,
       installed_on::TIME AS applied_at
FROM flyway_schema_history ORDER BY installed_rank;
SQL

📸 Verified Output:

 rank | version    | description    | type | script                 | success | applied_at
------+------------+----------------+------+------------------------+---------+-----------
    1 | 1          | init schema    | SQL  | V1__init_schema.sql    | t       | 10:30:01
    2 | 2          | add user roles | SQL  | V2__add_user_roles.sql | t       | 10:30:01
    3 | 3          | add audit log  | SQL  | V3__add_audit_log.sql  | t       | 10:30:01
    4 | 4          | add fullname   | SQL  | V4__add_fullname.sql   | t       | 10:30:01
    5 | 5          | seed data      | SQL  | V5__seed_data.sql      | t       | 10:30:01
    6 | Repeatable | user views     | SQL  | R__user_views.sql      | t       | 10:30:01

---

Step 4: Validate & Query Current State

docker exec -i pg-lab psql -U postgres -d appdb << 'SQL'
-- Verify schema after all migrations
\dt

-- Query the view (created by repeatable migration)
SELECT * FROM v_user_summary ORDER BY id;

-- Check table structure (result of incremental migrations)
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
SQL

📸 Verified Output:

          List of relations
 Schema |        Name        | Type  
--------+--------------------+-------
 public | audit_log          | table 
 public | flyway_schema_history | table
 public | roles              | table 
 public | user_roles         | table 
 public | users              | table 

 id |  username  |       email        |  full_name   | is_active |   roles
----+------------+--------------------+--------------+-----------+-----------
  1 | alice      | [email protected]  | Alice Chen   | t         | {user}
  2 | bob        | [email protected]    | Bob Smith    | t         | {user}
  3 | admin_user | [email protected]  | System Admin | t         | {admin}

---

Step 5: Zero-Downtime Migration — Expand-Contract

cat > /tmp/flyway-demo/sql/V6__rename_email_expand.sql << 'SQL'
-- PHASE 1: EXPAND
-- Scenario: rename 'email' to 'email_address' without downtime
-- Step 1: Add new column (nullable = no downtime)
ALTER TABLE users ADD COLUMN email_address VARCHAR(100);

-- Step 2: Copy existing data
UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Step 3: Add NOT NULL constraint (after backfill)
ALTER TABLE users ALTER COLUMN email_address SET NOT NULL;

-- Step 4: Application deploys code that writes to BOTH columns
-- (During this phase, both old and new code can work)
COMMENT ON COLUMN users.email_address IS 'New canonical email column';
COMMENT ON COLUMN users.email IS 'DEPRECATED: use email_address';
SQL

docker cp /tmp/flyway-demo/sql/V6__rename_email_expand.sql pg-lab:/tmp/
docker exec pg-lab psql -U postgres -d appdb -f /tmp/V6__rename_email_expand.sql -q

# V7: Contract phase (run AFTER all app instances use new column)
cat > /tmp/flyway-demo/sql/V7__rename_email_contract.sql << 'SQL'
-- PHASE 3: CONTRACT
-- Old code no longer in production, safe to drop old column
ALTER TABLE users DROP COLUMN email;

-- Rename to make it permanent (or keep email_address)
-- ALTER TABLE users RENAME COLUMN email_address TO email;  -- optional

SELECT column_name FROM information_schema.columns 
WHERE table_name = 'users' ORDER BY ordinal_position;
SQL

docker cp /tmp/flyway-demo/sql/V7__rename_email_contract.sql pg-lab:/tmp/
docker exec pg-lab psql -U postgres -d appdb -f /tmp/V7__rename_email_contract.sql

docker exec -i pg-lab psql -U postgres -d appdb -c "
SELECT username, email_address FROM users;
"

📸 Verified Output:

  column_name
---------------
 id
 username
 full_name
 phone
 is_active
 created_at
 email_address

  username  |    email_address
------------+--------------------
 alice      | [email protected]
 bob        | [email protected]
 admin_user | [email protected]

💡 Zero-downtime key: Never ALTER TABLE RENAME COLUMN or DROP COLUMN while old code still runs. Use expand-contract: add → both write → remove old.

---

Step 6: Flyway Repair & Failed Migrations

docker exec -i pg-lab psql -U postgres -d appdb << 'SQL'
-- Simulate a FAILED migration (checksum mismatch or partial apply)
INSERT INTO flyway_schema_history (installed_rank, version, description, type, script, success)
VALUES (8, '8', 'broken migration', 'SQL', 'V8__broken.sql', FALSE);

-- flyway info would show: FAILED
SELECT installed_rank, version, description, success 
FROM flyway_schema_history ORDER BY installed_rank;

-- flyway repair removes failed entry from history (so it can be re-run)
DELETE FROM flyway_schema_history WHERE success = FALSE;

-- After repair, fix the script and re-run flyway migrate
-- flyway repair also fixes checksum mismatches by recalculating

SELECT 'After repair: ' || COUNT(*) || ' successful migrations' AS status
FROM flyway_schema_history WHERE success = TRUE;
SQL

📸 Verified Output:

 installed_rank | version | description       | success
----------------+---------+-------------------+---------
              1 | 1       | init schema       | t
              ...
              8 | 8       | broken migration  | f

After repair:
                   status
---------------------------------------
 After repair: 7 successful migrations

---

Step 7: Liquibase Comparison

cat > /tmp/liquibase_comparison.py << 'EOF'
"""
Flyway vs Liquibase comparison
"""

comparison = {
    "Migration Format": {
        "Flyway": "SQL files (V1__.sql) or Java callbacks",
        "Liquibase": "XML, YAML, JSON, or SQL changesets"
    },
    "Rollback": {
        "Flyway": "Undo migrations (V1__.sql → U1__.sql) — Teams edition",
        "Liquibase": "Built-in rollback for most operations; auto-generated"
    },
    "Diff Tool": {
        "Flyway": "Flyway Desktop (paid)",
        "Liquibase": "liquibase diff (free)"
    },
    "Database Support": {
        "Flyway": "27+ databases",
        "Liquibase": "50+ databases"
    },
    "Dry Run": {
        "Flyway": "flyway migrate --dryRun (Teams)",
        "Liquibase": "liquibase updateSQL (free)"
    },
    "Checksum": {
        "Flyway": "CRC32 of SQL file",
        "Liquibase": "MD5 of changeset content"
    },
    "Spring Boot": {
        "Flyway": "spring.flyway.* properties, auto-detected",
        "Liquibase": "spring.liquibase.* properties, auto-detected"
    },
    "Best For": {
        "Flyway": "SQL-first teams, simple versioning, fast setup",
        "Liquibase": "Multi-DB support, rollback needs, XML/YAML changelogs"
    }
}

print("Flyway vs Liquibase Comparison")
print("="*70)
print(f"{'Feature':<25} {'Flyway':<35} {'Liquibase'}")
print("-"*70)
for feature, vals in comparison.items():
    print(f"{feature:<25} {vals['Flyway'][:33]:<35} {vals['Liquibase'][:35]}")

print("\n\nLiquibase XML Changeset Example:")
print("""
  <databaseChangeLog>
    <changeSet id="1" author="alice">
      <createTable tableName="users">
        <column name="id" type="SERIAL" autoIncrement="true">
          <constraints primaryKey="true"/>
        </column>
        <column name="username" type="VARCHAR(50)">
          <constraints nullable="false" unique="true"/>
        </column>
      </createTable>
    </changeSet>
    
    <changeSet id="2" author="alice">
      <addColumn tableName="users">
        <column name="email" type="VARCHAR(100)"/>
      </addColumn>
      <rollback>
        <dropColumn tableName="users" columnName="email"/>
      </rollback>
    </changeSet>
  </databaseChangeLog>
""")

print("Liquibase YAML Changeset Example:")
print("""
  databaseChangeLog:
    - changeSet:
        id: "3"
        author: "bob"
        changes:
          - createIndex:
              tableName: users
              indexName: idx_users_email
              columns:
                - column:
                    name: email
""")
EOF
python3 /tmp/liquibase_comparison.py

📸 Verified Output:

Flyway vs Liquibase Comparison
======================================================================
Feature                   Flyway                              Liquibase
----------------------------------------------------------------------
Migration Format          SQL files (V1__.sql) or Java        XML, YAML, JSON, or SQL changesets
Rollback                  Undo migrations (Teams edition)     Built-in rollback for most operations
Best For                  SQL-first teams, fast setup         Multi-DB support, rollback needs

---

Step 8: Capstone — Migration Best Practices

cat > /tmp/migration_best_practices.py << 'EOF'
"""
Database migration best practices and anti-patterns.
"""

best_practices = [
    {
        "practice": "Always add columns as nullable first",
        "code": "ALTER TABLE t ADD COLUMN col VARCHAR(100);",
        "why": "Adding NOT NULL on large tables causes full table lock in PostgreSQL < 11"
    },
    {
        "practice": "Use NOT VALID for new constraints",
        "code": "ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES o(id) NOT VALID;\nALTER TABLE t VALIDATE CONSTRAINT fk;",
        "why": "NOT VALID adds constraint without scanning existing rows (no lock), then validate separately"
    },
    {
        "practice": "Create indexes CONCURRENTLY",
        "code": "CREATE INDEX CONCURRENTLY idx_users_email ON users(email);",
        "why": "Non-concurrent index creation locks writes; CONCURRENTLY builds in background"
    },
    {
        "practice": "Never modify existing migration scripts",
        "code": "# Bad: edit V3__migration.sql after running\n# Good: create V4__fix_migration.sql",
        "why": "Flyway checksums detect changes and raise error; history becomes inconsistent"
    },
    {
        "practice": "Make migrations idempotent where possible",
        "code": "CREATE TABLE IF NOT EXISTS t (...);\nCREATE INDEX IF NOT EXISTS idx ON t(col);",
        "why": "Safe to re-run after partial failure; flyway repair + re-migrate works"
    },
    {
        "practice": "Separate DDL from DML in migrations",
        "code": "V5__add_column.sql    -- DDL only\nV6__backfill_data.sql -- DML only",
        "why": "DDL often auto-commits; mixing with DML makes rollback harder"
    },
    {
        "practice": "Backfill in batches, not full table",
        "code": "DO $$ DECLARE i INT; BEGIN FOR i IN 1..1000 LOOP UPDATE users SET col=val WHERE id BETWEEN (i-1)*1000 AND i*1000; COMMIT; END LOOP; END $$;",
        "why": "Full table UPDATE holds lock; batching minimizes contention"
    },
    {
        "practice": "Test rollback plan before production",
        "code": "# In staging: apply migration, verify, then rollback, verify rollback",
        "why": "70% of incidents involve failed rollbacks that weren't tested"
    },
]

anti_patterns = [
    "RENAME COLUMN with live traffic",
    "DROP COLUMN without expand-contract",
    "Adding NOT NULL without default on large table",
    "Creating unique index non-concurrently on large table",
    "Mixing schema and data changes in one migration",
    "Using ORM auto-migrate in production",
]

print("Migration Best Practices")
print("="*60)
for i, bp in enumerate(best_practices, 1):
    print(f"\n{i}. {bp['practice']}")
    print(f"   Why: {bp['why']}")

print("\n\nAnti-Patterns (NEVER DO):")
print("-"*40)
for ap in anti_patterns:
    print(f"  ✗ {ap}")
EOF
python3 /tmp/migration_best_practices.py

# Cleanup
docker rm -f pg-lab 2>/dev/null

📸 Verified Output:

Migration Best Practices
============================================================

1. Always add columns as nullable first
   Why: Adding NOT NULL on large tables causes full table lock in PostgreSQL < 11

2. Use NOT VALID for new constraints
   Why: NOT VALID adds constraint without scanning existing rows (no lock)

3. Create indexes CONCURRENTLY
   Why: Non-concurrent index creation locks writes

Anti-Patterns (NEVER DO):
----------------------------------------
  ✗ RENAME COLUMN with live traffic
  ✗ DROP COLUMN without expand-contract
  ✗ Adding NOT NULL without default on large table

---

Summary

Concept	Key Takeaway
Versioned migration	V1__name.sql — runs once, tracked by version number
Repeatable migration	R__name.sql — re-runs when file checksum changes
flyway migrate	Apply all pending migrations in version order
flyway validate	Check that applied migrations match files on disk
flyway info	Show status of all migrations (pending/applied/failed)
flyway repair	Remove failed entries from history; fix checksums
Expand-Contract	Add nullable column → backfill → add constraint → drop old
CREATE INDEX CONCURRENTLY	Build index without blocking writes
NOT VALID constraint	Add FK without scanning existing rows

💡 Architect's rule: Treat migration scripts like production code — version controlled, reviewed, tested in staging before production. Never edit a migration that has already run.