Flyway : Automatic database creation and updations for Web Applications (INTEGRATING FLYWAY IN A SPRING FRAMEWORK APPLICATION)

This post is for about how to integrate Flyway into a Spring/JPA application for database schema migration. To skip all the preambles and get straight to the instructions, jump to Project’s Dependencies Set-up

Flyway is a database migration tool which helps do to databases, what tools like git/svn/mercurial does for source code…which is versioning. With Flyway you can easily version your database: create, migrate and ascertain its state, structure, together with its contents. It basically allows you to take control of your database, and be able to recreate it across different environment or different versions of the application it runs with, while keeping track of the chronological changes made.

I recently worked on a project where such database schema migration was needed. And we found Flyway a good tool for the job. Prior to using Flyway, all of the schema updates that had to be done were delivered to the client in separate SQL files, with instruction on how they should be run.

The short coming of such a process soon became apparent. It was burdensome and error prone.

First of all, it calls for extra operation activities on the part of the client as they have to run whatever update scripts that are provided and ensure the database schema is in the required state before application deployment – Not an ideal process. An application should be as self-contained as possible, so when delivered, it should be deployable without much hassle on the client’s part.

Also it unnecessarily exposes the internals of the database state. Having the database modified outside of the application does not guarantee its integrity, especially when the database modification is being done by a party who is not privy to the internals of the application the database is running with.

So when the time came to upgrade the version of the application in production to a newer version, we decided to re-evaluate the process through which we provide schema upgrades. This is when Flyway entered the picture.

As mentioned, Flyway is a database migration tool. It can be used via its command line tool, via a maven plug-in or programmatic-ally from within an application, but Since the application I was working on was built with Spring framework, and uses a JPA backed database (provided by Hibernate), I then set about on how to have Flyway integrated into such an application. This post captures the typical step that would be involved.

But before getting into the steps, a little overview of how Flyway works would help have a clear understanding of the integration instructions.

How Flyway works. An Overview

Schema changes and content modification (adding, modifying or deleting contents in database tables) are done via SQL statements. What Flyway does is to provide a mechanism in which these changes can be recorded and versioned. This it does by 1) Being responsible for applying the schema changes specified in the SQL scripts and 2) By keeping an internal meta-data table named SCHEMA_VERSION through which it keeps track of various information regarding the applied scripts: when it was applied, by whom it was applied, description of the migration applied, its version number etc.

Flyway allows for the SQL scripts (which are frequently referred to as migrations) to be provided in the form of plain old SQL script files or via Java code. The important thing though, is that, whichever form the migration is written, the files need to be named following a convention, which is explainedbelow.

For more information on how Flyway works, check the How Does Flyway Work in the project website.

Now with the brief overview out of the way, we can go ahead and look at the instructions.

Integrating Flyway into a JPA, Spring backed application.

When integrating Flyway into a JPA, Spring application, what you want to do is to have Flyway kick in, find the migration files (either in SQL or JAVA) and apply the necessary modifications to the database at application start-up. The following steps show how to achieve this.

1. Project’s Dependencies Set-up

The first thing to do is to add the needed project dependencies, so apart from the Spring/JPA dependencies, include Flyway as a dependency. If you are using Maven, then the following lines should be added to the dependencies section of your POM.xml:

1	<dependency>

2	<groupId>org.flywaydb</groupId>

3	<artifactId>flyway-core</artifactId>

4	<version>${Flyway.version}</version>

5	</dependency>

In case you are not using maven for your dependency management, the process of including Flyway should be trivial in whatever tool you are using.

2. Configuring Flyway to integrate into Spring’s Container

The next steps would be the configuration needed to have Spring pick up Flyway as a managed bean and have it play nicely with other beans it would need to work it…to be specific the entity manager factory.

If you are using XML to configure spring the necessary configuration would look thus:

1

2	<bean id="flyway" class="org.Flyway.core.Flyway" init-method="migrate">

3	<property name="baselineOnMigrate" value="true" />

