Introduction to database migrations using Flyway

Let us first understand why are database migrations necessary?

Assume that we have a project called Shiny and it’s primary deliverable is a piece of software called Shiny Soft that connects to a database called Shiny DB. We not only have to deal with one copy of our environment, but with several.

So the simplest view of our problem will translate to:

This presents us with the challenge to ensure that a certain release is always delivered with the matching state of the database.

A solution to this problem is using a bunch of shell- and sql-scripts. But that is not really a sustainable solution as these scripts can become overly complex and hard to maintain.

So a better alternative to regain control of this mess this would be Database migrations.

It will allow us to:

• Recreate a database from scratch
• Make it clear at all times what state a database is in
• Migrate in a deterministic way from your current version of the database to a newer one

First steps to Flyway !

Flyway is an open-source database migration tool that strongly favors simplicity and convention over configuration.

It is based around 6 basic commands:

Migrate : 

• Scans the file system or your classpath for available migrations.
• Compares them to the migrations that have been applied to the database. If any difference is found, it will migrate the database to close the gap.

Clean :

• It gives you a fresh start, by wiping your configured schemas completely clean.
• All objects (tables, views, procedures, …) will be dropped.

Info :

• It lets you know where you stand.
• You can check which migrations have already been applied,which ones are still pending, when they were executed and whether they were successful or not.

Validate :

• It helps you verify that the migrations applied to the database match the ones available locally.
• It’s useful in detecting accidental changes that may prevent you from reliably recreating the schema.

Baseline :

• It causes migrate to ignore all migrations up to and including the baseline version.
• Newer migrations will then be applied as usual.

Repair :

• It fixes issues with the metadata table.
• Remove failed migration entries.
• Realign the checksums of the applied migrations to the ones of the available migrations.

Executing Flyway from Maven

Integrate Flyway and H2 into the pom.xml and configure Flyway so it can successfully connect to H2:

<build>
  <plugins>
    <plugin>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-maven-plugin</artifactId>
      <version>4.0.3</version>
      <configuration>
        <url>jdbc:h2:file:./target/foobar</url>
        <user>sa</user>
      </configuration>
      <dependencies>
        <dependency>
          <groupId>com.h2database</groupId>
          <artifactId>h2</artifactId>
          <version>1.4.191</version>
        </dependency>
      </dependencies>
    </plugin>
  </plugins>
</build>

Create your migrations in the directory

src/main/resources/db/migration/

Let’s call our first migration  V1__Create_person_table.sql

create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

And our second migration V1__Add_people.sql

insert into PERSON values(1,'foo');
insert into PERSON values(2,'bar');

Execute them by issuing:

mvn flyway:migrate

Let’s quickly check if the migrations were successful by typing:

mvn flyway:info

References:

• https://flywaydb.org/documentation/