Flyway - Database migrations made easy

This post is a ‘step-by-step’ tutorial how Flyway could be integrated into an existing application. For this purpose I’ve created a sample application which uses EclipseLink as a JPA-Provider and the Spring-Framework for Dependency injection (DI). Furthermore, the sample application uses Maven for dependency management and for building the application. This tutorial examines how a database migration can be executed at build time with Maven (e.g. as a part of a CI-build) and also at run time.

Step 1: Integrate Flyway dependencies

With Maven it’s very easy to manage third-party libraries. If a database migration with Java classes is required, then the ‘flyway-core’ dependency with the latest version must be included to the projects ‘pom’-file. Following snippet shows how I’ve added this dependency into the applications ‘pom’-file.

XHTML

1

2

3

4

5

<dependency>

<groupId>org.flywaydb</groupId>

<artifactId>flyway-core</artifactId>

<version>3.0</version>

</dependency>

To execute a migration at building process it’s also important to add the ‘flyway-maven-plugin’ to the ‘pom’-file. To establish a connection to the database, it’s required to configure the plugin. In section ‘configuration’ we define a host, a user and a password, which are the credentials to get access to the database. The property ‘location’ is required if the path to the repository with the migration files doesn’t match the default path ‘db/migrations’. The following snippet shows how I’ve added this Maven-Plugin into the ‘pom’-file and defined the credentials to get access to a DB2 database instance.

XHTML

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

<plugin>

<groupId>org.flywaydb</groupId>

<artifactId>flyway-maven-plugin</artifactId>

<version>3.0</version>

<dependencies>

<dependency>

<groupId>com.ibm</groupId>

<artifactId>db2jcc</artifactId>

<version>4.0</version>

</dependency>

</dependencies>

<configuration>

<user>db2admin</user>

<password>db2admin</password>

<url>jdbc:db2://localhost:50000/FLYWEXAM</url>

<locations>

<location>filesystem:src/main/resources/migrations</location>

</locations>

<skip>false</skip>

</configuration>

</plugin>

Step 2: Base configuration

Flyway updates the database from one version to the next version using migrations. Each migration must have a unique version identifier and a description. The migrations are then sorted based on their version number and applied in order if it’s necessary. The information’s about the applied migrations are stored in a ‘SCHEMA_VERSION’ table by default. This table is used to track the state of the database. As each migration gets applied, the metadata table is updated accordingly.

At this point of the tutorial, the ‘SCHEMA_VERSION’ table doesn’t exist yet and it should be created first. If you start Flyway with an empty database then don’t worry. Flyway will try to locate the metadata table. If the database is empty, Flyway won’t find it and will create it instead. If you have an existing database that has not been filled by Flyway, this is the way you go.

First, create and save a snapshot or a migration script of your existing database that will recreate your current state of your database. Then, open up a shell and navigate to the sample project and execute following command:

MS DOS

1

mvnflyway:clean

This command deletes all tables and data in the database. This step guarantees that further migrations are running correctly. The following picture shows the results of the cleaning process.

After the cleaning process, we can generate the ‘SCHEMA_VERSION’ table. Use the following command to create the table:

As you can see in the screenshot below, the ‘SCHEMA_VERSION’ table has been created. For this purpose, we initialized the table with version 1.

After you created the ‘SCHEMA_VERSION’ table, it’s now time to import the snapshot which was created before. For this step, the snapshot or migration script must be moved to the folder, where Flyway can find it. In this example the manually created directory ‘migrations’ is acting as a repository for Flyway. After you move the file to the repository, it’s important to customize the name of the migration file. Flyway only locates files which are valid with the Flyway-typical naming conventions. This requires a prefix (by default: V), a version number which must consists of one or more numeric parts and are separated with a dot (.) or an underscore (_), two underscores (__) as a delimiter and a description. The complete filename must look like:

1

[prefix][version]__[description].sql

The following screenshot shows, how it’s customized for the sample application.

For the data recovery, you execute the following command:

