Flyway Migration Naming: Versioned, Repeatable and Undo Scripts

A Flyway migration is found, ordered and tracked by nothing but its file name. Get the name right and Flyway does exactly what you expect; get it subtly wrong and the file is silently ignored. This guide covers the three prefixes, how versions are compared, what the history table records, and the configuration switches (validation, repair, outOfOrder, baseline) that decide what happens when reality and the file list disagree.

Anatomy of a migration file name#

Take V2_1__Add_visits_table.sql. Flyway reads it as five parts:

Every part except the description is configurable (sqlMigrationPrefix, sqlMigrationSeparator, sqlMigrationSuffixes and their repeatable and undo counterparts), but almost nobody changes them, and a reader who knows the defaults can read any Flyway project. The sample project used across this site is the plainest possible case:

V1__create_owners_and_pets.sql
V2__create_vets.sql
V3__create_visits.sql

How versions are compared#

Versions are compared part by part as numbers, not as strings. V1_10 runs after V1_9, V2 runs after V1_99, and leading zeros carry no meaning, so V001 and V1 are the same version and Flyway refuses to start with both present. Dots and underscores are interchangeable in the version, which is why V2.1__x.sql and V2_1__x.sql collide too.

Two version schemes dominate. Sequential integers (V1, V2, V3) are readable and make the order obvious, but two branches that both add V12 will fight at merge time. Timestamps (V20260512143000__add_visits.sql) almost never collide and sort naturally, at the cost of readability. Mixing the two in one project is legal and a bad idea: every timestamp is a larger number than every sequential version, so the day someone adds V13 after a timestamped migration it will be lower than the current version and will not run without outOfOrder.

Repeatable migrations#

A file that starts with R__ has no version. Flyway runs all repeatable migrations after the versioned ones in the same run, in the alphabetical order of their descriptions, and reruns any whose checksum has changed since it was last applied. That makes them the right home for objects you define as a whole rather than evolve step by step: views, functions, stored procedures, triggers, reference data loaded with idempotent statements.

-- R__owner_summary_view.sql
CREATE OR REPLACE VIEW owner_summary AS
SELECT o.id, o.last_name, COUNT(p.id) AS pets
FROM owners o
LEFT JOIN pets p ON p.owner_id = o.id
GROUP BY o.id, o.last_name;

Two rules follow. A repeatable migration must be safe to run again (CREATE OR REPLACE, DROP ... IF EXISTS followed by create, or an upsert), and it must not depend on another repeatable migration running first unless the alphabetical order guarantees it. Prefixing descriptions with a number (R__01_types.sql, R__02_views.sql) is the usual way to pin that order.

Undo migrations#

A file named U2__Add_visits_table.sql is the undo of version 2 and is expected to reverse what V2 did. Undo migrations are a feature of Flyway's paid editions; the open-source edition ignores them. Teams on the free edition, which is most teams, roll forward instead: a bad V7 is followed by a V8 that corrects it, and the history stays honest about what happened. If you do have undo, remember that undoing a migration that dropped a column cannot bring the data back, so undo scripts are for schema mistakes caught quickly, not for disaster recovery.

What the schema history table records#

Every applied migration becomes a row in flyway_schema_history. The columns worth knowing are installed_rank (the order of application, which is not necessarily version order), version (null for repeatable migrations), description, type (SQL, JDBC, BASELINE and a few more), script (the file name), checksum, installed_by, installed_on, execution_time and success. When something goes wrong in production, this table is the first thing to query: a row with success = false tells you which script failed, and on databases without transactional DDL it also tells you that part of it may have been applied.

Checksums, validate and repair#

Flyway computes a checksum of every SQL migration and stores it. Before migrating (with validateOnMigrate, on by default) it compares the stored checksum of each applied migration with the file on disk and stops on any difference. This is the guard against the most common migration mistake: editing a script that already ran somewhere. A whitespace or comment change trips it too, which surprises people, but the alternative, guessing which edits are harmless, is worse.

flyway repair is the escape hatch. It rewrites the stored checksums to match the current files, removes rows of failed migrations so they can be retried, and realigns descriptions and types. Run it deliberately, after a human has confirmed that the difference is benign, never automatically in a pipeline.

outOfOrder and baseline#

By default Flyway refuses to apply a migration whose version is lower than the highest one already applied; a late-arriving V11 after V12 ran is an error, because the schema might now depend on V12. Setting outOfOrder=true tells Flyway to apply such stragglers anyway. It is common on development databases where branches come and go, and questionable in production, where a migration running in an order nobody tested is a risk. ignoreMigrationPatterns can silence specific cases when you have decided they are fine.

Baseline is for adopting Flyway on a database that already exists. flyway baseline inserts a row of type BASELINE at baselineVersion (1 by default) and Flyway then applies only migrations above that version. With baselineOnMigrate=true the first migrate against a non-empty schema without a history table does this automatically. The migration files below the baseline should still exist in the repository, because a fresh environment needs them to be built from nothing.

Mistakes that bite#

• A single underscore. V4_add_index.sql has no __ separator, so Flyway cannot split it into version and description and, by default, skips it without complaint.
• Editing an applied script. Validation fails on every environment where it ran. Write a new migration.
• Two migrations, one version. Flyway refuses to start. Rebase one branch or move to timestamps.
• Uppercase suffix or wrong location. .SQL is not .sql, and a script outside the configured locations is invisible. Both produce a clean run that applied nothing.
• Descriptions with spaces. Use underscores in the file name; Flyway shows them as spaces. Spaces in file names work on most systems and break scripts on some.
• Relying on repeatable order. If R__views.sql needs R__functions.sql, name them so the alphabet agrees.

The wider list of habits to avoid, across every tool, is in database migration anti-patterns.

Where SchemaDrawer fits#

A quiet "applied nothing" run is easy to miss in a log and impossible to miss in a picture. SchemaDrawer runs your Flyway directory with Flyway itself against a throw-away database of the engine and version you choose and returns an ER diagram of the tables it produced; a misnamed script shows up as a table that is not there. The Flyway page lists what to upload, and the getting started guide shows the two HTTP calls. If you are still deciding between tools, Flyway vs Liquibase compares the two most common choices.

Key takeaways#

• V, R and U, a version for V and U, two underscores, a description, .sql.
• Versions compare numerically; pick sequential or timestamp and do not mix.
• Repeatable migrations rerun on change and must be idempotent.
• Validation protects applied scripts; repair is manual and deliberate.
• Enable validateMigrationNaming so a bad name is an error, not a silent skip.