Database Migrations with Flyway

Part 21 of 59

May 03, 2026 Abhay 7 min read

Database Migrations with Flyway

Never use spring.jpa.hibernate.ddl-auto=update in production. It’s unpredictable, irreversible, and can corrupt data. Flyway gives you version-controlled, audited, reproducible schema changes.

Why Flyway?

Every database change runs as a versioned SQL script. Flyway tracks which scripts have run in a flyway_schema_history table. When the app starts:

Flyway reads all migration files
Checks which have already run (by checking the history table)
Runs any new ones, in order
If the current state doesn’t match the expected state → fails fast

Benefits:

Schema changes are code-reviewed and version-controlled alongside application code
Every environment has the same schema (dev, staging, prod)
Rollbacks are explicit (you write a rollback migration)
The history table is an audit trail

Setup

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

<!-- For PostgreSQL -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

Disable Hibernate DDL auto (Flyway manages the schema now):

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # or 'none' — Flyway creates/updates the schema

  flyway:
    enabled: true
    locations: classpath:db/migration   # default — where your SQL files live
    baseline-on-migrate: false           # set to true only for existing databases

Migration File Naming

Flyway’s naming convention is strict:

V{version}__{description}.sql

Examples:
V1__create_orders_table.sql
V2__add_customer_id_index.sql
V3__add_order_items_table.sql
V3.1__add_item_quantity_constraint.sql
V4__add_payment_table.sql

Rules:

V prefix for versioned migrations (run once, in order)
R prefix for repeatable migrations (run every time the checksum changes — for views, stored procedures)
U prefix for undo migrations (Flyway Teams only)
Double underscore __ separates version from description
Description uses underscores for spaces

Place files in src/main/resources/db/migration/.

Writing Migration Scripts

V1 — Create the orders table

-- V1__create_orders_table.sql
CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_number VARCHAR(20) NOT NULL UNIQUE,
    customer_id  UUID NOT NULL,
    status       VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    total_amount NUMERIC(12, 2) NOT NULL DEFAULT 0,
    currency     CHAR(3) NOT NULL DEFAULT 'USD',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    version      BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

V2 — Create order_items table

