Flyway migrations: versioned naming and FK-safe ordering
Flyway's contract is simple: versioned files, applied exactly once, in version order, checksummed forever. Most Flyway pain is someone fighting one of those three properties.
The naming rules
V1__create_customers.sql
V2__create_orders.sql
V2.1__orders_status_index.sql
R__refresh_reporting_views.sqlV, a version, two underscores, a description,.sql. The single-underscore typo (V1_create...) is the most common beginner error; Flyway ignores the file and you get to find out in staging.- Versions compare numerically part by part (
V2 < V2.1 < V10), soV10does come afterV9. Where padding earns its keep is date-based schemes:V20260716103000__...sorts correctly everywhere, including in your file browser. R__(repeatable) migrations run whenever their checksum changes, after all versioned ones. Views, functions, grants. Never table DDL.
Pick one version scheme per repo and write it down. Sequential integers are
fine for a small team; timestamp versions avoid collisions when several
branches mint migrations in parallel (two people both creating V17
on the same day is a merge conflict Flyway can't see, it just applies whichever
lands first and fails, or worse, doesn't fail, on the other).
Order is part of the schema: FKs
Within one file, statement order matters the same way it does in
psql: a foreign key can only reference a table that already
exists.
-- V1__create_customers_and_orders.sql
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(180) UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers (id),
total NUMERIC(12, 2) NOT NULL
);Parents first, children after; for a bigger graph that means a topological
sort by FK dependencies (Schemint orders the generated migration that way
automatically). The alternative, creating tables in any order and bolting FKs
on with ALTER TABLE at the end, also works and is sometimes
necessary for circular references; it's just noisier to read and review.
Across files the same logic holds: the migration that adds
orders must carry a version after the one that added
customers. Flyway won't reorder for you; the version sequence
is the dependency declaration.
History is append-only
Flyway records a checksum per applied migration and validates it on every
run. Editing a migration that already ran anywhere, including a teammate's
laptop, produces FlywayValidateException: Migration checksum
mismatch. The rule that follows:
- Wrong migration still unmerged: edit freely.
- Wrong migration already applied somewhere: write a new
V<next>__fix_...sqlthat corrects it. The mistake stays in history; the history stays trustworthy. flyway repairexists for the checksum table, not as an eraser. Reach for it when you've consciously rewritten history in a dev database, not as a habit.
Corollary: migrations shouldn't be clever. No environment conditionals, no data reads that depend on the day they run. A migration is a letter to every future environment, including the CI database created from scratch tomorrow, and it must say the same thing to all of them.
A checklist that survives code review
- One logical change per file, named for what it does:
V8__orders_add_paid_at.sql, notV8__changes.sql. - Parents before children, indexes with (or right after) the table they serve.
- Destructive changes (drop, narrow, NOT NULL on existing data) get the expand/contract treatment, a diff tells you which ones those are.
- Never edit an applied migration. Fix forward.