Flyway - Database Migration

Overview

This article will give you a brief introduction to Flyway and how to use it for automating your database migration.

Database Migration

How to handle database migration in projects delivering software more frequently day by day is often a topic with a lot of discussions. For several projects I have been using Liquibase for this purpose and it has suited me well. Recently I was introduced to Flyway, and I had to check it out.

Easy to get started

My intial reaction is that this is really easy to set up and start using:

  • Add a plugin to your application's pom.xml (If you are using Maven)
  • Configure it to your existing database
  • Add any sql-scripts to your module (in the default location)
  • Run a simple command mvn flyway:migrateand your database is updated using Flyway (at first migration it will add a table automatically to keep track of versions migrated)

A sample configuration using Oracle database:

   <build>
        <plugins>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>4.0.3</version>
                <configuration>
                    <url>jdbc:oracle:thin:sample/sample@localhost:1521/XE</url>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>com.oracle</groupId>
                        <artifactId>ojdbc6</artifactId>
                        <version>${oracle.database.version}</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

Note: Oracle dependencies are not always simple to handle since they are generally not publicly available in any repositories, but some guidance on how to solve that can be found here: https://www.mkyong.com/maven/how-to-add-oracle-jdbc-driver-in-your-maven-local-repository/

Add your SQL scripts to the default folder: src/main/resources/db/migration and you are up and running.

Remember the naming of the scripts, e.g. V1__Create_person_table.sql. The name must start with a version number(unique running numbers for each script) followed by two underscores, and then a descriptive name.

Include the migration in your build phase

If you want, you can add this simple command to your build phase and it will always run when you build your code. If there is no new scripts to run, nothing will happen. Some will use Flyway all the way into the production database, while others will use it in all other environments with higher number of deployments

Include the migration in your application's startup procedure

You can even go one step further and let the application run the database scripts when it is started. For some this might be perfect, for others it is a bit too risky in development environments.

Simple Syntax

You do not have to learn a new syntax (you didn't have to do that with Liquibase either, but it was recommended). You simply use SQL for all database updates.

Strategy

If you have a codebase with development happening in a bunch of branches, you must ensure you have a proper strategy for how to handle scripts comming in from each of them, since Flyway has a strict numbering it follows, and if that is broken your scripts will fail.

Try it

My recommendation, if you are not using any tools for database migrations today, is to try it in your project and see if it gives you value. Since it is all SQL, the way back to not using Flyway is really simple if it turns out to not be the best fit for your project.

Be sure to add a strategy, as described above, since everyone needs to understand how to use it for it to shine.

References