What is Slick?

Slick is a library for Scala which allows creating and executing database queries. For working in Scala+Play Framework bunch, Typesafe explicitly recommends using Slick. The idea is that the developer works with the stored data as if he uses Scala-collections, which is a big advantage for Scala fans. You just write the code in Scala without distracting on writing SQL-code.

Slick 3

Slick 3 was released at the end of April 2015. The list of innovations and new features in Slick 3 can be viewed on the official website. In this article we will focus on the differences that we had faced in the process of migrating our project from Slick 2 to Slick 3.

Are there any changes in a DSL provided for defining tables and relations between them? We have only noticed that it is not necessary anymore to set such ColumnOptions as NotNull or Nullable for a field, since the allowing of null values is determined by the field type Option or non-Option. As for the rest, the description schema of table models remains the same. The example of the table with the user's data:

case class User(id: Option[Long], login: String, name: String, email: String)

class UsersTable(tag: Tag) extends Table[User](tag, "users") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def login = column[String]("login")

  def ucLogin = index("uc_login", login, unique = true)

  def name = column[String]("name")

  def email = column[String]("email")

  def * = (id.?, login, name, email) <> (User.tupled, User.unapply)
}

Along with the table model the TableQuery value (which represents the actual database table) is also needed. There is no difference at this point:

val users = TableQuery[UsersTable]

We are getting to an interesting part in our comparison of Slick 2 and Slick 3. Starting from version 3, there is an entirely new Database I/O Action API for compiling and executing of database actions. Execution has become asynchronous from now on. The old API (Invoker and Executor for blocking execution of calls to the database) is deprecated. From version 3.0, Slick becomes known as a.k.a "Reactive Slick".

All database operations, such as getting a query result, inserting data, creating a table or anything else, are represented by a DBIOAction instance. DBIOAction is parameterized by the result type that is produced when DBIOAction is executed.

The DBIOAction data can be combined of smaller actions, which are always executed strictly sequentially in a single database session. DBIOActions can be executed either with the goal of producing a fully materialized result or streaming data back from the database.

Let's move on to the examples.

The query for selecting data

For example, we need to get the users by login. In Slick 2 such query looks like this:

def getByLogin(login: String): Option[User] = DB.withSession { implicit session =>
    users.filter(_.login === login).firstOption
 }

In Slick 2 the queries are executed using the methods specified in the Invoker trait. Each method that executes the query takes Session as an implicit parameter. In such case this is a firstOption method but you can always specify it explicitly (like this: firstOption(session)).

In Slick 3 this query looks as follows:

def getByLogin(login: String): Future[Option[User]] = {
    val query = users.filter(_.login === login)
    val action = query.result.headOption
    db.run(action)
 }

As you can see in the example with Slick 3, the method is divided into 3 parts (query, action, result):

• actually the Query which defines the structure of a call to database;
• getting Action. This Action can be performed directly or combined with other Actions;
• executing Action and returning Future with the result of query.

In the new version of Slick, the first and firstOption methods were renamed to head and headOption respectively, which is consistent with the names in the Scala Collection API.

The query for adding a record

In terms of syntax, Slick 2 vs Slick 3 differences are minimal.

