Database Migrations with Flyway

Your schema is code. It needs version control, review, and a repeatable way to get every environment — your laptop, CI, staging, production — to the same state. Flyway does this with plain SQL files applied in order, and Spring Boot runs it automatically at startup.

Setup

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-flyway</artifactId>
</dependency>
<!-- For PostgreSQL, Flyway also needs its database module: -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

(In Boot 3 you add org.flywaydb:flyway-core directly instead of a starter. Since Flyway 10, most databases other than H2 need their flyway-database-* module.)

Migrations live in src/main/resources/db/migration:

db/migration/
├── V1__create_authors_and_books.sql
├── V2__add_book_language.sql
└── V3__create_loans.sql

The name is V<version>__<description>.sql — capital V, two underscores. Versions sort numerically (V2 < V10).

A first migration

-- V1__create_authors_and_books.sql
create table author (
    id          bigint generated by default as identity primary key,
    name        varchar(200) not null
);

create table book (
    id          bigint generated by default as identity primary key,
    title       varchar(300) not null,
    isbn        varchar(20)  not null unique,
    published   integer,
    author_id   bigint not null references author(id),
    version     bigint not null default 0
);

create index idx_book_author on book(author_id);

Pair it with spring.jpa.hibernate.ddl-auto: validate: Flyway creates the schema; Hibernate checks at startup that the entities match it. If someone adds a field to an entity without a migration, the app refuses to start instead of failing on the first query.

Worked example: what startup does

When this level's project starts on a fresh H2 database, the log shows (real lines, Flyway 12 with Boot 4.1):

o.f.core.internal.command.DbMigrate : Migrating schema "PUBLIC" to version "1 - create authors and books"
o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema "PUBLIC", now at version v1 (execution time 00:00.005s)

Start it again against a persistent database and Flyway applies nothing — it sees V1 is already recorded. Add V2__add_book_language.sql:

alter table book add column language varchar(8) not null default 'en';

and the next startup applies only V2.

The rules of the road

1. Never edit an applied migration. Flyway stores a checksum of each applied file; a changed file fails validation at startup ("Migration checksum mismatch"). Fix mistakes with a new migration.
2. One logical change per migration, with a descriptive name. Reviewers read these.
3. Migrations must work on the production database engine. Test against the real engine (Testcontainers, next lesson); H2's SQL dialect differs from PostgreSQL's in many small ways.
4. Data migrations are migrations too. Backfilling a column is a V file with an update statement — or, for huge tables, a batched job run separately.
5. Repeatable migrations (R__views.sql) rerun whenever their content changes — useful for views and functions that you want to redefine as a whole.

Changing a schema without downtime

During a rolling deployment, old and new versions of your app run against the same database at the same time. So every migration must be compatible with both the current code and the next code. Renaming a column in one step breaks the old version immediately. Use expand–contract instead:

Release	Migration	Code
1	Add full_name (nullable)	Write both name and full_name; read name
2	Backfill full_name from name	Read full_name
3	Make full_name not null	Stop writing name
4	Drop name	—

It is slower, and it is how you rename things in a live system. Also watch for migrations that lock large tables (adding a column with a volatile default, creating an index without CONCURRENTLY in PostgreSQL); on big tables these can block writes for minutes.

How It Actually Works

Boot's FlywayAutoConfiguration creates a Flyway bean configured from spring.flyway.* and a FlywayMigrationInitializer that calls flyway.migrate() during context startup. Boot also makes the JPA EntityManagerFactory depend on the Flyway initializer, so the schema is migrated before Hibernate validates it — that ordering is why validate works.

migrate() itself:

1. Takes a lock (on PostgreSQL, an advisory lock) so two instances starting at once do not both migrate.
2. Creates flyway_schema_history if missing. Each row records version, description, script name, checksum (CRC32 of the file), who ran it, when, how long, and success.
3. Scans the classpath locations, sorts migrations by version, and validates the applied ones by checksum.
4. Applies each pending migration, by default in its own transaction (PostgreSQL supports transactional DDL, so a failed migration rolls back cleanly; MySQL does not, so a half-applied migration there needs manual repair).

Common mistakes

• Editing V1 after it ran somewhere. Create V2.
• Mixing ddl-auto: update with Flyway, so two tools fight over the schema.
• Testing migrations only on H2. They pass, then fail on PostgreSQL in staging.
• Renaming or dropping columns in one release during rolling deploys.
• Running flyway clean against anything shared. It drops everything; Boot disables it by default (spring.flyway.clean-disabled=true) — keep it that way.

Exercise

1. Add Flyway to your project with V1 above and switch to ddl-auto: validate.
2. Add V2__add_book_language.sql and a language field on the entity. Confirm startup applies V2 once. Then remove the field from the entity only and observe that validation still passes (extra columns are allowed); remove it from the migration only and observe the failure.
3. Edit V1 after it has been applied to a file-based H2 or PostgreSQL database and read the checksum error.
4. Plan (in writing) an expand–contract rename of book.published to book.published_year, listing the migrations and code changes per release.