Flyway: Database Migration Versioning Done Right

You merged a feature branch on Friday afternoon. By Monday morning, three environments had diverged schemas, one developer was running manual ALTER TABLE statements no one else knew about, and your staging database was two migrations behind production. If any of this sounds familiar, you already understand the problem Flyway solves. Database migrations are code — they need version control, repeatability, and auditability just like application logic does. Flyway brings exactly that discipline to SQL schema management, giving teams a deterministic, trackable way to evolve a database over time.

Why Database Migrations Need Versioning

Application code changes live in Git. Every developer on the team checks out the same commit and gets the same source tree. But the database is stateful — it persists between deployments and cannot simply be replaced with the committed version. Without a migration tool, teams fall into familiar failure patterns: hand-applied DDL that never gets documented, environment drift where staging and production have silently diverged, and rollback anxiety because no one knows exactly what changed or in what order.

Flyway treats each schema change as an immutable, versioned artifact. Once a migration has been applied, it is locked in the history table. If someone modifies an already-applied migration file, Flyway's checksum validation catches the tampering and refuses to proceed. This creates a verifiable audit trail from database birth to current state — something no amount of ALTER TABLE discipline in a runbook can replicate.

The core contract is simple: given an empty (or baselined) database and a directory of migration files, flyway migrate will always produce the same schema. That determinism is what makes CI/CD pipelines for databases trustworthy.

How Flyway Works

Flyway connects to your database via JDBC, creates a metadata table called flyway_schema_history on first use, and then compares the migration files on disk against the rows in that table. Any versioned migration whose version number does not yet appear in the history table is considered pending and will be applied in ascending version order.

The flyway_schema_history table stores everything needed to audit and validate a migration's execution:

-- Approximate structure of flyway_schema_history
SELECT
    installed_rank,   -- Execution order
    version,          -- e.g. '1', '2', '3'
    description,      -- e.g. 'Create users table'
    type,             -- SQL, JDBC, BASELINE, etc.
    script,           -- Filename
    checksum,         -- CRC32 of file contents
    installed_by,     -- DB user that ran the migration
    installed_on,     -- Timestamp
    execution_time,   -- Milliseconds
    success           -- TRUE/FALSE
FROM flyway_schema_history
ORDER BY installed_rank;

When flyway validate runs, it recalculates the checksum of every migration file on disk and compares it against the stored checksum. Any mismatch is treated as a fatal error. This is deliberate: a migration is a contract. Changing it after the fact invalidates the guarantee that every environment applied the same change.

File Naming Conventions

Flyway's behaviour is driven almost entirely by filename conventions. Getting these right from the start saves significant pain later.

Versioned migrations follow the pattern V{version}__{description}.sql:

db/migration/
  V1__Create_users_table.sql
  V2__Add_email_index.sql
  V3__Create_orders_table.sql
  V4__Add_foreign_key_order_user.sql
  V4_1__Backfill_legacy_users.sql   ← sub-versions are valid

Repeatable migrations use the prefix R and no version number:

db/migration/
  R__Refresh_user_summary_view.sql
  R__Rebuild_search_index_procedure.sql

Repeatable migrations run after all versioned migrations and re-execute every time their file content changes. They are the correct tool for objects that can be safely dropped and recreated: views, stored procedures, functions, and materialized view refresh logic.

Core Commands

Install Flyway via Homebrew (brew install flyway), download the community binary, or pull the Docker image. The four commands you will use daily are:

# Apply all pending migrations
flyway migrate

# Show current migration status — which have run, which are pending
flyway info

# Verify that applied migrations match files on disk (checksum validation)
flyway validate

# Fix a failed migration entry in flyway_schema_history
flyway repair

flyway info output includes state codes: Success, Pending, Failed, Missing (file deleted after application), and Future (version newer than any file on disk — common when rolling back application code without rolling back the schema).

flyway repair does two things: it removes failed migration entries from the history table (so they can be retried) and it recalculates checksums for migrations whose files changed. It does not undo any SQL that was executed — it only repairs the metadata.

Configuration: flyway.conf and JDBC URLs

Flyway reads configuration from flyway.conf in the working directory, environment variables, or command-line flags. A minimal configuration for PostgreSQL looks like this:

# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/myapp_db
flyway.user=flyway_user
flyway.password=${FLYWAY_PASSWORD}
flyway.locations=filesystem:db/migration
flyway.schemas=public
flyway.baselineOnMigrate=false
flyway.outOfOrder=false
flyway.validateOnMigrate=true

JDBC URL formats for common databases:

