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.
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)
}