DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Hear from the experts in our "DZone 2023 Preview: DevOps Edition" on Fri, Jan 27!
Save your seat
  1. DZone
  2. Data Engineering
  3. Databases
  4. Simple Database Migrations With Flyway

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.

Himani Arora user avatar by
Himani Arora
·
Feb. 04, 17 · Tutorial
Like (8)
Save
Tweet
Share
9.44K Views

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:

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.

Database Flyway (software)

Published at DZone with permission of Himani Arora, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Using QuestDB to Collect Infrastructure Metrics
  • PostgreSQL: Bulk Loading Data With Node.js and Sequelize
  • Public Cloud-to-Cloud Repatriation Trend
  • Choosing the Best Cloud Provider for Hosting DevOps Tools

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: