Database Migrations with Flyway and Spring Boot

spring.jpa.hibernate.ddl-auto=update gets a schema into existence and cannot get it from one known state to another. It never drops a column, never narrows a type, never renames anything, and it has no record of what it did, so two environments that took different paths end up genuinely different, with nothing to compare.

Flyway makes the schema a sequence of versioned SQL files in your repository, applied in order, and recorded. The interesting parts are what happens when someone edits an applied file, and what MySQL does when a migration fails halfway.

Written against Spring Boot 3.2, Flyway 10 and Java 17.

Setup

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<!-- from Flyway 9, each database family is its own module -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

That second dependency catches people upgrading from an older Flyway. Support for MySQL, PostgreSQL and the rest used to live in flyway-core; since version 9 each is a separate artifact. Without it the startup failure is Unsupported Database: MySQL 8.4, on a database Flyway obviously supports.

PostgreSQL needs flyway-database-postgresql. Spring Boot’s dependency management supplies the versions.

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=false

spring.jpa.hibernate.ddl-auto=validate

ddl-auto=validate, never update. With both active, Hibernate and Flyway each modify the schema and neither knows about the other: Flyway applies a migration, Hibernate silently adds a column the migration did not, and the next environment differs. validate keeps the useful half: startup fails if the entities and the schema disagree, which catches a forgotten migration immediately.

Writing migrations

src/main/resources/db/migration/
├── V1__create_notes_table.sql
├── V2__add_author_to_notes.sql
├── V3__backfill_author_defaults.sql
└── R__note_search_view.sql

The naming is strict and the parts are load-bearing:

V     prefix: V versioned, R repeatable, U undo (paid feature)
1     version — dots or underscores for sub-versions: V1_1, V2.3
__    TWO underscores separating version from description
name  description, underscores become spaces in the log
.sql  suffix

One underscore instead of two is the most common mistake, and the file is simply ignored. No error, no migration, and a schema that is silently a version behind.

