Flyway Production Cheat Sheet

Executive Summary

Flyway is a minimalist, opinionated database migration tool that versions schema and reference-data changes through ordered migration scripts, tracked in a schema history table and applied consistently across all environments. It provides a small but powerful command set (migrate, clean, info, validate, repair, baseline, undo in paid editions) and integrates cleanly with Java stacks, especially Spring Boot, as well as with CLI, Maven, Gradle, and Docker workflows.

This cheat sheet focuses on production-grade usage patterns: team-friendly versioning strategies, Spring Boot integration, advanced features (callbacks, placeholders, Java migrations), CI/CD wiring, and operational concerns such as zero-downtime, rollbacks, and debugging.

---

1. Overview (Quick Recap)

1.1 What Flyway Does

• Treats your database schema like code: changes are stored as ordered migration scripts in VCS and applied incrementally to each environment.
• Manages a schema history table (default flyway_schema_history) in each database to record applied migrations, checksums, execution order, and success state.
• On each run, scans migration locations, compares against history, and executes only pending migrations in order, guaranteeing deterministic evolution.

1.2 Core Concepts

• Migrations
  • Scripts that describe an atomic database change, usually one semantic step such as “add column” or “create index”.
  • Written as SQL or Java; SQL is preferred for most DDL/DML, Java for complex data fixes or logic.
• Schema history table
  • Created automatically on first migration; default name flyway_schema_history (older versions used schema_version).
  • Stores: version, description, type, script name, checksum, installed-on timestamp, execution time, and success flag.
• Versioning
  • Versioned migrations use a numeric or dotted version (for example, 1, 1.1, 2025.03.31.01) that defines ordering.
  • Repeatable migrations have no explicit version and are re-run when their checksum changes.

---

2. Installation & Setup

2.1 Core Distribution Options

• CLI: stand-alone executable for all major OSes; good for local use, ad-hoc migrations, and CI/CD steps.
• Maven plugin: integrates migrations into Java build lifecycle (for example, mvn flyway:migrate), useful in legacy or non-Spring projects.
• Gradle plugin: similar integration for Gradle builds.
• Docker: Flyway provides official Docker images, allowing migrations to run as containers alongside the application or in pipelines.
• Embedded in Java: Use org.flywaydb.core.Flyway directly for programmatic control.

2.2 CLI Quick Setup

Typical directory layout:

flyway/
  flyway
  conf/
    flyway.conf
  sql/
    V1__init_schema.sql
    V2__add_user_table.sql

Minimal conf/flyway.conf for PostgreSQL:

flyway.url=jdbc:postgresql://localhost:5432/appdb
flyway.user=app
flyway.password=secret
flyway.locations=filesystem:sql

Run migrations:

./flyway migrate

2.3 Maven Integration

Add plugin to pom.xml:

<build>
  <plugins>
    <plugin>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-maven-plugin</artifactId>
      <version>${flyway.version}</version>
      <configuration>
        <url>jdbc:postgresql://localhost:5432/appdb</url>
        <user>app</user>
        <password>${db.password}</password>
        <locations>
          <location>classpath:db/migration</location>
        </locations>
      </configuration>
    </plugin>
  </plugins>
</build>

Run:

mvn flyway:migrate
mvn flyway:info

2.4 Gradle Integration

Basic Gradle configuration:

plugins {
  id 'org.flywaydb.flyway' version '10.20.0'
}

dependencies {
  implementation 'org.postgresql:postgresql:42.7.1'
}

flyway {
  url = 'jdbc:postgresql://localhost:5432/appdb'
  user = 'app'
  password = System.getenv('DB_PASSWORD')
  locations = ['classpath:db/migration']
}

Run:

gradle flywayMigrate
gradle flywayInfo

2.5 Docker Usage

Basic example with PostgreSQL:

docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  flyway/flyway:latest \
  -url=jdbc:postgresql://host.docker.internal:5432/appdb \
  -user=app \
  -password=secret \
  migrate

Use bind mounts or images that include migrations baked into the container image for deterministic pipelines.

2.6 Spring Boot Integration (high level)

• Use org.flywaydb:flyway-core and database driver dependency; Boot auto-configures Flyway if it finds both on the classpath and a DataSource.
• Default migration location is classpath:db/migration with standard naming conventions.
• Configuration is typically done via application.yml or application.properties using spring.flyway.* keys.

2.7 Folder Structure Best Practices

• For multi-module or microservice setups, keep migrations per service inside that service’s artifact:

service-a/
  src/main/resources/db/migration
service-b/
  src/main/resources/db/migration

