Manage doobie connections with Hikari

Question

I am connecting and firing queries on postgres in my scala play framework application. While optimizing my application, I saw that before firing any query a CONNECT call is being made to Postgres which AFAIK is not necessary before every SQL query and the connection pool should've been used. I tried to find the issue but was unable to figure out.

This is my code

object SQLClient extends SQLClientTrait {
  protected val rdsConfig = ConfigFactory.load().getConfig("rds.config")

  private implicit val contextShift: ContextShift[IO] = IO.contextShift(ExecutionContexts.synchronous)
  private val rdsEngine: Resource[IO, HikariTransactor[IO]] = initialiseRDSEngine()

  private def initialiseRDSEngine(): Resource[IO, HikariTransactor[IO]] = {
    val transactor = for {
      ce <- ExecutionContexts.fixedThreadPool[IO](20)
      be <- Blocker[IO]
      xa <- HikariTransactor.newHikariTransactor[IO](
        "org.postgresql.Driver",
        s"jdbc:postgresql://${rdsConfig.getString("host")}:${rdsConfig.getString("port")}/${rdsConfig.getString("dbname")}?prepareThreshold=${rdsConfig.getString("prepareThreshold")}",
        rdsConfig.getString("username"),
        rdsConfig.getString("password"),
        ce,
        be
      )
    } yield xa
    transactor
  } 

  override def getData(query: String): Future[List[Data]] = {
    try {
      val response = rdsEngine.use { xa =>
        Fragment.const(query)
          .query[ConsentDbModel]
          .to[List]
          .transact(xa)
      }.unsafeToFuture()

      logger.debug(s"Successfully executed query: ${query.trim}")
      response
    } catch {
      case ex: Exception =>
        ex.printStackTrace()
        throw RDSException(ex.getMessage)
    }
  }
}

Am I doing something while initializing or making a query?

Answer 1

Your issue is using globals - your code is defined in object so it has to be called as a global method. Meanwhile Resources like this are intended to be run ONCE in your main, so that this content of use reuse the same connection pool.

If your SQLClient needed connection pool it could take it as a dependency through a constructor:

object Main extends IOApp {

  private val transactor = for {
      ce <- ExecutionContexts.fixedThreadPool[IO](20)
      be <- Blocker[IO]
      xa <- HikariTransactor.newHikariTransactor[IO](
        "org.postgresql.Driver",
        s"jdbc:postgresql://${rdsConfig.getString("host")}:${rdsConfig.getString("port")}/${rdsConfig.getString("dbname")}?prepareThreshold=${rdsConfig.getString("prepareThreshold")}",
        rdsConfig.getString("username"),
        rdsConfig.getString("password"),
        ce,
        be
      )
    } yield xa  

  override def run(args: List[String]): IO[ExitCode] = transactor.use { xa =>
    val client = new SQLClient(xa)
    IO {
      // use client here
      ExitCode.Success
    }
  }
}

class SQLClient(xa: HikariTransactor[IO]) extends SQLClientTrait {
  protected val rdsConfig = ConfigFactory.load().getConfig("rds.config")

  private implicit val contextShift: ContextShift[IO] = IO.contextShift(ExecutionContexts.synchronous)

  override def getData(query: String): Future[List[Data]] = {
    try {
      val response =
        Fragment.const(query)
          .query[ConsentDbModel]
          .to[List]
          .transact(xa)
          .unsafeToFuture()

      logger.debug(s"Successfully executed query: ${query.trim}")
      response
    } catch {
      case ex: Exception =>
        ex.printStackTrace()
        throw RDSException(ex.getMessage)
    }
  }
}

In case you need to use Cats within some non-Cats code, I would recommend reading about Resource allocated and how to use it.