Thursday, January 3, 2013

Database Record Updates with Slick in Scala (and Play)

This is a simple operation that I found absolutely zero reference to in the documentation or the tutorials or the slides.  Eventually after digging through old mailing lists, I came across the solution:

        
(for { m <- MessageQ if m.id === oldMessage.id } yield(m))
  .mutate(r=>(r.row = newMessage))(session)

This is for a simple message class and a function that takes two arguments: oldMessage and newMessage.  The frustrating thing is that this is inconsistent with the simple formula for a single column update:
MessageQ.filter(_.id === 1234L).map(_.subject)
  .update("A new subject")(session)

When you try to apply this thinking to an update, you end up at a dead end.  The mutate operator is also used for deletion too:
MessageQ.filter(_.id === 1234L)
  .mutate(_.delete())(session)

Note that you can typically leave out the session argument as it's declared implicit within the appropriate scope. I'm also switching between syntax alternates because for some reason, either my IDE or the compiler gets grumpy when I try to use the filter() style rather than the list comprehension style in certain contexts. I still have to figure that out.

I'd like to write a longer post later at some point, but this at least covers the highlights.

5 comments:

  1. I was stuck with this and the lack of examples. I think there is an easier way to do update and delete by using the where method on the table.

    for example visualise the case class and table below

    case class ThirdParty(id: Option[Int] = None, ....)

    object ThirdParties extends Table[ThirdParty]("THIRD_PARTIES") {
    def id = column[Int]("SUP_ID", O.PrimaryKey, O.AutoInc)
    ....}

    Then if I want to update a third party record

    def update(thirdParty: ThirdParty)(implicit session: Session) = {
    ThirdParties.where(_.id === thirdParty.id.get).update(thirdParty)
    }

    and if i want to delete the record

    def delete(thirdParty: ThirdParty)(implicit session: Session) = {
    ThirdParties.where(_.id === thirdParty.id.get).delete
    }

    hope this helps. I've created a fuller example https://gist.github.com/4594635

    ReplyDelete
    Replies
    1. Oh this is brilliant! I was hoping there was a better way and somebody more brilliant than I would stumble upon this and help us out!

      Delete
    2. Helped me out too!
      Oh, for some more slick doco

      Delete
    3. Great! That was really helpful.

      Delete
  2. Brilliant - thanks for helping me have good smelling code!

    ReplyDelete