Introduction

This article intends to be an introduction to how to automate database migrations for 3 different environments: development, testing and production. Each one runing on its own Docker Compose virtual machine.

In order to see the implementation details, please check out GitHub repo.

What is Flyway

Flyway is a tool to automate database evolution. A developer creates a SQL script for the next change and the tool applies it to the target database. As the scripts are created incrementally and named to keep the creation order, Flyway will run all the scripts in that same order and the execution of every script is under a database transaction, so if anything goes wrong, the database executes a rollback. All of this results in a consistent database evolution in all environments.

How it works

The developer creates a configuration file where he defines the database connection, the database type, the folder where the scripts reside and many other settings.

The tool provides 3 ways to run the process of a database migration: Java API, Gradle/Maven or command line.

Concepts

Migrations

Every script created to change the database is called "migration". The name of each migration file follows a naming convention that allows keeping the files in the order they must be executed so the database can be created/modified properly.

Flyway adheres to the following naming convention for migration scripts:

[Version]__[Description].sql

Where: Prefix – The default prefix is V, which we can change in the above configuration file using the Flyway.sqlMigrationPrefix property. Version – Migration version number. Major and minor versions may be separated by an underscore. The migration version should always start with 1. Description – Textual description of the migration. A double underscore separates the description from the version numbers.

Example: V1.1__create_users_table.sql

History table

In order to keep track of applyed migrations, Flyway maintains a table for that - Flyway_schema_history. The table is used to check if

Commands

Flyway supports the following basic commands to manage database migrations:

• Info: Prints current status/version of a database schema. It prints which migrations are pending, which migrations have been applied, the status of applied migrations, and when they were applied.

• Migrate: Migrates a database schema to the current version. It scans the classpath for available migrations and applies pending migrations.

• Baseline: Baselines an existing database, excluding all migrations, including baselineVersion. Baseline helps to start with Flyway in an existing database. Newer migrations can then be applied normally.

• Validate: Validates current database schema against available migrations.

• Repair: Repairs metadata table.

• Clean: Drops all objects in a configured schema. Of course, we should never use clean on any production database.

The demo project

Setup

The following image shows the main parts of the project to look into. For details of each configuration open each file.

It can be seen that there are two migration folders, this is to show that is possible to have migration files in different folders according to each environment. The migrations files are in the default folder for Flyway on Spring Boot.

There are also 3 Spring Boot property files each responsible for its environment configuration.

How to run

Executing Tests

Tests are executed in the test environment and can be triggered by executing

.\gradlew test

Or by starting the unit testing in the IDE. There is a Spring boot annotation @ActiveProfiles("test") in the testing class that will define the correct configuration profile for the test.

Running web applications in the correct environment

In order to start the Spring Boot web app in the correct environment it was configured in Gradle 2 tasks "bootRunDev" and "bootRunProd" to define the proper environment for the application to run.