Getting started with FlyWay Database Migrations

This tutorial will show you how to perform database migrations using FlyWay in two different use cases: using Flyway Maven’s plugin and performing the Migration within an Enterprise Application running on WildFly.

Flyway is an open-source library that allows you to automate version-based database migrations. Flyway keeps track of all applied migrations into your relational database so that it can detect and execute the required migration steps to update your database to the latest version. You can trigger Flyway in several ways: either use its command line client or integrate it into your Java application. In this post, I will show you the steps required to perform a database migration using FlyWay Maven Plugin and then we will show you how to execute the migration programmatically from within an Enterprise application.

In order to perform a Database migration with Flyway here is our checklist:

  • An available Database which is supported by Flyway (See https://flywaydb.org/documentation/)
  • A JDBC Driver to access the Database
  • A set of SQL scripts containing the Database schema and its migrations.
  • Access to the flyway-core dependency, either directly from Java code or using Flyway shell script / Maven plugin

Starting the Database

We will be using PostgreSQL for our Migration examples. The quickest way to get started, is to launch it as Docker container:

$ docker run --rm=true --name flyway_test -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -p 5432:5432 postgres:10.5

Check that the PostgreSQL process is active:

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
3c22fb3e2adb        postgres:10.5       "docker-entrypoint..."   21 minutes ago      Up 21 minutes       0.0.0.0:5432->5432/tcp   flyway_test

Performing a Flyway migration using its Maven plugin

Now create the simplest Maven project and modify its pom.xml to include the following content:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.mastertheboss</groupId>
    <artifactId>flyway-demo</artifactId>
    <version>1.0</version>
    <properties>
        <flyway.version>6.0.0</flyway.version>
        <postgres.driver.version>9.2-1002.jdbc4</postgres.driver.version>
        <database.url>jdbc:postgresql://localhost:5432/postgres</database.url>
        <database.user>postgres</database.user>
        <database.password>postgres</database.password>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>${flyway.version}</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>${flyway.version}</version>
                <configuration>
                    <sqlMigrationSeparator>__</sqlMigrationSeparator>
                    <locations>
                        <location>filesystem:src/main/resources/flyway/migrations</location>
                    </locations>
                    <url>${database.url}</url>
                    <user>${database.user}</user>
                    <password>${database.password}</password>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>${postgres.driver.version}</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

We have included flyway-maven-plugin which requires in its properties the URL,username and password of the target database. We also have to specify the location where migrations files are located. In our case, they are placed in src/main/resources/flyway/migrations

Please note that SQL migration files must follow Flyway‘s convention See here for more details: https://flywaydb.org/documentation/migrations#naming

Here is the tree view of our basic project:

src
└── main
    ├── resources
    │   └── db
    │       └── migration
    │           ├── release_1
    │           │   └── V1_1__create_account_table.sql
    │           └── release_2
    │               └── V1_2__insert_account_table.sql

The first SQL script (V1_1__create_account_table.sql) contains the DDL of a table:

CREATE TABLE account(
   user_id VARCHAR PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   country VARCHAR (355) UNIQUE NOT NULL
);

The second script (V1_2__insert_account_table.sql), executes a couple of inserts in the Table:

INSERT into account ( user_id, username, password, country) VALUES ('a1234567','frank','smith','usa');
INSERT into account ( user_id, username, password, country) VALUES ('bc123454','mark','twain','usa');

To execute the example just run:

mvn clean compile flyway:migrate

You should see in your Console logs:

[INFO] Successfully validated 2 migrations (execution time 00:00.024s)
[INFO] Creating Schema History table "public"."flyway_schema_history" ...
[INFO] Current version of schema "public": << Empty Schema >>
[INFO] Migrating schema "public" to version 1.1 - create account table
[INFO] Migrating schema "public" to version 1.2 - insert account table

Now, if we check into the Docker process and we log into the Database, we can see that the migration was completed successfully:

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
3c22fb3e2adb        postgres:10.5       "docker-entrypoint..."   31 minutes ago      Up 31 minutes       0.0.0.0:5432->5432/tcp   flyway_test

$ docker exec -it 3c22fb3e2adb /bin/bash
root@3c22fb3e2adb:/# psql -U postgres
psql (10.5 (Debian 10.5-2.pgdg90+1))
Type "help" for help.

postgres=# \dt
                 List of relations
Schema |         Name          | Type  |  Owner

public | account               | table | postgres
public | flyway_schema_history | table | postgres
(2 rows)

postgres=# select * from account;
user_id  | username | password |      country
a1234567 | frank    | smith    | usa
bc123454 | mark     | twain    | usa
(2 rows)

So What happened?

When the migration process starts, Flyway tries to locate its schema history table. As the database is empty, Flyway won’t find it and will create it instead.

You now have a database with a single empty table called flyway_schema_history.

Important! This table will be used to track the state of the database.

Immediately afterwards Flyway will scan the filesystem or the classpath to find migration files which can be written in either Sql or Java.

The migrations are then sorted based on their version number and applied in order, as you can see from the following picture

flyway tutorial wildfly flyway tutorial wildfly

Running FlyWay with WildFly

We will now show how you can trigger Flyway from within an Enterprise application. We can use a simple Web application which contains an EJB startup class. Here is the application tree:

src
└── main
    ├── java
    │   └── com
    │       └── mastertheboss
    │           └── flyway
    │               └── MigrationEJB.java
    ├── resources
    │   └── db
    │       └── migration
    │           ├── release_1
    │           │   └── V1_1__create_account_table.sql
    │           └── release_2
    │               └── V1_2__insert_account_table.sql
    └── webapp
        └── index.jsp


So the key points: we have included the migration scripts under the path resources/db/migration as Flyway will search files in the application classpath under the default folder “db/migration”.

Here is our start up class:

import org.flywaydb.core.Flyway;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.ejb.Singleton;
import javax.ejb.Startup;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.sql.DataSource;

@Singleton
@Startup
@TransactionManagement(TransactionManagementType.BEAN)
public class MigrationEJB {

    @Resource(lookup = "java:/PostGreDS")
    private DataSource dataSource;

    @PostConstruct
    public void initFlyWay() {
        Flyway flyway = Flyway.configure().dataSource(dataSource).load();

        // Needed if you want to delete existing flyway_schema_history 
        flyway.clean();
        // Needed if the database is not empty
        flyway.baseline();

        flyway.migrate();
    }
}

So, within our class, we are configuring Flyway from WildFly datasource, which needs to be configured in WildFly. (See this tutorial to learn how to configure PostgreSQL with WildFly: Configuring a datasource with PostgreSQL and JBoss/WildFly

Next, the command migrate() executes the actual migration. Please notice that if you have already used this schema to run the standalone example, then you will need to clean the existing flyway_schema_history, otherwise a version mismatch will be raised:

 mismatch for migration version 1.2
[ERROR] -> Applied to database : 1330302330
[ERROR] -> Resolved locally    : 1333459263

Also, if the schema is not empty, the baseline() command is needed to re-create the flyway_schema_history. In order to compile your project, you will need the following dependency:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>6.0.0</version>
</dependency>

That’s all. You can compile and deploy the application on WildFly with:

$ mvn clean install wildfly:deploy

From the console’s logs, you will be able to check that the migration was performed successfully:

[org.flywaydb.core.internal.license.VersionPrinter] (ServerService Thread Pool -- 82) Flyway Community Edition 6.0.0 by Boxfuse
[org.flywaydb.core.internal.database.DatabaseFactory] (ServerService Thread Pool -- 82) Database: jdbc:postgresql://localhost/postgres (PostgreSQL 10.5)
[org.flywaydb.core.internal.command.DbClean] (ServerService Thread Pool -- 82) Successfully cleaned schema "public" (execution time 00:00.016s)
[org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory] (ServerService Thread Pool -- 82) Creating Schema History table "public"."flyway_schema_history" with baseline ...
[org.flywaydb.core.internal.command.DbBaseline] (ServerService Thread Pool -- 82) Successfully baselined schema with version: 1
[org.flywaydb.core.internal.command.DbValidate] (ServerService Thread Pool -- 82) Successfully validated 3 migrations (execution time 00:00.006s)
[org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 82) Current version of schema "public": 1
[org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 82) Migrating schema "public" to version 1.1 - create account table
[org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 82) Migrating schema "public" to version 1.2 - insert account table

You can find the source code for both examples here: https://github.com/fmarchioni/mastertheboss/tree/master/flyway

Fancy Spring Boot ?

Here is a tutorial that will show you how to perform FlyWay migrations with Spring Boot.

Quarkus / Thorntail developers ?

Here is the tutorial for you: Performing a FlyWay migration with Quarkus and Thorntail