How to correctly handle Hikari connection pool with Doobie

Question

I've been using doobie (cats) to connect to a postgresql database from a scalatra application. Recently I noticed that the app was creating a new connection pool for every transaction. I eventually worked around it - see below, but this approach is quite different from that taken in the 'managing connections' section of the book of doobie, I was hoping someone could confirm whether it is sensible or whether there is a better way of setting up the connection pool.

Here's what I had initially - this works but creates a new connection pool on every connection:

import com.zaxxer.hikari.HikariDataSource
import doobie.hikari.hikaritransactor.HikariTransactor
import doobie.imports._

val pgTransactor = HikariTransactor[IOLite](
  "org.postgresql.Driver",
  s"jdbc:postgresql://${postgresDBHost}:${postgresDBPort}/${postgresDBName}",
  postgresDBUser,
  postgresDBPassword
)
// every query goes via this function
def doTransaction[A](update: ConnectionIO[A]): Option[A] = {
    val io = for {
      xa <- pgTransactor
      res <- update.transact(xa) ensuring xa.shutdown
    } yield res
    io.unsafePerformIO
}

My initial assumption was that the problem was having ensuring xa.shutdown on every request, but removing it results in connections quickly being used up until there are none left.

This was an attempt to fix the problem - enabled me to remove ensuring xa.shutdown, but still resulted in the connection pool being repeatedly opened and closed:

val pgTransactor: HikariTransactor[IOLite] = HikariTransactor[IOLite](
  "org.postgresql.Driver",
  s"jdbc:postgresql://${postgresDBHost}:${postgresDBPort}/${postgresDBName}",
  postgresDBUser,
  postgresDBPassword
).unsafePerformIO

def doTransaction[A](update: ConnectionIO[A]): Option[A] = {
    val io = update.transact(pgTransactor)
    io.unsafePerformIO
}

Finally, I got the desired behaviour by creating a HikariDataSource object and then passing it into the HikariTransactor constructor:

val dataSource = new HikariDataSource()
dataSource.setJdbcUrl(s"jdbc:postgresql://${postgresDBHost}:${postgresDBPort}/${postgresDBName}")
dataSource.setUsername(postgresDBUser)
dataSource.setPassword(postgresDBPassword)

val pgTransactor: HikariTransactor[IOLite] = HikariTransactor[IOLite](dataSource)

def doTransaction[A](update: ConnectionIO[A], operationDescription: String): Option[A] = {
  val io = update.transact(pgTransactor)
  io.unsafePerformIO
}

Answer 1

You can do something like this:

val xa = HikariTransactor[IOLite](dataSource).unsafePerformIO

and pass it to your repositories. .transact applies the transaction boundaries, like Slick's .transactionally. E.g.:

def interactWithDb = {
  val q: ConnectionIO[Int] = sql"""..."""
  q.transact(xa).unsafePerformIO
}