Flyway Migrations Naming Strategy in a Big Project
Tags: database, flyway, programming, productivity
Published: 2023-01-08 15:13:07

[Flyway](https://flywaydb.org/) is the great tool for managing database migrations. Years ago Martin Fowler described [Evolutionary Database Design](https://martinfowler.com/articles/evodb.html) 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](https://github.com/SimonHarmonicMinor/flyway-migration-naming-pattern).

## Out of Order Merging Problem

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

```sql
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](https://www.techtarget.com/searchsoftwarequality/definition/user-acceptance-testing-UAT). 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.
1. 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](https://en.wikipedia.org/wiki/Single_source_of_truth) containing all existing and acquired migrations’ ids. Therefore, [the id has to be decentralized](https://en.wikipedia.org/wiki/Decentralized_identifier).

## 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](https://flywaydb.org/documentation/configuration/parameters/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](https://gradle.org/) but the idea remains the same for any other build tool.

Look at the Gradle task performing migration names validation.

```groovy
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](https://en.wikipedia.org/wiki/Regular_expression) 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](https://flywaydb.org/)
1. [Evolutionary Database Design](https://martinfowler.com/articles/evodb.html)
1. [The repository with code examples](https://github.com/SimonHarmonicMinor/flyway-migration-naming-pattern)
1. [UAT stand](https://www.techtarget.com/searchsoftwarequality/definition/user-acceptance-testing-UAT)
1. [Single Source of Truth](https://en.wikipedia.org/wiki/Single_source_of_truth) 
1. [Decentralized Identifier](https://en.wikipedia.org/wiki/Decentralized_identifier)
1. [The flyway.outOfOrder parameter](https://flywaydb.org/documentation/configuration/parameters/outOfOrder)
1. [Gradle](https://gradle.org/)
1. [RegExp](https://en.wikipedia.org/wiki/Regular_expression)