Flyway is the great tool for managing database migrations. Years ago Martin Fowler described Evolutionary Database Design and the idea still rocks.

However, Flyway has a slight caveat when you work on the same project in a big team of developers. In this article, I’m describing to you the potential problem about out of ordered migrations, what’s the way to fix it, and how you can validate the migration name pattern during the project build.

You can find the whole project setup in this repository.

Out of Order Merging Problem

Suppose we have 1 migration V1__create_user.sql. Look at the DDL below.

CREATE TABLE users
(
    id       BIGSERIAL PRIMARY KEY,
    username VARCHAR(200) NOT NULL,
    age      INTEGER      NOT NULL
)

Also, there are two developers working in different Git branches, Bob and Kate. Bob's task requires him to add user_group table. He creates the V2__create_user_group.sql migration. Whilst Kate wants to create the role table and defines the new migration name as V3__create_role.sql. Those tasks neither overlap nor block each other. Developers can complete them successfully without waiting for other to create a pull request. Anyway, there could be a problem.

Assuming that each merge to the main branch results in deploying the new version to the UAT stand. Kate has completed the task earlier than Bob. So, the new migrations order is:

1. V1__create_user.sql
2. V3__create_role.sql

What happens when Bob merges his pull request? The new migration should come between existing ones. Unfortunately, it'll result in an error during migration execution.

Validate failed: Migrations have failed validation
Detected resolved migration not applied to database: 2.

By default, Flyway doesn't allow to add new migrations in between of present ones. So, we have two problems here to solve:

1. Flyway should execute new migrations successfully even if they go in between of the present ones. We don’t want to make one developer to wait until other completes their job.
2. Migrations require a unique id (in this case, 1, 2, etc.). So, each developer should be able to assign a unique migration id. Besides, there should be no additional collaborations because it slows down the development and demands a single source of truth containing all existing and acquired migrations’ ids. Therefore, the id has to be decentralized.

Migrations Naming Strategy

As you may guess, a regular incrementing number as an id is not sufficient. Instead, I recommend you to apply this pattern:

V{CURRENT_DATE}.{TASK_ID}__{DESCRIPTION}.sql

Migration naming pattern is composite and contains of several blocks:

1. {CURRENT_DATE} is the date when the developer added the migration with the pattern YYYY.MM.DD.
2. {TASK_ID} is the unique task id that demands to create new migration. It could be Jira task, Trello, YouTrack and so on.
3. {DESCRIPTION} is the description that clarifies the migration purpose.

For example, this is how creating the new users table can look like with the new migration naming rules:

V2023.01.12.4343__create_users_table.sql

Meaning that one added the migration on 12th January 2023 and the corresponding task has id 4343.

So, now each migration has a unique id because every developer works on a separate task in the particular branch.

Enabling Out of Order Execution

Anyway, the new naming pattern does not solve the problem of out-of-order pull requests’ merging. Thankfully, all you have to do is to enable flyway.outOfOrder parameter.

Let's replay the case with Bob and Kate described earlier. Kate has merged the pull request and the order of migrations is:

1. V2023.01.12.4343__create_users_table.sql
2. V2023.01.14.4344__create_role.sql

When Bob merges his pull request, the order changes to:

1. V2023.01.12.4343__create_users_table.sql
2. V2023.01.13.4345__create_users_group_table.sql
3. V2023.01.14.4344__create_role.sql

If you enable the flyway.outOfOrder parameter, the execution will complete with no errors and Flyway will create all the tables successfully.

As a matter of fact, the developers now can merge the pull requests in any order without bothering about migration order issues. However, it works only if everyone follows the stated rules about naming. You could check it during the code review, but it’s not efficient. There is a possibility of missing a typo. We need an approach that’ll check the correctness of the naming pattern automatically.

Validation Automatization

I'm showing you the way of automatization with Gradle but the idea remains the same for any other build tool.

Look at the Gradle task performing migration names validation.

def migrationExclusions = [/* migration names exclusions */]

task validateFlywayMigrations {
    def migrationPattern = "^V\\d{4}\\.\\d{2}\\.\\d{2}\\.\\d+__[a-z_]+\\.sql\$"
    def datePattern = Pattern.compile("\\d{4}\\.\\d{2}\\.\\d{2}")

    doLast {
        for (def file in fileTree('src/main/resources/db/migration')) {
            final String migrationName = file.getName()
            if (!file.isFile() || migrationExclusions.contains(migrationName)) {
                continue
            }
            if (!migrationName.matches(migrationPattern)) {
                throw new GradleException("Migration '$migrationName' does not match pattern '$migrationPattern'")
            }
            def matcher = datePattern.matcher(migrationName)
            if (matcher.find()) {
                def date = matcher.group()
                try {
                    LocalDate.parse(date, DateTimeFormatter.ofPattern("yyyy.MM.dd"))
                } catch (DateTimeParseException e) {
                    throw new GradleException(
                            "Migration '$migrationName' has invalid date value. Couldn't be parsed with pattern 'yyyy.MM.dd'",
                            e
                    )
                }
            } else {
                throw new GradleException("Migration '$migrationName' has no date by pattern '$datePattern'")
            }
        }
    }
}

compileJava.dependsOn validateFlywayMigrations

The idea is trivial. We read all files from src/main/resources/db/migration directory and check that the name satisfies the RegExp pattern of ^V\d{4}\.\d{2}\.\d{2}\.\d+__[a-z_]+\.sql\$. If it does, when we validate that the \d{4}\.\d{2}\.\d{2} pattern contains the proper date but not just random string like 3033.45.98. If any check does not pass, the exception occurs and we get non-zero result code that leads to shell command fail.

Due to dependsOn clause, the validateFlywayMigrations runs automatically right before the code compilation.

The migrationExclusions field is helpful when you applying new naming rules to the existing project. You cannot change names of existing migrations. So, you ignore them during the validation process.

Conclusion

As a result, developers don’t need to worry neither about uniqueness of Flyway migration names nor about the order of pull requests merging. It’ll make the programming much more pleasant and less stressed. Tell your story in the comments. Have you had similar problems in your project? If you have, then how have you solved the issues?

That’s all I wanted to tell about Flyway migration naming. Thanks for reading!

Resources

1. Flyway
2. Evolutionary Database Design
3. The repository with code examples
4. UAT stand
5. Single Source of Truth
6. Decentralized Identifier
7. The flyway.outOfOrder parameter
8. Gradle
9. RegExp