4	<property name="locations" value="filesystem:/path/to/migrations/" />

5	<property name="dataSource" ref="dataSource" />

6

</bean>

7

8

9	<bean id="entityManagerFactory"class="o.s.orm.jpa.LocalContainerEntityManagerFactoryBean" depends-on="flyway">

10	<property name="dataSource" ref="dataSource" />

11	<property name="jpaVendorAdapter">

12	<bean class="o.s.orm.jpa.vendor.HibernateJpaVendorAdapter">

13	<property name="database" value="${jpa.database}"/>

14

</bean>

15	</property>

16

</bean>

17

18

19	<bean id="dataSource" class="o.a.commons.dbcp.BasicDataSource" lazy-init="true" destroy-method="close">

20	<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>

21	<property name="url" value="jdbc:hsqldb:mem:qade"/>

22	<property name="username" value="sa"/>

23	<property name="password" value=""/>

24

</bean>

If you are using JavaConfig over XML then the configuration would look thus:

1	@Configuration

2

3	public class AppConfig {

4

5	@Bean(initMethod = "migrate")

6	Flyway flyway() {

7	Flyway flyway = new Flyway();

8	flyway.setBaselineOnMigrate(true);

9	flyway.setLocations("filesystem:/path/to/migrations/");

10	flyway.setDataSource(dataSource());

11	return flyway;

12

}

13

14	@Bean @DependsOn("flyway")

15	EntityManagerFactory entityManagerFactory() {

16	LocalContainerEntityManagerFactoryBean bean = newLocalContainerEntityManagerFactoryBean();

17	bean.setDataSource(dataSource());

18	// other configurations

19	return bean.getObject();

20

}

21

22

@Bean

23	DataSource dataSource() {

24	DataSource dataSource = new BasicDataSource();

25	// data source configuration

26	return dataSource;

27

}

28

}

Some additional things to take note of in the configurations:

Flyway Configuration.
When configuring Flyway you need to specify the init method (which was done using the init-methodproperty in xml and initMethod property of the @Bean annotation in JavaConfig)

This is used to instruct Spring to call the migrate() method once all the properties of the Flyway bean has been initialized. The migrate() method is what is responsible for performing the migration logic: that is finding the migration scripts, applying them and keeping a tab of successful migrations etc. Which is why it is specified as the init method as you would want this to be executed as soon as the Flyway bean is initialized in Spring’s container.

The baseLineOnMigrate is also another interesting part of the configuration. It comes in handy when Flyway is initially used for the first time and no SCHEMA_VERSION table exists. It instructs Flyway that before the migration scripts are applied, it should create a migration entry within the SCHEMA_VERSION table which would serve as the baseline, and thus the available migration script would only be applied if their version is higher than the baseline version.

The dataSource is used to specify the dataSource. Nothing interesting about that.

The last item of interest in the configuration is the location through which you specify where Flyway should find the migration scripts. Flyway has the ability to scan either the filesystem or classpath for these migration scripts. In the above example the value given to the location is prefixed with “filesystem:” indicating to Flyway to use its file system scanner to locate the migration scripts. If no prefix is speicified or “classpath:” is used as the prefix, then Flyway uses its classloader scanner to locate the migration scripts.

Entity Manager Factory Configuration.

The one thing to take note in the Entity Manager Factory’s configuration is the depends on property that is set to reference the Flyway bean. What this does is to ensure that the Flyway bean is always created before the Entity Manager Factory bean. Which is what you would want as you need Flyway to already have been created, do its thing before the Entity Manager Factory kicks in. In the JavaConfig this is specified using the @DependsOn annotation.

3. Writing Migration Scripts

Now that you have the necessary beans configured, the next thing to do is to create the migration scripts which contains the necessary schema updates that needs to be applied to the database during a migration, As specified earlier, Flyway supports migration scripts written in plain old SQL or in Java.

Whichever method you use, all you have to do is to have the migration file named appropriately, following the convention required by Flyway and have them in the location specified in the configuration.

