Flyway Database Migrations

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: flyway for comprehensive documentation.

Maven Configuration

<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>

<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-database-postgresql</artifactId> </dependency>

<plugin> <groupId>org.flywaydb</groupId> <artifactId>flyway-maven-plugin</artifactId> <version>${flyway.version}</version> <configuration> <url>jdbc:postgresql://localhost:5432/mydb</url> <user>myuser</user> <password>mypass</password> </configuration> </plugin>

Application Configuration

spring: flyway: enabled: true baseline-on-migrate: true locations: classpath:db/migration validate-on-migrate: true out-of-order: false clean-disabled: true # Prevent clean in production!

Migration Naming Convention

V{version}{description}.sql # Versioned migrations U{version}{description}.sql # Undo migrations (Teams/Enterprise) R__{description}.sql # Repeatable migrations

Examples:

• V1__create_users_table.sql

• V1.1__add_email_index.sql

• V2__create_departments_table.sql

• R__create_views.sql

Initial Schema Migration

-- V1__init_schema.sql

-- Users table CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, role VARCHAR(20) NOT NULL DEFAULT 'USER', status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP, created_by VARCHAR(255), updated_by VARCHAR(255) );

CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_status ON users(status); CREATE INDEX idx_users_role ON users(role);

-- Roles table CREATE TABLE roles ( id BIGSERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) );

-- Insert default roles INSERT INTO roles (name, description) VALUES ('ADMIN', 'System administrator'), ('MANAGER', 'Department manager'), ('USER', 'Regular user');

Add Column Migration

-- V2__add_phone_to_users.sql

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

CREATE INDEX idx_users_phone ON users(phone);

Add Foreign Key Migration

-- V3__create_departments.sql

CREATE TABLE departments ( id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, code VARCHAR(10) NOT NULL UNIQUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );

ALTER TABLE users ADD COLUMN department_id BIGINT;

ALTER TABLE users ADD CONSTRAINT fk_users_department FOREIGN KEY (department_id) REFERENCES departments(id);

CREATE INDEX idx_users_department ON users(department_id);

Data Migration

-- V4__migrate_legacy_data.sql

-- Update existing data UPDATE users SET role = 'USER' WHERE role IS NULL;

-- Migrate data from legacy format INSERT INTO departments (name, code) SELECT DISTINCT department_name, UPPER(SUBSTRING(department_name, 1, 3)) FROM legacy_employees WHERE department_name IS NOT NULL;

Repeatable Migration (Views)

-- R__create_user_summary_view.sql

DROP VIEW IF EXISTS user_summary;

CREATE VIEW user_summary AS SELECT u.id, u.name, u.email, u.role, u.status, d.name AS department_name, u.created_at FROM users u LEFT JOIN departments d ON u.department_id = d.id;

Java-based Migration

@Component public class V5__ComplexDataMigration implements JavaMigration {

@Override
public void migrate(Context context) throws Exception {
    try (Statement stmt = context.getConnection().createStatement()) {
        // Complex migration logic
        ResultSet rs = stmt.executeQuery("SELECT id, data FROM legacy_table");
        while (rs.next()) {
            // Process and migrate data
        }
    }
}

@Override
public Integer getChecksum() { return null; }

@Override
public MigrationVersion getVersion() {
    return MigrationVersion.fromVersion("5");
}

@Override
public String getDescription() {
    return "Complex data migration";
}

}

Callback for Logging

@Component public class FlywayCallback implements Callback {

private static final Logger log = LoggerFactory.getLogger(FlywayCallback.class);

@Override
public boolean supports(Event event, Context context) {
    return event == Event.AFTER_EACH_MIGRATE ||
           event == Event.AFTER_MIGRATE_ERROR;
}

@Override
public boolean canHandleInTransaction(Event event, Context context) {
    return true;
}

@Override
public void handle(Event event, Context context) {
    if (event == Event.AFTER_EACH_MIGRATE) {
        MigrationInfo info = context.getMigrationInfo();
        log.info("Migrated: {} - {} ({}ms)",
            info.getVersion(),
            info.getDescription(),
            info.getExecutionTime());
    } else if (event == Event.AFTER_MIGRATE_ERROR) {
        log.error("Migration failed!");
    }
}

@Override
public String getCallbackName() {
    return "LoggingCallback";
}

}

Maven Commands

Run migrations

mvn flyway:migrate

Show migration info

mvn flyway:info

Validate migrations

mvn flyway:validate

Repair checksum mismatches

mvn flyway:repair

Clean database (careful!)

mvn flyway:clean

Baseline existing database

mvn flyway:baseline

Best Practices

Practice Description

Never edit applied migrations Create new migration instead

Test migrations Use H2 in tests

Backup before migrate Especially in production

Use transactions Wrap DDL in transactions

Version control Keep migrations in Git

Naming convention Descriptive names

When NOT to Use This Skill

• General migration strategies - Use migrations skill for concepts

• Liquibase - Use Liquibase-specific documentation

• Prisma migrations - Use prisma skill

• TypeORM migrations - Use typeorm skill

Anti-Patterns

Anti-Pattern Problem Solution

Modifying applied migrations Checksum validation fails Create new migration instead

No baseline on existing DB Fails on migrate Use baseline-on-migrate: true

Complex logic in SQL migrations Hard to test, debug Use Java-based migrations

Ignoring validation Inconsistencies between envs Always validate before deploy

clean-disabled: false in prod Risk of data loss Always disable clean in production

Out-of-order migrations Version conflicts Use sequential versioning

Quick Troubleshooting

Problem Diagnostic Fix

Checksum mismatch Compare file with flyway_schema_history flyway repair or create new

Out of order error Check version numbers Fix versioning or set out-of-order: true

Failed migration Check flyway_schema_history.success Repair, fix issue, retry

Missing migration Check locations config Verify classpath:db/migration path

Baseline conflict Check baseline-version Set correct baseline version

Reference Documentation

• Flyway Documentation

• Migrations

• Callbacks

• Spring Boot Integration