Scala: A quick look at the Slick database library

This is an excerpt from the Scala Cookbook. This is Recipe 16.9, “A quick look at the Slick database library.”

When it comes to working with relational databases, you can use the wealth of Java solutions that are available, but other tools are emerging to provide a “Scala way” of working with databases. One of these solutions is a library named Slick, from Lightbend, a company that was founded by the creators of the Scala language. According to their documentation, Slick provides a “modern database query and access library.”

This recipe doesn’t cover Slick in depth because it’s well documented on the Typesafe website, but instead offers a quick look at what Slick offers.

In short, Slick lets you define database table objects in your code like this:

object Authors extends Table[(Int, String, String)]("AUTHORS") {
    def id        = column[Int]("ID", O.PrimaryKey)
    def firstName = column[String]("FIRST_NAME")
    def lastName  = column[String]("LAST_NAME")
    def * = id ~ firstName ~ lastName
}

object Books extends Table[(Int, String)]("BOOKS") {
    def id    = column[Int]("ID", O.PrimaryKey)
    def title = column[String]("TITLE")
    def * = id ~ title
}

object BookAuthors extends Table[(Int, Int, Int)]("BOOK_AUTHORS") {
    def id        = column[Int]("ID", O.PrimaryKey)
    def bookId    = column[Int]("BOOK_ID")
    def authorId  = column[Int]("AUTHOR_ID")
    def bookFk    = foreignKey("BOOK_FK", bookId, Books)(_.id)
    def authorFk  = foreignKey("AUTHOR_FK", authorId, Authors)(_.id)
    def * = id ~ bookId ~ authorId
}

Having defined your tables in Scala code, you can refer to the fields in the tables in a type-safe manner. You can create your database tables using Scala code, like this:

(Books.ddl ++ Authors.ddl ++ BookAuthors.ddl).create

A simple query to retrieve all records from the resulting books database table looks like this:

val q = Query(Books)
q.list.foreach(println)

You can filter queries using a filter method:

val q = Query(Books).filter(_.title.startsWith("Zen"))
q.list.foreach(println)

You can write a join like this:

val q = for {
    b <- Books
    a <- Authors
    ba <- BookAuthors if b.id === ba.bookId && a.id === ba.authorId
} yield (b.title, a.lastName)

q.foreach(println)

Insert, update, and delete expressions follow the same pattern. Because you declared the database design in Scala code, Slick makes working with a database feel like working with collections.

Though I appreciate a good DSL, one thing I always look for in a database library is a way to break out of the library to let me write my own SQL queries, and Slick allows this as well.

As mentioned, the Slick documentation is thorough, so it’s not covered in this chapter. See the Slick website for more information.