Database access libraries in Scala

A word “Reactive” is using in various contexts recent years. Sometimes it means that we should throw away traditional RDBMS and move to scalable NoSQL. However, many business systems are still working on RDBMS in fact. RDBMS has enough functionality and performance for almost applications, so I think we shouldn’t choose NoSQL if we don’t need it actually.

Of course, there are many database access libraries in the Scala world, but some of them is maintained no longer and some others are not still practical in my opinion. I will look around to know what library is the best choice for now in this article.

Slick

Slick is the most popular database access library in Scala. Slick has the type-safe query builder inherited from ScalaQuery, but the truly distinctive feature is DBIO monadic API which is adopted since Slick3.

DBIO is IO monad, and returns Future when executed. It makes possible to embed the database processing to other Future based asynchronous and non-blocking processing.

val q = for (c <- coffees) yield c.name
val a = q.result
val f: Future[Seq[String]] = db.run(a)
f.onSuccess { case s => println(s”Result: $s”) }

However, in relational database centric applications, we don’t need this because JDBC is blocking in fact, and asynchronicity brings unnecessary complexity to our application.

In addition, Slick’s query builder generates complex and redundant SQL like this. In particular, it causes serious performance issue in MySQL. Slick’s query compiler implementation is much complex and fragile, so it looks hard to maintain and maybe difficult to improve generated SQL.

Another big problem of Slick is that Slick3 doesn’t have backward compatibility for Slick2. We have a lot of existing code written with Slick2. It’s very hard to port them to Slick3, but necessary to upgrade our applications to Scala 2.12 because Slick2 doesn’t work on Scala 2.12.

For this purpose, I made blocking-slick which provides the Slick2 compatible blocking API on Slick3. If you have existing Slick2 based applications, and you want to Slick3, this library might help you.

Quill

Quill also has the type-safe query builder, but it offers macro-based compile time SQL generation. Quill’s query builder API is similar to Slick, but generated SQL is simpler and predictable, and no need any definition to map tables and case classes.

case class Account(userName: String, mailAddress: String)
def find(userName: String) = quote {
  query[Account].filter(_.userName == lift(userName))
    .map(_.mailAddress)
}
val account: Option[Account] = ctx.run(find("takezoe")).headOption

Quill has IO monad and asynchronous processing as well, but they are optional features. We don’t need to use them if we don’t need. It’s reasonable strategy.

The Matter of concern in Quill is that also it is macro based. Macro makes a lot of things easy, but makes difficult in some points. For instance, we have to lift variables to look them up in macros as you see in the preceding example. And a little technique is necessary to assemble a query dynamically in Quill. Look the following code at first:

def find(email: String, includeRemoved: Boolean) = quote { 
  query[Account].filter { t =>
    if(lift(includeRemoved)){
     t.email.toLowerCase == lift(email).toLowerCase
    } else {
     t.email.toLowerCase == lift(email).toLowerCase &&
     t.removed == false
    }
  }
}

Quill generates the following SQL from this code. if-else statement is replaced with CASE WHEN in the generated SQL:

SELECT ... 
FROM account t 
WHERE CASE WHEN ? THEN 
  LOWER (t.email) = LOWER (?)
ELSE
  (LOWER (t.email) = LOWER (?)) AND (t.removed = false)
END

If we want to assemble the filter condition dynamically, we should write as following. Quill falls back SQL generation to runtime if necessary.

def find(email: String, includeRemoved: Boolean) = {
  val accounts = quote { 
    query[Account].filter { t =>
      t.email.toLowerCase == lift(email).toLowerCase
    }
  }
  val filtered = if(includeRemoved)
    quote { accounts.filter(_.removed == false) }
  else
    accounts
}

Although Quill looks good for static query, it might not fit for applications which have a lot of dynamic queries. Also as a potential issue, it might increase compilation time because it’s based on macros and generate SQL in the compile-time. Macro based libraries generally have this tendency.

Doobie

Doobie is a pure functional JDBC layer for Scala. It doesn’t have a typesafe query builder, so we have to write raw SQL as a string literal like:

def find(n: String): ConnectionIO[Option[Country]] = 
  sql"select code, name, population from country where name = $n"
    .query[Country].option
// And then
scala> find("France").transact(xa).unsafePerformIO

As you see, we can not reap of benefits of type safety in writing query or mapping result to case class. However Doobie provides runtime query and mapping checking. We can also check them in the unit test by throwing generated query to the actual database by Doobie’s unit test support.

This is Doobie’s policy: writing raw SQL and ensuring safety by providing a stuff to check it. This concept seems reasonable for applications which have large and complex queries which require performance tuning.

As a matter of concern, Doobie returns a Task object of Scalaz (Doobie also supports cats), so it fits with other asynchronous libraries such as http4s, but we need some knowledge of Scalaz to use Doobie. In addition, assembling SQL dynamically is not impossible, but very difficult in Doobie.

Doobie is still 0.4.x, and it looks under development. So API might be changed in the future. It is an interesting library, but it may still be premature to use in production.

ScalikeJDBC

ScalikeJDBC is a tidy DB access library for Scala. It offers some styles to access the database. One of them is writing a raw SQL using string interpolation.

// defines entity object and extractor
import org.joda.time._
case class Member(id: Long, name: Option[String], createdAt: DateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(rs: WrappedResultSet) = new Member(
    rs.long("id"), 
    rs.stringOpt("name"), 
    rs.jodaDateTime("created_at")
  )
}
// find all members
val members: List[Member] = 
  sql"select * from members".map(rs => Member(rs)).list.apply()

A trick called SQLSyntaxSupport is available with string interpolation to write SQL shortly.

val (m, g) = (GroupMember.syntax("m"), Group.syntax("g"))
val groupMember: Option[GroupMember] = sql"""
  select
    ${m.result.*}, ${g.result.*}
  from
    ${GroupMember.as(m)} left join ${Group.as(g)} 
    on ${m.groupId} = ${g.id}
  where
    ${m.id} = ${id}
  """
  .map(GroupMember(m.resultName, g.resultName)).single.apply()

Another one is QueryDSL which is a SQL-like and type-safe DSL to build SQL. It generates straightforward SQL, so we can predict intuitively what SQL will be generated.

val m = Member.syntax("m")
val name = "Alice"
val alice: Option[Member] = withSQL {
  select.from(Member as m).where.eq(m.name, name)
}.map(rs => Member(rs)).single.apply()

In addition, ScalikeJDBC doesn’t force functional programming style like IO monad, or asynchronous programming using Future. It’s easy to learn and much practical.

Conclusion

In my opinion, ScalikeJDBC is the best database access library in Scala for now. It has enough functionality and quality, useful APIs and also active maintainers. I think it will fit almost Scala applications, so I recommend it if you are wondering which library should be used to access RDBMS in Scala just now.