PostgreSQL:  jdbc:postgresql://host:5432/dbname
MySQL:       jdbc:mysql://host:3306/dbname
SQL Server:  jdbc:sqlserver://host:1433;databaseName=dbname
Oracle:      jdbc:oracle:thin:@host:1521:SID
H2 (test):  jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1

Repeatable Migrations in Practice

A realistic repeatable migration for a reporting view demonstrates the pattern:

-- R__Refresh_user_order_summary_view.sql
CREATE OR REPLACE VIEW v_user_order_summary AS
SELECT
    u.id            AS user_id,
    u.email         AS email,
    COUNT(o.id)     AS total_orders,
    SUM(o.amount)   AS lifetime_value,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

When the orders table gains a new column in V7__Add_order_currency.sql and you want to expose it in the view, you simply update R__Refresh_user_order_summary_view.sql. On the next flyway migrate run, Flyway detects the changed checksum and re-executes only this file, after applying V7.

Baseline for Existing Databases

Introducing Flyway into a project with an existing schema requires the baseline command. Rather than writing migration files that recreate everything from scratch, you tell Flyway to treat the current state as version 1 and only manage changes going forward:

# Mark the current database state as the baseline (version 1)
flyway baseline -baselineVersion=1 -baselineDescription="Existing schema before Flyway"

# All subsequent migrations must be V2 and above

After baselining, set flyway.baselineOnMigrate=false in your config so that the baseline command cannot be accidentally re-run against databases that already have a migration history.

CI/CD Integration

A GitHub Actions workflow that runs migrations before deploying the application is the minimal viable CI/CD integration:

# .github/workflows/deploy.yml
name: Deploy

on:
  push:
    branches: [main]

jobs:
  migrate-and-deploy:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: myapp_db
          POSTGRES_USER: flyway_user
          POSTGRES_PASSWORD: ${{ secrets.DB_PASSWORD }}
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Run Flyway migrations
        uses: docker://flyway/flyway:10
        with:
          args: migrate
        env:
          FLYWAY_URL: jdbc:postgresql://localhost:5432/myapp_db
          FLYWAY_USER: flyway_user
          FLYWAY_PASSWORD: ${{ secrets.DB_PASSWORD }}
          FLYWAY_LOCATIONS: filesystem:./db/migration
          FLYWAY_VALIDATE_ON_MIGRATE: "true"

      - name: Deploy application
        run: ./scripts/deploy.sh

Flyway with Docker

The official Docker image makes Flyway available without a local installation:

docker run --rm \
  -v "$(pwd)/db/migration":/flyway/sql \
  -e FLYWAY_URL=jdbc:postgresql://host.docker.internal:5432/myapp_db \
  -e FLYWAY_USER=flyway_user \
  -e FLYWAY_PASSWORD=secret \
  flyway/flyway:10 migrate

Spring Boot Auto-Migration

Spring Boot's Flyway autoconfiguration runs migrate automatically on application startup when the flyway dependency is on the classpath:

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp_db
    username: app_user
    password: ${DB_PASSWORD}
  flyway:
    enabled: true
    locations: classpath:db/migration
    validate-on-migrate: true
    out-of-order: false
    baseline-on-migrate: false

Advanced Topics: Out-of-Order and Undo Migrations

Out-of-order migrations (flyway.outOfOrder=true) allow a migration with a lower version number to be applied after higher-versioned migrations have already run. This is useful in trunk-based development when two feature branches create migrations concurrently — one branch's V5 might land in production before another branch's V4. With out-of-order enabled, Flyway will apply the late-arriving V4 and mark it with state Out of Order in the history table.

Undo migrations are a Flyway Teams feature. Each versioned migration can have a corresponding U{version}__{description}.sql file that reverses its changes. Running flyway undo applies the highest-versioned undo script available. This enables true rollback capability, though writing correct undo scripts (especially for data migrations) requires care. Most teams find that a forward-only migration strategy — where rollback means deploying a new corrective migration — is simpler to maintain than maintaining undo scripts for every change.

Key Takeaways

Managing Flyway Migrations on JusDB

Flyway gives you the tooling to manage schema evolution safely, but you still need a database platform that pairs reliability with operational simplicity. JusDB provides fully managed PostgreSQL, MySQL, and StarRocks instances with automated backups, point-in-time recovery, and connection pooling — so your Flyway migrations land on infrastructure that is already production-hardened.

Getting started is straightforward: provision a JusDB instance, copy the JDBC connection string into your flyway.conf or environment variables, and run flyway baseline or flyway migrate against a clean schema. JusDB's role-based access controls let you create a dedicated flyway_user with exactly the permissions needed to run DDL without granting broad administrative access.

Start your free JusDB trial and bring Flyway's migration discipline to a managed database that handles the infrastructure so your team can focus on shipping schema changes confidently.