This post is attempts cover some of best practices in using Flyway, the database migration tool, that I have learned or established after using it in few projects. It’s aimed at users already familiar with Flyway or any other database migration tools, so I’ll try to skip over the very basics.
I’m focusing on Flyway here, but I think the best practices will apply to majority of other database migration tools.
Each individual database migration is wrapped by Flyway inside a transaction. This means that:
You shouldn’t use transactions explicitly in your plain SQL migrations
If you are running multiple transactions at once (for example updating from version 1 to 4), this means that some it will stop running after first failure - some migrations will work, some won’t.
Dealing With Rollbacks
Flyway doesn’t support rollbacks explicitly, instead you should always create new migration that should revert recently introduced changes. This is the same patter used by
git revert $COMMIT.
Rollbacks During Development
During development those rules should be more relaxed. When you are working on creating correct database models or just migrations, you will often have a need to reset database at the specific state and re-run the migration. I have always found this a little annoying and here’s the process I’ve been following:
(Please note that this is a very bad idea to do this on the production database, but during development that’s in fact a good idea)
After I realize that I need to modify the last migration I have executed
Manually revert database changes
Delete last entry from
flywayInfoto confirm that Flyway doesn’t see my last migration
flywayMigrateto ’re-run' migration
I have found this method to work well in practice, especially when each individual migration is small and the iteration cycle is small. The only requirement is that the database migration I’m editing hasn’t been applied anywhere else outside of my local environment.
Use Baseline At The Start
Baseline command is used to introduce a database migration tool to the existing project, here’s how you should use it:
Create a database dump as SQL file
Add this file to Flyway by running
flywayBaselineintroducing this file as
What many people fail to do is to manually inspect the database dump to eliminate unnecessary or unwanted data. You should cleanup the database dump so it will contain only “database schemas” and “reference information”.
In cases when you are adding a large piece of reference data - tables with thousands or hundreds of thousands of rows, it’s good idea performance-wise to follow following schematics:
-- LOAD DATA CREATE INDEX A ON TABLE X ...
Creating indexes after loading in the data speeds up the process, because the database doesn’t need to keep the index up to date as you add rows, it will only perform a single index creation operation, the bigger your table is, the bigger the impact.
Missing Migrations Warning
It’s possible to programmatically inspect
schema_version (You can also use Flyway API but this requires adding dependency to your project) table and issue warning in the following cases:
You migrations that were not executed
You have migrations that resulted in errors
You have more migrations that were performed that application knows about
Missing Migrations Error
This idea builds on the previous one, instead of issuing warning you can selectively issue either warnings or errors or even exit your application when it detects serious problems with the state of the database.
Setup Database Users
Flyway will display information about which user performed the migration, each user/operator should have it’s own account in the database so this information will be persisted. Avoid using generic users or same user that your application uses for accessing database.
Don’t Create Users With Migrations
User and permission management shouldn’t be handled by database migrations. Here’s the reason behind it:
If you run your application in new or different environment you might need to create different users or setup different permissions (for example IP addresses) - I have found it easier not to include users as part of migration procedure.
Editing Previously Executed Migration
Let’s say you have found a bug in the migration that’s already been executed on the production database. You have 2 options here:
Create new migration that will fix the bug
This is the ideal scenario, but sometimes you would like to take the shortcut
Advanced Users can take the shortcut
There is a limited set of cases when the shortcut can be taken, I don’t have the list, so use your own judgement :)
Here are the rough steps:
Edit migration file in place
Manually perform the
diffmigration between what’s in the database and your new migration file (*)
flywayInfo, you should get an exception complaining about checksum mismatch
Edit row in
schema_versiontable updating checksum from existing value to the new one
(*) In order for this method to work it should be possible to create that diff and it should be relatively simple to perform to ensure you don’t introduce any issues
Flyway (or SQL database migration tools in general) is a tool that in my opinion is a must have for any serious project.
I have shown some of the patterns and best practices that I have worked out after using if in multiple projects.