How do you run a patch/partial database UPDATE in Scala Slick?

Question

We'd like to run a patch/partial UPDATE with Slick (3.0.0) so that we only modify some of the fields in a record. Exactly which fields will be updated exactly will only be known at runtime.

For example, for a REST PATCH request.

Currently we run a SELECT first to get the original record then run an UPDATE but it would be nicer to do this in a single SQL statement.

Something like this:

def patchPerson(name: Option[String], age: Option[Int]) = {
   people.filter(_.name === "M Odersky")
       .map(p => 
           (name, age) match {
              case (Some(_), Some(_)) => (p.name, p.age)
              case (Some(_), None)    => (p.name)
              case (None   , Some(_)) => (p.age)
           }
       )
       .update(
           (name, age) match {
              case (Some(_), Some(_)) => (name.get, age.get)
              case (Some(_), None)    => (name.get)
              case (None   , Some(_)) => (age.get)
           }
       )
}

(Please ignore the ugly code here)

The above does not compile with the following error message:

No matching Shape found. Slick does not know how to map the given types. Possible causes: T in Table[T] does not match your * projection. Or you use an unsupported type in a Query (e.g. scala List). Required level: slick.lifted.FlatShapeLevel Source type: Object Unpacked type: T Packed type: G

And:

not enough arguments for method map: (implicit shape: slick.lifted.Shape[_ <: slick.lifted.FlatShapeLevel, Object, T, G])slick.lifted.Query[G,T,Seq]. Unspecified value parameter shape.

I assume this is because Slick expects the tuple length and type to match the results for both the filter and update functions.

We've tried using the Slick heterogeneous list class but this also seems to expect the length and types to match.

Is there a way to write this in Slick so that we can update an arbitrary number of fields in a record with one database call?

Answer 1

You already have answers as written by @pedrorijo91 and @thirstycow but i'll try to explain why this doesnt work.

I'm havent used slick 3 but im guessing its because the map function doesnt return a consistent type for it to run update against. As a thought experiment, if you cut your call at map what do you think the type will be?

val partialQuery:??? = people.filter(_.name === "M Odersky")
       .map(p => 
           (name, age) match {
              case (Some(_), Some(_)) => (p.name, p.age)
              case (Some(_), None)    => (p.name)
              case (None   , Some(_)) => (p.age)
           }
       );

val fullQuery:??? = partialQuery.update {
       (name, age) match {
          case (Some(_), Some(_)) => (name.get, age.get)
          case (Some(_), None)    => (name.get)
          case (None   , Some(_)) => (age.get)
       }    
}

The matcher returns different "shapes" at compile time which im guessing will revert to Any type.

Answer 2

My best guess would be to run a plain SQL query

Even if the SQL query has 2 parts, the relational database management system (postgresql, mysql, etc) is able to tune the query under the hoods.

I'm not sure if in this case Slick is able to optimize, but in several cases it also optimizes the queries by itself.

Typical update:

def updateRecord(id: Long, field1: Int) = {
    db.withSession {
      self.filter(_.id === id).map(_.field1).update(field1)
    }
}

Doing your type of update would require a bit more logic like you did. Don't think it's possible to simplify if you only know at runtime which fields to change. But you can force the update, using existing value for the field on the record as a fallback (may lead to more updates on DB than it should)

def updateRecord(id: Long, field1: Option[Int], field2: Option[Int]) = {
    db.withSession {
        self.filter(_.id === id).map(_.field1, _.field2).update(field1.getOrElse(existingValue1), field2.getOrElse(existingValue2)) 
    }
}

Answer 3

Why not do the pattern matching before constructing the update query?

def patchPerson(name: Option[String], age: Option[Int]) = {
   val query = people.filter(_.name === "M Odersky")
   (name, age) match {
     case (Some(name), Some(age)) =>
       query.map(p => (p.name, p.age)).update(name, age)
     case (Some(name), None) =>
       query.map(p => p.name).update(name)
     case (None, Some(age)) =>
       query.map(p => p.age).update(age)
   }
}