• For monoliths, use logical subfolders or schemas per bounded context if needed, but still maintain a single logical migration sequence per database.
• Keep callbacks and repeatable scripts in the same tree, optionally under separate folders (for example, db/callbacks) if you prefer separation.

---

3. Migration Types

3.1 Versioned vs Repeatable Migrations

• Versioned
  • Prefix V, followed by version and __ description, for example V1__init.sql, V2025_03_31_01__add_orders.sql.
  • Executed exactly once per database; never edited after application in production.
• Repeatable
  • Prefix R__ only, no explicit version, for example R__views.sql, R__refresh_materialized_views.sql.
  • Re-executed whenever checksum changes, after all pending versioned migrations.
  • Good for views, functions, procedures, reference data snapshots built idempotently.

3.2 Naming Conventions

Recommended patterns:

• Versioned (SQL):

V1__init_schema.sql
V1_1__seed_reference_data.sql
V2_0_0__add_customer_table.sql
V2025_03_31_01__add_invoice_indexes.sql

• Repeatable (SQL):

R__rebuild_reporting_views.sql
R__refresh_dimension_tables.sql

• Java migrations:

V3__migrate_legacy_customer_data.java
R__recalc_statistics.java

3.3 Order of Execution

• Versioned migrations run first, ordered by version number, then filename if versions are equal.
• After all pending versioned migrations complete, repeatable migrations run ordered lexicographically.
• For schemas under baseline, migrations with versions greater than baseline version are eligible to run; lower versions are ignored.

---

4. Core Commands (CLI + Maven/Gradle)

Flyway exposes seven main commands.

4.1 migrate

• Applies pending migrations in order.
• CLI:

flyway migrate
flyway -locations=filesystem:sql -url=jdbc:postgresql://... migrate

• Maven:

mvn flyway:migrate

• Gradle:

gradle flywayMigrate

4.2 clean (never in production)

• Drops all objects in configured schemas, effectively wiping the database.
• Useful for dev/test reset; disable in production via flyway.cleanDisabled=true or environment-specific config.
• CLI:

flyway clean

4.3 info

• Shows current schema version, pending migrations, and history.
• CLI: flyway info
• Maven: mvn flyway:info
• Gradle: gradle flywayInfo

4.4 validate

• Verifies that applied migrations match files on disk by checksum and ordering.
• Catches accidental edits to applied scripts and missing scripts before running new migrations.
• CLI: flyway validate

4.5 repair

• Repairs the schema history table (for example, updates invalid checksums, removes failed entries) without changing the database schema itself.
• Typically used after fixing an out-of-band modification or when a script was manually adjusted and the change is known to be safe.
• CLI: flyway repair

4.6 baseline

• Brings an existing database under Flyway control without replaying all historical changes.
• Sets a baseline version; migrations below this version are ignored, and newer ones are applied on subsequent migrate calls.

flyway baseline -baselineVersion=100 -baselineDescription="Initial baseline"

4.7 undo (Enterprise only)

• Executes undo migrations (prefix U) that reverse specific versioned migrations; only in paid editions.
• Community users must implement rollback via forward-only fixes.

flyway undo

---

5. Configuration Deep Dive

5.1 flyway.conf (CLI)

Common options:

flyway.url=jdbc:postgresql://db:5432/appdb
flyway.user=app
flyway.password=${FLYWAY_DB_PASSWORD}
flyway.schemas=public
flyway.locations=filesystem:sql
flyway.table=flyway_schema_history
flyway.baselineOnMigrate=true
flyway.cleanDisabled=true
flyway.placeholders.env=prod

• Use environment variables (for example, ${FLYWAY_DB_PASSWORD}) or OS-level secrets instead of plain-text credentials in VCS.
• For Oracle, configure service and driver properly:

flyway.url=jdbc:oracle:thin:@//orcl-host:1521/ORCLPDB1
flyway.user=APP_SCHEMA
flyway.password=${DB_PASSWORD}
flyway.schemas=APP_SCHEMA

• For MySQL:

flyway.url=jdbc:mysql://db:3306/appdb?useSSL=false
flyway.user=app
flyway.password=${DB_PASSWORD}
flyway.schemas=appdb

5.2 Spring Boot application.yml / application.properties

Example for PostgreSQL with Hibernate disabled:

spring.datasource.url=jdbc:postgresql://localhost:5432/appdb
spring.datasource.username=app
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.schemas=public
spring.flyway.table=flyway_schema_history
spring.flyway.baseline-on-migrate=true
spring.flyway.clean-disabled=true

Equivalent YAML:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/appdb
    username: app
    password: ${DB_PASSWORD}
    driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      ddl-auto: none
  flyway:
    enabled: true
    locations: classpath:db/migration
    schemas: public
    table: flyway_schema_history
    baseline-on-migrate: true
    clean-disabled: true

