Supported Platforms

Flyway supports 25+ relational and analytical databases, including:

• PostgreSQL (including TimescaleDB, YugabyteDB, CockroachDB)
• MySQL variants (MariaDB, Percona, Aurora MySQL)
• SQL Server, Oracle, DB2, H2, SQLite, HSQLDB
• Cloud-native systems: Snowflake, BigQuery, Redshift, Databricks, Spanner
• Emerging SQL-compatible engines: ClickHouse, SingleStoreDB

Ecosystem Integration

Flyway integrates natively with:

• Spring Boot (auto-configuration, lifecycle hooks)
• Build tools: Maven, Gradle, SBT, Ant
• CI/CD pipelines: Jenkins, GitHub Actions, GitLab CI
• Infrastructure-as-Code: Kubernetes init containers, Helm hooks

Real-World Deployment Pattern

In large-scale industrial IoT systems managing over 100 factory-specific databases, manual script execution across dev → test → uat → production is infeasible. Flyway enables environment-agnostic automation via Spring Profiles:

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

Profile-specific configuration files (e.g., application-prod-factory73.yaml) isolate connection details while sharing the same migration logic:

spring:
  datasource:
    url: jdbc:mysql://factory73-db.internal:3306/iot_core
    username: app_user
    password: ${DB_PASSWORD}
flyway:
  enabled: true
  locations: classpath:db/migration
  validate-on-migrate: true

How It Works: Core Mechanics

1. Migration scanning: Loads .sql files from classpath:db/migration.
2. Metadata tracking: Creates flyway_schema_history to record applied versions.
3. Ordered execution: Sorts migrations lexicographically by version prefix (e.g., V1_0__init.sql, V1_1__add_timestamps.sql).
4. Integrity verification: Computes and validates SHA-256 checksums on each script before applying.

Naming Convention & Example Scripts

Valid filenames follow V{major}_{minor}__{description}.sql:

V1_0__create_users_table.sql:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

V1_1__add_status_column.sql:

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

Bootstrapping Migrations

In Spring Boot, enable auto-migration with:

spring.flyway.enabled=true

For programmatic control (e.g., CLI tools or non-Spring contexts):

Flyway.configure()
  .dataSource("jdbc:postgresql://localhost:5432/myapp", "user", "pass")
  .load()
  .migrate();

Operational Advantages

• SQL-first workflow: No abstraction layer—write raw DDL/DML you know and test.
• Predictable performance: Linear execution time; benchmarks show sub-second overhead per 100 scripts.
• Cross-platform consistency: Same script runs identically on PostgreSQL and SQL Server when syntax permits.
• CI/CD readiness: Idempotent migrate command fits seamlessly into pipeline stages.
• Recoverability: Free edition supports repair (flyway repair) for metadata reconciliation; undo migrations require custom rollback scripts.
• Community velocity: Active GitHub repository (22k+ stars), frequent releases, and responsive Stack Overflow support.

Production Hardening Practices

• Always validate migrations in staging before promoting to production.
• Disable flyway.clean() in all non-local environments—treat it as irreversible.
• Set flyway.validate-on-migrate=true to catch checksum mismatches early.
• Use flyway.placeholders for environment-specific values (e.g., ${schema_name}).

Handling Common Scenarios

Scenario 1: Accidentally Removing a Applied Migration

If V2_0__add_audit_fields.sql was applied but later deleted locally:

• Flyway throws Migration checksum mismatch on next startup.
• Solution A (recommended): Keep the file and add compensating logic in a new V2_1__revert_audit_fields.sql.
• Solution B (dev/test only): Manually delete the row from flyway_schema_history where script = 'V2_0__add_audit_fields.sql', then re-run migrate.

Scenario 2: Modifying an Already-Applied Script

Changing V3_0__create_index.sql alters its checksum, causing validation failure.

Resolution:

1. Update the script content.
2. Run migrate against one clean environment to generate the new checksum.
3. Update checksum in flyway_schema_history for all affected environments.
4. Re-enable full migration flow.

Scenario 3: Ad-Hoc Production Schema Change

A DBA added an index directly in production without a migration file:

• Option 1: Drop the index and let Flyway recreate it (not viable for large tables).
• Option 2 (preferred): Insert a matching row into flyway_schema_history with success = true, so Flyway skips the step.

Flyway vs Liquibase: Key Differentiators

Flyway’s latest releases emphasize cloud-native resilience—including automatic retry logic for transient connection failures and improved concurrency handling during high-frequency deployments.