ScalikeJdbc ConnectionPool wrapper utility

ConnectionPoolManager.scala

import java.sql.Connection

import com.typesafe.scalalogging.Logger
import com.company.utils.jdbc.MySQLConfig
import scalikejdbc.{ConnectionPool, ConnectionPoolSettings, DB, using}

import scala.util.control.NonFatal

object ConnectionPoolManager {

  private lazy val logger: Logger = Logger[ConnectionPoolManager.type]

  /**
    * Checks if pool for db dbName already exists
    * @param dbName       Name of db
    * @param poolNameOpt  (Optional) Name of pool, If not provided, dbName is used
    * @return
    */
  def poolExists(dbName: String, poolNameOpt: Option[String] = None): Boolean = {
    val poolName: String = poolNameOpt.getOrElse(dbName)

    lazy val poolInitialized: Boolean = ConnectionPool.isInitialized(poolName)
    lazy val poolAlive: Boolean = using(ConnectionPool.get(poolName).borrow()) { (connection: Connection) =>
      if (!connection.isClosed && connection.isValid(2)) true
      else {
        logger.info(s"Removing dead ConnectionPool: '$poolName' for db '$dbName'")
        removePool(poolName)
        false
      }
    }

    poolInitialized && poolAlive
  }

  /**
    * Creates a new pool for db dbName with given properties,
    * only if pool with given name not already present
    * @param dbName       Name of db
    * @param poolNameOpt  (Optional) Name of pool, If not provided, dbName is used
    * @param timeout      (Optional) Timeout (seconds) for pool connections. Default = 300s
    * @param initialSize  (Optional) Initial no of connections in pool. Default = 1
    * @param maxSize      (Optional) Maximum no of connections in pool. Default = 2
    * @return
    */
  def addPool(dbName: String,
              poolNameOpt: Option[String] = None,
              timeout: Int = 300,
              initialSize: Int = 1,
              maxSize: Int = 2
             ): Boolean = {
    if (poolExists(dbName, poolNameOpt)) false
    else {
      val poolName: String = poolNameOpt.getOrElse(dbName)

      try {
        val jdbcConfigs: MySQLConfig = MySQLConfig.read(dbName)

        ConnectionPool.add(
          name = poolName,
          url = jdbcConfigs.url,
          user = jdbcConfigs.user,
          password = jdbcConfigs.password,
          settings = ConnectionPoolSettings(
            initialSize = initialSize,
            maxSize = Math.max(maxSize, initialSize),
            connectionTimeoutMillis = timeout.toLong * 1000,
            validationQuery = "SELECT 1",
            driverName = jdbcConfigs.driver
          )
        )

        // wait for initialization of connection pool
        while (!ConnectionPool.isInitialized(poolName)) {}
        logger.info(s"Added ConnectionPool '$poolName' for db '$dbName'")

        true
      } catch {
        case NonFatal(e) => {
          logger.warn(s"MySQLConfigs not found for db: '$dbName'")
          false
        }
      }
    }
  }

  /**
    * Borrows a connection from pool for db dbName.
    * If pool not present, creates it and then borrows connection.
    * @param dbName       Name of db
    * @param poolNameOpt  (Optional) Name of pool
    * @return
    */
  def getDB(dbName: String, poolNameOpt: Option[String] = None): DB = {
    addPool(dbName, poolNameOpt)

    val poolName: String = poolNameOpt.getOrElse(dbName)
    DB(ConnectionPool.get(poolName).borrow())
  }

  /**
    * Removes the pool for db dbName
    * @param dbName       Name of db
    * @param poolNameOpt  (Optional) Name of pool
    * @return
    */
  def removePool(dbName: String, poolNameOpt: Option[String] = None): Boolean = {
    if (poolExists(dbName, poolNameOpt)) false
    else {
      val poolName: String = poolNameOpt.getOrElse(dbName)
      ConnectionPool.close(poolName)

      // wait for un-initialization of connection pool
      while (ConnectionPool.isInitialized(poolName)) {}
      logger.info(s"Removed ConnectionPool '$poolName' for db '$dbName'")

      true
    }
  }
}

ConnectionPoolUsage.scala

import ConnectionPoolManager

object ConnectionPoolUsage {
  
  def makeQuery(dbName: String, poolNameOpt: Option[String]) = {
    ConnectionPoolManager.getDB(dbName, poolNameOpt).localTx { implicit session: DBSession =>
      // perform ScalikeJdbc SQL query here
    }
  }
}