Doobie is not reusing connections, and the documentation doesn't have a single example of how to do it

[mantovani]

Doobie is not reusing connections, and the documentation doesn't have a single example of how to do it

[yevtyushkin]

According to the docs, use:

Allocates a resource and supplies it to the given function. The resource is released as soon as the resulting F[B] is completed, whether normally or as a raised error.

This means, the following code:

transactor.use {xa =>
    transaction.transact(xa)
}.timeout(1.minute).unsafeRunSync()

allocates a new transactor, calls transact on it, and releases it every time you call runWithRetryTransaction.

To make your app to reuse connections, you may want to, for example:

1. Refactor Db to accept an instance of transactor, rather than allocate it every time runWithRetryTransaction is called:

class Db(transactor: HikariTransactor[IO]) {
  ...
}

2. Allocate transactor once, at startup, e.g., in Main:

object Main extends ResourceApp.Forever {
  def run(args: List[String]): Resource[IO, Unit] =
    for {
      transactor <- createTransactor()
      db = new Db(transactor)
      ... // do stuff with Db
    } yield ()
}

[jatcwang]

I have expanded on the Hikari example. Hope it helps demonstrate how to use cats.effect.Resource as well as how a typical application using doobie is setup.

https://github.com/tpolecat/doobie/pull/2013/files

Note that at no point do I need to call unsafeRunSync since my main object is extending cats.effect.IOApp.Simple

[jatcwang]

I still recommend structuring your code following the examples to avoid unsafeRunSync. How your code is laid out right now (which forces you to call unsafeRunSync) isn't idiomatic nor ideal and will present problems in the future.
(For example, who calls cleanUp on your connection pool and when?)

[mantovani]

A person named "amumurst" found the problem.

"I can reproduce, and there is some different ways of fixing it.

The issue is kinda contrived and I don't know exactly the details, but it has something to do with something called "delayed init" which the App trait of scala 2 uses (fixed in scala 3 it seems). My best hunch is that the code in the App object is run before the implicit IORuntime has started correctly (or some other initialization order issue), leading to a deadlock.

You can circumvent it by moving the allocation to a lazy val (so other things get to initialize first)

So lazy val (xa, cleanUp): (HikariTransactor[IO], IO[Unit])"

This is my final solution:

case class DBx(transactor: HikariTransactor[IO]) { all my queries here }

object Foo {
  val transactor: Resource[IO, HikariTransactor[IO]] =
    for {
      hikariConfig <- Resource.pure {
        // For the full list of hikari configurations see https://github.com/brettwooldridge/HikariCP#gear-configuration-knobs-baby
        val config = new HikariConfig()
        config.setMaximumPoolSize(10)
        config.setDriverClassName(PostgresDriver)
        config.setJdbcUrl(s"jdbc:postgresql://${PostgresHostname}:${PostgresPort}/${PostgresDB}?currentSchema=yeedu")
        config.setUsername(PostgresUser)
        config.setPassword(PostgresPass)
        config.setConnectionTimeout(1000)
        config
      }
      xa <- HikariTransactor.fromHikariConfig[IO](hikariConfig)
    } yield xa

  lazy val (xa, cleanUp): (HikariTransactor[IO], IO[Unit]) = transactor.allocated.unsafeRunSync()

  val db = DBx(xa)
}

Thank you so much

[mantovani]

I've refactor my code do to this:

  val _db: Resource[IO, DB] = for {
    transactor <- System.transactor
    db = DB(transactor)
  } yield db

  val (db,cancel) = _db.allocated.unsafeRunSync() //never returns, get stuck forever

But it's the same problem as before; the 'db.allocated.unsafeRunSync()' never returns.

I can't perform all my tasks inside the 'for' loop; it doesn't make sense. The DB object is used throughout the application.