Database Schema Migration Tools: Flyway and Liquibase

Database schema migrations are critical for managing schema changes across development, testing, and production environments. Flyway and Liquibase are industry-standard tools that track and apply database migrations automatically, ensuring consistency and enabling rollback capabilities. This comprehensive guide covers installation, migration file creation, CI/CD integration, versioning strategies, and best practices for both tools.

Table of Contents

Migration Concepts

Database migrations track and apply incremental schema changes, allowing teams to version control schema definitions and reproduce database state consistently. Each migration is idempotent and versioned, enabling reproducible deployments and rollback capabilities. Migration tools maintain a history table tracking which migrations have been applied, preventing re-execution of previously run migrations.

Flyway Installation and Setup

Install Flyway as a standalone tool or Maven/Gradle plugin. Install standalone Flyway:

# Download latest Flyway
cd /opt
sudo wget https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/9.20.1/flyway-commandline-9.20.1-linux-x64.tar.gz

# Extract
sudo tar -xzf flyway-commandline-9.20.1-linux-x64.tar.gz
sudo ln -s flyway-9.20.1 flyway

# Add to PATH
echo "export PATH=/opt/flyway:\$PATH" >> ~/.bashrc
source ~/.bashrc

# Verify installation
flyway -version

Configure Flyway:

# Create configuration directory
mkdir -p ~/.flyway
nano ~/.flyway/flyway.conf

Add configuration:

# Database connection
flyway.driver=org.postgresql.Driver
flyway.url=jdbc:postgresql://192.168.1.10:5432/myapp
flyway.user=flyway_user
flyway.password=flyway_password

# Migration location
flyway.locations=filesystem:./sql

# Baseline version (for existing databases)
flyway.baselineVersion=1
flyway.baselineDescription=Baseline

# Validation
flyway.validateOnMigrate=true
flyway.cleanDisabled=false
flyway.outOfOrder=false

Or specify configuration via command line:

flyway -url=jdbc:postgresql://192.168.1.10:5432/myapp \
       -user=flyway_user \
       -password=flyway_password \
       -locations=filesystem:./sql \
       info

Install Flyway as Maven plugin:

# Add to pom.xml
nano pom.xml

Add plugin configuration:

<plugin>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-maven-plugin</artifactId>
  <version>9.20.1</version>
  <configuration>
    <url>jdbc:postgresql://192.168.1.10:5432/myapp</url>
    <user>flyway_user</user>
    <password>flyway_password</password>
    <locations>
      <location>filesystem:src/main/resources/db/migration</location>
    </locations>
  </configuration>
</plugin>

Flyway Migrations

Create Flyway migration files with versioning convention:

# Create migration directory
mkdir -p src/main/resources/db/migration

# Create first migration
nano src/main/resources/db/migration/V1__Create_users_table.sql

Migration file naming convention: V[version]__[description].sql

Add migration SQL:

-- V1__Create_users_table.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

Create additional migrations:

# Second migration
nano src/main/resources/db/migration/V2__Add_password_column.sql

Add:

-- V2__Add_password_column.sql
ALTER TABLE users ADD COLUMN password_hash VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

Create repeatable migrations (run on every deploy):

# Repeatable migrations use R prefix
nano src/main/resources/db/migration/R__Create_or_update_functions.sql

Add:

-- R__Create_or_update_functions.sql
CREATE OR REPLACE FUNCTION update_users_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS users_update_timestamp ON users;
CREATE TRIGGER users_update_timestamp BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_users_updated_at();

Run Flyway migrations:

# Check migration status
flyway info

# Apply pending migrations
flyway migrate

# Validate migrations
flyway validate

# Get detailed migration info
flyway info -detailedOutput

Run migrations via Maven:

# Check status
mvn flyway:info

# Apply migrations
mvn flyway:migrate

# Validate migrations
mvn flyway:validate

# Clean database (USE WITH CAUTION)
mvn flyway:clean

Liquibase Installation and Setup

Install Liquibase:

# Download latest Liquibase
cd /opt
sudo wget https://github.com/liquibase/liquibase/releases/download/v4.24.0/liquibase-4.24.0.tar.gz

# Extract
sudo tar -xzf liquibase-4.24.0.tar.gz
sudo ln -s liquibase-4.24.0 liquibase

# Add to PATH
echo "export PATH=/opt/liquibase:\$PATH" >> ~/.bashrc
source ~/.bashrc

# Verify installation
liquibase --version

Configure Liquibase:

# Create liquibase properties file
nano liquibase.properties

Add:

# Database connection
url=jdbc:postgresql://192.168.1.10:5432/myapp
username=flyway_user
password=flyway_password
driver=org.postgresql.Driver

# Changelog file location
changeLogFile=db/changelog/db.changelog-master.xml

# Contexts and labels
contexts=
labels=

Liquibase Changelogs

Create XML-based Liquibase changelogs:

# Create changelog directory
mkdir -p db/changelog

# Create master changelog
nano db/changelog/db.changelog-master.xml

Add master changelog:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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.1.xsd">

    <!-- Include all changelogs -->
    <include file="db/changelog/changes/001-create-users-table.xml"/>
    <include file="db/changelog/changes/002-add-email-index.xml"/>
    <include file="db/changelog/changes/003-create-functions.xml"/>

</databaseChangeLog>

Create individual change files:

# Create first change file
nano db/changelog/changes/001-create-users-table.xml

Add:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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.1.xsd">

    <changeSet id="001" author="dev-team">
        <createTable tableName="users">
            <column name="id" type="SERIAL">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="email" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="password_hash" type="VARCHAR(255)"/>
            <column name="is_active" type="BOOLEAN" defaultValue="true"/>
            <column name="created_at" type="TIMESTAMP" defaultValueDate="CURRENT_TIMESTAMP"/>
            <column name="updated_at" type="TIMESTAMP" defaultValueDate="CURRENT_TIMESTAMP"/>
        </createTable>
    </changeSet>