Slick 2:

  def save(user: User): Long = DB.withSession { implicit session =>
    (users returning users.map(_.id)) += user

Slick 3:

 def save(user: User): Future[Long] = {
    val action = (users returning users.map(_.id)) += user
    db.run(action)
  }

The query for adding related records

Suppose we want to add the user record and connect it with his profile on Facebook record. The operation consists of two parts: at first we save the user and then we save the Facebook profile data which is related to previously saved account.

How it was made in Slick 2:

 def create(login: String, userName: String)(implicit session: Session): Long = { 
    val userId = userDao.create(None, login, userName)
    (facebookUsers returning facebookUsers.map(_.id)) += FacebookUser(None, userId, userName)
  }

In this example, the session is created in service, which calls the given method, passed as an implicit parameter and used in both operations of adding data. It allows you to perform two operations in a single transaction. If the execution of one of them fails, all the committed actions will be rollbacked. Thus, the database will remain in a consistent state.

Implementation in Slick3:

def create(id: String, userName: String): Future[Long] = {
    val action = (for {
      userId <- (users returning users.map(_.id)) += User(None, login, userName)
      facebookUserId <- (facebookUsers returning facebookUsers.map(_.id)) += FacebookUser(None, userId, userName)
    } yield facebookUserId).transactionally

    db.run(action)
  }

In this query, DBIOAction, consisting of two smaller actions, is performed. When executing the query, a transactionally combinator is applied which forces the use of a transaction. This ensures that the execution of the external DBIOAction will succeed or fail in atomic form. In other words, if the user's record was saved successfully but saving information about Facebook profile has failed, both operations will not be executed and the external DBIOAction will be performed unsuccessfully. The actual transaction will be rolled back at the database level and the data will not be saved.

The query for unique data selection

Let's suppose that we need to select the user names without duplications. The first thing that comes to mind - most likely there should be the distinct method in Slick, which is compiled into the DISTINCT operator of SQL query. However, Slick 2 does not have this method and it leads to writing such code:

 def listUserName: Seq[String] = DB.withSession { implicit session =>
    users.groupBy(_.name).map(_._1).list
  }

As a result of this query, all data from the table is grabbed and distinct is executed on the client code side by the map operation.

In Slick 3.1, the distinct and dictinctOn methods were introduced and the implementation of the same task looks the following way:

 def listUserName: Future[Seq[String]] = {
    val query = users.map(_.name ).distinct
    val action = query.result
    db.run(action)
  }

We have found a bug when using distinct.lenght and distinctOn(<field>).length to get the number of unique entries for H2 base. On this issue, a bugreport was created on GitHub Slick.

The join operators

Among the innovations in Slick 3 worth mentioning is also a fact that the old methods leftJoin, rightJoin, outerJoin, innerJoin were replaced by joinLeft, joinRight, joinFull, join respectively. The old join operators were not able to handle the null values correctly and required complicated mappings in code. This is no longer necessary with the new outer join operators because they lift one (left or right outer join) or both sides (full outer join) of the join into an Option. The join method is used only for inner join operations now.

In our project we do not use these operators as we consider more preferable to combine queries in a for-statement with the following combining of the necessary fields.

def listByOwner(ownerId: Long): Future[Seq[(Computer, Item)]] = {
    val query = for {
      i <- Items if i.itemHolder_fk === ownerId
      ci <- CompositeItems if ci.item_fk === i.id
      c <- Computers if c.id === ci.itemHolder_fk
    } yield (c, i)
    val action = query.result
    db.run(action)
  }

The query compiler

Also, according to the Slick developers, they have implemented a new query compiler in version 3.1. Its main purpose is to avoid using subqueries wherever it is possible. This improvement is meant to optimize the execution of queries to database.

The generated queries can be viewed in the console if you enable this option in the config:

logger.scala.slick.jdbc.JdbcBackend.statement=DEBUG

Even though the generated queries may look scary (this is a common problem of all mappers) but they are nonetheless amenable to separation and analysis. The queries are executed quite fast, everything depends on the complexity and sophistication of the query. During the execution of request it would be nice just to write in the log.

If you are not satisfied with the speed of a particular query, you can always write it manually in Slick:

def insertUser(user: User): DBIO[Long] = 
 sqlu"insert into users values(${user.id.get}, ${user.login}, ${user.name}, ${user.email})"

Conclusion

Slick provides developers with a plethora of helpful features that make writing queries a breeze. The community is also friendly and the documentation is well-organized, making it easy to get started and seek help when needed.

Furthermore, when working with a Scala+Play Framework, integrating the Slick library makes complete sense. It seamlessly integrates with the application structure, keeping database logic separate and making it easier to work with stored data as if they were Scala collections.

Ultimately, the decision to use Slick in your application development is up to you. However, I highly recommend getting acquainted with this library regardless. Its numerous benefits can enhance your productivity and streamline the development process, resulting in a more efficient and effective workflow.