Flyway Database Schema Migrations

Introduction

In this article, I’m going to explain how Flyway works and how you can use it to run automated database schema migrations using incremental SQL migration scripts.

Flyway is an open-source project created by Axel Fontaine and later acquired by Red Gate . The database migrations can be defined either as SQL scripts or JDBC-based classes.

Database schema migration scripts

Just like the application source code, the database schema changes with time. To migrate the database schema from one version to another, you need to run DDL scripts.

For instance, we could store the DDL migration scripts in the src/main/folder , like this:

> tree src/test/resources

├── flyway
│   └── scripts
│       ├── postgresql
│           ├── migration
│           │   ├── V1_0__post_tag.sql
│           │   ├── V1_1__post_details.sql
│           │   └── V1_2__post_comment.sql

The migration folder contains three incremental migration scripts that follow the Flyway script file naming conventions. The double underline (e.g., __ ) separates the script version from the script name.

The V1_0__post_tag.sql file is the initial migration script and contains the following DDL statements:

CREATE SEQUENCE hibernate_sequence
START 1 INCREMENT 1;

CREATE TABLE post (
    id int8 NOT NULL,
    title varchar(255),
    PRIMARY KEY (id)
);

CREATE TABLE tag (
    id int8 NOT NULL,
    name varchar(255),
    PRIMARY KEY (id)
);

CREATE TABLE post_tag (
    post_id int8 NOT NULL,
    tag_id int8 NOT NULL,
    PRIMARY KEY (post_id, tag_id)
);

ALTER TABLE post_tag
ADD CONSTRAINT POST_TAG_TAG_ID_FK
FOREIGN KEY (tag_id) REFERENCES tag;

ALTER TABLE post_tag
ADD CONSTRAINT POST_TAG_POST_ID_FK
FOREIGN KEY (post_id) REFERENCES post;

The V1_1__post_details.sql file is the second migration script, and it creates the post_details table:

CREATE TABLE post_details (
    id int8 NOT NULL,
    created_by varchar(255),
    created_on TIMESTAMP,
    PRIMARY KEY (id)
);

ALTER TABLE post_details
ADD CONSTRAINT POST_DETAILS_POST_ID_FK
FOREIGN KEY (id) REFERENCES post;

The V1_2__post_comment.sql file is the third migration script, and it’s responsible for creating the post_comment table:

CREATE TABLE post_comment (
    id int8 NOT NULL,
    review varchar(255),
    post_id int8, PRIMARY KEY (id)
);

ALTER TABLE post_comment
ADD CONSTRAINT POST_COMMENT_POST_ID_FK
FOREIGN KEY (post_id) REFERENCES post;

Flyway configuration

Flyway is very easy to configure. All you need to do is instantiate the org.flywaydb.core.Flyway class and set the JDBC DataSource and the location of the database migration scripts.

If you’re using Spring Framework, then you can use the following Java-based configuration:

@Bean(initMethod = "migrate")
public Flyway flyway() {
    return Flyway.configure()
        .dataSource(dataSource())
        .baselineOnMigrate(true)
        .locations(
            String.format(
                "classpath:/flyway/scripts/%1$s/migration",
                databaseType.name().toLowerCase()
            )
    ).load();
}

Running the Flyway database schema migrations

When bootstrapping the Spring application context on an empty database schema, we can see in the logs that Flyway applies all the existing incremental migration scripts:

INFO  : Flyway Community Edition 6.4.4 by Redgate

DEBUG : Scanning for classpath resources at 
        'classpath:flyway/scripts/postgresql/migration' ...
DEBUG : Found resource: 
        flyway/scripts/postgresql/migration/V1_0__post_tag.sql
DEBUG : Found resource: 
        flyway/scripts/postgresql/migration/V1_1__post_details.sql
DEBUG : Found resource: 
        flyway/scripts/postgresql/migration/V1_2__post_comment.sql

INFO  : Current version of schema "public": << Empty Schema >>

DEBUG : Parsing V1_0__post_tag.sql ...
DEBUG : Starting migration of schema "public" 
        to version 1.0 - post tag ...
DEBUG : Successfully completed migration of schema "public" 
        to version 1.0 - post tag
DEBUG : Schema History table "public"."flyway_schema_history" 
        successfully updated to reflect changes

DEBUG : Parsing V1_1__post_details.sql ...
DEBUG : Starting migration of schema "public" 
        to version 1.1 - post details ...
DEBUG : Successfully completed migration of schema "public" 
        to version 1.1 - post details
DEBUG : Schema History table "public"."flyway_schema_history" 
        successfully updated to reflect changes

DEBUG : Parsing V1_2__post_comment.sql ...
DEBUG : Starting migration of schema "public" 
        to version 1.2 - post comment ...