5.3 Environment-Specific Configs

• Use per-profile properties (application-dev.yml, application-prod.yml) or externalized configuration.
• Example pattern:
  • Dev: clean-disabled=false, baseline-on-migrate=false, additional locations with sample data.
  • Prod: clean-disabled=true, strong validation, read-only connections for reporting databases.
• For CLI in CI, pass configuration via environment variables or command-line flags to avoid committing secrets.

5.4 Secrets Handling

• Never commit passwords inside flyway.conf or application.yml checked into version control.
• Use:
  • Vault or secret managers to inject env vars at runtime.
  • CI/CD variables for flyway.password and DB URLs.
  • Encrypted config stores when required by policy.

---

6. Schema Versioning Strategy

6.1 Versioning in Large Teams

• Use monotonic increasing versions combined with descriptive names; avoid renumbering or reusing versions once pushed.
• Recommended schemes:
  • Simple incremental integers per repo: V101, V102.
  • Timestamp-based: V2026_03_31_1300__description.sql to minimize collisions across teams.
• One DB change per ticket or pull request; script name should reference ticket ID.

6.2 Handling Merge Conflicts

• Avoid editing existing version numbers; instead, resolve conflicts by renaming local files to a new, higher version.
• Typical workflow:
  1. Branch A adds V101__add_users.sql and merges first.
  2. Branch B also created V101__add_roles.sql; on rebase, rename to V102__add_roles.sql.
  3. Resolve any dependency or foreign key ordering issues by adjusting versions.
• Do not change already-applied migration content in a shared environment; instead, create a new migration to fix whatever was wrong.

6.3 Branching Strategies

• Git Flow
  • Feature branches create migrations, merged into develop then main.
  • Ensure integration environments (for example, dev, staging) are migrated in the same order as merges.
• Trunk-based development
  • Prefer timestamp or large-gap integer versions (for example, jump by 10) to make insertions easier.
  • Short-lived branches reduce version collision risk.
• For hotfixes against production, create migrations with versions higher than any unreleased dev migrations and ensure consistent backporting to main.

---

7. Advanced Features

7.1 Callbacks

• Callbacks are scripts or Java code executed at specific lifecycle events such as beforeMigrate, afterMigrate, beforeEachMigrate, afterClean, etc.
• Implemented as SQL files with naming like beforeMigrate__init.sql in the same locations, or as Java classes implementing Callback.
• Example: Java callback configuration:

Flyway flyway = Flyway.configure()
    .dataSource(dataSource)
    .locations("db/migration", "db/callbacks")
    .callbacks(new ExampleFlywayCallback())
    .load();
flyway.migrate();

• Use cases: logging, auditing, toggling features, populating temp tables, or orchestrating optional tasks using placeholders.

7.2 Placeholders

• Key–value replacements used inside SQL scripts using ${name} syntax.
• Configure in flyway.conf or Boot config:

flyway.placeholders.env=dev
flyway.placeholders.batch_size=1000

• Example SQL using placeholders:

INSERT INTO audit_config(env, batch_size)
VALUES ('${env}', ${batch_size});

• Placeholders can also act as on/off switches in callbacks to conditionally execute blocks.

7.3 Java-Based Migrations

• Extends BaseJavaMigration or implements JavaMigration to run arbitrary Java code in migrations.

public class V5__Migrate_Legacy_Data extends BaseJavaMigration {
  @Override
  public void migrate(Context context) throws Exception {
    try (Statement stmt = context.getConnection().createStatement()) {
      stmt.executeUpdate("UPDATE customers SET status = 'ACTIVE' WHERE status IS NULL");
    }
  }
}

• Packaged on the classpath under db/migration or configured locations; versioning rules are the same as SQL migrations.
• Prefer SQL when possible; reserve Java migrations for complex transformations, large data corrections with streaming, or logic not easily expressed in SQL.

7.4 Multi-Schema Support

• Configure flyway.schemas to list schemas under Flyway control.
• Example for PostgreSQL:

flyway.schemas=public,reporting

• Example for Oracle with separate app and audit schemas:

flyway.schemas=APP_SCHEMA,AUDIT_SCHEMA

• Flyway will manage flyway_schema_history in the default schema and apply migrations across configured schemas where relevant.

---

8. Spring Boot Integration (Important)

8.1 Auto-Configuration

• With flyway-core on the classpath and a DataSource bean, Boot auto-configures a Flyway bean and runs migrate on startup by default.
• Boot respects spring.flyway.* properties to adjust paths, schemas, and behavior.

