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

Answer 2

This is the version I came up with:

val a = (
    products.filter(_.name==="foo").exists.result.flatMap { exists => 
      if (!exists) {
        products += Product(
          None,
          productName,
          productPrice
        ) 
      } else {
        DBIO.successful(None) // no-op
      }
    }
).transactionally

It's is a bit lacking though, for example it would be useful to return the inserted or existing object.

For completeness, here the table definition:

case class DBProduct(id: Int, uuid: String, name: String, price: BigDecimal)
class Products(tag: Tag) extends Table[DBProduct](tag, "product") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column
  def uuid = column[String]("uuid")
  def name = column[String]("name")
  def price = column[BigDecimal]("price", O.SqlType("decimal(10, 4)"))

  def * = (id, uuid, name, price) <> (DBProduct.tupled, DBProduct.unapply)
}
val products = TableQuery[Products]

I'm using a mapped table, the solution works also for tuples, with minor changes.

Note also that it's not necessary to define the id as optional, according to the documentation it's ignored in insert operations:

When you include an AutoInc column in an insert operation, it is silently ignored, so that the database can generate the proper value

And here the method:

def insertIfNotExists(productInput: ProductInput): Future[DBProduct] = {

  val productAction = (
    products.filter(_.uuid===productInput.uuid).result.headOption.flatMap { 
    case Some(product) =>
      mylog("product was there: " + product)
      DBIO.successful(product)

    case None =>
      mylog("inserting product")

      val productId =
        (products returning products.map(_.id)) += DBProduct(
            0,
            productInput.uuid,
            productInput.name,
            productInput.price
            )

          val product = productId.map { id => DBProduct(
            id,
            productInput.uuid,
            productInput.name,
            productInput.price
          )
        }
      product
    }
  ).transactionally

  db.run(productAction)
}

(Thanks Matthew Pocock from Google group thread, for orienting me to this solution).

Answer 3

I've run into the solution that looks more complete. Section 3.1.7 More Control over Inserts of the Essential Slick book has the example.

At the end you get smth like:

  val entity = UserEntity(UUID.random, "jay", "jay@localhost")

  val exists =
    users
      .filter(
        u =>
          u.name === entity.name.bind
            && u.email === entity.email.bind
      )
      .exists
  val selectExpression = Query(
    (
      entity.id.bind,
      entity.name.bind,
      entity.email.bind
    )
  ).filterNot(_ => exists)

  val action = usersDecisions
    .map(u => (u.id, u.name, u.email))
    .forceInsertQuery(selectExpression)

  exec(action)
  // res17: Int = 1

  exec(action)
  // res18: Int = 0