Flyway

Flyway: DB Migration In Continuous Integration Process

Written by Ivan on January 25th, 2016

Today we will talk about effective continuous integration process which includes Database migration using open source tool Flyway.

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 new build? Flyway is a perfect tool that greatly helps us and simplifies our data migration process and as the 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:

File structures

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:

Result of execution

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. 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 practises for today.

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

Solution: use subversion name. Moreover, this subversion should be unique. We recommend to use 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 developer work in parallel way and don’t know about current status of another team members.

Solution: use outOfOrder=true flag of flyway.

The third issue was the migration checksum mismatch for developers with different environment platforms. As appeared, calculation of 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 validate and migrate 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 site of Flyway.

Still have issues and don’t know how to migrate database or have any additional questions about database migration service? Feel free to contact us!

What practices or tools do you use to perform data/schema migrations and DB changes?

Ivan

Ivan

Hi, my name is Ivan, Senior Architect & R&D team expert at Romexsoft, keen on high-load Java application development and databases.

Error

Loading...

Comments

  • Hi ,

    Thank you for very nice article. I have started implementation of flyway db in my project and looking for an option to get the logs of executions to track the complete activity.

    Any links / reference would help

    Akeeb K Y