Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Upsert in Slick

Tags:

scala

slick

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 } 
like image 800
Synesso Avatar asked Jul 27 '13 07:07

Synesso


People also ask

What is Upsert command?

The word UPSERT combines UPDATE and INSERT , describing it statement's function. Use an UPSERT statement to insert a row where it does not exist, or to update the row with new values when it does.

What is Upsert operation in SQL?

The term upsert is a portmanteau – a combination of the words “update” and “insert.” In the context of relational databases, an upsert is a database operation that will update an existing row if a specified value already exists in a table, and insert a new row if the specified value doesn't already exist.

What is Scala slick?

Slick is a modern database query and access library for Scala. It allows you to work with stored data almost as if you were using Scala collections while at the same time giving you full control over when a database access happens and which data is transferred.


1 Answers

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)   } 
like image 177
stefan.schwetschke Avatar answered Oct 17 '22 02:10

stefan.schwetschke