Schema Migration with Flyway – JavaEE Series 2 Part 3
If you’re building any sort of JavaEE application, you’re likely using a database of some sort. As time goes on, your database schema will evolve to meet your application’s needs. There are many ways you can manage schema changes, one of them is with Flyway.
Flyway can be used as a command-line tool that does your schema migrations for you, or you can interface with its API in your application code to do it. Personally, I think it’s much easier to use the command-line version rather than roll it into your code. The main reason for this is if you’re working with a multi-node application, you will need to add code to ensure Flyway (or any other schema migration library) does not run multiple times. In the worst case, this could result in bad things happening, or it could also result in a messy server log with failed schema migrations.
In this article, I’ll walk you through a very simple example of setting up Flyway for our JavaEE project.
The first thing we need to do is revisit our pom.xml file and add this to our list of plugins:
<plugin> <groupId>org.flywaydb</groupId> <artifactId>flyway-maven-plugin</artifactId> <version>5.2.4</version> <configuration> <baselineOnMigrate>true</baselineOnMigrate> </configuration> </plugin>
As of this writing, 5.2.4 is the latest version of Flyway. The “baselineOnMigrate” tells Flyway to go ahead and initialize itself and add it’s schema history table to our database when we run migrations. This table is where Flyway will keep track of which migrations have run, by who, and if they failed or not.
Next, we’ll need to add the following properties to our property section of the pom.xml file:
<flyway.url>jdbc:mysql://10.0.0.128:3306/todo?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC</flyway.url> <flyway.user>todoadmin</flyway.user> <flyway.password>todopassword</flyway.password>
These were pulled directly from the “ms-ds.xml” file we created in the previous article.
Now, let’s create our SQL migration script in the src/main/resources/db/migration folder. Once you’ve created this folder, create a “V2__CreateCategoriesTable.sql” file and paste the following into it:
CREATE TABLE Categories ( id INT NOT NULL AUTO_INCREMENT, label VARCHAR(64) NOT NULL, PRIMARY KEY(id) );
This will simply create a new table called, Categories, that has two fields: id and label. The format of the file name does matter, and it needs to start with a version greater than 1. Version 1 is reserved for the initial baseline migration. The format of the filename is:
Where ### is any number that is larger than the previous migration. “AAAAA” is a descriptive name for the migration. Also note the double underscore between the version and the name.
With all of that out of the way, we can type the following command to perform our schema migration:
You should see the following output:
[INFO] --- flyway-maven-plugin:5.2.4:migrate (default-cli) @ todo --- [INFO] Flyway Community Edition 5.2.4 by Boxfuse [INFO] Database: jdbc:mysql://10.0.0.128:3306/todo (MySQL 8.0) [INFO] Successfully validated 1 migration (execution time 00:00.017s) [INFO] Creating Schema History table: `todo`.`flyway_schema_history` [INFO] Successfully baselined schema with version: 1 [INFO] Current version of schema `todo`: 1 [INFO] Migrating schema `todo` to version 2 - CreateCategoriesTable [INFO] Successfully applied 1 migration to schema `todo` (execution time 00:01.159s)
Note, that in the community edition of Flyway, you will be unable to undo the migration. So you will want to make a backup of your database prior to migrating your schema.
If you run into any problems or have any comments, please leave them in the comment section below.
Source code to the entire series is available here: