None
Explore Blog

Slick For Database Access In Scala

At Movio we try to always use the right tool for the job. That means we deal with a number of different databases - some NoSQL like Cassandra and Titan, some relational databases like MySQL and InfiniDB.

Our primary programming language is Scala, and there are a number of libraries to access relational (SQL) databases. Manuel Bernhardt created a nice overview of the available options. Based on that, and some prototyping, we decided to go for Slick - for a couple of reasons:

  • allows us to do two query styles: typesafe Scala query dsl and plain sql
  • big community: 950 stars, 300+ closed pull requests on github, active usergroup etc.
  • backed by Typesafe

This post describes a few of the basic and advanced concepts, and pitfalls, that we have learnt on the way, as well as general advise on how to use it. Note that we've only been using Slick for a few months.

As mentioned, the ability to choose between the two was one of the selling factors for Slick. Lifted embedding is a nice and simple typesafe query dsl, while plain sql cover's your back where the dsl abstraction layer get in your way with complex queries. We don't have a hard and fast rule, but try and stick to lifted embedding for the most simple queries. When it gets more complicated and performance sensitive, we fall back to plain sql.

Lifted Embedding basics

Here's how you can use lifted embedding. Check out Slicks's good documentation and examples for more complete examples.

In order to get typesafe queries we have to define the types by creating a class for each database table. From then on the compiler can derive all types in all queries you do. Note that it does not validate those types against your database schema.

case class Coffees(id: Int, name: String, supID: Int, price: Double)
class CoffeesT(tag: Tag) extends Table[Coffees](tag, "COFFEES") {
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("COF_NAME")
  def supID = column[Int]("SUP_ID")
  def price = column[Double]("PRICE")
  def * = (id, name, supID, price) <> (
    (Coffees.apply _).tupled, Coffees.unapply)

  //optional foreign key definition
  def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)
}

With that in place you can select data. This will return us all Coffees as case class instances:


coffees.list

Here are a few more examples of select statements:


val coffees = TableQuery[CoffeesT]
coffees.firstOption  // limit 1, wrap in an Option
coffees.drop(10).take(5) // offset 10 limit 5
coffees.filter(_.supID === 101) // where "SUP_ID" = 101
coffees.filter(_.supID inSet Set(1, 2, 3)) // where "SUP_ID" in a set
coffees.sortBy(_.name.desc.nullsFirst) //sort by name desc
coffees.length.run //count

Slick's queries are monads - you can use Scala's for comprehension to nicely align multiple steps, e.g. joining two tables. Note that we don't have to specify the foreign key - it magically finds the foreignKey supplier we defined in the Coffees class, purely based on the types!


for {
  c ← coffees
  s ← suppliers
} yield (c.name, s.name)

Another benefit of having a query dsl is that queries can be handed around and reused:


def coffeeBySupplierExists(supID: Column[Int]) =
  coffees.filter(_.supID === supID).exists

for {
  s ← suppliers if coffeeBySupplierExists(s.id)
} yield s

A typical query is groupBy and sum for which we need two steps. For some reason groupBy returns is an option...


coffees
  .groupBy(_.supID)
  .map { case (sup, coffee) ⇒ (sup, coffee.map(_.price).sum.get) }

Slick does not support distinct - instead we can use groupBy and take the key:


coffees.groupBy(col).map{case (key, _) ⇒ key}

Inserting and updating..

..is very straightforward for the most part. However there are a few things you should know:


coffees += (-99, "Colombian", 101, 7.99, 0, 0)

Why did we specify -99 for the id? Didn't we specify that one as AutoInc, i.e. the database will assign an id?
Good question! The database does indeed assign the id, so it doesn't matter what we pass in as the id. However because the id is a mandatory field in our Coffees case class we have to specify something. Slick ignores it silently because we specified it as AutoInc.

In that same situation we normally want to know the id that the database assigned. Easy, just specify that in your TableQuery and Slick will return the ID of the newly inserted row:


val coffees2 = TableQuery[CoffeesT] returning TableQuery[CoffeesT].map(_.id)
val newId = coffees2 += Coffees(-99, "Colombian", 101, 7.99)

And this is how you can update a single column or the whole row:


coffees.filter(_.name === "Espresso").map(_.price).update(10.49)
coffees.filter(_.id === newCoffee.id).update(newCoffee)

Debugging

