Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 66 ยท Data Access

Database Migrations with Flyway

Version your schema like code with Flyway in Spring Boot: naming rules, checksums, repeatable scripts, baselining, ddl-auto pitfalls and zero-downtime changes.

Your code lives in Git, so every change to it is reviewed, versioned and repeatable. Your database schema needs the same treatment. If one developer adds a column by hand on a laptop and forgets to tell anyone, the test server and production will disagree with the code, and you only find out when a query fails. A database migration tool fixes this: the schema is described by an ordered list of SQL scripts, and the tool applies every script that a given database has not seen yet.

Spring Boot supports two such tools out of the box: Flyway and Liquibase. This lesson uses Flyway because its model is the easiest to learn: plain SQL files with a version number in the name. The same ideas (history table, checksums, order) apply to Liquibase and to tools in other languages. For a tool-focused tour outside Boot, see Flyway Migrations.

Flyway and a real database cannot run on this site, so the Spring and SQL files below are read-only. The runnable examples build a small migrator on top of the real H2 database, so you can watch the history table, the checksums and a safe multi-step change happen.

One script per change

Versioned V1, V2, V3 run once, in order.
Repeatable R__ scripts rerun when their content changes.
History table The database remembers what it has run.

Never edit a script after it has been applied anywhere.

1 / 2

Adding Flyway to a Boot project

Add the dependency. Flyway 10 and later split database support into modules, so PostgreSQL and MySQL need an extra artifact:

XML
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<!-- pick the one that matches your database -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

Spring Boot manages the version through its BOM (see Starters and Dependency Management), so you do not write one. With Flyway on the classpath and a DataSource configured, Boot auto-configures a Flyway bean and runs migrate() at startup, before the JPA EntityManagerFactory is created. That order matters: Hibernate sees the finished schema.

By default Flyway looks in src/main/resources/db/migration. Put your first script there:

SQL
-- src/main/resources/db/migration/V1__create_customer.sql
create table customer (
    id         bigint generated by default as identity primary key,
    email      varchar(255) not null unique,
    created_at timestamp    not null default current_timestamp
);

Start the application and Flyway creates its own bookkeeping table, flyway_schema_history, runs the script and records it. Start it again and nothing happens, because the history says version 1 is done.

Naming rules

A versioned script name has four parts, and Flyway is strict about them:

TEXT
V2_1__add_customer_name.sql
^ ^   ^ ^
| |   | description (underscores become spaces)
| |   two underscores
| version (dots or single underscores separate parts)
prefix
  • V1, V2, V2_1 and V10 are ordered numerically by part, so V10 runs after V9, not after V1.
  • Two underscores separate the version from the description. One underscore is a mistake that Flyway reports as an invalid name.
  • Two files with the same version fail at startup.

A common team habit is to use a timestamp as the version (V20261108_1530__add_index.sql) so that two branches rarely collide on the same number.

Flyway's own undo scripts (U2__...) are a paid-edition feature. Most Boot teams skip them: a rollback is just another forward migration that reverses the change.

Checksums: never edit an applied script

For every script it applies, Flyway stores a checksum of the file in the history table. At each startup it recomputes the checksums of the files and compares them with the stored ones. If you edit V2__...sql after it ran anywhere, validation fails and the application refuses to start:

TEXT
Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 2
-> Applied to database : 1741390187
-> Resolved locally    : -914401712

That is a feature. The edited script would never run again on databases that already applied it, so those databases and fresh ones would silently diverge. The fix is always the same: restore the original file and write a new V3 that makes the change.

Repeatable migrations

A script named R__description.sql has no version. Flyway runs it after all versioned scripts, and runs it again whenever its checksum changes. That suits objects you can recreate in one statement, such as views, and stored procedures:

SQL
-- R__customer_summary_view.sql
create or replace view customer_summary as
select c.id, c.email, count(o.id) as order_count
from customer c
left join orders o on o.customer_id = c.id
group by c.id, c.email;

Write repeatable scripts so they are safe to run any number of times (create or replace, not plain create). Do not use them for tables: changing a table needs an ordered, one-time step.

Existing databases and baselining

What if the database already has tables from before you adopted Flyway? On a non-empty schema with no history table, Flyway stops and refuses to guess. Two settings handle it:

YAML
spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: 5

With these, Flyway records version 5 as the baseline on first contact: it assumes the schema already matches everything up to V5, ignores scripts V1 to V5 and applies V6 onward. The usual practice is to export the current production schema once as a V1 (or as the baseline version), test it on an empty database, and only then switch on baseline-on-migrate in production. Be careful with the flag on a fresh database that happens to contain other tables; it would silently skip your early scripts.

Boot settings worth knowing

