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.

Transactional Migrations

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 schema_version

• Run flywayInfo to confirm that Flyway doesn’t see my last migration

• Run flywayMigrate to ’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 flywayBaseline introducing this file as V1__Baseline.sql

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”.

Performance Tips

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

• Etc…

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 diff migration between what’s in the database and your new migration file (*)

• Run flywayInfo, you should get an exception complaining about checksum mismatch

• Edit row in schema_version table 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

Summary

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.