-- V2__create_order_items_table.sql
CREATE TABLE order_items (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id    UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  UUID NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

V3 — Add a column safely

-- V3__add_promo_code_to_orders.sql
ALTER TABLE orders
    ADD COLUMN promo_code VARCHAR(20),
    ADD COLUMN discount_amount NUMERIC(10, 2);

-- Adding a NOT NULL column to an existing table:
-- 1. Add as nullable
-- 2. Backfill
-- 3. Add NOT NULL constraint
ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(20);
UPDATE orders SET shipping_method = 'STANDARD' WHERE shipping_method IS NULL;
ALTER TABLE orders ALTER COLUMN shipping_method SET NOT NULL;
ALTER TABLE orders ALTER COLUMN shipping_method SET DEFAULT 'STANDARD';

V4 — Create a table, add constraints

-- V4__create_payments_table.sql
CREATE TABLE payments (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id     UUID NOT NULL REFERENCES orders(id),
    payment_type VARCHAR(20) NOT NULL,
    amount       NUMERIC(12, 2) NOT NULL,
    status       VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    processed_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_payment_type CHECK (payment_type IN ('CREDIT_CARD', 'BANK_TRANSFER', 'PAYPAL')),
    CONSTRAINT chk_payment_status CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED'))
);

CREATE INDEX idx_payments_order_id ON payments (order_id);
CREATE INDEX idx_payments_status ON payments (status) WHERE status != 'COMPLETED';

V5 — Rename a column (careful!)

-- V5__rename_total_to_subtotal.sql
ALTER TABLE orders RENAME COLUMN total_amount TO subtotal_amount;
-- Warning: this is a breaking change if old code is still deployed
-- Use blue-green deployment: expand/contract pattern

Repeatable Migration — a view that changes

-- R__order_summary_view.sql
-- No version number — Flyway re-runs this when it changes
CREATE OR REPLACE VIEW order_summary AS
SELECT
    o.id,
    o.order_number,
    o.customer_id,
    o.status,
    o.total_amount,
    COUNT(i.id) AS item_count,
    o.created_at
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

The flyway_schema_history Table

Flyway manages this table itself:

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

version	description	success
1	create orders table	true
2	create order items table	true
3	add promo code to orders	true

If a migration fails, it’s recorded with success = false. Flyway won’t start until you fix it:

# 1. Fix the SQL in your migration file
# 2. Repair the history table
./mvnw flyway:repair

# Or in application.properties
spring.flyway.repair-on-migrate=true

Configuration Options

spring:
  flyway:
    enabled: true
    locations:
      - classpath:db/migration
      - filesystem:/external/migrations   # load from filesystem (for secrets)
    schemas: public                        # schema to manage
    table: flyway_schema_history           # history table name
    baseline-on-migrate: false             # true when adding Flyway to existing DB
    baseline-version: 0                    # the baseline version
    validate-on-migrate: true              # validate checksums before running
    out-of-order: false                    # reject out-of-order migrations
    placeholders:                          # ${placeholder} substitution in SQL
      schema: public
      defaultUser: app_user
    placeholder-replacement: true

Using Placeholders

-- V6__create_audit_table.sql
CREATE TABLE ${schema}.audit_log (
    id         UUID PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    row_id     UUID NOT NULL,
    action     VARCHAR(10) NOT NULL,
    changed_by VARCHAR(50) NOT NULL DEFAULT CURRENT_USER,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

GRANT SELECT ON ${schema}.audit_log TO ${defaultUser};

Callbacks — Hook into Flyway Lifecycle

@Component
public class FlywayCallbacks implements FlywayCallback {

    @Override
    public void beforeMigrate(Connection connection) {
        log.info("Starting database migration...");
    }

    @Override
    public void afterEachMigrate(Connection connection, MigrationInfo info) {
        log.info("Applied migration {}: {}", info.getVersion(), info.getDescription());
    }

    @Override
    public void afterMigrate(Connection connection) {
        log.info("Database migration complete");
    }

    @Override
    public void afterMigrateError(Connection connection, MigrationInfo info, Exception e) {
        log.error("Migration {} failed: {}", info.getScript(), e.getMessage());
    }
}

Or as SQL callbacks — placed in db/migration/:

-- beforeMigrate.sql — runs before any migration
SET statement_timeout = '60s';

-- afterMigrate.sql — runs after all migrations complete
ANALYZE;

Safe Migration Patterns for Production

The Expand-Contract Pattern

Renaming a column or changing its type safely, across two deployments:

Phase 1 (Expand) — add new column, keep old one:

-- V10__expand_add_subtotal.sql
ALTER TABLE orders ADD COLUMN subtotal_amount NUMERIC(12, 2);
UPDATE orders SET subtotal_amount = total_amount;  -- backfill

Application code writes to both columns during this phase.

Phase 2 (Contract) — deploy new code, remove old column:

-- V11__contract_remove_total.sql
ALTER TABLE orders DROP COLUMN total_amount;

Non-Destructive Changes

Safe without downtime:

ADD COLUMN (nullable or with default)
CREATE INDEX CONCURRENTLY
CREATE TABLE
ADD CONSTRAINT (as NOT VALID, then VALIDATE separately)

Risky without care:

DROP COLUMN (use expand-contract)
RENAME COLUMN (use expand-contract)
ALTER COLUMN type (use expand-contract)
ADD NOT NULL (backfill then add constraint)

Creating Indexes Concurrently

-- Don't lock the table for index creation
-- V8__add_customer_status_index.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_status
    ON orders (customer_id, status);

Note: CONCURRENTLY can’t run inside a transaction. Flyway wraps each migration in a transaction by default. Disable for this migration:

@Component
public class CreateIndexConcurrently implements JavaMigration {

    @Override
    public MigrationVersion getVersion() {
        return MigrationVersion.fromVersion("8");
    }

    @Override
    public String getDescription() {
        return "add customer status index concurrently";
    }

    @Override
    public boolean canExecuteInTransaction() {
        return false;  // disable transaction wrapping
    }

    @Override
    public void migrate(Context context) throws Exception {
        try (var stmt = context.getConnection().createStatement()) {
            stmt.execute("""
                CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_status
                ON orders (customer_id, status)
                """);
        }
    }
}

Testing Migrations

Test that all migrations run cleanly against a real database:

@SpringBootTest
@Testcontainers
class FlywayMigrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("testdb");

    @DynamicPropertySource
    static void configureProperties(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 Flyway flyway;

    @Test
    void allMigrationsShouldRunSuccessfully() {
        MigrationInfoService infoService = flyway.info();
        MigrationInfo[] migrations = infoService.applied();

        assertThat(migrations).isNotEmpty();
        assertThat(Arrays.stream(migrations))
            .allSatisfy(m -> assertThat(m.getState()).isIn(
                MigrationState.SUCCESS,
                MigrationState.BASELINE
            ));
    }
}

What You’ve Learned

Flyway tracks migration scripts in flyway_schema_history — runs each script exactly once
Naming: V{version}__{description}.sql for versioned, R__{description}.sql for repeatable
spring.jpa.hibernate.ddl-auto=validate in production — Flyway manages the schema
For existing databases: baseline-on-migrate=true to start Flyway tracking from now
Use the expand-contract pattern for renaming or changing column types
CREATE INDEX CONCURRENTLY avoids table locks; implement as JavaMigration with canExecuteInTransaction = false
Test migrations with Testcontainers against a real PostgreSQL instance

Next: Article 22 — Spring Data with PostgreSQL — PostgreSQL-specific features: JSONB, arrays, full-text search, and connection pooling with HikariCP.