Flyway migration files

1. Migrations as the “source code” of the database schema

We have already seen that a healthy PostgreSQL instance does not yet mean a ready schema: the application needs not just a live database, but a specific state of tables and data. Now it is important to understand where this story actually lives and how Flyway understands which steps have already been taken and which have not.

If migrations are the source of truth about the schema, then they should live like ordinary project artifacts: in a predictable place, with clear names, and with a history that can be checked. Next, we will look at three things: the db/migration path, V1__... naming, and the flyway_schema_history table, which does not let the past be quietly rewritten.

Migrations in Spring Boot and Docker/Compose

In Spring Boot, it is convenient to follow the default path here. By default, Flyway looks for migrations on the classpath, and for us that means they live in src/main/resources/db/migration and travel together with the application.

The minimal structure looks like this:

src/main/resources/
`-- db/
    `-- migration/
        |-- V1__init.sql
        `-- V2__seed_demo_data.sql

For Docker, this is especially useful: these files end up inside the jar, and therefore inside the Docker image that we build. One and the same image brings not only the Java code with it, but also the instructions for bringing the database to the expected state.

If the SQL files live “somewhere on your machine,” the schema stops being part of the project again, and local environments start to drift apart. Flyway works the other way around: it looks at the set of files in the application, compares it with the history of this particular database, and applies only the missing steps. For this to be reliable, the files must be named formally, not however you feel like it.

2. Versioned migration names

The Flyway migration naming convention looks strange at first, especially because of the double underscore. But there is a reason for that: the file name must carry both the version and the human-readable description, and Flyway must be able to parse it reliably. As a result, we get a scheme that is easy to read and hard to break by accident, although developers are determined creatures and will break everything anyway, but at least Flyway tried.

The canonical format of a versioned migration in Flyway, the one that runs exactly once, looks like this:

# VERSION — the formal migration version (Flyway sorts it as versions)
# DESCRIPTION — a human-readable description (you will see it in logs)
V<VERSION>__<DESCRIPTION>.sql

The double underscore __ is the separator between the version and the description. A single _ inside the description is just part of the name. In other words, V1__init.sql means “version 1, description init,” and V2__seed_demo_data.sql means “version 2, description seed demo data.”

Here is a small table to help this “settle in” visually:

In our training project, we deliberately separate “structure” and “data” because that makes the schema clearer for beginners. V1__init.sql is responsible for making the tables exist at all, while V2__seed_demo_data.sql ensures that after startup we are not staring into emptiness, but can immediately call GET /api/catalog/items and see at least a couple of rows.

An example of a small structure migration:

-- V1__init.sql
-- Create the catalog item table: structure (DDL), no data.
CREATE TABLE catalog_item (
    id BIGSERIAL PRIMARY KEY,           -- surrogate key, generated by the DB
    sku VARCHAR(64) NOT NULL UNIQUE,     -- business identifier, must be unique
    title VARCHAR(255) NOT NULL,         -- displayed product name
    price NUMERIC(10, 2) NOT NULL        -- price with fixed precision
);

And an example of a separate migration with demo data:

-- V2__seed_demo_data.sql
-- Add demo data so the API does not return an empty list right after startup.
INSERT INTO catalog_item (sku, title, price)
VALUES ('BOOK-001', 'Docker Basics', 29.90);

Why is it important that names be “talking”? Because when something goes wrong, and it will go wrong because this is SQL, the logs will show you the exact file name. If the file is called V7__asdf.sql, it looks like “our migration broke, ‘someone hit a cat on the keyboard.’” Even if the cat was very talented, it will not help with diagnostics.

4. Migration application order

A very common beginner fear is: “What if Flyway applies files in the wrong order?” That fear is healthy. In programming, it is generally useful to be suspicious of the order of execution in anything that looks “automatic.” The good news is that in Flyway’s case the order is quite engineering-friendly and predictable — it is based on the version in the file name.

If you have V1, V2, V3, then the logic is obvious: first V1, then V2, then V3. It seems trivial — until V10 shows up, and your brain remembers a school trauma: “string comparison thinks that 10 is less than 2.” Well: Flyway sorts versions as versions, not as strings. That means V2 really comes before V10. You do not need V02 for the order to be correct.

For a feel of “what this really looks like,” here is a typical log fragment (simplified, but accurate in meaning):

# Flyway reports the current schema version in this database (from the history table)
app-1  | Current version of schema "public": 1
# Then it explicitly states which version it is migrating to now (and how it is described)
app-1  | Migrating schema "public" to version "2 - seed demo data"
# And confirms the successful application of migrations
app-1  | Successfully applied 1 migration to schema "public"

Notice two useful elements. First, Flyway explicitly reports the “current schema version” (that is, the last applied versioned file). Second, it says which version it is migrating to and what it is called in plain language (this comes from the DESCRIPTION part of the file name).

Now the important practical point: migrations should be a monotonic narrative. In other words, the schema and data evolve from version to version, and you do not try to “rewrite history” retroactively. It is a bit like Git: you can rewrite history, but you should think about the consequences in advance, because someone already has a different reality (in our case, a different database).

If you need to change the schema, the normal path looks like this: you add a new V3__... file, and Flyway will apply it to any database that already has V1 and V2. That is the main power: the database on your machine and the database on a colleague’s machine arrive at the same state in the same way.

There is a trap worth emphasizing here. Sometimes a developer thinks: “I can just tweak V1__init.sql, because it is the ‘initial schema.’” Flyway, of course, will let you feel the consequences of that idea — and it will do so without malice, but with cold accounting precision. To understand why, we need to look at how it stores history.

5. Migration history: flyway_schema_history

When you run an application with Flyway, it does not keep history “in memory” and does not write it to some file next to the project. The history lives where the schema lives, that is, in the database itself. This makes sense: the database is exactly the object whose state we want to control. Flyway simply adds a special table to that database and writes facts into it: which migration was applied, when, and with what result.

The table is usually called flyway_schema_history. (The name can be configured, but in our course we do not complicate life and consider the default to be normal.) Inside this table, Flyway stores many fields, but for our understanding a few are important: version, description, script name, checksum, and success.

In simplified form, it can be represented like this:

# success = true means the migration was applied and recorded in history.
| installed_rank | version | description      | script                 | checksum | success |
|---------------|---------|------------------|------------------------|----------|---------|
| 1             | 1       | init             | V1__init.sql           | 123456   | true    |
| 2             | 2       | seed demo data   | V2__seed_demo_data.sql | 654321   | true    |

If you really want to “poke at it” at the SQL level, and yes, it is useful to see it with your own eyes at least once, the query looks roughly like this:

-- Look at which migrations Flyway considers already applied to this database.
SELECT version, description, script, success
FROM flyway_schema_history
ORDER BY installed_rank;

The most important thing here is the logic, not the specific fields. Flyway does the following: it reads the history table, understands which versions have already been applied, and compares that with which migration files are available in the application. After that, it applies only what is still missing from history.

This has surprisingly many practical consequences:

First, if the database lives for a long time, for example because you have not recreated its state, Flyway will see that the migrations have already been applied and will not run them again. This behavior seems obvious, but it is exactly what often breaks the expectations of a beginner who thinks: “I restarted the container, so everything should start from scratch.” No. Restarting the application is not the same as “resetting the database.”

Second, the history table is why editing an already applied file almost always ends in drama. That is because the history stores the checksum of the file contents. Flyway remembers not only the fact that “V1 was applied,” but also that “V1 was applied in this exact form.”

6. Editing applied migrations

A very human scenario: you wrote V1__init.sql, started the application, everything came up. Then you noticed you forgot one column, and the first thought is, “I’ll just quickly add it to V1.” If you manage to do that before the migration has been applied to the database, there is no problem: Flyway will apply the already corrected file. But if the migration has already been applied, Flyway turns into that same pedantic accountant: it sees that “V1” is in history, but the checksum of the file is now different. And it concludes: “Someone rewrote the past. I am not playing that game.”

A typical symptom looks something like this, greatly shortened:

# Validate is the verification stage: Flyway compares the database history with the files in the application.
app-1  | Validate failed: Migrations have failed validation
# A checksum mismatch means the migration content was changed after it had already been applied.
app-1  | Migration checksum mismatch for migration version 1
# Applied to database — the checksum stored in flyway_schema_history.
app-1  | -> Applied to database : 123456
# Resolved locally — the checksum of the file currently in the jar/image.
app-1  | -> Resolved locally    : 999999

This message contains the logic of the world. “Applied to database” is the checksum of the file that was actually applied to this database at some point. “Resolved locally” is the checksum of the file that currently lives in your jar/image. They do not match, so you now have two different “V1”s. And that is no longer just an “SQL error,” it is a reproducibility violation: if one developer applied the old V1 and another the new V1, they have different schemas even though the “version” is formally the same. Flyway does not allow that.

How do you live correctly from there, without getting into advanced scenarios and without turning the course into a separate migration track? In a training and ordinary application-oriented approach, the basic strategy is this: if the schema needs to change, you do not rewrite the old migration, you add a new one.

For example, if you forgot the status column, you add a file:

-- V3__add_status.sql
-- Move the schema forward: add a new column in a separate migration (do not rewrite V1).
ALTER TABLE catalog_item
ADD COLUMN status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE';

Yes, that means a few more files. But it is an honest change history: first we created the table, then we added data, then we expanded the structure. And Flyway will bring both a “clean” database, which applies V1→V2→V3, and an old database, which applies only V3 if it already has V1 and V2, to the current state in the same way.

There is a nice bonus here: when in the next lecture we learn how to distinguish a broken migration from a connection problem, this kind of “history as files” helps a lot. Because Flyway logs always name the version and the file. If you have version “3 - add status,” you immediately understand what broke, even if you are not a SQL olympiad champion.

7. Typical mistakes when working with Flyway migrations

Mistake #1: migrations live “somewhere nearby,” but not in db/migration.
Sometimes a developer puts SQL files in resources/sql/ or in the project root “because it looks nicer.” As a result, Flyway simply does not see them, and you start suspecting that “Flyway is broken” or “Docker broke the classpath.” In a training project, it is better to stick to the default: src/main/resources/db/migration. That means fewer mysteries and more cause-and-effect.

Mistake #2: trying to “just tweak V1” after the database has already lived and the migration has already been applied.
This mistake is especially tricky because it seems logical: “I just added one column.” But for Flyway this is rewriting history. It will compare checksums and stop the application startup. Here it is important to accept the rule: an applied migration is like a published release. If you want to change behavior, add a new migration with a new version instead of rewriting the old one.

Mistake #3: one huge “mega-file” that contains table creation, indexes, data, and ten more schema changes.
When everything lives in a single V1__init.sql, you make not only reading harder, but also diagnostics. Any error inside such a file turns into archaeology: you have to figure out at exactly which step the file failed. It is much calmer when one file expresses one clear change, and structure and seed data are split into at least two steps, as in our V1 and V2.

Mistake #4: obscure migration names.
V4__fix.sql or V5__update.sql is not a migration name; it is a cry for help to the future. A couple of days later, you no longer remember what exactly “fix” means. And when Flyway fails, it will honestly print that name in the logs. So it is better to name the file in a way that can be understood without telepathy: V3__add_status.sql, V6__create_export_job_table.sql, and so on.

Mistake #5: expecting migrations to “just run every startup”
Versioned migrations run once. If you try to use them as “a script that should always run,” you will get strange behavior: one developer will see the effect, another will not, because the migration had already been applied earlier on their machine. In this course, we keep a simple model: versioned migrations are steps in history, each step happens once and is recorded in flyway_schema_history.