Introduction to Slick – plain SQL usage

Kfir Bloch February 24th 2013 Scala

In this post i will cover usage of the Slick library, which provides a Scala-centric API over JDBC.
Slick offers 3 flavors for querying the DB – I will focus on the plain SQL flavor.

Note: Slick is only compatible with Scala 2.10

Maven dependency:


DB table

CREATE TABLE animals (
  id varchar(50)  NOT NULL,
  name varchar(256)  NOT NULL,
  date_created_nano bigint(20) not NULL,

We will represent the Animal domain object using the following case class:

case class Animal(id: UUID, name: String, dateTime: DateTime)

Note: that the first parameter is complex type (UUID) and the 3rd parameter is also Complex type (Joda DateTime)

We will hide all DB operations using a DAO as described by the following Trait:

<pre>trait AnimalDao {
  def allAnimals: Seq[Animal]

  def animalById(id: String): Option[Animal]

  def deleteAnimalById(id: String): Int

And the implementation:

import scala.slick.session.Database
import Database.threadLocalSession
import slick.jdbc.{StaticQuery => Q, GetResult}
import javax.sql.DataSource
import Q.interpolation
import org.joda.time.DateTime
import java.util.UUID

class DefaultAnimalDao(dataSource: DataSource) extends AnimalDao {

  lazy val db = Database.forDataSource(dataSource)

  def allAnimals: Seq[Animal] = {
    db.withSession {
      Q.queryNA[Animal]("select * from animals").list

  def animalById(id: String): Option[Animal] = {
      sql"select * from animals where id = $id".as[Animal].firstOption

  def deleteAnimalById(id: String): Int = {
      sqlu"delete from animals where id = $id".first

  implicit val getAnimalsResult = GetResult(r => Animal(UUID.fromString(r.nextString), r.nextString, new DateTime(r.nextLong())))

  • The DAO is dependent on a javax.sql.DataSource
  • In the second line we create a Slick Database object using the provided DataSource
  • allAnimals() – uses the queryNA[Animal] function and returns a Seq of Animals.
  • animalById() – uses the sql String interpolator. The .as[Animal] method tells the function to return an instance of Animal and .firstOption() returns only the first result, wrapped in an Option
  • deleteAnimalById() – uses the sqlu String interpolator for update/insert – the result is always an Int representing the number of affected trows
  • the implicit val getAnimalResult is very important. It tells Slick how to create the object and encapsulates the logic constructing the complex types mentioned earlier .

Kfir Bloch

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>