Database Migrations with Flyway and Liquibase

Database migration tools like Flyway and Liquibase provide version-controlled, repeatable schema changes that track what has been applied and ensure consistency across environments. This guide covers both tools — when to choose each one, setup, configuration, and best practices for managing database schema evolution in production.

Why Use Migration Tools?

• Version control — schema changes are tracked alongside application code
• Repeatability — same migrations produce identical schemas across dev, staging, production
• Team collaboration — multiple developers can make schema changes without conflicts
• Audit trail — complete history of every schema change and when it was applied
• Rollback capability — undo changes when things go wrong

Flyway

Installation

# Download Flyway CLI
wget -qO- https://download.red-gate.com/maven/release/com/redgate/flyway/flyway-commandline/10.10.0/flyway-commandline-10.10.0-linux-x64.tar.gz | tar xz
sudo ln -s $(pwd)/flyway-10.10.0/flyway /usr/local/bin/flyway

# Or with Docker
docker run --rm flyway/flyway -url=jdbc:postgresql://host:5432/db -user=user -password=pass info

Configuration

# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/myapp
flyway.user=flyway_user
flyway.password=FlyPass123!
flyway.locations=filesystem:./migrations
flyway.baselineOnMigrate=true
flyway.validateOnMigrate=true
flyway.outOfOrder=false
flyway.cleanDisabled=true  # Prevent accidental clean in production

Creating Migrations

Flyway uses a naming convention: V{version}__{description}.sql

# migrations/V1__create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

# migrations/V2__add_orders_table.sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

# migrations/V3__add_user_role.sql
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'customer';
UPDATE users SET role = 'customer' WHERE role IS NULL;

Flyway Commands

# Show migration status
flyway info

# Apply pending migrations
flyway migrate

# Validate applied migrations match local files
flyway validate

# Repair checksums (after editing applied migrations — use with caution)
flyway repair

# Baseline an existing database
flyway baseline -baselineVersion=1

Liquibase

Installation

# Download Liquibase
wget https://github.com/liquibase/liquibase/releases/download/v4.27.0/liquibase-4.27.0.tar.gz
tar xzf liquibase-4.27.0.tar.gz -C /opt/liquibase
sudo ln -s /opt/liquibase/liquibase /usr/local/bin/liquibase

Configuration

# liquibase.properties
changeLogFile=changelog.xml
url=jdbc:postgresql://localhost:5432/myapp
username=liquibase_user
password=LiquiPass123!
driver=org.postgresql.Driver

Changelog Formats

Liquibase supports XML, YAML, JSON, and SQL changelog formats:

<!-- changelog.xml -->
<?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-latest.xsd">

    <changeSet id="1" author="developer">
        <createTable tableName="users">
            <column name="id" type="serial" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="email" type="varchar(255)">
                <constraints unique="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(255)">
                <constraints nullable="false"/>
            </column>
            <column name="created_at" type="timestamp" defaultValueComputed="CURRENT_TIMESTAMP"/>
        </createTable>
        <createIndex tableName="users" indexName="idx_users_email">
            <column name="email"/>
        </createIndex>
    </changeSet>

    <changeSet id="2" author="developer">
        <addColumn tableName="users">
            <column name="role" type="varchar(20)" defaultValue="customer"/>
        </addColumn>
    </changeSet>

    <changeSet id="3" author="developer">
        <createTable tableName="orders">
            <column name="id" type="serial" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="user_id" type="integer">
                <constraints foreignKeyName="fk_orders_user" references="users(id)"/>
            </column>
            <column name="total" type="decimal(10,2)"/>
        </createTable>
        <rollback>
            <dropTable tableName="orders"/>
        </rollback>
    </changeSet>
</databaseChangeLog>

Liquibase Commands

# Show status
liquibase status

# Apply changes
liquibase update

# Rollback last N changesets
liquibase rollbackCount 1

# Generate SQL without applying (dry run)
liquibase updateSQL

# Generate diff between databases
liquibase diff --referenceUrl=jdbc:postgresql://staging:5432/myapp

# Generate changelog from existing database
liquibase generateChangeLog

Flyway vs. Liquibase Comparison

• Flyway — simpler, SQL-first, convention-based naming, great for SQL-focused teams
• Liquibase — more features, database-agnostic XML/YAML, built-in rollback, diff/generate capabilities
• Flyway — better for projects using a single database type
• Liquibase — better for multi-database support and complex enterprise requirements

CI/CD Integration

# GitHub Actions example with Flyway
name: Database Migration
on:
  push:
    branches: [main]
    paths: ['migrations/**']

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Run Flyway migrations
        run: |
          flyway -url="${{ secrets.DB_URL }}" \
                 -user="${{ secrets.DB_USER }}" \
                 -password="${{ secrets.DB_PASS }}" \
                 migrate

Best Practices

• Never modify a migration after it has been applied — create a new migration instead
• Always include both up and down (rollback) logic when using Liquibase
• Test migrations against a copy of production data before deploying
• Use CREATE INDEX CONCURRENTLY in PostgreSQL migrations to avoid table locks
• Keep migrations small and focused — one logical change per migration file
• Include data migrations (backfills) as separate migration files from schema changes
• Set cleanDisabled=true in Flyway to prevent accidental database wipes
• Version your migration tool configuration alongside your application code