Performing a FlyWay migration with Quarkus and Thorntail

This second tutorial about FlyWay will show you how to run a Migration using Quarkus and Thorntail using PostgreSQL Database.

Flyway is an open-source library that lets you to automate version-based database migrations. Flyway records of all applied migrations into your RDBMs 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 the first tutorial we have learned how to perform a migration using a standalone application or within a WildFly application: Getting started with FlyWay Database Migrations

Now let’s see how to carry on the same tasks with Quarkus and Thorntail. First off, we need an available database:

Starting PostgreSQL Database

We will be using PostgreSQL for our Migration examples. The simplest 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

Create a Migration with Quarkus

Now let’s create a Quarkus project that will perform the migration. From the shell execute:

mvn io.quarkus:quarkus-maven-plugin:0.21.2:create \
    -DprojectGroupId=org.acme \
    -DprojectArtifactId=flyway-demo \
    -Dextensions=jdbc-postgresql,flyway\
    -Dpath="/helloflyway"

Ok, you will end with a nice project which contains all the required dependencies:

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>io.quarkus</groupId>
        <artifactId>quarkus-bom</artifactId>
        <version>${quarkus.version}</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-resteasy</artifactId>
    </dependency>
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-junit5</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>io.rest-assured</groupId>
      <artifactId>rest-assured</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-flyway</artifactId>
    </dependency>
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-jdbc-postgresql</artifactId>
    </dependency>
  </dependencies>

Configuring the Quarkus Project

Next step will be adding the Datasource settings in the application.properties file which will be read by FlyWay to perform the migration:

quarkus.datasource.url=jdbc:postgresql://localhost:5432/postgres
quarkus.datasource.driver=org.postgresql.Driver
quarkus.datasource.username=postgres
quarkus.datasource.password=postgres
quarkus.flyway.migrate-at-start=true

Notice the last parameter quarkus.flyway.migrate-at-start=true which will perform the migration as the application is started.

Add Migration files

Migration files are searched by FlyWay, by default, in this path src/main/resources/db/migration so let’s create it from the root of our project:

$ mkdir -p src/main/resources/db/migration

Within the migration folder, we will add a subfolder for each new migration we want to add:

src
└── main
    ├── docker
    │   ├── Dockerfile.jvm
    │   └── Dockerfile.native
    └── resources
        ├── application.properties
        ├── db
        │   └── migration
        │       ├── release_1
        │       │   └── V1_1__create_account_table.sql
        │       └── release_2
        │           └── V1_2__insert_account_table.sql
        └── META-INF
            └── resources
                └── index.html

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) 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 trigger the migration it’s enough to execute the install phase, that will trigger our tests:

mvn clean install

You should see in your Console logs:

2019-09-05 17:50:39,735 INFO  [org.fly.cor.int.lic.VersionPrinter] (main) Flyway Community Edition 5.2.4 by Boxfuse
2019-09-05 17:50:39,848 INFO  [org.fly.cor.int.dat.DatabaseFactory] (main) Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 10.5)
2019-09-05 17:50:39,909 INFO  [org.fly.cor.int.com.DbValidate] (main) Successfully validated 2 migrations (execution time 00:00.013s)
2019-09-05 17:50:39,916 INFO  [org.fly.cor.int.sch.JdbcTableSchemaHistory] (main) Creating Schema History table: "public"."flyway_schema_history"
2019-09-05 17:50:39,961 INFO  [org.fly.cor.int.com.DbMigrate] (main) Current version of schema "public": << Empty Schema >>
2019-09-05 17:50:39,963 INFO  [org.fly.cor.int.com.DbMigrate] (main) Migrating schema "public" to version 1.1 - create account table
2019-09-05 17:50:40,019 INFO  [org.fly.cor.int.com.DbMigrate] (main) Migrating schema "public" to version 1.2 - insert account table
2019-09-05 17:50:40,045 INFO  [org.fly.cor.int.com.DbMigrate] (main) Successfully applied 2 migrations to schema "public" (execution time 00:00.129s)

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 spring boot flyway tutorial flyway springboot postgresql

Performing the migration programmatically

You can also choose to trigger the migration manually. For this purpose, the quarkus.flyway.migrate-at-start variable must be set to false:

quarkus.flyway.migrate-at-start=false

Then, in your code, inject the Flyway object which has method to migrate() manually the schemas:

    @Inject
    Flyway flyway; 

    public void checkMigration() {
        // Use the flyway instance manually
        flyway.clean(); 
        flyway.migrate();
        // This will print 1.0.0
        System.out.println(flyway.info().current().getVersion().toString());
    }

Performing a FlyWay Migration with Thorntail

Next migration example will be executed on Thorntail. First off, bootstrap a Thorntail project using the project generator which is available at: https://thorntail.io/generator/

Choose to include the Datasources and Flyway fraction to our project:

flyway tutorial spring boot flyway tutorial flyway springboot postgresql

Next, as we need to use PostgreSQL driver, we will add its dependency in our pom.xml file which will include the following set of dependencies:

  <dependencies>
   <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>${version.postgresql}</version>
   </dependency>
    <dependency>
      <groupId>io.thorntail</groupId>
      <artifactId>datasources</artifactId>
    </dependency>
    dependency>
      <groupId>io.thorntail</groupId>
      <artifactId>flyway</artifactId>
    </dependency>
  </dependencies>

Done with the pom.xml, we need to configure the PostgreSQL module so that it’s loaded when Thorntail is started. We will therefore add in the resources/project-defaults.yml the following:

swarm:
  datasources:
    jdbc-drivers:
      org.postgresql:
        driver-class-name: org.postgresql.Driver
        xa-datasource-class-name: org.postgresql.xa.PGXADataSource
        driver-module-name: org.postgresql
    data-sources:
      ExampleDS:
        driver-name: org.postgresql
        connection-url: jdbc:postgresql://localhost:5432/postgres
        user-name: postgres
        password: postgres

In order to load the PostgreSQL driver, we need to include in resources/modules/org/postgresql/main the module.xml file which contains the module configuration:

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="org.postgresql">
  <resources>
    <artifact name="org.postgresql:postgresql:42.2.2"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

That’s all. Place the migration files as usual into the folder resources/db/migration. This is the final project tree:

src
└── main
    └── resources
        ├── db
        │   └── migration
        │       ├── release_1
        │       │   └── V1_1__create_account_table.sql
        │       └── release_2
        │           └── V1_2__insert_account_table.sql
        ├── modules
        │   └── org
        │       └── postgresql
        │           └── main
        │               └── module.xml
        └── project-defaults.yml

You can run the project as follows:

mvn clean install thorntail:run

Check out that the migration completed successfully:

2019-09-05 18:25:31,058 INFO  [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 5) Migrating schema "public" to version 1.1 - create account table
2019-09-05 18:25:31,115 INFO  [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 5) Migrating schema "public" to version 1.2 - insert account table
2019-09-05 18:25:31,127 INFO  [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 5) Successfully applied 2 migrations to schema "public" (execution time 00:00.141s)

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