Schema migrations with Flyway

Key points

• Migrations are SQL files under src/main/resources/db/migration, named V1__init.sql upwards.
• At startup Flyway compares them with the flyway_schema_history table and runs only what is still pending.
• An applied file is immutable. Editing one changes its checksum and the application refuses to start.
• Set spring.jpa.hibernate.ddl-auto=validate so JPA checks the schema against your entities but never changes it.
• Write migrations that are safe while the previous version is still running: add nullable, backfill, then enforce.
• Testcontainers plus Flyway gives every test the real schema on the real database engine.

Example

-- src/main/resources/db/migration/V1__init.sql
create table orders (
    id             bigserial primary key,
    customer_email varchar(255) not null,
    status         varchar(32)  not null,
    created_at     timestamptz  not null default now()
);

-- V2__add_total.sql : safe in three steps, deployable while v1 still runs
alter table orders add column total numeric(12,2);          -- 1. nullable
update orders set total = 0 where total is null;            -- 2. backfill
alter table orders alter column total set not null;         -- 3. enforce

-- V3__orders_email_idx.sql
create index orders_email_idx on orders (customer_email);

-- application.yml
--   spring.flyway.enabled: true
--   spring.flyway.baseline-on-migrate: true   # only for an existing database
--   spring.jpa.hibernate.ddl-auto: validate

The schema is code: reviewed, versioned, applied once, and never edited after it has run.