Spring Boot Flyway Complete Guide: Database Migration Strategy and Production Patterns

1. What is Flyway?
- Flyway vs Liquibase Comparison
- The flyway_schema_history Table
2. Dependencies and Basic Configuration
3. Migration File Naming Conventions
- Migration Types
- Naming Rules in Detail
4. Practical Migration Examples
5. Java-Based Migrations
- Implementing BaseJavaMigration
- Injecting Spring Beans into Java Migrations
6. Multi-Environment Configuration
7. Testing Strategies
8. Production Considerations
9. CI/CD Integration
- GitHub Actions Flyway Validation
- Pre-Deployment Validation Script
10. Common Issues and Solutions
- Checksum Mismatch Error
- Baseline Setup for Existing Databases
Quiz: Test Your Flyway Knowledge

1. What is Flyway?

Flyway is an open-source database migration tool that automates schema version control. It keeps DB schema changes in sync with application code changes, ensuring all team members work against a consistent database state.

Flyway vs Liquibase Comparison

Feature	Flyway	Liquibase
Migration format	SQL or Java	XML, YAML, JSON, SQL
Learning curve	Low	Moderate
Rollback support	Paid (Teams)	Free (built-in)
Spring Boot integration	Auto-configured	Auto-configured
Community	Very active	Active

Flyway is preferred in many Spring Boot projects for its SQL-centric simplicity and minimal learning curve.

The flyway_schema_history Table

Flyway uses the flyway_schema_history table to track migration execution history, recording each migration's version, checksum, execution time, and success status.

-- Querying flyway_schema_history
SELECT installed_rank, version, description, type, script, checksum, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;

2. Dependencies and Basic Configuration

Maven Dependency

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

<!-- Required for MySQL -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

<!-- PostgreSQL only needs flyway-core -->

Gradle Dependency

// build.gradle
dependencies {
    implementation 'org.flywaydb:flyway-core'
    // For MySQL
    implementation 'org.flywaydb:flyway-mysql'
}

application.yml Basic Configuration

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: myuser
    password: mypassword
    driver-class-name: org.postgresql.Driver
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    validate-on-migrate: true
    out-of-order: false
    table: flyway_schema_history
    encoding: UTF-8
    connect-retries: 3

Key Configuration Options:

enabled: Whether to activate Flyway auto-execution
locations: Migration script location(s), multiple paths allowed
baseline-on-migrate: Treat current state as baseline when applying Flyway to an existing DB
validate-on-migrate: Verify checksums before migration
out-of-order: Allow applying migrations out of version order (default false)
table: Customize the history table name

3. Migration File Naming Conventions

Migration Types

Versioned Migration:

V{version}__{description}.sql

Examples:

V1__create_users_table.sql
V2__add_email_index.sql
V1.2.3__fix_user_status_column.sql

Undo Migration (Flyway Teams only):

U{version}__{description}.sql

Example:

U2__add_email_index.sql

Repeatable Migration:

R__{description}.sql

Examples:

R__create_views.sql
R__update_stored_procedures.sql

Naming Rules in Detail

Version separators: digits with dots or underscores — V1, V1.1, V1_1 all valid
Description separator: double underscore (__)
Underscores in description are displayed as spaces
Case sensitivity: V must be uppercase

src/main/resources/
└── db/
    └── migration/
        ├── V1__init_schema.sql
        ├── V2__add_user_status.sql
        ├── V3__insert_seed_data.sql
        ├── V4__add_indexes.sql
        └── R__create_reporting_views.sql

4. Practical Migration Examples

V1__init_schema.sql - Initial Schema Creation

-- V1__init_schema.sql
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP    NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock       INTEGER        NOT NULL DEFAULT 0,
    created_at  TIMESTAMP      NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT       NOT NULL REFERENCES users(id),
    total       DECIMAL(10, 2) NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW()
);

V2__add_user_status.sql - Adding Columns

-- V2__add_user_status.sql
ALTER TABLE users
    ADD COLUMN status       VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    ADD COLUMN last_login   TIMESTAMP,
    ADD COLUMN phone_number VARCHAR(20);

UPDATE users SET status = 'ACTIVE' WHERE status IS NULL;

