Flyway Out of Order Migrations

1. Overview

In this tutorial, we’ll learn the basics of database migrations with Flyway and see a specific use case when we need to run migrations out of order.

2. Flyway Introduction

Flyway’s a tool that helps with database versioning via migrations. We can create scripts that alter the state of the database. They’re called migrations.

There’re several cases when we need migrations. For example, we might need to populate our database from a previous data source. Or we’ve got a released application that already uses a database, and we need to deploy a new version that relies on a modified database schema. In both cases, we could use migrations to achieve the desired result.

With Flyway, we could even upload these scripts to a version control system so that we’re able to track when and why we need to introduce a specific modification.

In this example, we’ll use versioned migrations. In other words, we’ll assign a version for each migration script that determines their order.

3. Example Migration

In our example, we’ll use a simple Spring Boot application with Flyway as a starting point.

3.1. Maven Plugin

Firstly, let’s add the Flyway Maven plugin to our pom.xml:

<build>
    ...
    <plugin>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-maven-plugin</artifactId>
        <version>10.17.0</version>
    </plugin>
    ...
</build>

We need this plugin to be able to run different Flyway goals. However, we need to configure the plugin before we can use it. This can include setting the database URL, username and password, etc.

3.2. Migration Scripts

Let’s create two SQL migrations in our project inside the db/out-of-order-migration directory. We must follow a naming convention for these files. Let’s name our first script V1_0__create_city_table.sql:

create table city (
  id numeric,
  name varchar(50),
  constraint pk_city primary key (id)
);

Then, create the other one named V2_0__create_person_table.sql:

create table person (
  id numeric,
  name varchar(50),
  constraint pk_person primary key (id)
);

Let’s execute these migrations:

mvn -Dflyway.user=sa -Dflyway.url=jdbc:h2:file:./database -Dflyway.locations=filesystem:db/out-of-order-migration flyway:migrate

In this command, we used the migrate goal of the flyway plugin with three parameters related to the database. Firstly, we set the username, then the URL where the database is located, and lastly, the location of the migration scripts.

As a result of this command, Flyway runs both of our scripts successfully. We can even check the status:

mvn -Dflyway.user=sa -Dflyway.url=jdbc:h2:file:./database -Dflyway.locations=filesystem:db/out-of-order-migration flyway:info

This prints the following message:

Schema version: 2.0
+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1.0     | create city table   | SQL  | 2023-01-02 21:08:45 | Success |
| Versioned | 2.0     | create person table | SQL  | 2023-01-02 21:08:45 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

4. Out-Of-Order Migrations

When we add new migrations, Flyway can detect the changes and execute the latest ones. However, it becomes problematic when the version number of the latest script isn’t the highest. In other words, it’s out of order.

By default, Flyway ignores our newest migration. Luckily, we’re able to resolve this issue. We can use the outOfOrder configuration parameter to tell Flyway to run these scripts instead of skipping them.

Let’s try it in our project by adding a new migration with the name V1_1__add_zipcode_to_city.sql:

alter table city add column (
  zip varchar(10)
);

The version of this script is 1.1, but according to Flyway, we’ve already migrated to version 2.0. This means that the script would be ignored. We can even check it with the info command:

mvn -Dflyway.user=sa -Dflyway.url=jdbc:h2:file:./database -Dflyway.locations=filesystem:db/out-of-order-migration flyway:info

Flyway recognizes the script, but the state’s ignored:

+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1.0     | create city table   | SQL  | 2023-01-02 21:08:45 | Success |
| Versioned | 2.0     | create person table | SQL  | 2023-01-02 21:08:45 | Success |
| Versioned | 1.1     | add zipcode to city | SQL  |                     | Ignored |
+-----------+---------+---------------------+------+---------------------+---------+

Now, if we get the status again but add the outOfOrder flag, the result’s different:

mvn -Dflyway.user=sa -Dflyway.url=jdbc:h2:file:./database -Dflyway.locations=filesystem:db/out-of-order-migration -Dflyway.outOfOrder=true flyway:info

The status of the latest migration changed to pending:

+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1.0     | create city table   | SQL  | 2023-01-02 21:08:45 | Success |
| Versioned | 2.0     | create person table | SQL  | 2023-01-02 21:08:45 | Success |
| Versioned | 1.1     | add zipcode to city | SQL  |                     | Pending |
+-----------+---------+---------------------+------+---------------------+---------+

This means that we can run the migrate command and apply the changes. Though, we must add the outOfOrder flag here as well:

mvn -Dflyway.user=sa -Dflyway.url=jdbc:h2:file:./database -Dflyway.locations=filesystem:db/out-of-order-migration -Dflyway.outOfOrder=true flyway:migrate

We successfully executed the new changes:

[INFO] Successfully validated 3 migrations (execution time 00:00.015s)
[INFO] Current version of schema "PUBLIC": 2.0
[WARNING] outOfOrder mode is active. Migration of schema "PUBLIC" may not be reproducible.
[INFO] Migrating schema "PUBLIC" to version "1.1 - add zipcode to city" [out of order]
[INFO] Successfully applied 1 migration to schema "PUBLIC", now at version v1.1 (execution time 00:00.019s)

These kinds of migrations have a different state after being applied by Flyway. Our first two migrations are in a “Success” state, but the third one’s “Out of Order” even though it was successful:

+-----------+---------+---------------------+------+---------------------+--------------+
| Category  | Version | Description         | Type | Installed On        | State        |
+-----------+---------+---------------------+------+---------------------+--------------+
| Versioned | 1.0     | create city table   | SQL  | 2023-01-02 21:08:45 | Success      |
| Versioned | 2.0     | create person table | SQL  | 2023-01-02 21:08:45 | Success      |
| Versioned | 1.1     | add zipcode to city | SQL  | 2023-01-02 21:17:38 | Out of Order |
+-----------+---------+---------------------+------+---------------------+--------------+

5. Conclusion

In this tutorial, we saw a brief introduction to Flyway migrations and focused on a specific use case afterward. We found a way to run migrations that are considered out of order based on their version number. Then, we applied this solution to our project.