Running queries in parallel in Doobie

Question

Is it possible to run multiple queries in parallel, using Doobie?

I have the following (pseudo)queries:

def prepareForQuery(input: String): ConnectionIO[Unit] = ???
val gettAllResults: ConnectionIO[List[(String, BigDecimal)]] = ???
def program(input : String) : ConnectionIO[List[(String, BigDecimal)]] = for{
    _ <- prepareForQuery(input)
    r <- gettAllResults
  } yield r

What I tried is the following:

import doobie._
import doobie.implicits._
import cats.implicits._
val xa = Transactor.fromDataSource[IO](myDataSource)
val result = (program(i1),program(i2)).parMapN{case (a,b) => a ++ b}
val rs = result.transact(xa).unsafeRunSync

However, no NonEmptyParallel instance is found for ConnectionIO.

Error:(107, 54) could not find implicit value for parameter p: cats.NonEmptyParallel[doobie.ConnectionIO,F] val result = (program(i1),program(i2)).parMapN{case (a ,b) => a ++ b}

Am I missing something obvious or trying something that cannot be done? Thanks

Does this help? https://stackoverflow.com/questions/50472904/doobie-and-db-access-composition-within-1-transaction — Toby, Sep 18 '18 at 11:58
Probably stating the obvious, but if you don't need to run both queries in the same transaction, you can `transact` them first and `parMapN` the resulting `IO`s. — devkat, Sep 18 '19 at 09:02
Perhaps this answer can help: https://stackoverflow.com/questions/55601408/how-to-execute-list-of-string-sql-statements-against-a-postgresql-db-in-scala-us — Vasily802, May 26 '20 at 19:17

score 8 · Accepted Answer · answered Sep 16 '20 at 07:04

You cannot run queries in the ConnectionIO monad in parallel. But as soon as you turn them into your actual runtime monad (as long as it has a Parallel instance), you can.

For example, with the cats-effect IO runtime monad:

def prepareForQuery(input: String): ConnectionIO[Unit] = ???
val gettAllResults: ConnectionIO[List[(String, BigDecimal)]] = ???
def program(input : String) : ConnectionIO[List[(String, BigDecimal)]] = for{
    _ <- prepareForQuery(input)
    r <- gettAllResults
  } yield r

Turn your ConnectionIO into an IO

val program1IO: IO[List[(String, BigDecimal)]]] = program(i1).transact(xa)
val program2IO: IO[List[(String, BigDecimal)]]] = program(i2).transact(xa)

You now have a monad which can do things in parallel.

val result: IO[List[(String, BigDecimal)]]] = 
    (program1IO, program2IO).parMapN{case (a,b) => a ++ b}

To understand why ConnectionIO doesn't allow you to do things in parallel, I'll just quote tpolecat:

You can't run ConnectionIO in parallel. It's a language describing the use of a connection which is a linear sequence of operations.

Using parMapN in IO, yes, you can run two things at the same time because they're running on different connections.

There is no parMapN with ConnectionIO because it does not (and cannot) have a Parallel instance.

Running queries in parallel in Doobie

1 Answers1