Database schema evolution: Flyway/Liquibase/Sqitch + PlantUML ER diagrams

Database schema evolution is the backend’s most common source of breakage — add column, drop column, change type, change constraint, every time you need to plan “how to do this without downtime, how to rollback, how to migrate data”. This is the Flyway / Liquibase / Sqitch comparison, ER diagram maintenance, zero-downtime migration patterns, and seven strategies for big-table ALTERs.

Three mainstream tools

Tool	Style	Strength	Weakness
Flyway	SQL-only	simple, low learning curve, deep Java/Spring integration	complex logic needs Java migration
Liquibase	XML/YAML/SQL	rollback support, cross-DB abstraction	XML verbose, complex
Sqitch	Perl/SQL	pure SQL, verify scripts are re-runnable	smaller community

New projects pick Flyway — deepest Spring Boot integration, SQL-only keeps things simple.

Flyway directory convention

db/migration/
├── V1__create_users_table.sql
├── V2__create_orders_table.sql
├── V3__add_email_to_users.sql
├── V4__create_index_users_email.sql
└── V5__add_orders_status_column.sql

Naming: V{version}__{description}.sql, version strictly increasing, description must be clear.

Basic ER diagram: PlantUML reflecting current schema

@startuml
hide circle

entity "users" {
  *id : BIGINT <<PK>>
  --
  *email : VARCHAR(255) <<UK>>
  *password_hash : VARCHAR(255)
  *name : VARCHAR(100)
  *created_at : TIMESTAMP
  *updated_at : TIMESTAMP
  *deleted_at : TIMESTAMP <<nullable>>
}

entity "orders" {
  *id : BIGINT <<PK>>
  --
  *user_id : BIGINT <<FK>>
  *total_cents : INT
  *status : VARCHAR(20)
  *created_at : TIMESTAMP
  *updated_at : TIMESTAMP
}

entity "order_items" {
  *id : BIGINT <<PK>>
  --
  *order_id : BIGINT <<FK>>
  *product_id : BIGINT <<FK>>
  *quantity : INT
  *price_cents : INT
}

entity "products" {
  *id : BIGINT <<PK>>
  --
  *name : VARCHAR(200)
  *sku : VARCHAR(50) <<UK>>
  *price_cents : INT
  *inventory_count : INT
}

