Introduction to database migrations using Flyway


https://flywaydb.org/assets/logo/flyway-logo-tm-sm.png

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:

https://flywaydb.org/assets/balsamiq/Environments.png

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

flywaymigrate

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

mvn flyway:info

flywayinfo

References:


KNOLDUS-advt-sticker

Advertisements

About Himani Arora

Software consultant at Knoldus Software LLP.
This entry was posted in Scala and tagged , , , , , . Bookmark the permalink.

2 Responses to Introduction to database migrations using Flyway

  1. Prabhat Kashyap says:

    Reblogged this on Prabhat Kashyap – Scala-Trek.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s