8.2 Customizing the Flyway Bean

• Example customization:

@Configuration
public class FlywayConfig {

  @Bean(initMethod = "migrate")
  public Flyway flyway(DataSource dataSource) {
    return Flyway.configure()
        .dataSource(dataSource)
        .schemas("public", "audit")
        .locations("classpath:db/migration", "classpath:db/callbacks")
        .baselineOnMigrate(true)
        .load();
  }
}

• Set spring.flyway.enabled=false if taking full control via custom bean and manual invocation.

8.3 Per-Environment Enable/Disable

• Example disabling Flyway in tests while enabling in other profiles:

spring:
  flyway:
    enabled: true
---
spring:
  config:
    activate:
      on-profile: test
  flyway:
    enabled: false

• Alternatively, use a profile-specific configuration class that only defines the Flyway bean in certain profiles.

---

9. Production Best Practices

9.1 Zero-Downtime Deployments

• Design migrations to be backward compatible with previous application versions:
  • Add nullable columns first, deploy app that writes both old and new, backfill data, then make columns non-nullable in a later release.
  • For MySQL and PostgreSQL, avoid lock-heavy operations during peak hours; use CONCURRENTLY for index creation where supported.
• Never drop or rename columns in the same release where the application still depends on them.

9.2 Backward-Compatible Migrations

• Use multi-step evolution:
  • Release 1: add new column with default or nullable; keep old column.
  • Release 2: backfill; update app to read from new column.
  • Release 3: remove or deprecate old column.
• For Oracle, consider edition-based redefinition for truly zero-downtime structural changes when available.

9.3 Blue-Green and Rolling Deployments

• In blue-green setups, both environments must be able to run with the same DB schema version, requiring backward-compatible migrations.
• For rolling deployments, ensure older and newer app instances can share the same database without schema assumptions breaking.
• In highly regulated environments, treat DB migrations as separate deployables with explicit approvals.

9.4 Rollback Strategies and Limitations

• Flyway Community is forward-only; there is no automatic rollback.
• Strategies:
  • Roll-forward fixes: push a new migration that reverts the change logically (for example, re-add dropped column, restore data from backup tables).
  • Use database backups or point-in-time recovery for catastrophic failures.
  • In paid editions with undo, maintain U scripts that safely reverse changes but still treat them with the same rigor as forward migrations.

---

10. Common Pitfalls & Debugging

10.1 Failed Migrations

• If a migration fails, the schema history entry is marked as failed and future migrate calls will stop until resolved.
• Typical resolution:
  1. Fix the SQL or Java migration logic.
  2. Manually correct the database if it is in an inconsistent state.
  3. Run flyway repair to remove or adjust the failed entry when safe.

10.2 Checksum Mismatches

• Occur when a migration file is modified after being applied and validated.
• Never edit production migrations; instead:
  • Revert the file to its original content, or
  • Create a new migration with the fix.
• repair can update checksums, but only use this after confirming the database already matches the new script content.

10.3 Dirty Schema Handling

• A schema is considered dirty when a migration failed part-way and left the history in a failed state.
• Cleanest recovery:
  • Manually fix data or structure.
  • Use repair to mark the migration as successful or remove the unneeded entry.
  • Re-run migrate.

10.4 Repair Usage

• repair is powerful and dangerous; it does not change schema objects, only the history table, so misuse can make Flyway believe schema is in a different state than reality.
• Use only when the actual database has been independently validated and reconciled.

---

11. Flyway vs Liquibase (Senior Comparison)

11.1 Conceptual Differences

Aspect	Flyway	Liquibase
Change representation	Ordered SQL or Java migrations	Changelogs (SQL, XML, YAML, JSON) with changeSets
Ordering	Version numbers and repeatables	Implicit ordering by file and changeSet, can be rearranged
Philosophy	Imperative, migration-scripts-first	More declarative, rich changeSet model

Flyway favors simplicity and explicit, linear migrations, whereas Liquibase emphasizes declarative changelogs and conditional execution.

11.2 When to Use Which

• Prefer Flyway when:
  • Team is Java-centric and comfortable writing plain SQL.
  • Migration needs are mostly linear without complex branching or dynamic selection.
  • Integration with Spring Boot and JVM CI/CD is primary.
• Prefer Liquibase when:
  • Requirements include complex conditional logic, preconditions, and rollbacks managed within the tool.
  • There is a need to describe changes in non-SQL formats (XML/YAML/JSON) for cross-database reuse.
  • A richer command set and declarative change modeling is desired despite additional complexity.

---

12. Performance & Scaling

12.1 Large Databases

