How to Configure Flyway for Seamless Database Migration

Flyway is a tool that supports database migrations on multiple types of relational databases. It is open-source and licensed under Apache 2.0 License.

What are the Advantages?

• Facilitates seamless updates to a database schema from one version to another as an application evolves with the code.
• Keeps a record of the updates applied on a schema in a separate table. By default this table is called SCHEMA_VERSION.
• Couple database updates to an application’s deployment pipeline and make sure that those changes are properly applied.
• Keeps track of which changes were already applied and which were not.

In Flyway, updates or changes made to a database schema are known as migrations.

Migration Types

• SQL script based migrations: For straightforward schema changes.
• Java based migrations: For advanced database transformations.

Naming Conventions

Migrations should be named in the default naming conventions for Flyway to identify them properly.

For sql based migrations the naming convention is V<version-number>__<description>.sql

Example: V2__Add_new_table.sql

For Java based migrations, lass name must comply with the pattern V<version-number>__<description>

Example: V2__Add_new_table

Also there are mainly 3 ways to configure and run Flyway for your application

• Integrate flyway directly into your application.
• Run it as a stand-alone tool as part of your build process.
• Execute manually from the command line (using the command line client)

In this post, I will show how you can easily integrate Flyway in to a Java application.

Flyway is implemented in Java and hence the integration is quite straight-forward.

Prerequisites

Configure a database of your own choice. I will be using a MySQL database for this demonstration.

Steps

1. Create a maven based Java project and add the flyway-core dependency under dependencies section in your pom.xml file.

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>8.5.0</version>
</dependency>

2. Inside the build section of POM file, we need to configure the Flyway maven plugin. Also we need to configure the database and Java Connector for the specific database type, as class path dependencies of the plugin.

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>8.5.0</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/flyway_demo</url>
        <user>test</user>
        <password>test</password>
        <locations>
            <location>classpath:db/migration</location>
        </locations>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-mysql</artifactId>
            <version>8.2.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
        </dependency>
    </dependencies>
</plugin>

You need to change the following parameters in the above configuration to suit your integration.

• <url> : This should be your database connection URL.
• <user> : Username for the database connection.
• <password> : Password for the database connection.
• The two dependencies added under <dependencies> section inside the <plugin> should be the database and database Java connector of the particular type of database you are using. I have used the corresponding dependencies to connect to a MySQL database.

3. Create a folder named db.migrations inside src/main/resources folder of your project. Here we will be defining all the migrations. For this project, I will be using SQL script based migrations.

4. Inside db.migrations folder create a new file called V1__add_user_table.sql and add the following code to create our initial table.

create table USER (
    ID int not null,
    NAME varchar(100) not null
);

5. Now execute the following command from the root of your project.

mvn flyway:migrate

6. After the above command is successful, you should be able to see a table called USER created in your database.

7. Let’s add a new migration inside db.migrations with the name V2__add_new_column_email.sql Here we will be altering the schema of the previously created table by adding a new column.

ALTER TABLE USER ADD EMAIL varchar(200);

8. Now when browse the USER table again, you should be able to see that a new column with the title EMAIL is available in the schema.

Similarly, you can add new scripts to modify the schema and insert data to your tables. However make sure to version them properly with the correct naming convention.

I hope you got an idea on what is Flyway and how it can be integrated easily to your project. Here you can refer the complete source code of my project. For additional information you can refer to Official Flyway Documentation.