Today I’d like to talk about very important but sometimes very painful thing like doing database migrations with Flyway.

What are database migrations about?

Just imagine that you have a database in some state A. Everything works fine,  but eventually, requirements changed and now you need to make changes to your database.

You have 2 options:

  • The Awful way – change your database manually via PgAdmin, MySQLWorkbench or whatever tool you like and, what is even worse, to not document changesDO NOT DO THAT!
  • The Right way is to use database migration tool. What tool you pick depends on your needs, but you definitely need one. Tools usually help to apply and rollback granulated changes. It allows to see history of changes applied to database.

Better safe with backups than sorry with broken database

Before we start talking about a tool for database migrations, you must do BACKUPS before migrations! Imagine, that something went wrong but backups will not let you down.

It’s easy to automate, so I strongly advise to spend some time on making sure that you have backups.

Flyway – tool for database migrations

Flyway – it’s a tool for database migrations used widely in Java/Scala world.  It supports Oracle, SQL Server, DB2SQL Azure, DB2 z/OS, MySQL , MariaDB, Google Cloud SQL, PostgreSQL, Redshift, Vertica, H2, HsqlDerby, SQLite and solidDB.

It apply  SQL scripts to the target database if needed and keeps track on which of migrations (SQL scripts) ran successfully or failed, so we could apply and rollback between different migrations versions.

Flyway commands

Flyway has applies SQL scripts to the database by invoking set of commands. Let’s make a brief overview for each command.

Migrate – migrates database schema to the latest migration version. Flyway will store metadata information about migration scripts you have and info about which scripts ran successfully or failed.

For example, you have a database schema with applied migrations 1,2 and 3 and you want to apply migration 4. Flyway will check which scripts ran before and apply migration 4 only in case if it was not done before.

If 1,2,3,4 are already applied, Flyway will do nothing.

Clean – wipes database schema. Do not use it in production! All database objects like tables, views or procedures will be dropped. This command is very helpful in development when you test or develop your migration scripts.

Validate – validates wich migrations were applied to the database. It helps to validate the current state of the db schema against meta info table.

Info – prints information about migrations that were applied to the database schema, shows wich ran successfully and wich failed.

Baseline – useful for integrating Flyway with existing databases. For example, you have a defined schema and some useful data in there. Obviously, you don’t need to create it again so y0u can use baseline to create a table with meta information about current database state. After that flyway will apply new migrations to baselined schema.

Repair – helps to fix issues with metadata table in your database. For example, you have applied migration and it failed. In meta information table this migration will be marked as failed and you will need to change migration SQL script.

Changing SQL migration script will change it’s checksum and Migrate command will not work right away. Here you would use repair command.

Learn by example

Best way to learn new things, it’s learning by example, so let’s build a simple database using Flyway migrations scripts like you see below:

database schema created and managed by flyway

Sample database schema created and managed by flyway

I suggest to define the schema as  Flyway V1 migration  and then apply our schema definition script to an empty database.

First of all, we would need to install flyway command line tools, as the easiest way to play around with this migration tool. Here you will find binaries to download for each platform or if you use MacOS just run brew install  flyway.  Once  we are done with it, we can create a first migration script. Let’s name it as: V1__Create_table.sql

Then we need to trigger migration by running flyway migrate and make sure that database has been changed from zero to working scheme.

But what if we want to change existing database schema? It could catch your eye, that some of the fields in are looking strange. Probably you would want to change names a bit, so let’s create a Flyway migration script and name it as V1_2__Alter_names.sql :

Runningflyway migrate will trigger another migration and this time V1_2__Alter_names.sql script will be applied. Once migration is done your schema will be altered and you will find meta information table with information about 2 successfully applied migrations.

You can play around with more complicated migration scenarios, but workflow will be very similar. Create script, test the migration, validate results and clear stuff in case of errors. I hope that this article showed you advantages of db migration tools and you will not alter your database manually anymore.