Database Version Control: Flyway and Liquibase Guide

-- V1__create_users.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);

-- V2__add_orders.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total DECIMAL(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    shipping_address TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

# flyway.toml (Flyway 10+)
[environments.default]
url = "jdbc:postgresql://localhost:5432/mydb"
user = "app_user"
password = "${DB_PASSWORD}"
schemas = ["public"]

[flyway]
locations = ["filesystem:db/migrations", "filesystem:db/repeatable"]
baselineOnMigrate = true
baselineVersion = 0

# GitHub Actions - Flyway migration step
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Run Flyway migrations
        run: |
          docker run --rm \
            -v ${{ github.workspace }}/db:/flyway/project/db \
            -e FLYWAY_URL=jdbc:postgresql://${{ secrets.DB_HOST }}:5432/${{ secrets.DB_NAME }} \
            -e FLYWAY_USER=${{ secrets.DB_USER }} \
            -e FLYWAY_PASSWORD=${{ secrets.DB_PASSWORD }} \
            flyway/flyway:10 migrate

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd">

    <!-- Include individual changelogs -->
    <include file="changelogs/001-create-users.xml"/>
    <include file="changelogs/002-create-orders.xml"/>
    <include file="changelogs/003-add-indexes.sql"/>
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd">

    <changeSet id="1" author="dodatech">
        <createTable tableName="users">
            <column name="id" type="bigint" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="varchar(100)">
                <constraints nullable="false"/>
            </column>
            <column name="email" type="varchar(255)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>

        <rollback>
            <dropTable tableName="users"/>
        </rollback>
    </changeSet>

    <changeSet id="2" author="dodatech">
        <addColumn tableName="users">
            <column name="last_login" type="timestamp"/>
        </addColumn>
        <rollback>
            <dropColumn tableName="users" columnName="last_login"/>
        </rollback>
    </changeSet>
</databaseChangeLog>

# Deploy pending changesets
liquibase --changeLogFile=db/changelog-master.xml update

# Rollback last 2 changesets
liquibase --changeLogFile=db/changelog-master.xml rollbackCount 2

# Rollback to specific date
liquibase --changeLogFile=db/changelog-master.xml rollbackToDate 2026-06-20

# Generate diff between databases
liquibase --changeLogFile=db/changelog-master.xml diff \
  --referenceUrl=jdbc:postgresql://prod-host/proddb

# Require unique migration versions in CI
jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Check for duplicate migration versions
        run: |
          db/migrations/*.sql | grep -oP 'V\d+' | sort | uniq -d
          if [ $? -eq 0 ]; then
            echo "Duplicate migration versions found!"
            exit 1
          fi

-- Developer A creates V3__add_profile.sql
-- Developer B creates V3__add_settings.sql (conflict!)

-- Resolution: Rename one to V4 and reorder
-- V3__add_profile.sql stays as-is
-- V4__add_settings.sql (renamed, depends on V3 only if needed)

-- R__create_user_view.sql
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE deleted_at IS NULL;

-- R__grant_permissions.sql
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO readwrite_role;

-- V5__add_preferred_name.sql
-- Step 1: Add column as nullable (no downtime)
ALTER TABLE users ADD COLUMN preferred_name VARCHAR(100);

-- V6__backfill_preferred_name.sql
-- Step 2: Backfill in batches (run separately, may take hours)
DO $$
DECLARE
    batch_size INT := 10000;
BEGIN
    LOOP
        UPDATE users
        SET preferred_name = name
        WHERE preferred_name IS NULL
        AND ctid IN (
            SELECT ctid FROM users
            WHERE preferred_name IS NULL
            LIMIT batch_size FOR UPDATE SKIP LOCKED
        );
        EXIT WHEN NOT FOUND;
        COMMIT;
    END LOOP;
END $$;

-- V7__make_preferred_name_not_null.sql
-- Step 3: Add NOT NULL constraint (run after backfill)
ALTER TABLE users ALTER COLUMN preferred_name SET NOT NULL;