The Complete Guide to Flyway -- Automating Database Migrations

Introduction

Have you ever SSH-ed into a production server and ran ALTER TABLE directly? Have you ever received an incident report saying, "The schema change we applied to staging yesterday was missing from production"?

Managing database schemas manually is no different from managing application code without Git. You cannot track schema change history, environments drift apart, and rollbacks become impossible.

Database migration tools solve this problem. In this article, we take an in-depth look at Flyway, one of the most widely adopted DB migration tools.

1. What Is Database Migration

1.1 Why Is It Needed

Database migration is the process of systematically managing schema changes and applying them sequentially by version.

Problems with manual management:

Cannot track which changes have been applied to which environment
Schema drift between dev/staging/production
Conflicts when multiple developers change the schema concurrently
Accidental DDL execution is hard to roll back
No audit trail

1.2 The Importance of Version Control

We version-control application code with Git, so why not the database schema? A DB migration tool manages schema changes as code, making it possible to track who changed what and when.

app-code    v1.0 -> v1.1 -> v1.2 -> v2.0
             |       |       |       |
db-schema   V1   -> V2   -> V3   -> V4

When application versions and DB schema versions are managed together, it becomes clear which schema a given code version expects.

2. Flyway vs Liquibase vs Alembic

Criteria	Flyway	Liquibase	Alembic
Language	Java	Java	Python
Schema definition	SQL file-based	XML/YAML/JSON/SQL	Python code
Learning curve	Low	Medium	Medium
DB support	20+	50+	SQLAlchemy-supported DBs
Rollback	Paid (Teams) or manual	Built-in (auto-rollback)	Built-in (downgrade)
Spring Boot integration	Native	Native	N/A
Pricing	Community free	Community free	Fully free
Philosophy	SQL-centric, simple	Abstraction layer	ORM integration

Selection criteria:

Flyway: For teams that prefer writing raw SQL and value simplicity. Ideal for Java/Spring Boot projects
Liquibase: For teams that need vendor-agnostic schema management. When auto-rollback is important
Alembic: For Python/SQLAlchemy-based projects

3. Installing Flyway

3.1 CLI Installation

# macOS
brew install flyway

# Linux (tar.gz)
wget -qO- https://download.red-gate.com/maven/release/com/redgate/flyway/flyway-commandline/10.x/flyway-commandline-10.x-linux-x64.tar.gz | tar xz
sudo ln -s $(pwd)/flyway-10.x/flyway /usr/local/bin

# Check version
flyway --version

3.2 Docker

docker run --rm flyway/flyway:10 -url=jdbc:postgresql://host:5432/mydb -user=admin -password=secret migrate

Using with Docker Compose:

services:
  flyway:
    image: flyway/flyway:10
    command: migrate
    volumes:
      - ./sql:/flyway/sql
      - ./conf:/flyway/conf
    depends_on:
      db:
        condition: service_healthy
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d mydb"]
      interval: 5s
      timeout: 5s
      retries: 5

3.3 Maven/Gradle

<!-- Maven pom.xml -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>10.15.0</version>
    <configuration>
        <url>jdbc:postgresql://localhost:5432/mydb</url>
        <user>admin</user>
        <password>secret</password>
    </configuration>
</plugin>

// Gradle build.gradle
plugins {
    id "org.flywaydb.flyway" version "10.15.0"
}

flyway {
    url = 'jdbc:postgresql://localhost:5432/mydb'
    user = 'admin'
    password = 'secret'
}

3.4 Spring Boot Integration

With Spring Boot, simply adding the dependency triggers automatic migration on startup.

<!-- pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

# application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: '0'

4. Core Concepts

4.1 Migration File Naming

Flyway uses file naming conventions to distinguish migration type, version, and description.

V2__add_email_column.sql
|  |  +-- Description (separated by double underscores)
|  +-- Version number
+-- Type (V=Versioned, U=Undo, R=Repeatable)

Naming rules:

Version separator: double underscore (__)
Word separator: single underscore (_)
Version number: digits, dots (.), underscores allowed
Examples: V1__init.sql, V1.1__add_index.sql, V2_1__create_orders.sql

4.2 The flyway_schema_history Table

Flyway records which migrations have been applied in the flyway_schema_history table.

SELECT installed_rank, version, description, checksum, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;

 installed_rank | version |      description      |  checksum   |     installed_on     | success
