Doobie. Compose .update.withGeneratedKeys() and .update.run

Question

Referencing to this question.
I want to insert some entity by some condition. It can either be inserted or not. If the condition is true the entity is inserted. I want to insert some other data in various tables. It looks like this:

val q = sql"insert into some_table (some_field) select 42 where ...(some condition)"

val inserts = List(
  sql"insert ...",
  sql"insert ...",
  sql"insert ..."
)

for {
  id <- q.update.withGeneratedKeys[Long]("id")   
  _ <- inserts.reduce(_ ++ _).update.run
} yield id

The problem is this does not compile because the first insert is a fs2.Stream but the second one is not.

I was trying to replace _ <- inserts.reduce... with _ = inserts.reduce. The app can compile but inserts in the second line does not occur.

UPD
My possible way to solve this problem:

...
for {
  idOpt <- q.update.withGeneratedKeys[Long]("id").compile.last   
  _ <- idOpt.fold(0.pure[ConnectionIO])(_ => inserts.reduce(_ ++ _).update.run)
} yield idOpt

This works, but IMHO this is not pretty. Is there a better way to do it?

you could ask to doobie gitter https://gitter.im/tpolecat/doobie — gekomad, Jun 06 '19 at 12:05
@gekomad unfortunatelly, gitter banned on my current workplace. Don't know why. — Oleg, Jun 06 '19 at 13:25

Valy Dia · Accepted Answer · 2019-06-18T21:51:02.367

One way to perform your batch inserts - if you have similar data - is to use updateMany - see doc:

import doobie._
type PersonInfo = (String, Option[Short])

def insertMany(ps: List[PersonInfo]): ConnectionIO[Int] = {
  val sql = "insert into person (name, age) values (?, ?)"
  Update[PersonInfo](sql).updateMany(ps)
}

// Some rows to insert
val data = List[PersonInfo](
  ("Frank", Some(12)),
  ("Daddy", None))

Also, if you remove.compile.last, you can use the fact that if your resulting Stream q.update.withGeneratedKeys[Long]("id") is empty, you 'exit early' the for-comprehension.

So all in all, here is what you could do:

import fs2.Stream

val result =
  // Now the for-comprehension operates on a Stream instead of an Option
  for {
    r <- q.update.withGeneratedKeys[Long]("id")   
    _ <- Stream.eval(insertMany(data)) // insertMany(data) is defined like in the snippet above
  } yield r

result.compile.last

Doobie. Compose .update.withGeneratedKeys() and .update.run

1 Answers1