• Migrations that touch large tables must be carefully designed to avoid prolonged locks:
  • Use batched updates with predicates.
  • Use index creation options like CONCURRENTLY in PostgreSQL where supported.
  • Schedule heavy migrations during maintenance windows.
• For Oracle and MySQL, consider partitioning and incremental archives for data-movement-heavy migrations.

12.2 Parallel Teams

• Clear ownership per schema or service reduces collisions.
• Enforce patterns:
  • Each team owns its set of migration locations and schemas.
  • Schema review gates for cross-team changes.
• Use timestamp-based versioning to minimize frequent renumbering.

12.3 CI/CD Integration Basics

• Always run validate in CI before migrate to catch ordering and checksum issues early.
• Pipeline pattern:
  1. Build application artifacts.
  2. Run DB migrations against integration database (flyway migrate).
  3. Run tests.
  4. Promote artifacts and migrations together.
• For canary or blue-green deployments, run migrate before routing traffic to the new application version.

---

13. CI/CD Integration

13.1 GitHub Actions Example

Basic job running migrations against PostgreSQL:

name: Flyway Migrations

on:
  push:
    branches: [ main ]

jobs:
  migrate-db:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up JDK
        uses: actions/setup-java@v4
        with:
          distribution: temurin
          java-version: 21

      - name: Run Flyway migrations
        uses: docker://flyway/flyway:latest
        env:
          FLYWAY_URL: jdbc:postgresql://db-host:5432/appdb
          FLYWAY_USER: app
          FLYWAY_PASSWORD: ${{ secrets.DB_PASSWORD }}
        with:
          args: -locations=filesystem:sql migrate

13.2 GitLab CI Example

migrate_db:
  image: flyway/flyway:latest
  stage: deploy
  script:
    - flyway -url=$DB_URL -user=$DB_USER -password=$DB_PASSWORD -locations=filesystem:sql migrate
  only:
    - main

13.3 Deployment Pipeline Integration

• Treat DB migrations as a first-class deployable:
  • Option 1: Run Flyway as a job before application deployment.
  • Option 2: Let the application (Spring Boot) run migrations on startup but gate deployment with DB health checks.
• For highly critical systems, use:
  • Pre-prod dry run migrations.
  • Snapshot or PITR backup before running prod migrations.

---

14. Real-World Examples

14.1 Microservices Architecture Lifecycle

• Each microservice has its own database or schema and a dedicated db/migration folder.
• Lifecycle per release:
  1. Developer adds migration V2026_03_31_01__add_orders_table.sql in service A.
  2. CI runs flyway validate and flyway migrate against a shared integration database.
  3. Contract tests ensure schema and API alignment.
  4. On deployment, a DB migration job runs first for service A’s schema; if successful, application pods for service A roll out.

14.2 Multi-Environment Deployment

• Recommended environment order: dev → qa → staging → prod.
• Each environment uses the same migration artifacts from VCS, never edited per environment.
• Environment-specific behavior is controlled via configuration, feature flags, and placeholders, not divergence in migrations.

---

15. Cheat Sheet (Quick Reference)

15.1 Core Commands

Command	Purpose	Typical Usage
migrate	Apply pending migrations	flyway migrate
clean	Drop all objects in schemas (dev/test only)	flyway clean
info	Show current and pending migrations	flyway info
validate	Check migrations vs history, verify checksums	flyway validate
repair	Fix schema history table metadata	flyway repair
baseline	Start managing existing schema from a version	flyway baseline -baselineVersion=100
undo	Run undo migrations (Enterprise)	flyway undo

15.2 Naming Conventions

• Versioned SQL:

V1__init.sql
V2_1__add_customer_table.sql
V2026_03_31_01__add_index_on_order_date.sql

• Repeatable SQL:

R__views.sql
R__materialized_views.sql

• Java migrations:

V3__backfill_customer_status.java
R__recalc_statistics.java

15.3 Common Spring Boot Properties

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.schemas=public
spring.flyway.table=flyway_schema_history
spring.flyway.baseline-on-migrate=true
spring.flyway.clean-disabled=true
spring.flyway.placeholders.env=dev

15.4 Troubleshooting Quick Fixes

• Migration fails: fix SQL, correct DB manually if needed, then run flyway repair and migrate.
• Checksum mismatch: restore original script or create new migration; use repair only after confirming DB matches script.
• Dirty schema: inspect schema history, resolve partial changes, then repair to clear dirty flag.
• Accidental clean in prod: recover from backups; prevent recurrence with cleanDisabled=true in production configs.

This cheat sheet is optimized for experienced Java engineers who need a compact but practical reference for Flyway in production-grade environments.