Flyway: An introduction and best practices
What is Flyway?
Flyway is an OpenSource Project (https://github.com/flyway/flyway) developed in Java, that automates Database Schema Migrations for Java projects. Typically the Schema Migration Scripts are placed in the same repository as the code. Like this, a project achieves direct versioning of the scripts alongside the versioning of the application.
Why using Flyway?
Still, a lot of projects are changing their database schema manually during a release. This requires good documentation of all required steps to be done on the production environment. Quick fixes that were applied directly on an environment and that were forgotten to be documented could cause Application startup failure on a different environment.
Flyway is there to prevent such situations. The general purpose of putting the migration scripts directly to the code repository is to enforce no manual changes on the database done by a single person. Like this, a project can be sure to have applied exactly the same set of scripts on all environments.
It not only enables less failure and error rates of releases but also makes our lives easier with regards to the reproducibility of our database in case of migrating the underlying database or having a new rollout into another environment.
How does it work?
To preserve the state of the database schema, flyway creates and stores metadata about all applied schema migration in an own table (see picture below).
The table shows in which order, which script has been applied and when. Additionally, flyway stores the checksum of each migration script to preserve immutability of a script, once it has been applied to a database.
To apply these database migrations flyway offers several ways of doing so (https://flywaydb.org/getstarted/):
- Using your build tool (Gradle or Maven)
- Using the Java API (e.g. with Spring Boot)
- Using the command line tool
When applying your scripts, flyway first checks the database for the above-mentioned schema history table. If there have already been scripts applied, flyway checks them by comparing the stored checksum to the checksum created on the fly of all existing scripts located in the migration directory. If there is a single checksum mismatch, the migration will be aborted. Otherwise, flyway checks which scripts are new and it applies them sorted by their version number.
How to apply flyway to existing projects?
Up until now, we have only talked about how we can use flyway on new projects. But flyway also enables already existing projects to migrate their databases using their baseline feature (https://flywaydb.org/documentation/command/baseline). Using this command, flyway sets a baseline with the existing tables and will later ignore all scripts up until the baseline version. For new databases, it will apply all scripts as usual. Full documentation of what needs to be done when wanting to apply flyway on existing projects can be found here: https://flywaydb.org/documentation/existing
Best Practices
Now that we have seen how automated database schema migrations, I would like to introduce Do's and Dont's when using flyway.
The first thing that should be kept in mind when writing migration scripts is to use idempotent operations, to ensure they are not failing if an operation has already been executed for whatever reason. It also helps to try to use database independent SQL syntax (if possible). This will help to migrate from one to another database.
Further things to keep in mind are:
- Do not create users with migrations
- Always roll forward
- Do not change existing scripts
Demo Application
If you now want to get your hands on a small Demo Application that uses flyway you can check out this Github repository: https://github.com/TwoDigits/flywaydemo
The application is based on Spring Boot and will migrate the database during startup. The Readme file explains how a MySql database can be started using Docker-Compose and how the application needs to be started.
