Play Scala & Slick3, got "Column xx cannot be null" error at a column with "NOT NULL DEFAULT CURRENT_TIMESTAMP"

Question

I'm creating a web app using Play Framework 2.6 with slick 3.2.1. When I try to insert a record into the table "USER", which has created_at column with "NOT NULL DEFAULT CURRENT_TIMESTAMP" (I'm using MySQL), the database throws an error "Column 'created_at' cannot be null".

I know that the SQL generated by slick is wrong. The statement is trying to insert null into the created_at column. What is the proper way to let slick generate SQL that doesn't contain created_at column?

Excerpt of the scala code.

import org.joda.time.DateTime

case class User(
    id: Option[Long],
    accountId: Long,
    name: String,
    description: Option[String] = None,
    createdAt: Option[DateTime: = None,
)

class UserTable(tag: Tag) extends Table[User](tag, "user") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def accountId = column[Long]("account_id")
  def name = column[String]("name")
  def description = column[Option[String]]("description")
  def createdAt = column[Option[DateTime]]("created_at")
  def * = (id.?, accountId, name, description, createdAt) <> (User.tupled, User.unapply)
}

object Users extends TableQuery(new UserTable(_)) {
}

val user = User(None, accountId, name, description)
val insert = for {
  newId <- (Users returning Users.map(_.id)) += user
} yield newId

db.run(insert)

generated SQL

[debug] s.j.J.statement - Preparing insert statement (returning: id): insert into `user` (`account_id`,`name`,`description`,`created_at`)  values (?,?,?,?)

[debug] s.j.J.parameter - /------+--------+---------+-----------\
[debug] s.j.J.parameter - | 1    | 2      | 3       | 4         |
[debug] s.j.J.parameter - | Long | String | VARCHAR | TIMESTAMP |
[debug] s.j.J.parameter - |------+--------+---------+-----------|
[debug] s.j.J.parameter - | 1    | user01 | NULL    | NULL      |
[debug] s.j.J.parameter - \------+--------+---------+-----------/

Answer 1

I have found two ways to work (hack) around this issue (using Slick 3.2.3). Fortunately, for my use case, I am not using Slick to create the tables or generate the table classes, so I don't really care if the schema it would generate is valid.

Assume we have a simple user model/table:

case class User(id: Long, name: String, createdAt: Timestamp, updatedAt: Timestamp)

class UsersDAO(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def createdAt = column[Timestamp]("created_at", O.AutoInc)
  def updatedAt = column[Timestamp]("updated_at", O.AutoInc)

  override def * = (id, name, createdAt, updatedAt) <>
    (User.tupled, User.unapply)
}

Option 1: Use a custom insert method

object UsersDAO extends TableQuery(new UsersDAO(_)) {
  def create(u: User) = this map { c =>
    (c.name) // only include columns you want to send
  } returning this.map(_.id) += (u.name)
}

Option 2: Mark the fields as O.AutoInc

If you mark the createdAt and updatedAt columns with O.AutoInc (as I did above), you can simply use the += syntax:

object UsersDAO extends TableQuery(new UsersDAO(_)) {
  def create(u: User) = this returning this.map(_.id) += user
}

There seems to be several issues currently open related to this. Hopefully once they're resolved, there will be a better way. https://github.com/slick/slick/issues/1448 https://github.com/slick/slick/pull/1533