-- V1__create_notes_table.sql
CREATE TABLE notes (
    id         BIGINT       NOT NULL AUTO_INCREMENT,
    title      VARCHAR(200) NOT NULL,
    content    TEXT         NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_notes_created ON notes (created_at);

-- V2__add_author_to_notes.sql
ALTER TABLE notes ADD COLUMN author_id BIGINT NULL;
CREATE INDEX idx_notes_author ON notes (author_id);

Nullable, deliberately. Adding a NOT NULL column to a table with rows fails unless you supply a default, and a default on a large table can mean a full rewrite. The safe sequence is three migrations: add nullable, backfill, then add the constraint.

Repeatable migrations (R__) run whenever their checksum changes, after all versioned ones. They suit objects you replace wholesale rather than alter:

-- R__note_search_view.sql
CREATE OR REPLACE VIEW note_search AS
SELECT n.id, n.title, u.name AS author
FROM notes n LEFT JOIN users u ON u.id = n.author_id;

Views, stored procedures, functions. Edit the file, and it reapplies on the next start.

What happens at startup

Flyway Community Edition 10.x by Redgate
Database: jdbc:mysql://localhost:3306/notes (MySQL 8.4)
Successfully validated 4 migrations (execution time 00:00.031s)
Creating Schema History table `notes`.`flyway_schema_history` ...
Migrating schema `notes` to version "1 - create notes table"
Migrating schema `notes` to version "2 - add author to notes"
Successfully applied 2 migrations (execution time 00:00.187s)

Flyway runs before Hibernate initialises, so entities always see a migrated schema. It creates flyway_schema_history and records every applied migration:

mysql> SELECT installed_rank, version, description, checksum, success FROM flyway_schema_history;
+----------------+---------+------------------------+------------+---------+
|              1 | 1       | create notes table     | 1284677401 |       1 |
|              2 | 2       | add author to notes    | -892014339 |       1 |
+----------------+---------+------------------------+------------+---------+

That checksum column is the mechanism behind the most important rule.

Never edit an applied migration

FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 2
-> Applied to database : -892014339
-> Resolved locally    : 1194855012

On every start, Flyway hashes each file and compares it to the recorded checksum. A changed file fails validation and the application refuses to start.

That is correct behaviour and it saves you from the alternative. Your machine has the new version of V2; production applied the old one. If Flyway ignored the difference, the two schemas would diverge permanently with nothing recording it.

The fix is always a new migration, never an edit:

-- V4__fix_author_column_type.sql
ALTER TABLE notes MODIFY COLUMN author_id BIGINT UNSIGNED NULL;

flyway repair rewrites the stored checksums to match the files. It is the right tool when a migration was edited before reaching any shared environment, and the wrong one everywhere else. It makes the error disappear without making the schemas agree.

The corollary: a migration file is immutable once merged. Fixing a typo in a description is enough to break every environment that already ran it.

An existing database

Pointing Flyway at a database that already has tables fails: it finds a non-empty schema and no history table, and stops rather than guessing.

spring.flyway.baseline-on-migrate=true
spring.flyway.baseline-version=1
spring.flyway.baseline-description=existing schema

This records version 1 as already applied without running anything, so migrations from V2 onward proceed. Write V1__baseline.sql containing the current schema, mysqldump --no-data is a reasonable start, so a fresh environment can build from nothing.

Turn baseline-on-migrate off again once every environment has a history table. Left on. It will silently baseline a database that should have failed loudly, which hides a genuinely broken deployment.

The MySQL problem

PostgreSQL has transactional DDL: a migration that fails halfway rolls back completely, and you fix the file and rerun.

MySQL does not. Every DDL statement commits implicitly, so a migration with three ALTERs that fails on the second leaves the first applied and the third not. Flyway marks the migration failed in the history table, and the next startup refuses to continue:

Detected failed migration to version 5 (add constraints).
Please remove any half-completed changes then run repair to fix the schema history.

There is no automatic recovery, and it is worth being clear that this is the database’s limitation rather than Flyway’s. The practical defences:

• One logical change per migration. A file with a single ALTER cannot half-apply.
• Idempotent statements where the dialect allows (DROP INDEX IF EXISTS, ADD COLUMN IF NOT EXISTS on MySQL 8) so a rerun after manual cleanup succeeds.
• Rehearse on a copy of production data. A migration that passes on an empty test schema and fails on 40 million rows is the normal case, not an unlucky one.

Testing migrations

The migrations are code and deserve a test that runs them against the real engine:

@Testcontainers
@SpringBootTest
@ActiveProfiles("test")
class MigrationTest {

    @Container
    @ServiceConnection
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.4");

    @Autowired
    private JdbcTemplate jdbc;

    @Test
    void appliesEveryMigration() {
        Integer applied = jdbc.queryForObject(
                "SELECT COUNT(*) FROM flyway_schema_history WHERE success = 1", Integer.class);
        assertThat(applied).isGreaterThan(0);
    }

    @Test
    void schemaMatchesEntities() {
        // ddl-auto=validate: the context would not have started otherwise
        assertThat(jdbc.queryForObject(
                "SELECT COUNT(*) FROM information_schema.columns " +
                "WHERE table_name = 'notes' AND column_name = 'author_id'", Integer.class))
                .isEqualTo(1);
    }
}

@ServiceConnection (Spring Boot 3.1+) wires the container’s JDBC URL into the context with no property configuration. Running against MySQL rather than H2 is the point: H2’s compatibility mode accepts syntax MySQL rejects, so a green H2 test proves very little about a MySQL migration.

Operational notes

# fail rather than reorder if a lower version appears after a higher one has run
spring.flyway.out-of-order=false

# never enable in a shared environment: clean() drops every object in the schema
spring.flyway.clean-disabled=true

# substitute values into migration SQL
spring.flyway.placeholders.app_user=notes_app

out-of-order=false is the default and worth leaving. Two developers branching from V5 and both writing V6 is the common cause of a merge that looks clean and produces two migrations with the same version.

clean-disabled defaults to true from Flyway 9, after enough people ran clean against production. Leave it.

For larger teams, running migrations as a separate deploy step, a job that exits, rather than on application startup avoids several instances racing to migrate simultaneously. Flyway takes a lock, so it is safe either way, but a slow migration then delays every instance’s startup and can trip a readiness probe.

Frequently asked questions

Should I use Flyway or ddl-auto=update?

Flyway, with ddl-auto=validate. update never drops or narrows anything, keeps no record, and lets environments diverge silently.

Why is my migration file being ignored?

Almost always one underscore instead of two after the version, V2_add_column.sql rather than V2__add_column.sql. It is skipped without an error.

What does “checksum mismatch” mean?

An already-applied migration file has changed. Never edit an applied migration; add a new one. flyway repair only rewrites the recorded checksums and does not reconcile the schemas.

How do I start using Flyway on an existing database?

baseline-on-migrate=true with a baseline-version, plus a V1__baseline.sql capturing the current schema. Turn the flag off once every environment has a history table.

Why does Flyway say my database is unsupported?

Since Flyway 9 each database family is a separate module. Add flyway-mysql, flyway-database-postgresql, or the one you need.

What happens if a migration fails halfway on MySQL?

It stays half-applied, MySQL commits each DDL statement implicitly. Clean up manually, then repair. Keep migrations to one logical change to limit the blast radius.

Can I roll back?

Not in the community edition; undo is a paid feature. Write a forward migration that reverses the change, which is what most teams do regardless.

What are repeatable migrations for?

Objects you replace rather than alter, views, procedures, functions. R__ files rerun whenever their checksum changes, after all versioned migrations.

Should migrations run at application startup?

Fine for small teams. For larger deployments, a separate migration step avoids a slow migration delaying every instance’s startup and tripping readiness probes.

How do I test migrations?

Testcontainers against the real database engine. H2 in compatibility mode accepts SQL the real database rejects, so a passing H2 test proves little.

Where should I go next?

The Spring Boot REST API guide covers the entities this schema backs, and the PostgreSQL guide covers a database where a failed migration rolls back cleanly.