Flyway: DB Migration In Continuous Integration Process

When talking about source code migration, everything is simple. Our team uses Git, Maven, and Jenkins to deliver the code on the production server. But what to do with database migration? How to track it and how to make sure that everything works properly after a new build? Flyway is a perfect tool that greatly helps us and simplifies our data migration process and as a result the whole development process.

What is Flyway?

So, what exactly is Flyway and why is it so cool? It helps you evolve your database schema easily and is reliable in all instances. The main idea of this tool is that it scans an appropriate folder with QSL scripts, executes them on a project startup or by command, calculates the checksums of executed files and finally stores this info in a special table. In this way, you don’t need to execute them manually. The common file structures from your project should look like those in the picture below:

Where name convention of file is: V{version_number}_{subversion number}_{description}.sql

After that you can easily integrate Flyway into your Java project as a Spring bean and run on a project startup:

<beans:bean id="flyway" class="org.flywaydb.core.Flyway" init-method="migrate">
<beans:property name="dataSource" ref="dataSource"/>
<beans:property name="locations" value="classpath:db/migration"/>
<beans:property name="initOnMigrate" value="true" />
<beans:property name="outOfOrder" value="true" />
</beans:bean>

And the result of execution will we be look like:

That’s all! Every time the need to upgrade the database arises, whether it is the structure (DDL) or reference data (DML), simply create a new migration with a version number higher than the current one. ext time Flyway starts, it will find it and upgrade the database accordingly.

Tips and tricks

But the real-life is a little bit more painful than theory is! In the next section, I will show you some common issues our team faced when using Flyway and their solutions – the best practices for today.

The first issue was the version number during parallel teamwork when two developers got source code from the repository, created a new file with the same next version name and committed them back. Obviously, after code synchronization Flyway threw an exception because there were two different files with the same version.

Solution: use subversion name. Moreover, this subversion should be unique. We recommend using a task number. In this way, your SQL file name should look like V5_2343_users.sql

The second issue followed after the first one when SQL with name V5_2343_users.sql has been committed and executed before SQL V5_2344_users.sql Flyway threw an exception of version control. This also could happen because the developer works in a parallel way and don’t know about the current status of other team members.

Solution: use the outOfOrder=true flag of the flyway.

The third issue was the migration checksum mismatch for developers with different environment platforms. As appeared, the calculation of the checksum is platform dependent. Developer on Mac/Linux creates a SQL migration file and checks it into Git. It runs on production (which is on Linux) just fine. Then a Windows developer takes a copy of the production database and loads it into their local machine. But when they pulled the SQL migration file from Git, it automatically converted the line endings to CRLF (Windows style). Now Flyway validates and migrates fail to say there is a checksum mismatch.

Solution: use Git with core.autocrlf=input and run flyway with init-mathod=”repair”

This approach gives an opportunity to recalculate checksums according to the current platform. After that, you can use a flyway in the usual way.