DEBUG : Successfully completed migration of schema "public" 
        to version 1.2 - post comment
DEBUG : Schema History table "public"."flyway_schema_history" 
        successfully updated to reflect changes

INFO  : Successfully applied 3 migrations to schema "public" 
        (execution time 00:00.146s)

As we can see in the log, the database migration scripts have been executed successfully by Flyway, and, if we inspect the database schema, we can see that it looks as follows:

We can identify the post , tag , post_tag , post_details , and post_comment tables that were created by running the three migration scripts.

The only table that was not included in the migration scripts is the flyway_schema_history , which is created by Flyway upon running for the very first time. The goal of the flyway_schema_history table is to store the database schema migration history, and, in our case, it looks as follows:

| installed_rank | version | description  | type | script                 | checksum   | installed_by | installed_on   | execution_time | success |
|----------------|---------|--------------|------|------------------------|------------|--------------|----------------|----------------|---------|
| 1              | 1       | post tag     | SQL  | V1_0__post_tag.sql     | -611721954 | postgres     | 30-06-20 15:21 | 61             | TRUE    |
| 2              | 1.1     | post details | SQL  | V1_1__post_details.sql | 511495203  | postgres     | 30-06-20 15:21 | 13             | TRUE    |
| 3              | 1.2     | post comment | SQL  | V1_2__post_comment.sql | 762350400  | postgres     | 30-06-20 15:21 | 14             | TRUE    |

The flyway_schema_history table is used by Flyway to know what’s the latest version that was applied successfully, so upon a new execution, only the newer migration scripts will be run.

Running a new Flyway database schema migration script

Now, let’s assume we are implementing a new application feature that requires adding a new database table, called users . For this, we need to create a new migration script with a version that’s greater than any of the previously executed migration scripts.

So, we need to create the new migration script, called V1_3__users.sql , in the same src/main/resources/flyway/scripts/postgresql/migration folder, where the other migration scripts are already stored.

The V1_3__users.sql script contains the following DDL statements:

CREATE TABLE post_comment (
    id int8 NOT NULL,
    review varchar(255),
    post_id int8, PRIMARY KEY (id)
);

ALTER TABLE post_comment
ADD CONSTRAINT POST_COMMENT_POST_ID_FK
FOREIGN KEY (post_id) REFERENCES post;

When restarting the Spring application, Flyway is going to discover the new V1_3__users.sql migration script and run it, as illustrated by the logs:

INFO  : Current version of schema "public": 1.2

DEBUG : Parsing V1_3__users.sql ...
DEBUG : Starting migration of schema "public" 
        to version 1.3 - users ...
DEBUG : Successfully completed migration of schema "public" 
        to version 1.3 - users
DEBUG : Schema History table "public"."flyway_schema_history" 
        successfully updated to reflect changes
        
INFO  : Successfully applied 1 migration to schema "public" 
        (execution time 00:00.064s)

If we inspect the database schema, we can see that it contains the newly created users tables:

And, if we inspect the flyway_schema_history table, we can see that the V1_3__users.sql script has been applied successfully:

| installed_rank | version | description  | type | script                 | checksum   | installed_by | installed_on   | execution_time | success |
|----------------|---------|--------------|------|------------------------|------------|--------------|----------------|----------------|---------|
| 1              | 1       | post tag     | SQL  | V1_0__post_tag.sql     | -611721954 | postgres     | 30-06-20 15:21 | 61             | TRUE    |
| 2              | 1.1     | post details | SQL  | V1_1__post_details.sql | 511495203  | postgres     | 30-06-20 15:21 | 13             | TRUE    |
| 3              | 1.2     | post comment | SQL  | V1_2__post_comment.sql | 762350400  | postgres     | 30-06-20 15:21 | 14             | TRUE    |
| 4              | 1.3     | users        | SQL  | V1_3__users.sql        | -596399497 | postgres     | 30-06-20 15:55 | 32             | TRUE    |

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Incremental migrations scripts are the best way to capture the changes undergone by a given database schema, and just like you store the application source code in VCS (Version Control System) (e.g., git), the schema migration scripts should also reside in VCS. This way, if you wonder when a given schema changed has occurred, you can find the info by scanning the commit log.

What’s great about automatic schema migration tools, like Flyway, is that the migrations can be validated in the QA (Quality Assurance) environments, so, when deploying to production, we know that the migration scripts are going to be executed successfully. Without an automatic database schema migration tool, it would be impossible to deploy the QA or production servers automatically.

All in all, you should never run migration scripts manually. Manual actions are prone to human errors, so it’s better to have a tool that runs the migration scripts automatically when upgrading a given system.