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. Esta guía completa cubre installation, migration file creation, CI/CD integration, versioning strategies, and best practices for both tools.

Tabla de Contenidos

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 Instalación and Configuración

Instala Flyway as a standalone tool or Maven/Gradle plugin. Instala 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

# Verifica installation
flyway -version

Configura Flyway:

# Crea 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

Instala 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

Crea Flyway migration files with versioning convention:

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

# Crea 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);

Crea 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;

Crea 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();

Ejecuta Flyway migrations:

# Check migration status
flyway info

# Apply pending migrations
flyway migrate

# Valida migrations
flyway validate

# Get detailed migration info
flyway info -detailedOutput

Ejecuta migrations via Maven:

# Check status
mvn flyway:info

# Apply migrations
mvn flyway:migrate

# Valida migrations
mvn flyway:validate

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

Liquibase Instalación and Configuración

Instala 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

# Verifica installation
liquibase --version

Configura Liquibase:

# Crea 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

Crea XML-based Liquibase changelogs:

# Crea changelog directory
mkdir -p db/changelog

# Crea 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>

Crea individual change files:

# Crea 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>

Crea 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>

Ejecuta 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
    servicios:
      postgres:
        imagen: 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: Valida 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
  tareas:
    - name: Copy migration files
      copy:
        src: ./sql/
        dest: /opt/migrations/

    - name: Valida 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)
# Crea 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

Monitoreo and Validation

Monitorea 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;

Mejores Prácticas

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. Prueba migrations thoroughly
mvn flyway:validate
mvn test # Ejecuta application tests after migration

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

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

# 6. Prueba rollback procedures
# Crea 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

Conclusión

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.