Database Migrations with Flyway & Liquibase in Java

Why Database Migrations?

Imagine this: your code is in Git, perfectly versioned. But your database? Someone ran a SQL script manually in production, another dev has a different schema locally, and no one knows what the "correct" state should be.

Database migrations solve this. Every change is a versioned script that runs in order, everywhere.

Reproducible

Same migrations run on dev, test, and prod. Identical databases everywhere.

Version Controlled

Database changes tracked in Git alongside your code.

Automated

Migrations run automatically on application startup or deployment.

Flyway: Simple and Straightforward

<!-- pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>  <!-- or flyway-database-postgresql -->
</dependency>

# application.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true

Flyway Migration Files

Create SQL files in src/main/resources/db/migration/:

-- V1__Create_users_table.sql
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V2__Add_role_to_users.sql
ALTER TABLE users ADD COLUMN role VARCHAR(50) DEFAULT 'USER';

-- V3__Create_orders_table.sql
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Naming Convention

V1__Description.sql      # Versioned migration (runs once)
V1.1__Description.sql    # Sub-version
V2__Another_change.sql   # Next version

R__Repeatable_view.sql   # Repeatable (runs when changed)

Flyway Commands

# Maven commands
mvn flyway:info      # Show migration status
mvn flyway:migrate   # Run pending migrations
mvn flyway:validate  # Check migrations match database
mvn flyway:repair    # Fix failed migration metadata
mvn flyway:clean     # DROP everything (dangerous!)

# Or configure in pom.xml
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <configuration>
        <url>jdbc:mysql://localhost:3306/mydb</url>
        <user>root</user>
        <password>password</password>
    </configuration>
</plugin>

Liquibase: More Features, More Control

<!-- pom.xml -->
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

# application.properties
spring.liquibase.enabled=true
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml

Liquibase Changelog

Create src/main/resources/db/changelog/db.changelog-master.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-4.20.xsd">

    <changeSet id="1" author="developer">
        <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 unique="true" nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="2" author="developer">
        <addColumn tableName="users">
            <column name="role" type="VARCHAR(50)" defaultValue="USER"/>
        </addColumn>
    </changeSet>

    <changeSet id="3" author="developer">
        <createIndex tableName="users" indexName="idx_users_email">
            <column name="email"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>

Liquibase with YAML (Cleaner)

# db.changelog-master.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: developer
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: name
                  type: VARCHAR(100)
                  constraints:
                    nullable: false
              - column:
                  name: email
                  type: VARCHAR(255)
                  constraints:
                    unique: true

  - changeSet:
      id: 2
      author: developer
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: created_at
                  type: TIMESTAMP
                  defaultValueComputed: CURRENT_TIMESTAMP

Rollback Support

<changeSet id="4" author="developer">
    <addColumn tableName="users">
        <column name="phone" type="VARCHAR(20)"/>
    </addColumn>

    <!-- Liquibase can auto-generate rollback for simple changes -->
    <!-- Or define custom rollback: -->
    <rollback>
        <dropColumn tableName="users" columnName="phone"/>
    </rollback>
</changeSet>

# Rollback commands
mvn liquibase:rollback -Dliquibase.rollbackCount=1
mvn liquibase:rollback -Dliquibase.rollbackTag=v1.0
mvn liquibase:rollback -Dliquibase.rollbackDate=2024-01-01

Flyway vs Liquibase

Feature	Flyway	Liquibase
Learning Curve	Simple - just SQL files	More complex - XML/YAML/JSON
Rollback	Manual (paid feature)	Automatic rollback generation
Database Agnostic	Write SQL per database	Same changelog, multiple DBs
Best For	Simple projects, SQL-first	Enterprise, multi-database

Best Practices

Never edit migrations: Once a migration runs in any environment, treat it as immutable
Test migrations: Run against a copy of production data before deploying
Keep migrations small: One logical change per migration file
Use descriptive names: V3__Add_index_to_users_email.sql
Include rollback: For critical changes, always define how to undo
Separate DDL and DML: Schema changes and data changes in different migrations

Database Migrations