</databaseChangeLog>

Create second change file:

nano db/changelog/changes/002-add-email-index.xml

Add:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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.1.xsd">

    <changeSet id="002" author="dev-team">
        <createIndex tableName="users" indexName="idx_users_email">
            <column name="email"/>
        </createIndex>
    </changeSet>

    <changeSet id="003" author="dev-team">
        <addColumn tableName="users">
            <column name="phone" type="VARCHAR(20)"/>
        </addColumn>
    </changeSet>

</databaseChangeLog>

Run Liquibase migrations:

# Check status
liquibase status --changelog-file=db/changelog/db.changelog-master.xml

# Apply changes
liquibase update --changelog-file=db/changelog/db.changelog-master.xml

# Preview next changes
liquibase futureRollbackSql --changelog-file=db/changelog/db.changelog-master.xml

# Generate SQL script (for review)
liquibase updateSQL --changelog-file=db/changelog/db.changelog-master.xml > migration.sql

CI/CD Integration

Integrate migrations into CI/CD pipelines:

# GitLab CI example
stages:
  - build
  - test
  - deploy

database_migration:
  stage: test
  script:
    - mvn flyway:validate
    - mvn flyway:info
  only:
    - merge_requests

deploy_migrations:
  stage: deploy
  script:
    - mvn flyway:migrate
  environment:
    name: production
  only:
    - main

GitHub Actions example:

name: Database Migrations

on:
  push:
    branches: [main, develop]

jobs:
  migrate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: myapp
          POSTGRES_USER: flyway_user
          POSTGRES_PASSWORD: flyway_password
    steps:
      - uses: actions/checkout@v3
      
      - name: Set up Java
        uses: actions/setup-java@v3
        with:
          java-version: '17'
          distribution: 'temurin'
      
      - name: Validate migrations
        run: mvn flyway:validate
      
      - name: Check migration status
        run: mvn flyway:info
      
      - name: Apply migrations
        if: github.ref == 'refs/heads/main'
        run: mvn flyway:migrate

Ansible playbook for migrations:

---
- hosts: database_servers
  tasks:
    - name: Copy migration files
      copy:
        src: ./sql/
        dest: /opt/migrations/

    - name: Validate migrations
      shell: flyway -locations=filesystem:/opt/migrations validate
      environment:
        FLYWAY_URL: "jdbc:postgresql://localhost:5432/myapp"
        FLYWAY_USER: "{{ db_user }}"
        FLYWAY_PASSWORD: "{{ db_password }}"

    - name: Apply migrations
      shell: flyway -locations=filesystem:/opt/migrations migrate
      environment:
        FLYWAY_URL: "jdbc:postgresql://localhost:5432/myapp"
        FLYWAY_USER: "{{ db_user }}"
        FLYWAY_PASSWORD: "{{ db_password }}"
      when: ansible_env.ENV == "production"

Rollback Strategies

Implement proper rollback capabilities:

# Flyway rollback (requires undo migrations)
# Create undo migration: U2__Add_password_column.sql
nano src/main/resources/db/migration/U2__Add_password_column.sql

# Add undo SQL
ALTER TABLE users DROP COLUMN password_hash;

# Undo migration
mvn flyway:undo

Liquibase rollback:

# Rollback to specific changeset
liquibase rollbackToDate 2024-01-15 --changelog-file=db/changelog/db.changelog-master.xml

# Rollback N changes
liquibase rollbackCount 5 --changelog-file=db/changelog/db.changelog-master.xml

# Dry-run rollback
liquibase rollbackSQL --changelog-file=db/changelog/db.changelog-master.xml > rollback.sql

Monitoring and Validation

Monitor migration status:

# Flyway info with detailed output
flyway -url=jdbc:postgresql://192.168.1.10:5432/myapp \
       -user=flyway_user \
       -password=flyway_password \
       info -detailedOutput

# Get migration history
SELECT version, description, type, installed_on, success
FROM flyway_schema_history
ORDER BY version DESC;

# Liquibase status
liquibase status --changelog-file=db/changelog/db.changelog-master.xml --verbose

# Liquibase history
SELECT ID, AUTHOR, CHANGELOGFILE, DATEEXECUTED, TAG, EXECTYPE, DEPLOYMENT_ID
FROM databasechangelog
ORDER BY DATEEXECUTED DESC;

Best Practices

Follow these practices for effective migration management:

# 1. Use version control for all migration files
git add src/main/resources/db/migration/*.sql
git commit -m "Add user profile migration"

# 2. Keep migrations focused and small
# Each migration should do one logical thing

# 3. Test migrations thoroughly
mvn flyway:validate
mvn test # Run application tests after migration

# 4. Use checksums to detect unauthorized changes
# Flyway and Liquibase track checksums automatically

# 5. Never modify applied migrations
# Create new migrations to undo changes

# 6. Test rollback procedures
# Create undo migrations and test them

# 7. Document complex migrations
-- Migration description in SQL comments
-- This migration adds audit tracking to users table
-- Related issue: JIRA-1234

# 8. Use dry-run before production migrations
liquibase updateSQL > migration_review.sql
# Review SQL before applying

Conclusion

Flyway and Liquibase provide powerful, industry-standard solutions for managing database schema migrations. Flyway's simplicity makes it ideal for teams preferring SQL-based migrations, while Liquibase's flexibility supports multiple formats and advanced features. By integrating migrations into CI/CD pipelines, maintaining proper version control, and testing thoroughly, you can confidently deploy schema changes across environments. Proper migration management ensures database consistency, enables rollback capabilities, and documents schema evolution over time.