Over a million developers have joined DZone.

Database Migration in Continuous Integration Processes

How to use the open source tool Flyway to automatically migrate databases in your CI pipelines.

· DevOps Zone

The DevOps zone is brought to you in partnership with Sonatype Nexus. The Nexus suite helps scale your DevOps delivery with continuous component intelligence integrated into development tools, including Eclipse, IntelliJ, Jenkins, Bamboo, SonarQube and more. Schedule a demo today

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" />

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!

The DevOps zone is brought to you in partnership with Sonatype Nexus. Use the Nexus Suite to automate your software supply chain and ensure you're using the highest quality open source components at every step of the development lifecycle. Get Nexus today

database,database migration,flyway,database schema

Published at DZone with permission of Ivan Shulak. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}