Update Scala Slick rows with optional columns

Question

I have a Slick Table with multiple columns, and I want to update some of those columns based on user input. My table looks like this:

class Users(_tableTag: Tag) extends Table[User](_tableTag, "users") {
  def * = (id, name, email, phone, passwordHash, createdAt) <> (User.tupled, User.unapply)

  val id: Rep[Long] = column[Long]("id", O.AutoInc, O.PrimaryKey)
  val name: Rep[String] = column[String]("name")
  val email: Rep[String] = column[String]("email")
  val phone: Rep[String] = column[String]("phone")
  val passwordHash: Rep[String] = column[String]("password_hash")
  val createdAt: Rep[java.sql.Timestamp] = column[java.sql.Timestamp]
      ("created_at", SqlType("TIMESTAMP NOT NULL DEFAULT current_timestamp"))
}

The User case class looks like this:

case class User(
  id: Long,
  name: String,
  email: String,
  phone: String,
  passwordHash: String,
  createdAt: Timestamp)

I am using slick with Play, so I thought I'd build an update case class based on user-provided optional json fields like this:

case class UserUpdate(
  id: Long,
  name: Option[String],
  email: Option[String],
  phone: Option[String],
  passwordHash: Option[String])

How can I make a slick 3.1 query that updates the fields which are of type Some(value) in this update object?

You could fetch old oldUser: User record, and do UserUpdate.field.getOrElse(oldUser.field) — insan-e
@insan-e yes, that would work but take 2 queries (a select and then an update). I'd like to do it in a single update query. — Jacob Lyles

Chetan Kumar Meena Chetan Kumar Meena · Accepted Answer · 2017-04-12T00:22:20

As @insan-e suggested, you could fetch existingUser by userUpdate.id and update using existingUser.field.getOrElse(existingUser.field).

Right now it is not possible to do it in single update query using slick api, this is a known issue

Update Scala Slick rows with optional columns

1 Answers