Insert if not exists in Slick 3.0.0

Question

I'm trying to insert if not exists, I found this post for 1.0.1, 2.0.

I found snippet using transactionally in the docs of 3.0.0

val a = (for {
  ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
  _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

val f: Future[Unit] = db.run(a)

I'm struggling to write the logic from insert if not exists with this structure. I'm new to Slick and have little experience with Scala. This is my attempt to do insert if not exists outside the transaction...

val result: Future[Boolean] = db.run(products.filter(_.name==="foo").exists.result)
result.map { exists =>  
  if (!exists) {
    products += Product(
      None,
      productName,
      productPrice
    ) 
  }  
}

But how do I put this in the transactionally block? This is the furthest I can go:

val a = (for {
  exists <- products.filter(_.name==="foo").exists.result
  //???  
//    _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

Thanks in advance

Answer 1

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