Scala Slick: Dealing with Datetime/Timestamp Attributes

Scala Slick: Dealing with Datetime/Timestamp Attributes

Last updated:

Dynamic Default Values

The problem

It's very common to make timestamp and datetime have defaults that evaluate to the current time (at the time of script execution).

Since Slick associates column types to Scala/Java types, it's not (as far as I know) possible to do this in Java, because if you write something like this

def createdAt = {
  val currDate = new UtilDate
  //this is not the correct answer, see below for the correct solution
  column[Timestamp]("createdAt", O.NotNull, O.Default(new Timestamp(currDate.getTime())))

Then each time your code runs it'll evaluate to a different static timestamp, which will amount to different DDL code each time it runs (and, if you're using Slick in Play Framework, will prompt you do perform evolutions ad infiinitum because each time it'll be different).

The answer

Use Timestamp, set a raw DBType and include the defaults in the type (might be RDBMS-specific though)

//tried this in postgres but it should work with other DBMSs too.
def createdAt = column[Timestamp]("createdAt", O.NotNull, O.DBType("timestamp default now()"))

This is the SQL it'll generate:

"createdAt" timestamp default now() NOT NULL

Update for Slick 3

Some things have changed in Slick 3, in comparison to Slick 2.

Lots of additional examples on Slick 3 by yours truly

As far as dynamic default values go, there is no more DBType, as Sunil Sandhu has correctly pointed out in the comments, but we can still do it, using SQLType:

All code can be found in this github repo

import slick.driver.MySQLDriver.api._
import java.sql.Timestamp

// must import this as well
import slick.profile.SqlProfile.ColumnOption.SqlType

case class User(id: Long, name: String, email: Option[String] = None, created: Timestamp, updated: Timestamp)

class Users(tag: Tag) extends Table[User](tag, "users") {

  def id = column[Long]("id", O.PrimaryKey)

  def name = column[String]("name")

  def email = column[Option[String]]("email")

  // this feature is particular to MySQL so it may not work 
  // in other rdbms backends
  def created = column[Timestamp]("created", SqlType("timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"))

  def updated = column[Timestamp]("updated", SqlType("timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"))

  def * = (id, name, email, created, updated) <>(User.tupled, User.unapply)


Dialogue & Discussion