users ||--o{ orders : "places"
orders ||--|{ order_items : "contains"
products ||--o{ order_items : "referenced by"

@enduml

Conventions:

* NOT NULL
<<PK>> primary key
<<FK>> foreign key
<<UK>> unique key
<<nullable>> nullable
-- separates key from attributes

Read Flyway schema, generate ER diagram

# gen_er_from_flyway.py
import re, pathlib

# extract CREATE TABLE from V*.sql files
schema_sql = ""
for f in sorted(pathlib.Path("db/migration").glob("V*.sql")):
    schema_sql += f.read_text() + "\n"

# parse CREATE TABLE
tables = re.findall(
    r'CREATE TABLE (\w+)\s*\((.*?)\);',
    schema_sql, re.DOTALL,
)

# output PlantUML ER
print("@startuml")
print("hide circle\n")
for table_name, columns_sql in tables:
    print(f'entity "{table_name}" {{')
    columns = re.findall(r'(\w+)\s+(\w+(?:\(\d+\))?)', columns_sql)
    for i, (col_name, col_type) in enumerate(columns):
        marker = "*" if "NOT NULL" in columns_sql.split("\n")[i] else ""
        suffix = ""
        if "PRIMARY KEY" in columns_sql.split("\n")[i]:
            suffix = " <<PK>>"
        elif "UNIQUE" in columns_sql.split("\n")[i]:
            suffix = " <<UK>>"
        print(f"  {marker}{col_name} : {col_type}{suffix}")
    print("}")
print("@enduml")

Run this in CI — every migration regenerates the ER diagram, guaranteeing diagram matches schema.

Flyway migration patterns

V1: create table

-- V1__create_users_table.sql
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP
);

CREATE INDEX idx_users_deleted_at ON users(deleted_at)
  WHERE deleted_at IS NOT NULL;

V3: add column

-- V3__add_email_to_users.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- backfill
UPDATE users SET phone = '+86-default' WHERE phone IS NULL;

-- add NOT NULL (two-step: add nullable → fill default → alter NOT NULL)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

V4: add index

1
2
3

-- V4__create_index_users_email.sql
CREATE INDEX CONCURRENTLY idx_users_email_lower
  ON users(LOWER(email));

CONCURRENTLY doesn’t lock the table — required for big tables.

V5: add foreign key

-- V5__add_orders_status_column.sql
ALTER TABLE orders ADD COLUMN status VARCHAR(20);

-- CHECK constraint
ALTER TABLE orders ADD CONSTRAINT chk_orders_status
  CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'));

-- index
CREATE INDEX CONCURRENTLY idx_orders_status
  ON orders(status) WHERE status != 'cancelled';

Zero-downtime migration patterns

Pattern 1: Expand-Migrate-Contract

@startuml
title Expand-Migrate-Contract Pattern

participant "App v1" as V1
database "DB" as DB
participant "App v2" as V2

== Phase 1: Expand (compatible with old format) ==
V1 -> DB : ① add new column phone (NULL allowed)
note right of V1
  Old V1 still runs
  (new column NULL, no impact)
end note

== Phase 2: Migrate (data migration) ==
V1 -> DB : ② one-shot backfill old data\nUPDATE users SET phone = '+86-default'\nWHERE phone IS NULL
V1 -> DB : ③ ALTER COLUMN phone SET NOT NULL
note right of V1
  DB constraint tightened
  but app doesn't use new column yet
end note

== Phase 3: Deploy V2 (dual write) ==
V2 -> DB : ④ deploy new version, write old + new formats
note right of V2
  V2 dual-writes, ensures V1/V2
  both work normally
end note

== Phase 4: Migrate Read (V2 leads) ==
V2 -> DB : ⑤ deploy V2.1, read only from new column
V1 -> DB : ⑥ old version retired

== Phase 5: Contract (cleanup) ==
V2 -> DB : ⑦ DROP COLUMN old column (if exists)

@enduml

Core idea: every phase is rollbackable — rolling back to the previous phase needs no data migration.

Pattern 2: Shadow Table

@startuml
title Shadow Table Pattern (rename column)

participant "App" as App
database "DB" as DB

== Step 1: add new column ==
App -> DB : ① ALTER TABLE users ADD COLUMN email_new VARCHAR(255)
App -> DB : ② CREATE TRIGGER sync_email\nBEFORE INSERT OR UPDATE ON users\nFOR EACH ROW EXECUTE FUNCTION copy_email();

== Step 2: app layer dual write ==
note over App
  App writes both email and email_new,
  reads prefer email_new,
  old code reading email still works
end note

== Step 3: data migration ==
App -> DB : ③ UPDATE users SET email_new = email\nWHERE email_new IS NULL;

== Step 4: switch app ==
note over App
  Deploy new version, read email_new only
end note

== Step 5: cleanup ==
App -> DB : ④ DROP COLUMN email;
App -> DB : ⑤ DROP TRIGGER sync_email;

@enduml

Fits: rename column, change column type (VARCHAR(50) → VARCHAR(255)), split column (name → first_name + last_name).

Pattern 3: Big table ALTER (INPLACE vs COPY)

PostgreSQL ALTER TABLE ... ALTER COLUMN type has two algorithms:

-- ACCESS EXCLUSIVE lock (default)
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);
-- full table rewrite, locks table, big table (>1M rows) takes 10+ min

-- no lock (some type conversions supported)
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30) USING phone::VARCHAR(30);
-- still full table rewrite

-- truly no lock: use pg_repack
-- 1. add new column
ALTER TABLE users ADD COLUMN phone_new VARCHAR(30);
-- 2. trigger sync
-- 3. backfill
UPDATE users SET phone_new = phone;
-- 4. switch
-- 5. drop old column

Seven strategies for big-table ALTERs

@startuml
title Big Table ALTER Strategies

start

:add column (ADD COLUMN);
note right: PG 11+ add column doesn't rewrite table by default

:drop column (DROP COLUMN);
note right: PG 11+ doesn't rewrite table, only marks

:change type (ALTER COLUMN TYPE);
note right: full table rewrite, locks table\n→ batch or pg_repack

:add index (CREATE INDEX);
note right: locks table → CONCURRENTLY doesn't lock

:drop index (DROP INDEX);
note right: short lock → CONCURRENTLY

:add NOT NULL;
note right: full table scan → fill default first, then alter

:add FK (ADD CONSTRAINT FOREIGN KEY);
note right: full table scan to validate → NOT VALID + VALIDATE

stop
@enduml

