Flyway with SBT

Table of contents

Reading Time: 3 minutes

Flyway is the tool that acts as version control for your database and mainly used for database migrations. By version control, it means to record each and every change with the SQL scripts so that at a later point of time one can recall the specific version of the database.

But why database migration is needed?

Let’s understand the need for database migration through a simple diagram below.

Assuming you are building a deliverable software. And for that, you are running your application on different environments like Prod, UAT, Dev, and your Local system as shown in the above diagram. And all the different environments run on different versions of the application. This means that you have to manage every environment’s database separately due to different versions.
And it is well known that each version of the software has a high chance of having different database schema.
Now, it brings a challenge to manage these different databases of different environments manually.

Flyway is made to handle these types of situations. With the help of Flyway, you can easily manage your database in different environments individually.

How does Flyway work?

When Flyway runs, it first tries to find the flyway_schema_history table in your database. This is the table that is created and maintained by Flyway and is used to track the current state of the database.
If this table is not found in the database, it means that you are building a fresh database.
Flyway will then find all the SQL scripts in the application classpath that are build-up for the database schema and sort all the files according to their version number.

For example, the following is the sorted order of the SQL scripts.

V1_create_table.sql
V2_alter_table.sql
V3_add_table.sql

And finally, it will run each SQL file one by one on the database. After every file run, Flyway will make an entry to the flyway_schema_history table for that file.
So in the end, there will be 3 records in the schema_history table(one for each file).

Next time if there comes a new SQL file for version V4, the Flyway will then skip re-running the file already stated in the flyway_schema_history table, and will only run the V5 file.

How to integrate Flyway with SBT?

Now it is clear that how Flyway works, let’s integrate Flyway with an SBT project.

Add the following plugin to your plugins.sbt file

 addSbtPlugin("io.github.davidmweber" % "flyway-sbt" % "6.2.3")

Refresh your project and then add the following lines of code to your project’s build.sbt file.

enablePlugins(FlywayPlugin)

libraryDependencies += "mysql" % "mysql-connector-java" % "6.0.6"
flywayUrl := "jdbc:mysql://localhost:3306/test_flyway"
flywayUser := "root"
flywayPassword := "password"
flywayLocations += "db/migration"

The sample code above uses MySQL DB as the database. You can take any other database as per your requirements like Oracle, Postgres, Firebird, DB2, MSSQL, etc.

Add your SQL scripts to src/main/resources/db/migration folder of the SBT project. This is the default classpath from whare flyway picks the SQL files. Make sure that the SQL scripts are properly versioned like V1_, V2_, etc.

You can take scripts from here.

Run the following command to start migration on the above-mentioned database.

sbt flywayMigrate

And you will see that your database is now filled with two tables, flyway_schema_history, and person.
The table flyway_schema_history will contain two records as there were two SQL files for migration.

Now if you want to add more migrations, add one more file with proper versioning to the same folder and then run the migrate command again.
You will see that this time only the newly added SQL file is executed because the two previously added files were already executed.

How to revert the migration?

Till now, you have seen that how to migrate to the newer version of the database. Now assume that while executing one of the newly added migration scripts V3, it broke down and execution failed.

The system is inconsistent now because your code is the latest one but your database schema is not up-to-date. To handle this situation, you should roll back all the changes to the previous stable version and start investigating the problem. Once identified the problem, you can fix the problem & move ahead with the newer version.

To do the fresh start again on the same database, run-