YAML
spring:
  flyway:
    enabled: true                 # the default
    locations: classpath:db/migration
    validate-on-migrate: true     # the default; keep it on
    out-of-order: false           # the default
    clean-disabled: true          # the default; keeps 'clean' (drop everything) off
  jpa:
    hibernate:
      ddl-auto: validate          # Hibernate checks the schema, never changes it

You can set a different locations per profile, for example classpath:db/migration,classpath:db/dev-data for sample data that only the dev profile loads.

The ddl-auto trap

spring.jpa.hibernate.ddl-auto=update makes Hibernate alter tables to match your entities. It is tempting because it needs no scripts, but it is the wrong tool for anything beyond a throwaway demo. It never drops columns, cannot rename them (it adds a new one and leaves the old), cannot migrate data, does not tell you what it ran and behaves differently from one start to the next. With Flyway, set it to validate (fail at startup if entities and tables disagree) or none. For the in-memory test database used by quick tests, create-drop is fine; see Testing the Data Layer.

A mini migrator you can run

The first stand-in does what Flyway does at startup, in about forty lines: keep a history table, order scripts by version, skip applied ones, compare checksums and refuse changed scripts. It uses real H2 through JDBC. Real Flyway adds locking, callbacks, per-database quirks, repeatable and Java migrations, but the core loop is this one.

Java A mini Flyway on H2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Notice that 10 ran after 2 and that 10.1 ran after 10, even though the scripts were passed in a different order: the version, not the file listing, decides. The history lines are in the order the scripts were applied. Notice also that the validation of start 4 happens before any script runs.

Zero-downtime changes: expand and contract

During a deployment, the old version of your application and the new one often run side by side for a few minutes (rolling updates). The schema must work for both. A change like "rename name to full_name" breaks the old version the moment the old column disappears. The safe pattern is expand, then contract, spread over several releases:

flowchart LR
    A["Release 1<br/>expand: add full_name<br/>(nullable)"] --> B["Release 2<br/>app writes both<br/>backfill old rows"]
    B --> C["Release 3<br/>app reads only full_name"]
    C --> D["Release 4<br/>contract: drop name"]

Each step is compatible with the application version before and after it.

The rules of thumb: add columns as nullable (or with a default) first; add NOT NULL only after every row has a value; add indexes with the concurrently or online options your database offers on big tables; drop things only when no running code uses them. The second stand-in plays an old and a new application against each schema step.

Java Expand and contract on H2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

In step 2 the old application is rejected as soon as full_name becomes NOT NULL: it does not know the new column. This is the lesson of the demo. Making the column required is itself a contract-style change, so it belongs in the same late release as dropping the old column, after the old code is retired. Reorder the steps in your own plan to match: expand, deploy code that writes both, backfill, deploy code that reads the new column, then tighten and drop.

Common mistakes

  • Editing an applied script. Add a new version instead; the checksum check exists to stop exactly this.
  • Using ddl-auto=update next to Flyway. Two tools changing the schema produce drift. Use validate.
  • Huge data fixes in the same script as DDL. On big tables, long updates hold locks; batch them or run them as separate, monitored steps.
  • Environment-specific SQL in shared scripts. Keep test data in a profile-specific location, not in the versions that production runs.
  • Skipping an empty-database test. Run the full chain on a blank database in CI regularly. It proves the scripts build the schema from nothing, not just on top of today's production.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which file name is a valid versioned Flyway migration?

    The prefix V, the version, two underscores and a description are all required. One underscore is an invalid name, and R means a repeatable migration, which has no version.
  2. 2. You fix a typo in V2__add_name.sql after it has already run in production. What happens at the next startup there?

    Flyway stores a checksum of every applied script and compares it at startup. Fix problems with a new, higher version.
  3. 3. What does spring.jpa.hibernate.ddl-auto=validate do when you use Flyway?

    validate never changes the schema. It catches a mismatch between the scripts and the entities early, while the scripts stay the single source of truth.
  4. 4. Why is a column rename usually done as several releases (expand and contract)?

    A direct rename breaks whichever application version does not know the new name. Adding first and removing last keeps both versions working.
  5. 5. When does a repeatable migration (R__...sql) run?

    Repeatable scripts are re-applied when their content changes, which suits views and procedures written with create or replace.

Summary

  • Flyway applies ordered SQL scripts from db/migration and records each in flyway_schema_history; Boot runs it before JPA starts.
  • Names follow V<version>__<description>.sql; versions sort numerically, and R__ scripts rerun when their content changes.
  • Checksums make applied scripts immutable: fix mistakes with a new version.
  • Use baseline-on-migrate only to adopt Flyway on an existing database, and set ddl-auto to validate or none.
  • Change schemas in expand and contract steps so old and new application versions both work during deployment.

Next: Testing the Data Layer.