Simple Database Migrations with Scala and Querulous

We hate doing it, but one time or the other, each of us ends up writing a quick-and-dirty database migration utility for a project we’re working on. I recently had to do the same, and surprisingly the process was really smooth .. largely thanks to Scala goodness and Querulous — a very cool, simple and lightweight database library created by some folks at Twitter.

Let’s start by creating a Mavenized Scala project, check out Getting Started – Maven Archetype for Scala.

Does the Scala project build successfully? Good.

The next step, add the Querulous dependencies to your project pom.xml file:

In our example, we will migrate a source database boysDb to a destination database girlsDb. No real migration business logic is being applied, think of it as a one-to-one migration.

We start off by declaring QueryEvaluators for our source and destination database schemas:

val sourceDb = QueryEvaluator("localhost/boysdb", "root", "")
val destinationDb = QueryEvaluator("localhost/girlsdb", "root", "")

QueryEvaluator object allows us to execute SQL queries on a referenced database.

Next step, get boys from the source database:

val boys = sourceDb.select("select id, name from boys") {
      row => (row.getInt(1), row.getString(2))
    }

boys is a collection of Tuple2[Int, String] containing the id and name attributes of a boy.

For each boy, insert a girl:

boys.foreach(boy => insertGirls(boy))

Let’s now define our insertGirls() method

def insertGirls(boy: Tuple2[Int, String]): Unit = {
  destinationDb.execute("insert into girls (id, name) values (?, ?)", boy._1, boy._2)
}

..and we’re done.

Scala + Querulous makes it easy to connect to multiple database instances, makes querying extremely straightforward by exposing SQL directly without unnecessary ORM’ing or configuration / property file mess.

Full source code has been published on GitHub – https://github.com/Srirangan/scala-querulous-simple-migrations

For serious database migrations, do consider Scala Migrations.