V3__insert_seed_data.sql - Initial Data

-- V3__insert_seed_data.sql
INSERT INTO products (name, description, price, stock) VALUES
    ('Laptop', 'High-performance developer laptop', 1500.00, 50),
    ('Mouse', 'Wireless ergonomic mouse', 80.00, 200),
    ('Keyboard', 'Mechanical RGB keyboard', 120.00, 150);

INSERT INTO users (username, email, password, status) VALUES
    ('admin', 'admin@example.com', '$2a$10$hashedpassword', 'ACTIVE');

V4__add_indexes.sql - Index Optimization

-- V4__add_indexes.sql
CREATE INDEX idx_users_email     ON users(email);
CREATE INDEX idx_users_status    ON users(status);
CREATE INDEX idx_orders_user_id  ON orders(user_id);
CREATE INDEX idx_orders_status   ON orders(status);
CREATE INDEX idx_orders_created  ON orders(created_at DESC);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

5. Java-Based Migrations

Complex data transformations that are difficult to express in SQL can be implemented as Java migrations.

Implementing BaseJavaMigration

package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * V5__MigrateUserData
 * Splits the full_name column in the users table into first_name and last_name
 */
public class V5__MigrateUserData extends BaseJavaMigration {

    @Override
    public void migrate(Context context) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(
            new SingleConnectionDataSource(context.getConnection(), true)
        );

        List<String[]> users = new ArrayList<>();

        try (Statement stmt = context.getConnection().createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT id, full_name FROM users WHERE full_name IS NOT NULL")) {
            while (rs.next()) {
                long id = rs.getLong("id");
                String fullName = rs.getString("full_name");
                String[] parts = fullName.split(" ", 2);
                users.add(new String[]{
                    String.valueOf(id),
                    parts[0],
                    parts.length > 1 ? parts[1] : ""
                });
            }
        }

        for (String[] user : users) {
            jdbcTemplate.update(
                "UPDATE users SET first_name = ?, last_name = ? WHERE id = ?",
                user[1], user[2], Long.parseLong(user[0])
            );
        }

        System.out.printf("Migrated %d user records%n", users.size());
    }
}

Injecting Spring Beans into Java Migrations

package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class V6__EncryptSensitiveData extends BaseJavaMigration {

    // @Component enables Spring bean injection
    // FlywayAutoConfiguration auto-discovers JavaMigration beans
    @Autowired
    private EncryptionService encryptionService;

    @Override
    public void migrate(Context context) throws Exception {
        var jdbcTemplate = new org.springframework.jdbc.core.JdbcTemplate(
            new org.springframework.jdbc.datasource.SingleConnectionDataSource(
                context.getConnection(), true)
        );

        jdbcTemplate.query(
            "SELECT id, phone_number FROM users WHERE phone_number IS NOT NULL",
            (rs) -> {
                long id = rs.getLong("id");
                String phone = rs.getString("phone_number");
                String encrypted = encryptionService.encrypt(phone);
                jdbcTemplate.update(
                    "UPDATE users SET phone_number = ? WHERE id = ?",
                    encrypted, id
                );
            }
        );
    }
}

6. Multi-Environment Configuration

Spring Profile-Specific Flyway Settings

# application.yml (shared)
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration

---
# application-dev.yml
spring:
  config:
    activate:
      on-profile: dev
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/migration/dev
    clean-on-validation-error: true # only for development

---
# application-test.yml
spring:
  config:
    activate:
      on-profile: test
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/migration/test
    clean-disabled: false # allow DB reset before tests

---
# application-prod.yml
spring:
  config:
    activate:
      on-profile: prod
  flyway:
    locations: classpath:db/migration
    clean-disabled: true # prevent clean command in production
    out-of-order: false
    validate-on-migrate: true

Vendor-Specific Migration Branching

spring:
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/migration/{vendor}

db/migration/
├── V1__create_tables.sql          # shared
├── V2__add_indexes.sql            # shared
├── postgresql/
│   └── V3__add_pg_specific.sql   # PostgreSQL only
└── mysql/
    └── V3__add_mysql_specific.sql # MySQL only

Fine-Grained Java Configuration

@Configuration
public class FlywayConfig {

