Database migrations in Scala

https://github.com/geirolz/fly4s

The most famous library to handle database migrations in Java is for sure Flyway.
It works very well and the community edition has a lot of features as well.

Problem

Flyway APIs are written in the standard OOP paradigm, so throwing exceptions, manually managing resources, etc...

Solution

Fly4s is a lightweight, simple and functional wrapper for Flyway.
The aim of Fly4s is straightforward, wrapping the Flyway APIs in order to guarantee referential transparency, pureness, resource handling and type safety. To achieve this goal, Fly4s use the typelevel libraries cats and cats-effect.

Getting started

The first step, import the Fly4s library in our SBT project.
So, add the dependency in your build.sbt file.
Fly4s depends on Flyway, so we'll have access to Flyway as well

(check the GitHub repo for the latest version)

libraryDependencies += "com.github.geirolz" %% "fly4s-core" % "0.0.4"

Migrations files

As the plain Flyway, we have to create a folder that will contain our migrations scripts, often in resources/db.

In this folder, we have to put all our migration. We can have:

• Baseline migrations
• Repeatable migrations
• Undo migrations

For this example, we are going to use a simple baseline migration to add a table to our database schema.

Baseline migrations are versioned and executed only when needed. The version is retrieved from the script file name.

So in this case, V001__create_user_table.sql, the version will be 001(remember the double underscore after V).

Here we have our first migration(for MySQL database)

resources/db/V001__create_user_table.sql

CREATE TABLE `user` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` varchar(30) NOT NULL,
    `surname` varchar(30) NOT NULL
);

Defining database configuration (optional)

A good practice is to create a case class to handle the database configuration(this combined with PureConfig or other config libraries makes your app very robust from the configuration point of view)

Let's create a simple case class to achieve this.

case class DatabaseConfig(
  url: String,
  user: Option[String],
  password: Option[Array[Char]],
  migrationsTable: String,
  migrationsLocations: List[String]
)

N.B. apart from the common fields such url, user and password we'll use: migrationsTable to define the Flyway table name(used to store the migration status) and migrationsLocations to specify a list of the folders that contain our migration scripts.

Instantiating Fly4s

Ok so, now we have all our migration scripts in our folder(resources/db), we have Fly4s as a dependency of our project, and we have a case class that will contain the database configuration.

To instantiate Fly4s we can use make to create a new DataSource(under the hood) starting from the parameters or makeFor in order to create it for an already existent DataSource(for example from Doobie HikariDataSource).
make and makeFor method returns a Resource type class that when released/interrupted safely close the DataSource connection.

In both make and makeFor methods, we can specify the parameter config. Fly4sConfig is a trivial wrapper for flyway Configuration but instead of having a builder, we have a case class.

val dbConfig: DatabaseConfig = ???
val fly4sRes: Resource[IO, Fly4s] = Fly4s.make[IO](
  url                 = dbConfig.url,
  user                = dbConfig.user,
  password            = dbConfig.password,
  config = Fly4sConfig(
    table     = dbConfig.migrationsTable,
    locations = Location.of(dbConfig.migrationsLocations)
  )
)

Using Fly4s

Ok, we have done with the configuration!
We are ready to migrate our database schema with the power of Flyway and the safety of Functional Programming!

We can use use or evalMap from Resource to safely access to the Fly4s instance. In case we have multiple Resources in our application probably evalMap allow us to better combine them using and release them all together at the same time.

We can create a simple utility method to do this

  private def migrateDb(
    dbConfig: DatabaseConfig
  ): Resource[IO, Unit] =
    Fly4s.make[IO](
      url                 = dbConfig.url,
      user                = dbConfig.user,
      password            = dbConfig.password,
      config = Fly4sConfig(
        table     = dbConfig.migrationsTable,
        locations = Location.of(dbConfig.migrationsLocations)
    )
   ).evalMap(fly4s =>
        for {
          _               <- logger.debug(s"Applying migration for ${dbConfig.name}")
          migrationResult <- fly4s.validateAndMigrate[IO].result
          _ <- logger.info(
            s" Applied ${migrationResult.migrationsExecuted} migrations to ${dbConfig.name} database"
          )
        } yield ()
      )

Conclusions

We have done it! So, to recap, we have:

1. Created a folder under resources to put our migrations(db)
2. Imported Fly4s as a dependency in our project
3. Created a configuration case class to describe our database configuration
4. Instantiated a Fly4s instance creating a new DataSource
5. Migrated our database using validateAndMigrate
6. At the application shutdown/interruption Resource(from cats-effect) will safely release the DataSource

With a few lines, we have migrated our database safely handling the connection and the configuration.

As flyway, Fly4s provides multiple methods such as:

• validateAndMigrate
• migrate
• undo
• validate
• clean
• info
• baseline
• repair

Useful links

https://github.com/geirolz/fly4s
https://flywaydb.org/documentation
https://typelevel.org/cats/
https://typelevel.org/cats-effect/
https://pureconfig.github.io/