Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Simple Database Migrations With Flyway

DZone's Guide to

Simple Database Migrations With Flyway

The open source tool Flyway, as well as a maven script, will help you easily migrate and cleanup your databases, avoiding a buildup of unpleasant SQL and shell scripts.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

Before we dive into using Flyway for database migrations, let's cover why it might be necessary in the first place.

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

So the simplest view of our problem will translate to:

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

This presents us with the challenge of ensuring 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 would be a database migration.

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 on six 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

And that's it — your migrations are complete. No sweat.

Aggregations provide vital intelligence to the success of a business. Crush the challenge of providing real time aggregations for daily, weekly, and monthly totals without having to tie up your servers.

Topics:
flyway ,database ,database migration

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}