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]] ?

Answer 1

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.

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

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

Answer 2

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