slick maintain updated_at and inserted_at field

Question

Most of my database table models have inserted_at and updated_at timestamp fields which must be updated on creation and update events respectively. Is it possible to do this in a most DRY and transparent way in Slick. These audit columns are also not required in my Table projection (*) and is only used for auditing and debugging. One option was to use custom Sqltype like below.

  val insertedAt = column[Timestamp]("inserted_at", O.SqlType("TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"))
  val updatedAt = column[Timestamp]("updated_at", O.SqlType("TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

But the above code is database specific and H2 doesn't support it.

Answer 1

I suspect this won't be an ideal solution, but you can however always do something like this:

protected def customColumn[T: TypedType](name: String, 
h2Type: SqlType, mySqlType: SqlType)
(implicit driver: BasicDriver): Rep[T] = driver match {
  case H2Driver.api.slickDriver => column[T](name, mySqlType)
  case MySQLDriver.api.slickDriver => column[T](name, h2Type)
  case _ => throw new IllegalArgumentException("Only MySQL and H2 profiles are supported...")
}

This should obviously be in some kind of your common Table class or some trait that you would later mix into your table definitions.

and then...

val insertedAt = customColumn[Timestamp]("inserted_at",
                     O.SqlType("TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"), 
                     O.SqlType("TIMESTAMP... whatever works for MySql"))

val updatedAt = customColumn[Timestamp]("updated_at", 
                     O.SqlType("TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), 
                     O.SqlType("TIMESTAMP... whatever works for MySql"))

Far from perfect, but should do what you need in certain more complicated cases.