MS DOS

1

mvnflyway:migrate

The next screenshot shows that the database has been restored. The version of the database has been changed from 1.0 to 1.1.

In addition, a new entry to the ‘SCHEMA_VERSION’ table has been inserted. This is the evidence that the snapshot has been migrated successfully.

Thus, the production data has been restored. Furthermore our first point has been established and works like a charm. All further migrations are executed with the same steps. Every time when you have to update the database in the future, simply create a new migration with a version number higher than the current one. If Flyway starts it will find it and upgrade the database accordingly. However, it’s important to make sure that the version number is correct.

Step 3: Configuration for Java migrations

As I described before, the sample application uses the Spring Framework for DI. The required dependencies are defined in the projects ‘pom.xml’ file. To check the state of the database while bootstrapping, it’s important to squeeze in the Flyway dependencies to the normal startup process. This requires a reconfiguration of the applications ‘ApplicationContext’. A Java migration is necessary, if BLOBs or CLOBs should be inserted. It’s also useful to change a massive amount of data or for migrations which requires the usage of regular expression.

The following listing shows the typical configuration of the ‘dataSource’ for the ‘EntityManagerFactoryBean’ and the ‘PlatformTransactionManager’ with Spring.

In the context of this bean we instantiate the class ‘Flyway’ and set the ‘data Source’ with a setter. With ‘setLocations’ we define the package path which contains the migration java files. While migrating, Flyway checks if all migrated files are available. For the implicit migration process, it’s important to define ‘initMethod=migrate’ as bean property. This setting guarantees that Flyway checks the version of the database and migrate it to a new state while bootstrapping the application.

To ensure that the ‘EntityManagerFactoryBean’ starting up after the migration, we are using the Annotation ‘@DependsOn(“flyway”)’. The next listing shows the enhanced configuration class.

Step 4: Create a Java type migration file

Java migrations must follow the same naming conventions as migration scripts based on SQL. This means that a prefix, a version, a separator and a description must be specified as class name for Flyway. It should also be mentioned that a class and also a SQL file always migrated within a transaction. Furthermore, each Java migration class must implement the interface ‘JdbcMigration’. The following listing shows a correct implemented migration class.

The application uses the Spring Framework. So it makes sense to use ‘SpringJdbcMigration’ instead of ‘JdbcMigration’. The execution processes of both implementation classes are identical. The main difference between these is that ‘SpringJdbcMigration’ enables to use the ‘SpringJDBC’-Template class to define SQL statements. The following class implements the interface ‘SpringJdbcMigration’.

Step 5: Execution

As described in step 3, a migration is executed if it’s necessary while bootstrapping the application. This applies to Java classes and also to SQL scripts. Thus, the second key point, as described above, has been implemented.

The question is if Maven also executes Java migration classes. The answer is ‘Yes’. For this purpose we need to adjust the ‘flyway-maven-plugin’. A second ‘location’ with the name of the package which I’ve created before must be added. The next listing shows how it’s defined.

XHTML

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

<plugin>

<groupId>org.flywaydb</groupId>

<artifactId>flyway-maven-plugin</artifactId>

<version>3.0</version>

<dependencies>

<dependency>

<groupId>com.ibm</groupId>

<artifactId>db2jcc</artifactId>

<version>4.0</version>

</dependency>

</dependencies>

<configuration>

<user>db2admin</user>

<password>db2admin</password>

<url>jdbc:db2://localhost:50000/FLYWEXAM</url>

<locations>

<location>filesystem:src/main/resources/migrations</location>

<location>classpath:info.novatec.eap.persistence.migration</location>

</locations>

<skip>false</skip>

</configuration>

</plugin>

This setting enables Flyway to access Java migration classes and considers them for migration. The next screenshots shows the ‘SCHEMA_VERSION’ table again. The ‘script’ column shows that Java and also SQL migrations have been performed on the database.

For further information’s and tutorials, I recommend the documentation of this project, which can be found here. I’ve also attached the flyway-sample application.