Flyway - Database Java Migration Open Source Framework

Axel Fontaine, https://www.axelfontaine.com

Flyway is a popular open source database migration framework for Java. It brings structure and confidence to the evolution of your database schema. It is really easy to use, yet powerful and both developer and DBA-friendly. Flyway supports the industry's most common databases including Oracle, SQL Server, DB2, MySQL, PostgreSQL, HsqlDB, H2 and Derby. Migrations can be written in plain old SQL or Java and can be executed through the API, the Maven plugin, the Ant tasks or the Command-Line tool.

Web Site: https://flywaydb.org
Version Tested: 2.0
System Requirements: Java 5+, JDBC driver for your database
License & Pricing: Open Source, Apache 2.0 License, Free
Support: Stack Overflow (http://stackoverflow.com/questions/tagged/flyway), commercial support and training from http://flywaydb.org/support

Why do you need a database migration tool?

In a Java project where data is persisted in a relational database, our software uses a JDBC driver through which it talks to the database to store and query data. So far, so good.

But on all, but the most trivial projects, this isn't the whole truth. We usually have to deal with multiple environments (dev, test, production, etc) each containing separate instances of both the software and the database. On the software side of things, this problem has been widely addressed through the use of version control, automated and reproducible builds, continuous integration and in some cases automated deployment. On the database side of things, we usually find a different story. Many projects still rely on manually applied SQL scripts. And sometimes not even that (a quick SQL statement here or there to fix a problem). And soon many questions arise:

• What state is the database in on this machine?
• Has this script already been applied?
• Has the quick fix in production been applied in test afterwards?
• How do you set up a new database instance from scratch?
• Will our software run against our database in its current state?

More often than not the answer to these questions is: We don't know.

Database migration tools let you regain control of this mess. They allow you to:

• Recreate a database from scratch
• Make it clear at all times what state a database is in
• Review which changes have been applied when and by whom
• Migrate in a deterministic way from the current version of the database to a newer one
• Be confident that your software will run with the current state of the database

Simple yet Powerful

Flyway is designed and built on the idea that adding database migrations to your project should be as easy as possible. It is kept deliberately small. It strongly embraces convention over configuration and its API is composed of just six main methods: clean, init, migrate, validate, info and repair.

To know which state your database is in, Flyway relies on a special metadata table for all its internal bookkeeping. Flyway creates it automatically when it is run for the first time. After that, every time a migration is applied, its details are stored in that table. Flyway can then query it to know which migrations have been applied, what the current version of the schema is and whether the last migration succeeded or failed.

Flyway works very hard to support your existing infrastructure and to avoid lock-in. By default Flyway works with plain old SQL scripts. Its parser, however, is very sophisticated. It allows you to use both regular SQL statements and statements relying on database-specific extensions such as PL/SQL and T-SQL. This enables you to easily define advanced indexes, stored procedures, materialized views or any other object your database supports. You have the full power of your database available to you and DBAs can easily assist you with their expertise. It also makes it very easy to get started on existing projects. You can dump your schema using your database's native tools and apply the resulting SQL script as a first migration with virtually no changes.

For more advanced scenarios where SQL may not be the best option anymore, Flyway supports migrations written in Java. This is especially useful when dealing with BLOBs and CLOBs, and for performing advanced data transformations such as smart column splitting (Think splitting the country and area code out of phone numbers for example).

Reliable

Relying on a tool to deal with changes to your database requires trust. Flyway works very hard to achieve and maintain this.

All migrations are run within transactions. On databases with support for DDL transactions (PostgreSQL, DB2, SQL Server and Derby), Flyway will automatically roll back the entire migration when a failure occurs and report an error. On all other databases the migration will be marked as failed in the metadata table and an error will be reported. You will then have to manually revert the changes and run Flyway's repair command to fix the metadata table. This follows the fail fast rule. It surfaces errors as soon as possible to avoid having them sneak unnoticed through your system.

Flyway also relies on careful locking to orchestrate concurrent migration attempts. This is crucial for application startup in cluster environments.

Works within your environment

Flyway supports a large range of databases and can be run in a variety of ways to best suit your environment.

The most powerful and effective way to run Flyway is on application startup through the API. This allows you to bundle all migrations together with your application and have a single deliverable you can throw at any environment. It will then automatically detect the state of the database and migrate accordingly on startup, ensuring your application code will always work against the database it is currently accessing.

If for whatever reason this is not an option, Flyway supports other ways to run it, all equally powerful, exposing the same commands and configuration options.

For Maven users, there is a Maven plugin. Like Flyway itself, it is of course also available from Maven Central and can be defined like this in the POM:

    <groupId>com.googlecode.flyway</groupId>

    <artifactId>flyway-maven-plugin</artifactId>

    <version>1.8</version>

Ant users have an Antlib available to them. Ivy users can easily reference it by adding this line to their configuration:

<dependency org="com.googlecode.flyway" name="flyway-ant" rev="1.8"/>

And for those who prefer the command-line or shell scripting, there is a standalone command-line tool available supporting Windows, Linux and OSX.

Flyway supports the industry's most common databases including Oracle, SQL Server, DB2, MySQL, PostgreSQL, HsqlDB, H2 and Derby. It also works on Google AppEngine with full support for Google Cloud SQL.

Getting started

Flyway is available for download from both Maven Central and the Flyway web site.

In this example we will use Maven as the build tool and H2 as the database.

Let's start by adding the Flyway Maven plugin to our POM:

    <groupId>com.googlecode.flyway</groupId>

    <artifactId>flyway-maven-plugin</artifactId>

    <version>1.8</version>

<driver>org.h2.Driver</driver>

<url>jdbc:h2:${project.build.directory}/testdb</url>

<user>SA</user>

    <groupId>com.h2database</groupId>

    <artifactId>h2</artifactId>

    <version>1.3.168</version>

Running mvn flyway:info shows that our database is still empty:

[INFO] [flyway:info {execution: default-cli}]
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | No migrations applied and no migrations pending                      |
[INFO] +-------------+------------------------+---------------------+---------+

So far so good. We can now create our first migration following Flyway's default naming conventions. Flyway will scan the classpath and automatically pick it up. Through the naming convention it will automatically detect both the version and the description of the migration.

File: src/main/resources/db/migration/V1__Initial_Migration.sql

After running mvn compile flyway:info, we now have our first pending migration

[INFO] [flyway:info {execution: default-cli}]
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 1           | Initial Migration      |                     | Pending |
[INFO] +-------------+------------------------+---------------------+---------+ 

We apply it by running mvn compile flyway:migrate

[INFO] [flyway:migrate {execution: default-cli}] 
[INFO] Creating Metadata table: schema_version (Schema: PUBLIC) 
[INFO] Current schema version: null 
[INFO] Migrating to version 1 
[INFO] Successfully applied 1 migration (execution time 00:00.038s). 

The metadata table has been created and the migration has been successfully applied. Running mvn compile flyway:info now shows the installation details.

[INFO] [flyway:info {execution: default-cli}]
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 1           | Initial Migration      | 2012-09-11 10:15:53 | Success |
[INFO] +-------------+------------------------+---------------------+---------+ 

Let's now add a second migration containing reference data.

File: src/main/resources/db/migration/V1_1__Reference_Data.sql

Again, mvn compile flyway:info will show it as pending.

[INFO] [flyway:info {execution: default-cli}]
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 1           | Initial Migration      | 2012-09-11 10:15:53 | Success |
[INFO] | 1.1         | Reference Data         |                     | Pending |
[INFO] +-------------+------------------------+---------------------+---------+ 

Once it has been applied with mvn compile flyway:migrate, it is now marked as success.

[INFO] [flyway:info {execution: default-cli}] 
[INFO] +-------------+------------------------+---------------------+---------+ 
[INFO] | Version     | Description            | Installed on        | State   | 
[INFO] +-------------+------------------------+---------------------+---------+ 
[INFO] | 1           | Initial Migration      | 2012-09-11 10:15:53 | Success | 
[INFO] | 1.1         | Reference Data         | 2012-09-11 10:28:02 | Success | 
[INFO] +-------------+------------------------+---------------------+---------+ 

In development, it is also very easy to revert to an empty database to start over. Simply issue the mvn flyway:clean flyway:info command.

[INFO] [flyway:clean {execution: default-cli}]
[INFO] Cleaned database schema 'PUBLIC' (execution time 00:00.012s)
[INFO] [flyway:info {execution: default-cli}]
[INFO] +-------------+------------------------+---------------------+---------+ 
[INFO] | Version     | Description            | Installed on        | State   | 
[INFO] +-------------+------------------------+---------------------+---------+ 
[INFO] | 1           | Initial Migration      |                     | Pending | 
[INFO] | 1.1         | Reference Data         |                     | Pending | 
[INFO] +-------------+------------------------+---------------------+---------+ 

Both migrations are now pending again. Running mvn compile flyway:migrate takes us right back to where we were.

Adding a Java migration is easy too. There again are sensible defaults and naming conventions you can follow.

File: src/main/java/db/migration/V2__Age.sql

Running mvn compile flyway:migrate flyway:info produces the expected result:

[INFO] [flyway:info {execution: default-cli}] 
[INFO] +-------------+------------------------+---------------------+---------+ 
[INFO] | Version     | Description            | Installed on        | State   | 
[INFO] +-------------+------------------------+---------------------+---------+ 
[INFO] | 1           | Initial Migration      | 2012-09-11 11:20:02 | Success | 
[INFO] | 1.1         | Reference Data         | 2012-09-11 11:20:02 | Success | 
[INFO] | 2           | Age                    | 2012-09-11 11:20:02 | Success | 
[INFO] +-------------+------------------------+---------------------+---------+ 

Application Integration

Integrating Flyway directly in your application is very straightforward. It has zero required dependencies. All you need to get started is the flyway-core JAR, the JDBC driver for your database and Java 5+.

In a Spring application you can define it as a bean.

<bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate">

    <property name="dataSource" ref="..."/>

    ...

This bean can then be depended upon by ORM frameworks like Hibernate to ensure they always have a fully up to date schema before starting up.

<bean id="sessionFactory" class="..." depends-on="flyway">

    ...

If Spring isn't your cup of tea, you can also rely on the easy Java API which effectively achieves the same thing.

import com.googlecode.flyway.core.Flyway;

Flyway flyway = new Flyway();

flyway.setDataSource(...);

flyway.migrate();

Additional Flyway Features

Behind this apparent simplicity lies a powerful array of features to deal with a variety of scenarios not discussed here, including:

• Validation: Through checksums Flyway can detect if migration scripts have been altered after they have been applied. This can be a source of subtle errors and Flyway helps you guard against it. Checksums are automatically calculated for SQL migrations and can be optionally defined for Java ones.
• Multiple schema support: Flyway has built-in support for migrating, validating and cleaning multiple schemas sharing the same lifecycle.
• OSGi support: Flyway fully supports the Equinox OSGi container and goes to great length to ensure features like classpath scanning work flawlessly for both SQL and Java migrations.
• Placeholder replacement: Placeholder replacement with configurable placeholder markers (default: ${ }) is supported out of the box for SQL migrations.
• Multiple locations: Migrations can be loaded from multiple locations on the classpath. You can vary these locations in your configuration according to your environment. This enables you to have a location with test data being used in development, but excluded in production.
• Configurable naming: Whether it is the name of the metadata table, the locations to load the migrations from, or the naming convention of the SQL and Java migrations, all are fully configurable to your liking.

Next steps

At https://flywaydb.org you will find extensive documentation, a FAQ, downloads, the changelog, the roadmap and more.

You will also find a comparison matrix of the most popular database migration tools available for the Java platform, so you can pick the right tool for the job.

For questions, there is a Flyway tag on StackOverflow available for you to use.

Commercial Support and Training is available from https://flywaydb.org/support.

More Java and Database Knowledge

• Java Videos and Tutorials
• Database Videos and Tutorials

Click here to view the complete list of tools reviews

This article was originally published in the Fall 2012 issue of Methods & Tools