Upsert in Slick

Question

Is there a way I can neatly do an upsert operation in Slick? The following works but is too obscure/verbose and I need to explicitly state the fields that should be updated:

val id = 1 val now = new Timestamp(System.currentTimeMillis) val q = for { u <- Users if u.id === id } yield u.lastSeen  q.update(now) match {   case 0 => Users.insert((id, now, now))   case _ => Unit } 

Answer 1

Updated for native upsert/merge support in Slick 2.1

Attention

You have to use plain SQL embedding with your database native MERGE statement. All trials to simulate this statement will very likely lead to incorrect results.

Background:

When you simulate the upsert / merge statement, Slick will have to use multiple statements to reach that goal (e.g. fist a select and then either an insert or an update statement). When running multiple statements in a SQL transaction, they usually doe not have the same isolation level as one single statement. With different isolation levels, you will experience strange effects in massive concurrent situations. So everything will work fine during the tests and fail with strange effects in production.

A database usually has a stronger isolation level while running one statement as between two statements in the same transaction. While one running statement will not be affected by other statements that run in parallel. The database will either lock everything the statement touches or it will detect interefence between running statements and automatically restart the problematic statements when necessary. This level of protection does not hold, when the next statement in the same transaction is executed.

So the following scenario may (and will!) happen:

1. In the first transaction the select statement behind user.firstOption doesn't find a database row for the current user.
2. A parallel second transaction inserts a row for that user
3. The first transaction inserts a second row for that user (similar to a phantom read)
4. You either end with two rows for the same user or the first transaction fails with a constraint violation although its check was valid (when it ran)

To be fair, this will not happen with the isolation level "serializable". But this isolation level is comes with a huge performance hit is rarely used in production. Additionally serializable will need some help from your application: The database management system will usually not really serializable all transaction. But it will detect violations against the serializable requeirement and just abort the transactions in trouble. So your application must be prepared for rerunning transaction that are aborted (randomly) by the DBMS.

If you rely on the constraint violation to occur, design your application in a way that it will automatically rerun the transaction in question without bothering the user. This is similar to the requirement in isolation level "serializable".

Conclusion

Use plain SQL for this scenario or prepare for unpleasant surprises in production. Think twice about possible problems with concurrency.

Update 5.8.2014: Slick 2.1.0 has now native MERGE support

With Slick 2.1.0 there is now native support for the MERGE statement (see the release notes: "Insert-or-update support which makes use of native databases features where possible").

The code will look like this (taken from the Slick test cases):

  def testInsertOrUpdatePlain {     class T(tag: Tag) extends Table[(Int, String)](tag, "t_merge") {       def id = column[Int]("id", O.PrimaryKey)       def name = column[String]("name")       def * = (id, name)       def ins = (id, name)     }     val ts = TableQuery[T]      ts.ddl.create      ts ++= Seq((1, "a"), (2, "b")) // Inserts (1,a) and (2,b)      assertEquals(1, ts.insertOrUpdate((3, "c"))) // Inserts (3,c)     assertEquals(1, ts.insertOrUpdate((1, "d"))) // Updates (1,a) to (1,d)      assertEquals(Seq((1, "d"), (2, "b"), (3, "c")), ts.sortBy(_.id).run)   }