----------------+---------+-----------------------+-------------+----------------------+---------
              1 | 1       | init                  |  1234567890 | 2026-04-01 10:00:00  | t
              2 | 2       | add email column      | -987654321  | 2026-04-05 14:30:00  | t
              3 | 3       | create orders table    |  1122334455 | 2026-04-10 09:15:00  | t

4.3 Checksums

Flyway calculates a checksum (CRC32) for each migration file and records it. If an already-applied migration file is modified, the checksum will change and an error will be raised on the next run.

This guarantees migration immutability. Once a migration has been applied, never modify it -- always add a new migration.

5. Migration Types

5.1 Versioned Migration (V)

The most basic migration type, executed once in version-number order.

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_username ON users(username);

5.2 Undo Migration (U) -- Teams Edition

Defines the reverse of a Versioned Migration. This is a Flyway Teams (paid) feature.

-- U1__create_users_table.sql
DROP TABLE IF EXISTS users;

5.3 Repeatable Migration (R)

Has no version number and re-executes whenever the file content changes. Ideal for managing views, functions, and procedures.

-- R__refresh_user_statistics_view.sql
CREATE OR REPLACE VIEW user_statistics AS
SELECT
    u.id,
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

5.4 Callbacks

Scripts that execute at specific points in the migration lifecycle.

sql/
  beforeMigrate.sql      -- Before migration starts
  afterMigrate.sql       -- After migration completes
  beforeEachMigrate.sql  -- Before each migration file
  afterEachMigrate.sql   -- After each migration file
  beforeValidate.sql     -- Before validation

Example -- refreshing statistics after migration:

-- afterMigrate.sql
ANALYZE;
SELECT schemaname, tablename, last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';

6. Flyway Commands

6.1 migrate

Executes pending migrations in order.

flyway migrate

6.2 info

Displays the current migration status.

flyway info

+-----------+---------+---------------------+----------+---------------------+----------+----------+
| Category  | Version | Description         | Type     | Installed On        | State    | Undoable |
+-----------+---------+---------------------+----------+---------------------+----------+----------+
| Versioned | 1       | init                | SQL      | 2026-04-01 10:00:00 | Success  | No       |
| Versioned | 2       | add email column    | SQL      | 2026-04-05 14:30:00 | Success  | No       |
| Versioned | 3       | create orders table | SQL      |                     | Pending  | No       |
+-----------+---------+---------------------+----------+---------------------+----------+----------+

6.3 validate

Compares the checksums of applied migrations against the files on disk.

flyway validate

It is best practice to run validate before migrate in CI/CD pipelines.

6.4 repair

Cleans up failed migration records.

flyway repair

Key behaviors:

Removes failed migration entries from flyway_schema_history
Recalculates checksums for applied migrations based on current files

6.5 clean

Warning: Drops all objects. Never use in production!

flyway clean

Used in development to start from a clean state. Since Flyway 10, you must explicitly set flyway.cleanDisabled=false.

6.6 baseline

Used when introducing Flyway to an existing database.

flyway baseline -baselineVersion=5 -baselineDescription="existing schema"

If a database already has schemas V1 through V5 applied, set the baseline to V5 and start adding new migrations from V6.

7. Configuration

7.1 flyway.conf

# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=admin
flyway.password=secret
flyway.schemas=public
flyway.locations=filesystem:sql
flyway.baselineOnMigrate=true
flyway.baselineVersion=0
flyway.connectRetries=3
flyway.cleanDisabled=true
flyway.outOfOrder=false
flyway.validateOnMigrate=true

7.2 Spring Boot (application.yml)

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: admin
    password: secret
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: '0'
    clean-disabled: true
    validate-on-migrate: true
    out-of-order: false
    connect-retries: 3
    table: flyway_schema_history

7.3 Environment-Specific Configuration

With Spring Boot, separate configurations by profile.

# application-dev.yml
spring:
  flyway:
    clean-disabled: false
    locations: classpath:db/migration,classpath:db/seed

# application-prod.yml
spring:
  flyway:
    clean-disabled: true
    locations: classpath:db/migration
    validate-on-migrate: true

8. Practical Examples

8.1 V1 -- Create Users Table

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT uq_users_username UNIQUE (username)
);

COMMENT ON TABLE users IS 'User information';
COMMENT ON COLUMN users.username IS 'Login ID';

8.2 V2 -- Add Email Unique Constraint

-- V2__add_email_unique_constraint.sql
-- Clean up existing duplicates
DELETE FROM users a
USING users b
WHERE a.id > b.id
  AND a.email = b.email
  AND a.email IS NOT NULL;

-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Add UNIQUE index
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users(email);
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE USING INDEX idx_users_email;

