Slick-pg Enum + Hstore example

While creating my application with Play Framework 2.3, I've spent lots of time for searching some things that seemed to be easy at first glance. One of such a thing was the use of PostgreSQL specific types: hstore and enums. I found a great library for that. Slick-pg provides good covers for the most of the PostgreSQL specific data types. However, I bumped into some problems using enums, so I provide here my example of using it (+ hstore + date). Hope, it'll be helpfull.



build.sbt (Add all necessary dependencies)

libraryDependencies ++= Seq(
  cache,
  ws,
  "org.postgresql" % "postgresql" % "9.3-1100-jdbc41",
  "com.typesafe.slick" %% "slick" % "2.1.0",
  "com.typesafe.play" %% "play-slick" % "0.8.0",
  "com.github.tminglei" %% "slick-pg" % "0.6.3"
)

First of all you need to enable Hstore and create a enum type in your database.

CREATE EXTENSION IF NOT EXISTS hstore;
CREATE TYPE pg_enum_type AS ENUM ('CREATED', 'JOINED', 'CLOSED');

app.utils.helpers.PostgresDriverPlus.scala (extend standart PostgreSQL driver with slick-pg)

package utils.helpers

import com.github.tminglei.slickpg._
import slick.driver.PostgresDriver
import models.MyTableStatus

trait WithPostgresDriver {
  val driver: PostgresDriverPlus
}

trait PostgresDriverPlus extends PostgresDriver 
                            with PgHStoreSupport
                            with PgEnumSupport
                            with PgDateSupport{

  override lazy val Implicit = new ImplicitsPlus {}
  override val simple = new SimpleQLPlus {}
  
  trait EnumImplicits {
    implicit val myTableStatusTypeMapper = createEnumJdbcType("pg_enum_type", MyTableStatus )
    implicit val myTableStatusTypeListMapper = createEnumListJdbcType("pg_enum_type", MyTableStatus )
    
    implicit val myTableStatusExtensionMethodsBuilder = createEnumColumnExtensionMethodsBuilder(MyTableStatus)
    
  }

  trait ImplicitsPlus extends Implicits 
                         with HStoreImplicits
                         with DateTimeImplicits
                         with EnumImplicits
  
  trait SimpleQLPlus extends SimpleQL with ImplicitsPlus
}

object PostgresDriverPlus extends PostgresDriverPlus

app.models.MyTable.scala (all type mappers are here)

package models

import play.api.Play.current
import play.api.db.slick.{ DB, Session }
import utils.helpers.PostgresDriverPlus.simple._
import java.sql.{Timestamp, Date}
import scala.Enumeration

object MyTableStatus extends Enumeration {
  type MyTableStatus = Value
  val CREATED = Value("CREATED")
  val JOINED = Value("JOINED")
  val CLOSED = Value("CLOSED")
}

case class MyTable(id: Option[Int], date_start: Timestamp, status: MyTableStatus.MyTableStatus, date_finish: Date, spec: Map[String, String])

class MyTables(tag: Tag) extends Table[MyTable](tag, "mytable") /*Don't forget to be "mytable" in small letters!!! */ {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def date_start = column[Timestamp]("date_start", O.NotNull)
  def status = column[MyTableStatus.MyTableStatus]("status", O.NotNull)
  def date_finish = column[Date]("date_finish", O.NotNull)
  def spec = column[Map[String, String]]("spec", O.NotNull) //hstore is mapped to Map[String, String]

  def * = (id.?, date_start, status, date_finish, spec) <> (MyTable.tupled, MyTable.unapply)
}

object MyTables extends TableQuery(new MyTables(_)) {

  def findById(id: Int): Option[MyTable] = {
    DB withSession {
      implicit session: Session =>
        MyTables.filter(_.id === id).firstOption
    }
  }

  def all = {
    DB withSession {
      implicit session: Session =>
        MyTables.run
    }
  }

  def create(myTable: MyTable) = {
    DB withSession {
      implicit session: Session =>
        MyTables.insert(myTable)
    }
  }
}

So now you can use your model, for example, to insert data. You can run the following with "test-only InsertTestData" command. 

test.InsertTestData.scala

import org.specs2.mutable.Specification
import org.specs2.runner._
import org.junit.runner._
import play.api.test._
import play.api.test.Helpers._
import play.api.Play.current
import models._
import scala.collection.immutable.HashMap

@RunWith(classOf[JUnitRunner])
class InsertTestData extends Specification {
  
  "MyTables insert" should {
    "insert test data if MyTable model is empty" in {
      running(FakeApplication()) {
        if (MyTables.all.isEmpty) {
          import java.sql.Timestamp
          import java.text.SimpleDateFormat
          
          val sdf = new SimpleDateFormat("dd-M-yyyy")
          val joinBefore = "17-12-2014"
          val dateExpiry = "31-12-2014"
                       
          MyTables.create(MyTable(None, new Timestamp((new java.util.Date).getTime()),  MyTableStatus.CREATED,
              new java.sql.Date(sdf.parse(dateExpiry).getTime()), 
              HashMap("type" -> "SuperType", "bla" -> "alb", "qwerty" -> "asdf")
              ))
        }
        MyTables.all must not be empty
      }
    }
  }
}