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 |