Database migrations: Flyway

The ddl-auto: update mode is handy on day one but risky later: Hibernate never drops or renames columns, and on a server you cannot tell what SQL it will run. The proper way is migrations: SQL files with version numbers that live in the project next to the code. The Flyway library checks at startup which ones were already applied and runs the new ones in order.

Setup

<!-- pom.xml -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-flyway</artifactId>
</dependency>

For PostgreSQL also add the org.flywaydb:flyway-database-postgresql module — start.spring.io adds it by itself when you pick Flyway and PostgreSQL together.

The first migration

Create the src/main/resources/db/migration folder and a file in it:

-- src/main/resources/db/migration/V1__create_tables.sql
create table users (
    id       bigint generated by default as identity primary key,
    username varchar(255) not null unique
);
 
create table posts (
    id         bigint generated by default as identity primary key,
    title      varchar(120)   not null,
    body       varchar(10000) not null,
    created_at timestamp(6) with time zone not null,
    author_id  bigint not null references users (id)
);
 
create table comments (
    id         bigint generated by default as identity primary key,
    text       varchar(1000) not null,
    created_at timestamp(6) with time zone not null,
    post_id    bigint not null references posts (id),
    author_id  bigint not null references users (id)
);

The file name follows a strict pattern: V, a version number, two underscores, a description. This SQL works for both H2 and PostgreSQL.

Hibernate only checks

Now Flyway creates the tables, and Hibernate only compares them with the entities:

# src/main/resources/application.yaml
spring:
  jpa:
    hibernate:
      ddl-auto: validate

Delete the data folder to start with an empty database and run the application:

INFO ... Database: jdbc:h2:file:./data/blog (H2 2.4)
INFO ... Creating Schema History table "PUBLIC"."flyway_schema_history" ...
INFO ... Current version of schema "PUBLIC": << Empty Schema >>
INFO ... Migrating schema "PUBLIC" to version "1 - create tables"
INFO ... Successfully applied 1 migration to schema "PUBLIC", now at version v1

Flyway records the applied versions in the flyway_schema_history table. On the next start it sees version 1 and runs nothing. A warning that the H2 version is newer than the one Flyway was verified with does not affect anything.

A new migration

Need a new column — write the next file and leave the old ones alone:

-- src/main/resources/db/migration/V2__add_password_and_role.sql
alter table users add column password varchar(100) not null default '';
alter table users add column role varchar(20) not null default 'USER';

This migration is needed in the security chapter. Flyway applies it on the next start — on your computer and on the server alike.

When an entity and a table differ

Add a field to an entity and forget the migration — validate refuses to start:

Schema validation: missing column [subtitle] in table [posts]

This is a safeguard: failing at startup is better than an SQL error on the very first request.

Common mistake

You must not change a migration that has already been applied. Flyway stores a checksum of every file and compares it at startup:

Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1
-> Applied to database : 2136568703
-> Resolved locally    : -1628166330
Either revert the changes to the migration, or run repair to update the schema history.

Restore the file and make the change as a new V2__... migration. On your own computer, while the data does not matter, you can simply delete the data folder — the database is created again.