NOT VALID + VALIDATE pattern:

-- Step 1: add FK without validation (seconds)
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Step 2: validate in background (only SHARE UPDATE EXCLUSIVE lock, DML allowed)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

Liquibase rollback capability

# changelog.yml
databaseChangeLog:
  - include:
      file: db/changelog/001-create-users.yaml
  - include:
      file: db/changelog/002-add-orders.yaml

# db/changelog/001-create-users.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: alice
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: bigint
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: email
                  type: varchar(255)
                  constraints:
                    nullable: false
                    unique: true
      rollback:
        - dropTable:
            tableName: users

rollback block lets Liquibase run liquibase rollbackCount 1 automatically.

Flyway has no native rollback — to roll back, write U{version}__*.sql yourself (undo migration).

Sqitch: verify scripts guarantee idempotent migration

-- sqitch.plan
%syntax-version=1.0.0

users 2026-01-15T10:00:00Z alice <email@example.com> # add users table
add_orders 2026-01-20T10:00:00Z alice <email@example.com> # add orders table

-- deploy/users.sql
CREATE TABLE users (...);

-- verify/users.sql
SELECT id, email FROM users WHERE FALSE;

-- revert/users.sql
DROP TABLE users;

verify/*.sql is Sqitch-only — run sqitch verify anytime to check schema is in place.

Migration tests

# tests/test_migrations.py
import subprocess, pytest
from pathlib import Path

MIGRATION_DIR = Path("db/migration")

@pytest.fixture
def db():
    """fresh database"""
    subprocess.run(["docker", "run", "--rm", "-d",
                    "--name", "pg-test", "-e", "POSTGRES_PASSWORD=***                    "-p", "5433:5432", "postgres:16"], check=True)
    subprocess.run(["sleep", "2"], check=True)
    yield "postgresql://postgres:***@localhost:5433/postgres"
    subprocess.run(["docker", "rm", "-f", "pg-test"], check=True)

def test_full_migration(db):
    """All migrations run, schema should look like this"""
    subprocess.run(["flyway", "-url", db, "migrate"], check=True)

    actual_schema = subprocess.check_output(
        ["psql", db, "-c", "\\dt"]
    ).decode()
    assert "users" in actual_schema
    assert "orders" in actual_schema

def test_individual_migrations(db):
    """Each migration runs successfully in isolation"""
    for migration in sorted(MIGRATION_DIR.glob("V*.sql")):
        subprocess.run(["flyway", "clean", "-url", db], check=True)
        # run this one
        ...

ER diagram stays in sync with code

Architecture drift test — read schema, generate ER, compare to docs:

def test_er_diagram_matches_schema():
    actual_tables = get_tables_from_db()
    documented_tables = parse_puml("docs/er.puml")
    assert actual_tables == documented_tables

CI runs the test → new table added but ER diagram not updated → fail.

Field foot-guns

Flyway clean drops everything — never run in production. Use in local / test envs only.
Migrations are immutable — deployed V1__ cannot be modified, only add V2__ to fix.
ADD COLUMN NOT NULL without default — big table + NOT NULL = full table update, super long lock. Two steps: add nullable → UPDATE default → alter NOT NULL.
SERIAL type — PG 10+ uses BIGINT GENERATED ALWAYS AS IDENTITY, SERIAL still works but not recommended.
Liquibase XML indentation wrong — one tweak triggers syntax error, use YAML for compactness.
Multi-database abstraction trap — Liquibase BOOLEAN in MySQL becomes TINYINT(1), type differences cause more headaches. Direct SQL is more controllable.
Migration half-runs and fails — transaction not committed, migration table state inconsistent. Each migration is its own file + single transaction.

Decision tree

What do you need?
├─ New project → Flyway + SQL-only
├─ Java/Spring Boot → Flyway (deep integration)
├─ Need automatic rollback → Liquibase
├─ Multi-DB compat (Oracle/PG/MySQL) → Liquibase
└─ Pure SQL + verify → Sqitch

Migration size?
├─ Small (<100k rows) → ALTER TABLE directly
├─ Large (>1M rows) → add new column → dual write → switch → drop old
└─ Huge (>100M rows) → pt-online-schema-change / pg_repack / gh-ost

Minimum setup: Flyway + db/migration/V*.sql + CI runs flyway migrate + a gen_er.py script for ER sync. When changing big table schema, always think three questions first: can I batch? can I avoid downtime? can I roll back?