flyway-migrations

Overview

Flyway tracks applied migrations in a per-database flyway_schema_history table and applies pending migrations in order by version number (fw-how).

When to use

• The repo has a db/migration/ (or configured) directory of V*.sql / R*.sql files.
• The user works with Flyway CLI / Docker / Maven plugin / Gradle plugin.
• A CI workflow needs a migration gate against a per-PR ephemeral database (e.g., Testcontainers) before merge.
• The team migrates from manual SQL scripts to versioned migration control.

How to use

Follow Steps 1 - 7 below in order; each numbered step is the single source for that part of the workflow.

Step 1 - Install

Flyway runs on Windows, macOS, Linux, and Docker, plus Maven and Gradle plugin distributions (fw-home). Common install paths:

# Docker (zero-install for CI)
docker run --rm flyway/flyway -url=jdbc:postgresql://host/db -user=usr -password=pwd migrate

# Homebrew (macOS / Linux)
brew install flyway

# Maven plugin (Spring Boot etc.)
# add to pom.xml under <build><plugins>

Step 2 - First migration

Migrations may be written in SQL, Java, or other scripting languages (fw-how). File naming places migrations in the configured locations (default db/migration):

db/migration/
├── V1__create_users.sql
├── V2__add_email_index.sql
├── R__refresh_active_users_view.sql      # repeatable, reruns on checksum change
└── U1__remove_users.sql                   # undo (Flyway Teams)

The prefix scheme:

__ (double underscore) separates version + description; .sql (or configured suffix) marks the file as a migration.

Step 3 - Core commands

The daily loop is flyway info (preview pending) -> flyway migrate (apply pending) -> flyway validate (checksum-verify applied files before deploy). After a failed migration, flyway repair fixes flyway_schema_history - never edit an applied file. Full command reference (baseline, undo, clean, and the rest): references/commands.md.

Step 4 - Pending-migration semantics

Migrations with a version lower than the history table's current version are ignored by default; the rest are pending - available but not applied (fw-how). Safety property: a developer who pulls main and runs flyway migrate applies only the new migrations; those already in flyway_schema_history are not re-run.

Step 5 - Configuration

Configuration via flyway.conf file, env vars (FLYWAY_*), or CLI flags. Key settings:

flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=myuser
flyway.password=mypass
flyway.locations=filesystem:db/migration,classpath:db/migration
flyway.baselineOnMigrate=true        # auto-baseline empty schemas
flyway.cleanDisabled=true            # CRITICAL for prod - disable destructive `clean`
flyway.outOfOrder=false              # reject migrations with versions lower than max applied
flyway.validateOnMigrate=true        # checksum-validate before applying

cleanDisabled=true is a mandatory production guard - flyway clean drops every object in the schema. Always set this in production config; only enable for ephemeral test databases.

Step 6 - CI integration

Gate every PR on an ephemeral DB (Docker / Testcontainers): spin the DB, apply migrations, run tests against the migrated schema. The full GitHub Actions job and the Testcontainers @BeforeAll pattern are in references/commands.md.

Step 7 - Composition with sister tools

Before merge, apply adversarial review of new migrations - classify each as additive / breaking / data-loss / locking.

Worked example

Add an index on users.email and ship it through CI:

1. Create db/migration/V2__add_email_index.sql with CREATE INDEX idx_users_email ON users(email);.
2. Locally run flyway info - it lists V2 as pending while V1 shows applied.
3. flyway migrate applies only V2 and appends a row to flyway_schema_history with its checksum.
4. In the PR, CI starts an ephemeral Postgres, runs the Docker flyway ... migrate, then mvn test against the migrated schema.
5. A teammate later edits the merged V2 file; on their next run flyway validate fails with a checksum mismatch, so they add V3__... instead of mutating V2.

Anti-patterns

Limitations

• Undo migrations are a Teams (paid) feature - OSS users implement rollback manually via inverse versioned migrations.
• flyway clean is irreversible; the cleanDisabled=true guard is the only protection.
• 50+ supported DBMS but rule depth varies - consult per-database pages on fw-home for vendor-specific syntax.
• Requires JVM (CLI bundles its own JRE; Docker / Maven plugin inherit it).

References

• references/commands.md - full command table + CI job
• fw-home - main documentation, command list, supported databases
• fw-how - conceptual model: schema_history table, pending-migration semantics, ordering
• github.com/flyway/flyway - repository
• liquibase-migrations, atlas-migrations, sqlmesh-migrations - sister tools (Liquibase = changelog-driven; Atlas = declarative HCL; SQLMesh = data-pipeline + schema)