If you care about the sql that is being created by your queries (you should!), it's easy to print the resulting query at any time. I often just found myself just in sbt console playing around with queries and printing out the generated statements like this:


query.selectStatement
query.insertStatement
query.deleteStatement
query.updateStatement

All in all

As you can see Slick's lifted embedding uses Scala's typesystem very nicely for our benefit. It's very expressive - queries look just like in Scala's standard collection library, and because it's not just text (like sql), they can be reused and combined.

One of the things I like most about Slick is that it's very predictable. Unlike fully fledged ORMs like Hibernate, it doesn't try to do anything behind the scenes. No multiple levels of caches, no lazy fetching of collections as you work with some proxy classes. A real delight!
All it does is to help you with type safety at compile time, so that you can refactor your code and won't make any silly mistakes, which invariably happen when you write SQL as text.

Plain SQL

SQL is actually a really good and widely understood DSL, and there are situations where an abstraction gets in your way, even if it's as nice as Slick's lifted embedding. Thankfully Slick allows to run plain sql queries and integrates it seamlessly with what we've seen before. We tend to use it for more complex queries where SQL sometimes is more concise and easier to read. Other use cases are if you want to use some database specific features, or you are not happy with the generated SQL.

You can either use string interpolation:


import scala.slick.jdbc.{GetResult, StaticQuery => Q}
import scala.slick.jdbc.JdbcBackend.Database
import Q.interpolation
sql"select * from coffees where name = $name".as[Coffees]
//use sqlu for update/delete
sqlu"delete from coffees where name = $name".first

Or a query builder, e.g. to construct a query that takes the coffee's price (a Double) and returns the coffee's and supplier's names:


val query = Q.query[Double, (String, String)]("""
  select c.name, s.name
  from coffees c, suppliers s
  where c.price < ? and s.id = c.sup_id
  """)
query.list(9.0)

Unfortunately there is no nice way to use a set as a query parameter. One has to use positioned parameters:


val supplierIds = Set(5,6,7)
val queryParams = supplierIds.toSeq.map(_ ⇒ "?").mkString(",")
val query = Q.query[Traversable[Int], String]("""
  select c.name
  from coffees c
  where c.supID IN ($queryParams)
  """)
query.list(supplierIds)

This has the problem that the database cannot optimise the query plan for performance, as we probably submit a different number of parameters each time. It's also quite hairy. Note that we have to transform supplierIds to a seq first in order to get our queryParams, otherwise we would always end up with Set("?"), no matter how many entries supplierIds had.

The session object

Usually you want to set your transaction boundaries at a service level, e.g. in an endpoint, and then call a couple of DAO methods within that one session/transaction. It's handy to pass the session implicitly:


class CoffeesDao {
  def findAll()(implicit session: Session) = Coffees.list
}

// inside CoffeesEndpoint:
db.withSession { implicit session ⇒
  coffeesDao.findAll()
  coffeesDao.somethingElse()
}

Asynchronous programming

The Scala community is big about non-blocking code, and it has many advantages to write asynchronous code. Slick (just like most other alternatives) depends on JDBC though, and that is inherently blocking.
So if you want to run multiple queries in parallel: no problem, just start them in separate Futures. However you won't have performance benefits, JDBC simply blocks a different Thread, not your main Thread of execution.

Please be warned: if you do use Futures be careful to not close over the session object.The session might get closed outside the Future. Instead create the session inside the Future and close it after the Future completes, e.g. using andThen.

Date and time

One of the most important rules of thumb on the JVM is to never use java.util.Date. The most popular alternative is Joda-Time. If you want to use it with Slick, just use Tototoshi's Slick-Joda-Mapper - this allows you to persist DateTime, Instant, LocalDateTime, LocalDate, LocalTime and DateTimeZone with Slick.


import com.github.tototoshi.slick.MySQLJodaSupport._

Enumerations

If you've specified a column to be an enumeration (e.g. restricted to a few allowed strings) in your database, then you might want to map it to a custom type in your Scala code.
Slick allows that, but it does get tricky and doesn't support all cases - e.g. I reported one such setup on the mailing list where I can do Inserts and Updates fine, but no filters...
As a consequence we moved away from mapping custom types, and am now mapping these to just Strings.

That's all for now - I hope this is helpful in your adventures with Slick!

Written by Michael Pollmeie

blog comments powered by Disqus

Subscribe to our newsletter

Keep me
in the loop

Our monthly email update with marketing tips, audience insights and Movio news.