Expose ConnectionIO API for Doobie module

[salamonpavel]

Background

At the moment the DoobieEngine executes queries within a single complete transaction. Nevertheless Doobie offers also ConnectionIO API which allows to process several queries as one transaction within a for-comprehension block. In other words ConnectionIO allows for functional composition given it's a Monad.

val xa = Transactor.fromDriverManager[IO](
  driver = "org.postgresql.Driver", 
  url = "jdbc:postgresql:world", 
  user = "postgres",    
  password = "password"    
)

val program: ConnectionIO[(Int, Double)] =
  for {
    a <- sql"select 42".query[Int].unique // here imagine our db function invocation with connectionIO
    b <- sql"select random()".query[Double].unique // here imagine our db function invocation with connectionIO
  } yield (a, b)

program.transact(xa).unsafeRunSync()

As mentioned above the DoobieEngine currently defines the operation in terms of higher-kinded effect type F[_].

private def executeQuery[R](query: QueryType[R])(implicit readR: Read[R]): F[Seq[R]] = {
    query.fragment.query[R].to[Seq].transact(transactor)
  }

We could expose also the ConnectionIO. ConnectionIO is not tied to cats.effect.IO or any other specific effect type. ConnectionIO[A] is simply a type alias for Free[ConnectionOp, A], where ConnectionOp is a type that represents a low-level JDBC operation and Free is a type from the Cats library that represents a computation in the free monad of a functor.

private def executeQueryConnectionIO[R](query: QueryType[R])(implicit readR: Read[R]): ConnectionIO[Seq[R]] = {
    query.fragment.query[R].to[Seq]
  }

Having access to ConnectionIO API we could implement better integration tests as we could execute multiple database calls withing a single transaction.

Moreover Transactor's Strategy can be configured as needed for the purposes of testing, for instance setting auto-rollback.

val testXa = Transactor.after.set(xa, HC.rollback)

Compositionality of ConnectionIO together with Transactor with rollback effectively enables easy integration testing of our database functions. No additional testing library would be needed. Test assertions could be performed within a for-comprehension block (transaction boundary) and automatically rolled back.

Unfortunately Slick and Doobie have very different APIs and this posses a design challenge. Ideally we would like to keep Slick and Doobie APIs exposed by fa-db the same for features supported by both libraries and at the same time expose functionality Doobie offers.

[miroslavpojer]

The current idea of DB testing is not based on fa-db. This slick vs doobie API differences confirm this decision.
DB functions come from projects, not from fa-db.

[salamonpavel]

The current idea of DB testing is not based on fa-db. This slick vs doobie API differences confirm this decision. DB functions come from projects, not from fa-db.

This is misunderstanding of the proposed functionality. The fa-db shouldn't act as a testing library. Classes representing our db functions should be testable within standard testing libraries like Scalatest. The functionality proposed in this issue only aims to allow grouping of multiple function calls within single transaction because doing that plus having Transactor with a rollback will enable us seamlessly execute integration tests. No additional libraries (other than standard testing frameworks like the mentioned Scalatest) needed.