Why a naming convention? The naming convention is required, as this is the default way Flyway keeps track of its versioning (I say default because when using Java, there is the possibility to override this naming mechanism. This is explained below). It uses it to determine the order in which migration scripts needs to be applied and to keep track of the scripts applied and ones pending.

The naming convention is as follows:

V__.

Where  is a numeric value that can contain a point (“.”) or an underscore (“_”) for example 3, 3.1, 3_1 are all valid for the version. (Not that if an underscore is used, it would be turned into a dot “.” at runtime by Flyway)

The double underscore, __ is what is used to separate the version number from the description.

 is a short description of what the scripts contained in the file is about. It would be extracted and added to the description column of the schema_version table.

For the ,  you can have either sql or java depending on the method being used to supply the migrations.

So the following are all valid names

V3__description_of_migration.sql
V3.1__description_of_migration .java
V3_1__description_of_migration .sql

If the migrations are specified in SQL there is little else that can be done apart from specifying the schema updates and naming them appropriately. On the other hand, with Java we have a little more flexibility. I would quickly take a look at how to use Java to write the migration and some of its available features not present when using SQL.

3. Writing Flyway Migration in Java.

As with migration written in SQL you need to have the Java migration in the location specified in the configuration. You then also need to have the class in which the sql migration are written implement the  JdbcMigration or SpringJdbcMigration interface.

The difference between these two interfaces is that jdbcMigration makes a Connection object available for interacting with the database while SpringJdbcMigration makes a JdbcTemplate object available for database interaction.

So a migration script written in Java may look like this:

1	import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;

2	import org.springframework.jdbc.core.JdbcTemplate;

3

4	public class V2__moveDBStateToV2 implements SpringJdbcMigration {

5

6

@Override

7	public void migrate(JdbcTemplate jdbcTemplate) throws Exception {

8	// programmatically change the database state using JdbCTemplate

9

}

10

11

}

And that is it. This way you can perform complex checks and operations needed to move the database state into a required state.

Apart from that, the Java option also provides some additional functionality like allowing you to disregard the naming convention and instead have the version and description specified in the Java class. This is done by implementing the MigrationInfoProvider Interface. For example:

1	public class WhateverName implements SpringJdbcMigration, MigrationInfoProvider {

2

3

@Override

4	public void migrate(JdbcTemplate jdbcTemplate) throws Exception {

5	// programmatically change the database state using JdbCTemplate

6

}

7

8

@Override

9	public MigrationVersion getVersion() {

10	return MigrationVersion.fromVersion("2"); //return 2 as version

11

12

}

13

14

@Override

15	public String getDescription() {

16	return "Moves DB to State Two";

17

}

18

19

}

Some closing thoughts.

When I first approached Flyway as the tool to be used for database migration, one of the first thoughts that came to mind was, why use an tool when I already have hibernate hbm2ddl which can also be used to modify the database schema. I already have hibernate being the JPA provider, why an external tool?

The answer is simple. Hibernates hbm2ddl is in no way as flexible or as powerful compared to a database migration tool like Flyway. At its core, hbm2ddl could be seen as just a tool for extending your database to keep it in line with new additions to the backing entity. It does not have the ability to modify field names, transfer data from one column to another etc. Tasks which always pop up in any real life database migration situation.

So if you need to do proper database migration and versioning, a tool like Flyway is quite handy.

A thing to also note is that Flyway can be used as a standalone migration tool. This is possible via Flyway’s robust command line tool, whose instruction can be found here. Flyway also provides callbacks which can be used as extension points to hook into Flyway’s lifecycle to perform advance operations. The instruction on the callback can be found her

e.

All in all, Flyway really shines in that it gets the job done and it gets it done well. It is relatively simple with little learning curve and flexible. The documentation and JavaDoc is also decent, which makes working with it painless.

Reference: https://flywaydb.org/documentation/callbacks.html,

http://blog.trifork.com/2014/12/09/integrating-flywaydb-in-a-spring-framework-application/