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.
Join the DZone community and get the full member experience.Join For Free
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:
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:
- 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.
- It gives you a fresh start, by wiping your configured schemas completely clean.
- All objects (tables, views, procedures, …) will be dropped.
- 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.
- 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.
- It causes migrate to ignore all migrations up to and including the baseline version.
- Newer migrations will then be applied as usual.
- 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:
Let’s call our first migration
create table PERSON ( ID int not null, NAME varchar(100) not null );
And our second migration
insert into PERSON values(1,'foo'); insert into PERSON values(2,'bar');
Execute them by issuing:
Let’s quickly check if the migrations were successful by typing:
And that's it — your migrations are complete. No sweat.
Published at DZone with permission of Himani Arora, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.