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 [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
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
