Compose optional queries for for-comprehension in doobie?

Question

I would like to run several queries in one transaction using a for-comprehension in doobie. Something like:

def addImage(path:String) : ConnectionIO[Image] = {
  sql"INSERT INTO images(path) VALUES($path)".update.withUniqueGeneratedKeys('id', 'path')
}

def addUser(username: String, imageId: Optional[Int]) : ConnectionIO[User] = {
  sql"INSERT INTO users(username, image_id) VALUES($username, $imageId)".update.withUniqueGeneratedKeys('id', 'username', 'image_id')
}

def createUser(username: String, imagePath: Optional[String]) : Future[User] = {
  val composedIO : ConnectionIO[User] = for {
    optImage <- imagePath.map { p => addImage(p) }
    user <- addUser(username, optImage.map(_.id))
  } yield user

  composedIO.transact(xa).unsafeToFuture
}

I just started with doobie (and cats) so I'm not that familiar with FreeMonads. I've been trying different solutions but for the for-comprehension to work it looks like both blocks needs to return a cats.free.Free[doobie.free.connection.ConnectionOp,?].

If this is true, is there a way to transform my ConnectionIO[Image] (from the addImage call) into a cats.free.Free[doobie.free.connection.ConnectionOp,Option[Image]] ?

since it wasn't mentioned in the answer, you should also be aware that you do not need to know anything about `Free` to use doobie. Just know that `ConnectionIO` gives you a monadic interface (flatMap etc) and that you can `xa.transact(myCIO)` into the task type of your transactor. — Daenyth, Feb 23 '18 at 17:55

Gary Coady · Accepted Answer · 2018-02-23T10:09:55.793

For your direct question, ConnectionIO is defined as type ConnectionIO[A] = Free[ConnectionOp, A], i.e. the two types are equivalent (no transformation required).

Your issue is different, and can be easily seen if we step through the code step by step. For simplicity, I will use Option where you used Optional.

imagePath.map { p => addImage(p) }:

imagePath is an Option, and map uses an A => B to convert Option[A] to Option[B].

Since addImage returns a ConnectionIO[Image], we now have an Option[ConnectionIO[Image]], i.e. this is an Option program, not a ConnectionIO program.

We can instead return a ConnectionIO[Option[Image]] by replacing map with traverse, which uses the Traverse typeclass, see https://typelevel.org/cats/typeclasses/traverse.html for some details on how this works. But a basic intuition is that where map would have given you an F[G[B]], traverse instead gives you a G[F[B]]. In a sense, it works similarly to Future.traverse from the standard library, but in a more general way.
addUser(username, optImage.map(_.id))

The issue here is that given optImage which is an Option[Image], and its id field, which is an Option[Int], the result of optImage.map(_.id) is an Option[Option[Int]], not the Option[Int] which your method expects.

One way of solving this (if it matches your requirements), is to change this part of code to

addUser(username, optImage.flatMap(_.id))

flatMap can "join" an Option with another created by its value (if it exists).

(note: you need to add import cats.implicits._ to get the syntax for traverse).

In general, some of the ideas here about Traverse, flatMap, etc., are useful to study, and two books for doing so are "Scala With Cats" (https://underscore.io/books/scala-with-cats/) and "Functional Programming with Scala" (https://www.manning.com/books/functional-programming-in-scala)

The author of doobie also recently gave a talk about "effects", which may be of use in improving your intuition about types like Option, IO, etc.: https://www.youtube.com/watch?v=po3wmq4S15A

Thanks for an excellent and informative answer. I got it working by using traverse. InitelliJ complains about "Expression of type doobie.ConnectionIO[models.Image] doesn't conform to the expected type G_[B_] - but no objections from the compiler. The Optional was a typo - too much Java. — janne, Feb 23 '18 at 13:05
@janne That's very hard to avoid with IDEA unfortunately. The best method I've had is to sprinkle type annotations liberally to minimize the span of red squiggles. — Daenyth, Feb 23 '18 at 17:54

score 2 · Answer 2 · answered Feb 23 '18 at 09:54

If I got your intention right, you should use traverse instead of map:

  val composedIO : ConnectionIO[User] = for {
    optImage <- imagePath.traverse { p => addImage(p) }
    user <- addUser(username, optImage.map(_.id))
  } yield user

You might need to import cats.instances.option._ and/or cats.syntax.traverse._

Compose optional queries for for-comprehension in doobie?

2 Answers2