Multiple queries in a single transaction with Doobie?

Question

Let's say I have two case classes C1 and C2 that are each inherit from trait T. I have two Query0 objects parameterized by these case classes Query0[C1] and Query0[C2]. The goal is that when I execute those queries I want to obtain a single List[T] that contains the results of both of those queries. Now I have it working just fine with separate transactions.

def executeQuery[A <: T](query: Query0[A]): List[A]=
    transactor.use { xa => query.stream.compile.toList.transact(xa) }.unsafeRunSync

val query1: Query0[C1] = generateQuery1
val query2: Query0[C2] = generateQuery2

val results: List[T] = executeQuery(query1) ++ executeQuery(query2)

The issue is that I'm using BigQuery as the database backend, and a transaction has a lot of overhead associated with it. I was hoping to execute both queries in a single transaction and have it return a List[T]object. Is there a way to do that?

Answer 1

If you don't care about order of these two queries I would advise you compile each query into ConnectionIO[List[A]] independently, combine them using mapN (because ConnectionIO[T] has Apply instance) and transact combined ConnectionIO[List[A] in one transaction:

import cats.effect.{Async, ContextShift, Resource}
import cats.syntax.apply._
import doobie.Query0
import doobie.implicits._
import doobie.util.transactor.Transactor

class SomeDAO[F[_]: Async: ContextShift, T] {

  val transactor: Resource[F, Transactor[F]] = ???

  def compile[A <: T](query: Query0[A]): doobie.ConnectionIO[List[A]] =
    query.stream.compile.toList

  def executeTwo[A <: T](query1: Query0[A], query2: Query0[A]): F[List[A]] =
    transactor.use { xa =>
      (compile(query1), compile(query2))
        .mapN(_ ++ _)
        .transact(xa)
    }
}

Answer 2

I also got a solution simply by appending the streams to each other

transactor.use { xa =>
    val stream = generateQuery1.stream ++ generateQuery2.stream
    stream.compile.toList.transact(xa)
}.unsafeRunSync