- Published on
Slick Recipes
- Authors
- Name
- Roy van Kaathoven
- @razko
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.
Map[String, Any]
Mapping resultset to 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