Database Migrations with Flyway

Version your schema as numbered SQL files that Flyway applies once, in order, on every environment before the application starts serving.

Syntax

src/main/resources/db/migration/
  V1__create_room.sql        # versioned: runs once, in order
  V2__add_room_floor.sql
  R__room_summary_view.sql   # repeatable: reruns when its checksum changes

spring.flyway.locations=classpath:db/migration

Your schema changes as the code changes. Running ALTER TABLE by hand on each environment works until someone forgets one. Flyway keeps the changes as numbered files in the repository, records which ones each database has applied in a table called flyway_schema_history, and applies the missing ones at start-up. Every environment ends up with the same schema, built the same way.

Setting it up

Add spring-boot-starter-flyway. For PostgreSQL also add org.flywaydb:flyway-database-postgresql; Flyway keeps database support in separate modules, and without it start-up fails with an unsupported database error. Spring Boot manages both versions. Migrations go in src/main/resources/db/migration:

V1__create_room.sql
V2__create_booking.sql
V3__add_booking_status.sql
R__booking_summary_view.sql

The name is the contract: V, a version, two underscores, a description. Versioned files run once, in version order. Repeatable files (R__) run again whenever their content changes, which suits views and functions that you redefine with CREATE OR REPLACE.

The rules that keep you out of trouble

Never edit an applied migration. Flyway stores a checksum of each file and refuses to start when one changes. Fix a mistake with a new migration.
Let Flyway own the schema. With JPA, set spring.jpa.hibernate.ddl-auto=validate so Hibernate checks the schema but never changes it. Spring Boot runs Flyway before the EntityManagerFactory starts, so validation sees the migrated tables.
Two developers, same version number. Both branches add V7 and the second merge breaks. Timestamp-style versions such as V20261013_1__... avoid the clash, but a branch merged late can now carry a version lower than one already applied, and Flyway rejects that pending migration by default. Rebase it to a newer version before merging, or deliberately enable spring.flyway.out-of-order.

Production consequences

Migrations run during start-up, so a slow one delays readiness and a failed one stops the app. On PostgreSQL most DDL is transactional and a failed migration rolls back cleanly; on MySQL it is not, and a half-applied file needs manual repair. When several replicas start at once, Flyway takes a lock so only one migrates while the rest wait.

During a rolling deploy the old version still runs against the new schema. Make changes in compatible steps: add the new column, deploy code that writes both, backfill, and only drop the old column in a later release. Renaming a column in one migration breaks every pod still on the old code.

flyway clean drops everything in the schema. Spring Boot leaves spring.flyway.clean-disabled at true; keep it that way everywhere outside throwaway test databases.

Example

-- V1__create_room.sql
CREATE TABLE room (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      VARCHAR(100) NOT NULL UNIQUE,
    capacity  INT          NOT NULL CHECK (capacity > 0)
);

-- V2__create_booking.sql
CREATE TABLE booking (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id     BIGINT       NOT NULL REFERENCES room (id),
    guest_name  VARCHAR(200) NOT NULL,
    starts_at   TIMESTAMPTZ  NOT NULL,
    nights      INT          NOT NULL CHECK (nights > 0)
);
CREATE INDEX booking_room_starts_idx ON booking (room_id, starts_at);

-- V3__add_booking_status.sql (additive: old code keeps working)
ALTER TABLE booking ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'CONFIRMED';

When to use it

Add a non-null column with a default to a live bookings table without downtime during a rolling deploy.
Bring an existing hand-built production database under version control with baseline-on-migrate.
Give every developer and every Testcontainers test the same schema built from the same files.

More examples

Flyway settings for an existing database

baseline-on-migrate is for introducing Flyway to a database that was built by hand: it records a baseline instead of trying to create tables that already exist. The {vendor} placeholder lets you keep database-specific scripts in folders such as db/postgresql. Baselining only happens when the schema is not empty and has no history table, so turn the flag off again once the baseline is recorded: left on, pointing the application at the wrong non-empty schema would quietly baseline it instead of failing.

spring.flyway.locations=classpath:db/migration,classpath:db/{vendor}
# Adopt a database that already has tables: mark it as version 1, then apply V2 onwards
spring.flyway.baseline-on-migrate=true
spring.flyway.baseline-version=1
# Fail start-up if a migration file was edited after it ran
spring.flyway.validate-on-migrate=true
spring.flyway.clean-disabled=true
# Flyway owns the schema; Hibernate only checks it
spring.jpa.hibernate.ddl-auto=validate

A Java migration for a data backfill

Flyway reads the version and description from the class name, exactly as it does for SQL files, and scans the db.migration package by default. Reach for Java only when SQL cannot express the change; a single UPDATE ... SET email = lower(trim(email)) would do this job better. Never inject your application's repositories into a migration: they reflect the current code, not the schema at version 4.

package db.migration;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;

public class V4__Normalise_guest_emails extends BaseJavaMigration {

    @Override
    public void migrate(Context context) throws Exception {
        try (Statement select = context.getConnection().createStatement();
             ResultSet rows = select.executeQuery("SELECT id, email FROM guest WHERE email <> lower(trim(email))");
             PreparedStatement update = context.getConnection()
                     .prepareStatement("UPDATE guest SET email = ? WHERE id = ?")) {
            while (rows.next()) {
                update.setString(1, rows.getString("email").trim().toLowerCase());
                update.setLong(2, rows.getLong("id"));
                update.addBatch();
            }
            update.executeBatch();
        }
    }
}

Repairing before migrating in development

A FlywayMigrationStrategy bean replaces the default call to migrate(). Repair realigns stored checksums with the files on disk, which is handy while you iterate on a migration that has not been merged yet. Limit it to a dev profile: in production it would hide the warning that someone edited an applied migration.

import org.springframework.boot.flyway.autoconfigure.FlywayMigrationStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;

@Configuration
@Profile("dev")
public class DevFlywayConfig {

    @Bean
    FlywayMigrationStrategy repairThenMigrate() {
        return flyway -> {
            flyway.repair();
            flyway.migrate();
        };
    }
}

Discussion

Be the first to comment on this lesson.