User-Defined Features

This chapter describes how to use custom data types and database functions in the Lifted Embedding API.

Scala Database functions

If your database system supports a scalar function that is not available as a method in Slick you can define it as a SimpleFunction. There are predefined methods for creating unary, binary and ternary functions with fixed parameter and return types.

// H2 has a day_of_week() function which extracts the day of week from a timestamp
val dayOfWeek = SimpleFunction.unary[Date, Int]("day_of_week")

// Use the lifted function in a query to group by day of week
val q1 = for {
  (dow, q) <- salesPerDay.map(s => (dayOfWeek(s.day), s.count)).groupBy(_._1)
} yield (dow, q.map(_._2).sum)

If you need more flexibility regarding the types (e.g. for varargs, polymorphic functions, or to support Option and non-Option types in a single function), you can use SimpleFunction.apply to get an untyped instance and write your own wrapper function with the proper type-checking:

def dayOfWeek2(c: Column[Date]) =
  SimpleFunction[Int]("day_of_week").apply(Seq(c))

SimpleBinaryOperator and SimpleLiteral work in a similar way. For even more flexibility (e.g. function-like expressions with unusual syntax), you can use SimpleExpression.

Other Database functions and stored procedures

For database functions that return complete tables or stored procedures please use Plain SQL Queries. Stored procedures that return multiple result sets are currently not supported.

Scalar Types

If you need a custom column type you can implement ColumnType. The most common scenario is mapping an application-specific type to an already supported type in the database. This can be done much simpler by using MappedColumnType which takes care of all the boilerplate. It comes with the usual import from the driver. Do not import it from the JdbcDriver singleton object.

// An algebraic data type for booleans
sealed trait Bool
case object True extends Bool
case object False extends Bool

// And a ColumnType that maps it to Int values 1 and 0
implicit val boolColumnType = MappedColumnType.base[Bool, Int](
  { b => if(b == True) 1 else 0 },    // map Bool to Int
  { i => if(i == 1) True else False } // map Int to Bool
)

// You can now use Bool like any built-in column type (in tables, queries, etc.)

You can also subclass MappedJdbcType for a bit more flexibility.

If you have a wrapper class (which can optionally be a case class and/or value class) for an underlying value of some supported type, you can make it extend MappedTo to get a macro-generated implicit ColumnType for free. Such wrapper classes are commonly used for type-safe table-specific primary key types:

// A custom ID type for a table
case class MyID(value: Long) extends MappedTo[Long]

// Use it directly for this table's ID -- No extra boilerplate needed
class MyTable(tag: Tag) extends Table[(MyID, String)](tag, "MY_TABLE") {
  def id = column[MyID]("ID")
  def data = column[String]("DATA")
  def * = (id, data)
}

Record Types

Record types are data structures containing a statically known number of components with individually declared types. Out of the box, Slick supports Scala tuples (up to arity 22) and Slick’s own experimental HList implementation (without any size limit, but currently suffering from long compilation times for arities > 25). Record types can be nested and mixed arbitrarily in Slick.

If you need more flexibility, you can add support for your own by defining an implicit Shape definition. Here is an example for a type Pair:

// A custom record class
case class Pair[A, B](a: A, b: B)

Shape implementations for record types extend MappedScalaProductShape. They are are generally very simple but they require some boilerplate for all the types involved. A MappedScalaProductShape takes a sequence of Shapes for its elements and provides the operations buildValue (for creating an instance of the record type given its elements) and copy (for creating a copy of this Shape with new element Shapes):

// A Shape implementation for Pair
final class PairShape[Level <: ShapeLevel, M <: Pair[_,_], U <: Pair[_,_], P <: Pair[_,_]](
  val shapes: Seq[Shape[_, _, _, _]])
extends MappedScalaProductShape[Level, Pair[_,_], M, U, P] {
  def buildValue(elems: IndexedSeq[Any]) = Pair(elems(0), elems(1))
  def copy(shapes: Seq[Shape[_, _, _, _]]) = new PairShape(shapes)
}

implicit def pairShape[Level <: ShapeLevel, M1, M2, U1, U2, P1, P2](
  implicit s1: Shape[_ <: Level, M1, U1, P1], s2: Shape[_ <: Level, M2, U2, P2]
) = new PairShape[Level, Pair[M1, M2], Pair[U1, U2], Pair[P1, P2]](Seq(s1, s2))

The implicit method pairShape in this example provides a Shape for a Pair of two element types whenever Shapes for the inidividual element types are available.

With these definitions in place, we can use the Pair record type in every location in Slick where a tuple or HList would be acceptable:

// Use it in a table definition
class A(tag: Tag) extends Table[Pair[Int, String]](tag, "shape_a") {
  def id = column[Int]("id", O.PrimaryKey)
  def s = column[String]("s")
  def * = Pair(id, s)
}
val as = TableQuery[A]
as.ddl.create

// Insert data with the custom shape
as += Pair(1, "a")
as += Pair(2, "c")
as += Pair(3, "b")

// Use it for returning data from a query
val q2 = as
  .map { case a => Pair(a.id, (a.s ++ a.s)) }
  .filter { case Pair(id, _) => id =!= 1 }
  .sortBy { case Pair(_, ss) => ss }
  .map { case Pair(id, ss) => Pair(id, Pair(42 , ss)) }