Incrementing a column without using plain sql

Question

I am currently incrementing a column (not a auto-increment PK column) in my database using the following:

def incrementLikeCount(thingId: Int)(implicit session: Session) = {
  sqlu"update things set like_count = like_count + 1 where id = $thingId".first
}

Is this currently (slick 2.0.2) the best and fastest way to do this? (I'm using postgresql)

I was hoping for a more typesafe way of doing this e.g. if I rename my table or column I want compile time errors.

I don't want to read in the row and then update, because then I would have to wrap the call in a transaction during the read + write operation and that is not as efficient as I would want.

I would love if there was a way to do this using the normal slick api, and also be able to update/increment multiple counters at the same time in a single operation (but even one column increment/decrement at a time would be lovely)

@Dimitri doesn't seem to be the same at all, that is based on a inner query. I just want to do: ``` set like_count = like_count + 1 ``` (or subtracting 1). — Blankman, Jun 05 '14 at 22:13
I haven't tried this, but could you use the `$` or `#$` variable substitutions to supply the `things`, `like_count` and `id` as vals? If it did work, those same vals could then also be used in the table definition and give a small measure of safety. — n0741337, Jun 06 '14 at 04:37

score 0 · Answer 1 · answered Jun 07 '14 at 08:38

Not on Slick, in the lovely ScalaQuery stone ages here, but you should be able to use what was called a MutatingUnitInvoker to modify DB row in place (i.e. perform a single query).

Something like:

val q = for{id <- Parameters[Int]; t <- Things if t.id is id} yield t
def incrementLikeCount(thingId: Int)(implicit session: Session) = {
  q(thingId).mutate(r=> r.row.copy(like_count = r.row.like_count + 1))
}

Performance should be acceptible, prepared statement generated once at compile time and a single query against the database. Not sure how you can improve on that in a type safe manner with what Slick currently has on offer.

Incrementing a column without using plain sql

1 Answers1