    @Bean
    public FlywayMigrationStrategy migrationStrategy() {
        return flyway -> {
            System.out.println("Starting Flyway migration...");
            flyway.migrate();
            System.out.println("Flyway migration completed.");
        };
    }

    @Bean
    @Profile("!prod")
    public FlywayMigrationStrategy devMigrationStrategy() {
        return flyway -> {
            // Development: clean schema then re-migrate
            flyway.clean();
            flyway.migrate();
        };
    }
}

7. Testing Strategies

Using @FlywayTest

<dependency>
    <groupId>org.flywaydb.flyway-test-extensions</groupId>
    <artifactId>flyway-spring-test</artifactId>
    <version>9.5.0</version>
    <scope>test</scope>
</dependency>

@SpringBootTest
@FlywayTest
class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    @FlywayTest(locationsForMigrate = {"classpath:db/migration/test"})
    void testUserCreation() {
        User user = new User("testuser", "test@example.com");
        User saved = userRepository.save(user);
        assertThat(saved.getId()).isNotNull();
    }
}

H2 In-Memory Database Testing

# application-test.yml
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    driver-class-name: org.h2.Driver
    username: sa
    password:
  flyway:
    enabled: true
    locations: classpath:db/migration

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ActiveProfiles("test")
class ProductRepositoryTest {

    @Autowired
    private ProductRepository productRepository;

    @Test
    void findByStatus_returnsActiveProducts() {
        List<Product> products = productRepository.findByStatus("ACTIVE");
        assertThat(products).isNotEmpty();
    }
}

TestContainers + Flyway (Recommended)

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class IntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15")
            .withDatabaseName("testdb")
            .withUsername("testuser")
            .withPassword("testpass");

    @DynamicPropertySource
    static void setProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private UserRepository userRepository;

    @Test
    void flywayMigration_appliesAllScripts() {
        assertThat(userRepository.count()).isGreaterThanOrEqualTo(0);
    }
}

8. Production Considerations

Three-Step Column Deletion Strategy

Deleting a column immediately in a live system can cause application errors during rolling deployments. Use the three-step approach:

Step 1: Make column nullable (Deploy N)

-- V10__make_old_column_nullable.sql
ALTER TABLE users ALTER COLUMN old_field DROP NOT NULL;

Step 2: Remove all references in application code (Deploy N+1)

Remove from JPA entity, queries, and repositories

Step 3: Drop the column (Deploy N+2)

-- V12__drop_old_column.sql
ALTER TABLE users DROP COLUMN IF EXISTS old_field;

PostgreSQL Large Index Creation Strategy

public class V15__AddLargeIndexConcurrently extends BaseJavaMigration {

    @Override
    public boolean canExecuteInTransaction() {
        return false; // execute outside transaction
    }

    @Override
    public void migrate(Context context) throws Exception {
        try (Statement stmt = context.getConnection().createStatement()) {
            // CONCURRENTLY avoids table locking but cannot run inside a transaction
            stmt.execute(
                "CREATE INDEX CONCURRENTLY IF NOT EXISTS " +
                "idx_orders_large ON orders(created_at, status)"
            );
        }
    }
}

Large Table Batch Migration

public class V16__BatchMigrateLargeTable extends BaseJavaMigration {

    private static final int BATCH_SIZE = 1000;

    @Override
    public void migrate(Context context) throws Exception {
        JdbcTemplate jdbc = new JdbcTemplate(
            new SingleConnectionDataSource(context.getConnection(), true)
        );

        Long maxId = jdbc.queryForObject("SELECT MAX(id) FROM orders", Long.class);
        if (maxId == null) return;

        long processedCount = 0;
        for (long offset = 0; offset <= maxId; offset += BATCH_SIZE) {
            final long batchOffset = offset;
            int updated = jdbc.update(
                "UPDATE orders SET new_status_code = CASE status " +
                "WHEN 'PENDING' THEN 1 WHEN 'COMPLETED' THEN 2 ELSE 0 END " +
                "WHERE id > ? AND id <= ? AND new_status_code IS NULL",
                batchOffset, batchOffset + BATCH_SIZE
            );
            processedCount += updated;
            System.out.printf("Progress: %d records updated%n", processedCount);
        }
    }
}

