Implementing GCM server with Scala and Akka

Working with Slick is a breeze when doing standard data manipulation. However when edge cases are popping up it is hard to find answers in the documentation.

A lot of tips are hidden in Github Issues and answers given in the Gitter channels

In this blog i share some of the things i use in most of my projects which aren't obvious from the documentation.

Mapping resultset to `Map[String, Any]`

Slick supports executing plain SQL queries. The result of the query has to be mapped to a tuple.

In this example it maps both the name of the coffee and the company to a tuple (String, String)

sql"""select c.name, s.name
      from coffees c, suppliers s
      where c.price < $price and s.id = c.sup_id""".as[(String, String)]

When the query selects 20+ columns you might want to map it to a case class or just a simple Map[String, Any].

There is no standard way to map to a Map[String, Any], but it can be done by creating a implicit GetResult[Map[String, Any]].

object QueryUtils {
  implicit val resultAsAnyMap = GetResult[Map[String, Any]] ( prs =>
    (1 to prs.numColumns).map(_ =>
      prs.rs.getMetaData.getColumnName(prs.currentPos + 1) -> prs.rs.getObject(prs.currentPos + 1)
    ).toMap
  )
}

When QueryUtils is imported then the query can be mapped as follows:

import QueryUtils._

val coffee = sql"""select * from coffees c where id = $id""".as[Map[String Any]].headOption

Dynamic schema names

Sometimes the same model can be mapped to multiple tables or the database- or tablename has to be specified dynamically. For example if you defined a different database for every tenant in a multi-tenant architecture. Or when a new database is created and destroyed during unittests.

The below example is taken from the Slick Documentation

// Model
case class User(id: Option[Int], first: String, last: String)

// Table Mapping
class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def first = column[String]("first")
  def last = column[String]("last")
  def * = (id.?, first, last) <> (User.tupled, User.unapply)
}

// QueryBuilder
val users = TableQuery[Users]

The Users table mapping is mapped to the users table as specified in the Table[User](tag, "users") contructor. The table can be made dynamic by making a few changes.

The database name and table name have to be added to the Users constructor and passed to the base class constructor.

class Users(tag: Tag, databaseName: String = "crm", tableName: String = "users") extends Table[User](tag, Some(databaseName), tableName) {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def first = column[String]("first")
  def last = column[String]("last")
  def * = (id.?, first, last) <> (User.tupled, User.unapply)
}

The table and database name can now be specified.

val users = TableQuery[Users]((tag: Tag) => new Users(tag, "crm_2", "users_2")

Working around the 22 tuple limit using Slickless

When mapping columns to a case class you need to use a tuple for the mapping. For example:

case class User(id: Option[Int], first: String, last: String)

// Table Mapping
class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def first = column[String]("first")
  def last = column[String]("last")
  def * = (id.?, first, last) <> (User.tupled, User.unapply)
}

When there are more than 22 columns then you run into the 22 tuple limit. The Slickless library provides a method to define the mapping using Shapeless.

import slick.jdbc.H2Profile.api._
import shapeless.{ HList, ::, HNil, Generic }
import slickless._

case class User(id: Long, email: String)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id    = column[Long]( "id", O.PrimaryKey, O.AutoInc )
  def email = column[String]("email")

  def * = (id :: email :: HNil).mappedWith(Generic[User])
}

With this method you no longer run into the tuple limit and you can map as many columns as you like

Mapping resultset to Map[String, Any]

Dynamic schema names

Working around the 22 tuple limit using Slickless

Mapping resultset to `Map[String, Any]`