Schema migrations with Flyway

spring.jpa.hibernate.ddl-auto=update will keep your schema in step with your entities, and it is the reason this lesson exists. It works on your laptop, it works in the demo, and the first time it meets production data it will decide \u2014 silently, at startup, with no review \u2014 what to do about a column you renamed.

A migration tool replaces that guess with a file somebody wrote and somebody else read.

#Why a file beats an inference

ddl-auto=update compares your entities to the database and issues DDL to close the gap. Three things it cannot do, and each is fatal in production:

• It never drops or narrows anything. Rename a field and you get a new column beside the old one, with the old data still in the old one and nothing in the new. No error.
• It cannot move data. Splitting name into first_name and last_name is a data problem, and Hibernate only speaks schema.
• It is not reviewable. Nobody can read a diff of what it is about to do, because there is no artefact until it runs.

A migration is an ordinary SQL file in the repository. It goes through review, it is the same on every machine, and the order it runs in is written down.

#The conventions are the whole API

Files live in src/main/resources/db/migration and the name carries the meaning:

V1__create_account.sql
V2__add_email_to_account.sql
V3__backfill_email.sql
R__account_summary_view.sql

• V is a versioned migration: runs once, in version order, ever.
• R is repeatable: runs whenever its checksum changes, after all pending Vs. For views, functions and stored procedures \u2014 things you would rather edit in place than accumulate versions of.
• Two underscores separate version from description. One is a common and confusing typo.

Flyway keeps a flyway_schema_history table in your database recording every migration it applied, when, and a checksum of the file's contents.

#The checksum that stops a deploy

That checksum is the mechanism behind the rule people learn the hard way. Apply a migration, then edit the file \u2014 as you might if you spotted a typo, or widened a column while "fixing up" your own change:

Migration checksum mismatch for migration version 1
-> Applied to database : 1047314683
-> Resolved locally    : 1938079900
Either revert the changes to the migration, or run repair to update the schema history.

The application does not start. Not a warning, not a degraded mode \u2014 the flywayInitializer bean fails and the context never comes up.

That is deliberate, and it is the feature. The database in front of you was built by the old content of that file. Your repository now says something different. Flyway cannot know whether the difference matters, so it refuses to guess \u2014 and it refuses before your application serves a single request against a schema nobody can describe.

The rule that follows: a migration that has run anywhere is immutable. Not on production \u2014 anywhere. If a colleague has run it, editing it breaks their machine. If CI has run it, editing it breaks the build. Write V4 instead.

flyway repair rewrites the history table's checksums to match the files. It is the right tool for exactly one situation: you edited a migration that had only ever run on your own laptop. Reaching for it against production is telling the tool to stop checking the thing it exists to check.

#Roll forward, not back

Flyway Community has no undo, and the instinct that this is a gap is worth examining, because the gap is mostly in the idea of rollback itself.

A migration that ran on production has already had traffic against it. drop column email cannot be undone \u2014 the data is gone. A down-migration that recreates the column gives you a schema that matches, holding nothing. The schema rolled back; the data did not.

So the discipline is forward-only, in small steps, and the pattern for anything destructive is expand \u2192 migrate \u2192 contract:

1. Expand. Add the new column. Deploy. Old code ignores it; new code can use it.
2. Migrate. Backfill, and have the application write both for a while. Deploy.
3. Contract. Once nothing reads the old column, drop it. Deploy.

Three deploys instead of one, and at no point is there a version of the application that cannot run against the database in front of it. That property \u2014 every deploy is compatible with the schema both before and after it \u2014 is what makes a rollback of the application possible, which is the rollback you actually want at three in the morning.

#Starting on a database that already exists

Most teams adopt Flyway on a schema that is already years old. baseline is the answer: it writes a history row saying everything up to version N was already here, and Flyway then only applies migrations above it.

spring.flyway.baseline-on-migrate=true
spring.flyway.baseline-version=1

The honest way to do it is to dump the current schema into V1__baseline.sql first, so a fresh database \u2014 a new developer, a test container \u2014 can be built from the repository alone. A project whose migrations only work against a database somebody already has is a project with no reproducible environment.