flyway repair Command

Use this to recover the migration history after a failed migration:

# Maven
./mvnw flyway:repair

# Gradle
./gradlew flywayRepair

# CLI
flyway -url=jdbc:postgresql://localhost:5432/mydb \
       -user=myuser \
       -password=mypassword \
       repair

9. CI/CD Integration

GitHub Actions Flyway Validation

# .github/workflows/flyway-validate.yml
name: Flyway Validation

on:
  pull_request:
    paths:
      - 'src/main/resources/db/migration/**'
      - 'src/main/java/db/migration/**'

jobs:
  flyway-validate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

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

      - name: Validate Flyway migrations
        run: |
          ./mvnw flyway:validate \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

      - name: Run migration
        run: |
          ./mvnw flyway:migrate \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

      - name: Verify migration info
        run: |
          ./mvnw flyway:info \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

Pre-Deployment Validation Script

#!/bin/bash
# scripts/pre-deploy-flyway-check.sh

set -e

echo "Running Flyway validation against production database..."

./mvnw flyway:validate \
  -Dflyway.url="${PROD_DB_URL}" \
  -Dflyway.user="${PROD_DB_USER}" \
  -Dflyway.password="${PROD_DB_PASSWORD}" \
  -Dflyway.cleanDisabled=true

echo "Flyway validation passed. Safe to deploy."

10. Common Issues and Solutions

Checksum Mismatch Error

ERROR: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 3

Cause: A previously applied migration file was modified.

Solution: Never modify already-applied migration files. Either run flyway repair to sync the checksum in the history table (development only), or create a new migration file with the corrective changes (recommended for all environments).

Baseline Setup for Existing Databases

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: 1
    baseline-description: 'Initial baseline'

# Set baseline via CLI
flyway -url=jdbc:postgresql://localhost:5432/mydb \
       -user=myuser \
       -password=mypassword \
       baseline

Quiz: Test Your Flyway Knowledge

Q1. What role does the flyway_schema_history table play?

Answer: It tracks migration execution history as a metadata table.

Explanation: The flyway_schema_history table stores the version, filename, checksum (for detecting file changes), execution time, and success status of each applied migration script. Flyway uses this table to determine which migrations have already been run and only executes newly added scripts in order. It also uses checksums to detect unauthorized changes to previously applied scripts.

Q2. When should you use the baseline-on-migrate option?

Answer: When introducing Flyway to an existing database that is already in production and has no Flyway history table.

Explanation: When you first apply Flyway to an existing database without a flyway_schema_history table, errors can occur. Setting baseline-on-migrate: true causes Flyway to treat the current DB state as a baseline on first run and only apply subsequent migrations. However, avoid using this option on a database that is already managed by Flyway.

Q3. When should you use Repeatable Migrations (R__ prefix)?

Answer: For managing SQL objects like views, stored procedures, and functions that need to be re-executed whenever their content changes.

Explanation: While Versioned Migrations run only once, Repeatable Migrations re-execute whenever their checksum changes. This means you can put a view definition in R__create_views.sql and just update the file content when the view needs to change — Flyway will automatically re-apply it. Repeatable Migrations always run after all Versioned Migrations have been applied.

Q4. How should you handle Flyway transactions when adding an index to a large table in production?

Answer: Use a Java migration that overrides canExecuteInTransaction() to return false, and use PostgreSQL's CREATE INDEX CONCURRENTLY.

Explanation: PostgreSQL's CREATE INDEX CONCURRENTLY creates an index without locking the table, but cannot be used inside a transaction. By returning false from canExecuteInTransaction() in a Java migration, Flyway executes that migration outside a transaction. This allows you to safely add indexes to production tables without service interruption.

Q5. How do you resolve a "checksum mismatch" error that occurs after modifying an already-applied migration file?

Answer: Run flyway repair or add a new versioned migration file with the corrective changes.

Explanation: Flyway raises a checksum mismatch error when an already-applied migration file is changed — this is a safety guard to protect DB consistency. There are two resolutions. First, run flyway repair to synchronize the checksums in the history table with the current files (recommended only in development when a file was changed by mistake). Second, never modify existing files and instead add a new versioned migration with the corrective changes (mandatory for production environments).