Microservices Databases Migration Java

Anyone who knows me well, knows that I like to make things easier for myself where I can. I’d like to briefly show you a tool that helps me do that when creating DB interacting web services in Java…Flyway.

For context, I’ve been designing and developing Microservices for the last few years of my career - a Microservice is essentially a web service that encapsulates a very specific set of functionality (authorisation, as an example).

These Microservices are often deployed with their own Database (DB) and managing that DB is where Flyway comes in to play.

Without Flyway

Traditionally, you would design what data needs to be stored in what table within your service’s database. Then, you’d create that database (including any tables, sequences, etc) on your machine (locally) before developing your service against it.

When you’re ready to deploy to an actual environment, you need to do the dance again - create the DB and it’s internals before deploying the actual service that interacts with it. Every time you promote your service to a new environment, you dance again.

All things well, you won’t need to make any further adjustments to your DB. Should you need to, you’ll have to manually log into each database in each environment to make sure that all of the required changes are there before you can update your service in the corresponding environment.

Manually keeping track of multiple changes across multiple environments offers plenty of opportunity for human error.

Enter Flyway

Flyway is a tool that takes care of your database management and migrations auto-majically. If you develop your Microservices in Java, you can add Flyway as a dependency in your chosen build tool.

I will talk about using Flyway from a Java development perspective going forwards, as this is my experience with the tool. Do be aware that it’s more than just a Java library - it even has it’s own CLI - so do still check it out if Java isn’t your thing.

As your service starts, Flyway will detect the service’s DB connection and compare how that database looks against the SQL files you have stored alongside your codebase. If certain SQL scripts haven’t been run against this particular DB instance, Flyway will run them so that your DB is in perfect order before your service completes initialisation.

The way this works behind the scenes is that the first time you run your Flyway enabled service in an environment (including locally), Flyway will create a small table for itself and will use it to log which SQL scripts have been run in that environment.

This table/log ensures that any previously run scripts don’t get executed multiple times and that any new SQL scripts are executed before your service has completely started up.

DB Versioning

Flyway adds versioning to your DB. When it creates its own management table (also called its baseline) - that’s version one. For the following versions, unless configured otherwise, Flyway will look for SQL scripts in src/main/resources/db/migration.

These SQL files should follow a naming convention: version__description - Where version is an ordered number which allows Flyway to execute each script in order (I recommend a date and time stamp, just don’t use any non-numerical characters) and description is simply a description of the change the SQL script is making to the DB.

For example, you might have 202002010900__create_tables.sql which would create an initial set of tables for your service. Next time your service starts in an environment, every SQL statement in that file will be executed as a pre-cursor to your Microservice starting up.

You might later decide that some tables could do with an extra couple of columns. Instead of editing your existing file and faffing about in each environment, you’d just create an addition SQL file (e.g. 202003281416__additional_fields.sql) and write an ALTER TABLE statement or two in there. Next time your service starts in an environment, Flyway will detect that there is a new migration for the DB and every SQL statement in that new file will be executed.

Won’t this create a boat load of SQL files eventually? - It could do, yeah. One way to avoid this is to merge a collection of previously migrated SQL files into one. Just make sure the filename matches the name of the last script which has been migrated to all of your environments - possibly the only manual check you might want to do.

Use Flyway in a Spring Boot Service

Add the Dependency

At the time of writing, to add the latest version of Flyway to a Maven based project, you’d add the following to your pom.xml:

1
2
3
4
5

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>6.2.1</version>
</dependency>

You should be able to check for the latest version, and how to use it in any other build tool, here.

Configuration

Open your application.yaml file and add the following:

1
2
3

spring:
  flyway:
    baseline-on-migrate: true

This means that if Flyway hasn’t been run in an environment before, it will create it’s table for tracking migrations.

Create SQL Files

Under src/main/resources/db/migration create your first SQL file containing your initial set of statements.

Done

Simply run the service, and you’ll be able to see Flyway working it’s magic in the logs during application start-up.

For more information on Flyway, check out the documentaion.