8.3 V3 -- Create Orders Table

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_number VARCHAR(20) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    currency VARCHAR(3) NOT NULL DEFAULT 'KRW',
    shipping_address TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT chk_orders_status CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

COMMENT ON TABLE orders IS 'Order information';

8.4 R -- Refresh Views

-- R__refresh_views.sql
CREATE OR REPLACE VIEW v_user_order_summary AS
SELECT
    u.id AS user_id,
    u.username,
    u.email,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COALESCE(AVG(o.total_amount), 0) AS avg_order_amount,
    MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'CANCELLED'
GROUP BY u.id, u.username, u.email;

CREATE OR REPLACE VIEW v_daily_order_stats AS
SELECT
    DATE(created_at) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status NOT IN ('CANCELLED')
GROUP BY DATE(created_at);

8.5 Project Directory Structure

src/main/resources/
  db/
    migration/
      V1__create_users_table.sql
      V2__add_email_unique_constraint.sql
      V3__create_orders_table.sql
      V4__create_order_items_table.sql
      V5__add_user_profile_fields.sql
      R__refresh_views.sql
      R__update_functions.sql
    seed/
      V100__insert_test_users.sql
      V101__insert_test_orders.sql

9. Team Collaboration Strategies

9.1 Branch-Based Migration Conflicts

When multiple developers write migrations concurrently, version numbers can collide.

Use timestamp-based version numbers:

V20260412_001__feature_a_create_table.sql   (Developer A)
V20260412_002__feature_b_add_column.sql     (Developer B)

Or enable outOfOrder:

spring:
  flyway:
    out-of-order: true

With this option, a V2.5 added after V3 will still be executed. However, establishing clear team conventions should come first.

9.2 Naming Conventions

Things the team should agree on:

# Approach 1: Sequential numbers
V1__create_users.sql
V2__add_index.sql

# Approach 2: Date-based
V20260412.1__create_users.sql
V20260412.2__add_index.sql

# Approach 3: Include ticket number
V1__JIRA_123_create_users.sql
V2__JIRA_456_add_index.sql

9.3 Code Review Checklist

Items to check when reviewing migration PRs:

Does the filename follow the naming convention
Were any existing migration files modified
Are there DDL operations that cause locks on large tables
Are rollback scripts prepared
Is CONCURRENTLY used for index creation

10. CI/CD Integration

10.1 GitHub Actions

name: Database Migration
on:
  push:
    branches: [main]
    paths:
      - 'src/main/resources/db/migration/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Run Flyway Validate
        run: |
          docker run --rm --network host \
            -v $(pwd)/src/main/resources/db/migration:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=test \
            -password=test \
            validate

      - name: Run Flyway Migrate
        run: |
          docker run --rm --network host \
            -v $(pwd)/src/main/resources/db/migration:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=test \
            -password=test \
            migrate

      - name: Run Flyway Info
        run: |
          docker run --rm --network host \
            -v $(pwd)/src/main/resources/db/migration:/flyway/sql \
            flyway/flyway:10 \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=test \
            -password=test \
            info

10.2 Kubernetes Init Container

apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-app
spec:
  template:
    spec:
      initContainers:
        - name: flyway-migrate
          image: flyway/flyway:10
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              value: "jdbc:postgresql://postgres-service:5432/mydb"
            - name: FLYWAY_USER
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: username
            - name: FLYWAY_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: db-credentials
                  key: password
          volumeMounts:
            - name: migration-scripts
              mountPath: /flyway/sql
      containers:
        - name: app
          image: my-app:latest
      volumes:
        - name: migration-scripts
          configMap:
            name: flyway-migrations

11. Zero-Downtime Migrations

11.1 Dangerous DDL Operations

Operations that require caution in production:

Operation	Risk	Reason
Adding NOT NULL	High	Requires full table scan
Changing column type	High	Full table rewrite
Creating index	Medium	Table lock (without CONCURRENTLY)
Adding column (no default)	Low	Metadata-only change
Adding column (with default)	Medium	Full rewrite before PostgreSQL 11

11.2 The Expand-Contract Pattern

A safe way to rename a column:

Phase 1 -- Expand:

-- V10__expand_add_new_column.sql
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- Copy existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Set up trigger to write to both columns
CREATE OR REPLACE FUNCTION sync_user_name()
RETURNS TRIGGER AS '
BEGIN
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        IF NEW.name IS DISTINCT FROM OLD.name THEN
            NEW.full_name := NEW.name;
        END IF;
        IF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
            NEW.name := NEW.full_name;
        END IF;
    END IF;
    RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_user_name
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION sync_user_name();

