Tags: flyway, database, migrations, devops, java, spring-boot, sql

Level: intermediate

TL;DR: Open-source database migration tool that uses versioned SQL or Java scripts to track, automate, and apply schema changes reliably across environments.

Key concepts

Schema versioning tool favoring plain SQL scripts over DSL abstractions.

Tracks applied migrations using the metadata table flyway_schema_history.

Versioned migrations run once in ascending numeric order.

Undo migrations allow reverting existing versioned migrations.

Repeatable migrations execute whenever their script checksum changes.

Calculates SHA-256 checksums on scripts to detect unexpected modifications.

Baseline sets a starting version on pre-existing, unmanaged databases.

Repair fixes broken metadata entries and recalculates modified script checksums.

Supports programmatic execution via Java API and build tools like Maven/Gradle.

Prevents concurrent migration execution across nodes via distributed metadata locking.

Typical questions and ready answers

Q: How does Flyway know which migrations to run?R: Flyway checks the flyway_schema_history table in the target database. It scans configured migration locations, compares script versions and checksums against existing history records, and applies only new versions in ascending order.

Q: What happens if someone edits an already-applied migration script?R: Flyway fails validation during startup because the script's checksum no longer matches the stored checksum in flyway_schema_history. The execution aborts immediately to prevent schema drift.

Q: How do you handle a failed migration script in production?R: I inspect the error in flyway_schema_history, manually roll back any partially applied DDL/DML if non-transactional, run flyway repair to remove the failed entry, and apply a corrected migration script.

Q: How do Repeatable Migrations differ from Versioned Migrations?R: Versioned migrations have unique version numbers, run once, and are immutable. Repeatable migrations use prefix R__, have no version numbers, run after all versioned scripts, and re-execute whenever their content changes.

Pitfalls and common mistakes

Modifying an already applied versioned migration script causes checksum validation failure.

Relying on automated rollback on databases without transactional DDL support like MySQL or Oracle.

Inserting scripts out of sequence without enabling the outOfOrder configuration flag.

Running parallel application instances without table locking support, risking race conditions.

Committing schema changes without matching code changes in backward-compatible rollout patterns.

Snippets and commandsSQL

```-- V1__create_users_table.sql (Versioned migration)

CREATE TABLE users (

id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

username VARCHAR(50) NOT NULL UNIQUE,

email VARCHAR(255) NOT NULL,

created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

);

-- R__create_active_users_view.sql (Repeatable migration)

CREATE OR REPLACE VIEW active_users_view AS

SELECT id, username, email

FROM users

WHERE email IS NOT NULL;

Core CLI commands

flyway migrate

flyway info

flyway validate

flyway repair

flyway baseline -baselineVersion=1.0.0

application.properties (Spring Boot)

spring.flyway.enabled=true

spring.flyway.locations=classpath:db/migration

spring.flyway.baseline-on-migrate=true

spring.flyway.validate-on-migrate=true

```

To review

[ ] Filename naming conventions: V for versioned, U for undo, R for repeatable.

[ ] Transactional vs non-transactional DDL behavior across different database engines.

[ ] Blue-green deployment schema strategies: expand and contract pattern.

[ ] Usage of flyway.baselineOnMigrate for legacy databases.

[ ] Integration with CI/CD delivery pipelines and Kubernetes init containers.