Mapping Slick query results to case classes 1.0.1
This tutorial shows you how to handle database query results in a Slick application by building on the previous Slick tutorials.
By default, Slick query results are collections of Scala tuples, whose structure corresponds to a Slick projection. When each row is more than a single value, it is more natural to use domain model case classes than tuples. To to this, add a mapping to the table definition’s default projection, in the form of two functions that convert between tuples and case class instances.
Summary
Perform the following tasks to learn how to map query results.
-
Define a table mapping to a tuple
-
Define a default mapping to a case class
-
Mapping multiple columns to custom types
-
Use implicit conversions for custom types (or not)
Source code: https://github.com/lunatech-labs/play-slick-examples.
Defining a table mapping to a tuple
To continue from previous tutorials, start with the table mapping from Getting started with Play and Slick. In your Play application, this is a database model in:
// app/models/database/Cocktails.scala
package models.database
import play.api.db.slick.Config.driver.simple._
class Cocktails extends Table[(Long, String)]("COCKTAIL") {
def id = column[Long]("ID")
def name = column[String]("NAME")
def * = id ~ name
}
This class defines a mapping to the COCKTAIL
database table, with two
columns and a projection called *
.
Use this table mapping to define a finder' method in your application’s
model layer, as explained in
Executing
database queries with Slick. In `app/models/Cocktail.scala
:
// app/models/Cocktail.scala
package models
import models.database.Cocktails
import play.api.Play.current
import play.api.db.slick.Config.driver.simple._
import play.api.db.slick.DB
import scala.slick.session.Session
object Cocktail {
val table = new Cocktails
def find: List[(Long, String)] = DB.withSession { implicit session: Session =>
Query(table).list
}
}
The query results a list of (Long, String)
pairs (tuples). Now you’re
ready for the next step.
Defining a default mapping to a case class
Working with tuples is not convenient, and results in code that is hard
to maintain, because it is an anonymous structure. Instead, use add the
following case class to your model, so you can refer to id
and name
by name.
Add the case class, before the object Cocktail
declaration:
// app/models/Cocktail.scala
case class Cocktail(id: Option[Long], name: String)
The id
has to be an Option
type so you can create instances that do
not have an ID yet, before you insert them into the database.
Slick will return instances of this case class from queries if you
change the table mapping. To do this, first change the table superclass
type from Table[(Long, String)]
to Table[models.Cocktail]
. Next, use
the <>
operator to add a mapping to the table’s default projection:
// app/models/database/Cocktails.scala
def * = id.? ~ name <> (Cocktail.apply _, Cocktail.unapply _)
Note that you have to change the projection’s first column from id
to
id.?
, wrapping the column with Slick’s option type mapper to match the
Option[Long]
type in the case class.
The mapping’s first function converts a (Option[Long], String)
tuple
from the projection to a Cocktail
instance. The second function
performs the opposite conversion,
Cocktail ⇒ Option[(Option[Long], String)]
. In this case, you can use
the case class’ apply
and unapply
functions for the mapping, because
the case class structure matches the column types, in order.
The complete table mapping is now:
// app/models/database/Cocktails.scala
package models.database
import play.api.db.slick.Config.driver.simple._
import models.Cocktail
class Cocktails extends Table[Cocktail]("COCKTAIL") {
def id = column[Long]("ID")
def name = column[String]("NAME")
def * = id.? ~ name <> (Cocktail.apply _, Cocktail.unapply _)
}
In general, it is so convenient to be able to use case class apply
and
unapply
functions (most of the time) that it is worth using this as a
starting point, even if you ultimately want a different case class
structure in your domain model - without IDs and with foreign keys
replaced by instances.
Now change the finder return type to Cocktail
:
// app/models/Cocktail.scala
def find: List[Cocktail] = DB.withSession { implicit session: Session =>
Query(table).list
}
Mapping multiple columns to custom types
Sometimes you don’t want to map column values directly, because multiple values represent a custom type. For example, add price information to the cocktails table (or in a separate table if you know how to do joins):
// app/models/database/Cocktails.scala
def priceCurrency = column[String]("PRICE_CURRENCY")
def priceAmount = column[BigDecimal]("PRICE_AMOUNT", O.DBType("DECIMAL(13,3)"))
To support multiple currencies, these two columns will store a monetary
value as a three-letter ISO 4217
currency code and a decimal amount, with a MySQL custom column type. You
could model these as two separate properties in the Cocktail
case
class, but what you really want to do is use
Joda Money.
Add "org.joda" % "joda-money" % "0.9"
to the application dependencies
in project/Build.scala
, and change the case class:
// app/models/Cocktail.scala
import org.joda.money.Money
case class Cocktail(id: Option[Long], name: String, price: Money)
To make this work, you have to change the projection mapping functions
to convert between the String
and BigDecimal
values and
org.joda.money.Money
instances. You can do this inline in the
projection declaration:
// app/models/database/Cocktails.scala
def * = id.? ~ name ~ priceCurrency ~ priceAmount <> (
c => Cocktail(c._1, c._2, money(c._3, c._4)),
(c: Cocktail) => {
Some((c.id, c.name, c.price.getCurrencyUnit.getCode, c.price.getAmount))
})
private def money(currencyCode: String, amount: BigDecimal): Money = {
val currency = CurrencyUnit.of(currencyCode)
Money.of(currency, amount.bigDecimal, RoundingMode.DOWN)
}
This is already a lot less readable, even with this few database columns. In practice, you should probably extract the functions to ‘map’ and ‘un-map’ a table row:
// app/models/database/Cocktails.scala
def * = id.? ~ name ~ priceCurrency ~ priceAmount <> (mapRow _, unMapRow _)
private def mapRow(id: Option[Long], name: String, currency: String,
amount: BigDecimal): Cocktail = {
Cocktail(id, name, money(currency, amount))
}
private def unMapRow(cocktail: Cocktail) = {
val currency = cocktail.price.getCurrencyUnit.getCode
val amount: BigDecimal = cocktail.price.getAmount
val tuple = (cocktail.id, cocktail.name, currency, amount)
Some(tuple)
}
Note that the mapRow
and unMapRow
functions are called map
and
comap
in the Slick source code.
Using implicit conversions for custom types
You may want to use an implicit conversion instead of
money(currency, amount)
. For example, you could just write
(currency, amount)
or currency → amount
if you define the following
conversion from the tuple to Money
:
// app/models/database/Cocktails.scala
implicit def tuple2Money(money: (String, BigDecimal)): Money = {
val currency = CurrencyUnit.of(money._1)
Money.of(currency, money._2.bigDecimal, RoundingMode.DOWN)
}
Whether you do this is a matter of personal taste, and whether you think using Scala implicit declarations like this is better than having maintainable code.
Next steps
Now you can map query results to case classes, you can:
-
define join queries
-
define aggregate and group-by queries
-
insert, update and delete data.