Phase 2 -- Deploy Application: Deploy new code that uses the full_name column.

Phase 3 -- Contract:

-- V11__contract_remove_old_column.sql
DROP TRIGGER IF EXISTS trg_sync_user_name ON users;
DROP FUNCTION IF EXISTS sync_user_name();
ALTER TABLE users DROP COLUMN name;

11.3 Adding Indexes on Large Tables

-- V12__add_index_concurrently.sql
-- To use CONCURRENTLY in Flyway, you must disable the transaction
-- In flyway.conf: flyway.postgresql.transactional.lock=false
-- Or use a separate non-transactional migration in Spring Boot

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at_status
ON orders(created_at, status);

12. Troubleshooting

12.1 Checksum Mismatch

Symptom: Migration checksum mismatch error

Cause: An already-applied migration file was modified

Resolution:

# Option 1: Repair to update checksum (if the change was intentional)
flyway repair

# Option 2: Restore original file (if the change was accidental)
git checkout -- src/main/resources/db/migration/V1__init.sql

12.2 Recovering from Failed Migrations

Symptom: A migration failed mid-execution, leaving the DB in an inconsistent state

# 1. Check failure status
flyway info

# 2. Remove failed migration record
flyway repair

# 3. Fix SQL file and re-run
flyway migrate

PostgreSQL supports transactional DDL, so failures are automatically rolled back. However, MySQL implicitly commits DDL, which may require manual recovery.

12.3 Using Baseline

When introducing Flyway to an existing project:

# 1. Dump current schema as V1
pg_dump --schema-only mydb > V1__baseline.sql

# 2. Set baseline
flyway baseline -baselineVersion=1

# 3. Write new migrations starting from V2

12.4 Common Mistakes

Mistake 1: Modifying an already-applied migration file

Always add a new migration instead

Mistake 2: Skipping version numbers (V1, V3, V5)

Flyway expects sequential numbering by default

Mistake 3: Copying production config after running clean in dev

Separate configuration files by environment

Mistake 4: Mixing DML and DDL in a single migration

Separate them when possible. If DDL fails, the DML may be rolled back as well

13. Best Practices

13.1 Rollback Strategy

Since Flyway Community does not support automatic Undo, prepare manual rollback scripts.

sql/
  migration/
    V5__add_payment_table.sql
  rollback/
    V5__rollback_add_payment_table.sql

-- V5__rollback_add_payment_table.sql (for manual execution)
DROP TABLE IF EXISTS payments;
DELETE FROM flyway_schema_history WHERE version = '5';

13.2 Data Migration

Separate schema changes from data changes into different migrations.

-- V6__add_role_column.sql (DDL)
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'USER';

-- V7__migrate_admin_roles.sql (DML)
UPDATE users SET role = 'ADMIN' WHERE username IN ('admin', 'superadmin');
UPDATE users SET role = 'MODERATOR' WHERE username IN ('mod1', 'mod2');

13.3 Seed Data

Manage seed data for dev/test environments in a separate directory.

# application-dev.yml
spring:
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/seed

-- db/seed/V1000__seed_test_users.sql
INSERT INTO users (username, email, password_hash, role) VALUES
('testuser1', 'test1@example.com', 'hashed_pw_1', 'USER'),
('testuser2', 'test2@example.com', 'hashed_pw_2', 'USER'),
('testadmin', 'admin@example.com', 'hashed_pw_3', 'ADMIN')
ON CONFLICT (username) DO NOTHING;

13.4 Complete Checklist

Items to verify when writing migrations:

You have not modified any previously applied migrations
The version number does not conflict with existing migrations
You have reviewed lock impact for ALTER TABLE on large production tables
Indexes are created with the CONCURRENTLY option
Rollback scripts are prepared
Data migrations and schema migrations are separated
Environment-specific configuration is correct

Conclusion

Flyway is a simple yet powerful DB migration tool. Its SQL file-based approach is familiar to both DBAs and developers, and its native integration with Spring Boot shines especially in the Java ecosystem.

Key principles in summary:

Migrations are immutable: Never modify an applied migration file
Always move forward: If there is a problem, fix it with a new migration
Separate environments: Clearly distinguish dev, staging, and prod configurations
Establish team conventions: Agree on naming, review processes, and rollback strategies
Integrate with CI/CD: Manual execution is the beginning of mistakes

Database schema management is part of software engineering. Version-control it, test it, and automate it -- just like code.