DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

Trending

  • Revolutionizing Algorithmic Trading: The Power of Reinforcement Learning
  • Scaling Site Reliability Engineering (SRE) Teams the Right Way
  • Automating the Migration From JS to TS for the ZK Framework
  • Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers

Trending

  • Revolutionizing Algorithmic Trading: The Power of Reinforcement Learning
  • Scaling Site Reliability Engineering (SRE) Teams the Right Way
  • Automating the Migration From JS to TS for the ZK Framework
  • Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers
  1. DZone
  2. Testing, Deployment, and Maintenance
  3. Deployment
  4. Database Migration in Continuous Integration Processes

Database Migration in Continuous Integration Processes

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

Ivan Shulak user avatar by
Ivan Shulak
·
Aug. 26, 16 · Tutorial
Like (9)
Save
Tweet
Share
11.99K Views

Join the DZone community and get the full member experience.

Join For Free

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!

Database Continuous Integration/Deployment Flyway (software) Integration

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

Opinions expressed by DZone contributors are their own.

Trending

  • Revolutionizing Algorithmic Trading: The Power of Reinforcement Learning
  • Scaling Site Reliability Engineering (SRE) Teams the Right Way
  • Automating the Migration From JS to TS for the ZK Framework
  • Effortlessly Streamlining Test-Driven Development and CI Testing for Kafka Developers

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: