Database Migration in Continuous Integration Processes

When talking about source code migration, everything is simple. My team uses Git, Maven, and Jenkins to deliver code to the production server. But what do we do with database migration? How do we track it make sure that everything works properly after a new build? Flyway is a perfect tool that greatly 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? Simply put, 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. 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 the name convention of a 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 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>

The result of execution will look like this:

That's all! Everytime 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. Each time Flyway starts, it will find that migration 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 first issue was the version number during parallel teamwork, when two developers got source code from a repository, created a new file with the same 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 names. Moreover, this subversion should be unique. We recommend to use task numbers. 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 in version control. This could happen because developers worked in parallel, and didn't know about the current status of other team members.

Solution: use the outOfOrder=true flag in Flyway.

The third issue was the migration checksum mismatch for developers with different environment platforms. As it appears, calculation of checksum is platform dependent. A developer on Mac/Linux can create a SQL migration file and check 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 to 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 Flyway in the usual way.

And that's it! More information you can find on the official. Still have issues and don’t know how to migrate databases, or have any additional questions about database migration services? Feel free to contact me!