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 [email protected]:/# 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
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:
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
