Insert if not exists in Slick 3.0.0

by

It is possible to use a single insert ... if not exists query. This avoids multiple database round-trips and race conditions (transactions may not be enough depending on isolation level).

def insertIfNotExists(name: String) = users.forceInsertQuery {
  val exists = (for (u <- users if u.name === name.bind) yield u).exists
  val insert = (name.bind, None) <> (User.apply _ tupled, User.unapply)
  for (u <- Query(insert) if !exists) yield u
}

Await.result(db.run(DBIO.seq(
  // create the schema
  users.schema.create,

  users += User("Bob"),
  users += User("Bob"),
  insertIfNotExists("Bob"),
  insertIfNotExists("Fred"),
  insertIfNotExists("Fred"),

  // print the users (select * from USERS)
  users.result.map(println)
)), Duration.Inf)

Output:

Vector(User(Bob,Some(1)), User(Bob,Some(2)), User(Fred,Some(3)))

Generated SQL:

insert into "USERS" ("NAME","ID") select ?, null where not exists(select x2."NAME", x2."ID" from "USERS" x2 where x2."NAME" = ?)

Here’s the full example on github

More Related Contents:

  1. scala slick method I can not understand so far
  2. How to bucket the range of values from a column and count how many values fall into each interval in scala?
  3. What are all the uses of an underscore in Scala?
  4. What does `:_*` (colon underscore star) do in Scala?
  5. Case objects vs Enumerations in Scala
  6. How to build an Uber JAR (Fat JAR) using SBT within IntelliJ IDEA?
  7. Get companion object instance with new Scala reflection API
  8. private[this] vs private
  9. By-name parameter vs anonymous function
  10. call of distinct and map together throws NPE in spark library
  11. How to create a Source that can receive elements later via a method call?
  12. Why is the Aux technique required for type-level computations?
  13. java.lang.NoSuchMethodError: scala.Predef$.refArrayOps
  14. Why are scala functions limited to 22 parameters?
  15. Method Override with Scala 3 Macros
  16. Replace missing values with mean – Spark Dataframe
  17. Accessing the underlying ActorRef of an akka stream Source created by Source.actorRef
  18. Usages of Null / Nothing / Unit in Scala
  19. Spark-Monotonically increasing id not working as expected in dataframe?
  20. What is the fastest way to write Fibonacci function in Scala?
  21. How do I parse command line arguments in Scala? [closed]
  22. How to print the contents of RDD?
  23. Is there an equivalent to SuppressWarnings in Scala?
  24. Why isn’t Validation a Monad?
  25. Coalesce reduces parallelism of entire stage (spark)
  26. How do I get an instance of the type class associated with a context bound?
  27. Scala – initialization order of vals
  28. SBT stop run without exiting
  29. What is the differences between Int and Integer in Scala?
  30. scala parallel collections degree of parallelism

Leave a Comment