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

Rethink your example. Your table does not accept null as value of column created_at. But your domain model allows that this field can be None which cannot be represented in database in any other way than null. So you if you want slick to generate correct query you have to change type of created_at to DateTime.

Answer 2

createdAt cannot be an Option, you may put there DateTime.now to avoid usage of Option. Also DateTime column mapper is required:

import slick.lifted.MappedTypeMapper
import java.sql.Date
import org.joda.time.DateTime
import slick.lifted.TypeMapper.DateTypeMapper

object DateTimeMapper {
  implicit def date2dt = MappedTypeMapper.base[DateTime, Date] (
    dt => new Date(dt.getMillis),
    date => new DateTime(date)
  )
}

Or you will most probably need it for Timestamp, not Date:

 implicit def dateTime  =
      MappedColumnType.base[DateTime, Timestamp](
        dt => new Timestamp(dt.getMillis),
        ts => new DateTime(ts.getTime)
  )

And if you do not want to deal with DateTime.now, you may do something like this:

def created = column[DateTime]("createdAt", SqlType("timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"))

Additional info here: http://queirozf.com/entries/scala-slick-dealing-with-datetime-timestamp-attributes

Answer 3

If you look at your projection you have to lift created_at, just like you lift your id.

So your projection would become:

def * = (id.?, accountId, name, description, createdAt.?) <> (User.tupled, User.unapply)

Notice the .? after createdAt.

Answer 4

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