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

Solution 1

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).

Solution 2

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

Solution 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

Comments

[dwickern]

If you're OK with rewriting the record if it already exists, slick supports upsert via products.insertOrUpdate

[User]

This looks great, +1. Could you please elaborate on this (name.bind, None) <> (User.apply _ tupled, User.unapply), it's clear what it does but the syntax not so much (it's probably also better to have an example with more than one field, like Product), thanks!

[dwickern]

@Ixx It's the only way I found to run a trivial query like select 1 (it generates the select ?, null in the example). You'll see <> used when defining a Table, usually to map a tuple (col1, col2, etc) into a case class.

[RoyB]

Your answer would be more usefull if you explicitly add types to the vals. That way i'd be able to understand more clearly what kind of query you are composing.

[dwickern]

@RoyB The actual types are Slick query monads so I don't think they will help much with your understanding. I added a link to github so feel free to explore

[Klugscheißer]

@dwickern great answer. Do you know if its possible to compile the insertIfNotExists query or does the query compiler have to run on each method call?

[RoyB]

@dwickern, I thnik "val exists" in your example is a Rep[Boolean]

[Simon]

This solution is not good for me! If a column with the same primary key is inserted during the query, it leads to a SQLException: ERROR: duplicate key value violates unique constrain.

[Jack Cheng]

I found the equivalent code without the for-yield syntax to be slightly more understandable: def insertIfNotExists(name: String) = { val exists = users.filter(_.name === name).map(_ => 1).exists; val constRow = (name.bind, None) <> (User.apply _ tupled, User.unapply); users.forceInsertQuery(Query(constRow).filterNot(_ => exists)) }

[crawfobw]

Be careful with this approach, you could potentially cause a dead lock scenario if one thread calls this method and while waiting on the result of the first filter query, another thread calls the method and is granted a temp lock, the first thread would then come back and try to update the table but be in a deadlock.