Slick + HikariCP + Play is causing Postgres to run out of connections after often restarting Play

Question

I am developing a Play application with Slick and HikariCP for connection pooling. After a few restarts of Play, my development Postgres server runs out of connections and shows,

db_1  | 2019-11-19 21:06:46.583 GMT [395] FATAL:  remaining connection slots are reserved for non-replication superuser connections
db_1  | 2019-11-19 21:06:46.886 GMT [396] FATAL:  remaining connection slots are reserved for non-replication superuser connections
db_1  | 2019-11-19 21:06:48.167 GMT [397] FATAL:  remaining connection slots are reserved for non-replication superuser connections

I monitored with a SQL query SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state; and it seems true that the count of idle connection increases rapidly. I would like to resolve this issue so that I don't have leaking connections in development or production.

Any suggestions on how I can fix my idle connections leaking?

Setup

build.sbt

My build.sbt has the following dependencies,

      "com.typesafe.play" %% "play-slick" % "4.0.2",
      "com.typesafe.play" %% "play-slick-evolutions" % "4.0.2",
      "com.typesafe.slick" %% "slick-codegen" % "3.3.2",
      "com.typesafe.slick" %% "slick" % "3.3.2",
      "org.slf4j" % "slf4j-nop" % "1.7.26",
      "com.typesafe.slick" %% "slick-hikaricp" % "3.3.2",
      "org.postgresql" % "postgresql" % "42.2.8",

Application.conf

My configuration of postgres is stored in my application.conf

slick {
  dbs {
    default {
      profile="slick.jdbc.PostgresProfile$"
      db {
        connectionPool = "HikariCP" //use HikariCP for our connection pool
        profile = "org.postgresql.Driver"
        dataSourceClass = "org.postgresql.ds.PGSimpleDataSource" //Simple datasource with no connection pooling. The connection pool has already been specified with HikariCP.
        properties = {
          serverName = "localhost"
          portNumber = "5432"
          databaseName = "website"
          user = "websiteserver"
          password = "397c9140fb0e2424396510b8d6e29a07aa1a92420027d3750ef1faed87bb617a"
        }
      }
      numThreads = 10
      connectionTimeout = 6000 // In the hope that this resolves the connection errors.
      leakDetectionThreshold=60000 // In the hope that this resolves the connection errors.
    }
  }
}

Play app

Within my play 2.7.3 app I load the database configuration using,

@Singleton
class PersonRepositoryImpl @Inject() ()(implicit ec: PersonExecutionContext)
  extends PersonRepository {
  // We want the JdbcProfile for this provider
  private val db = Database.forConfig("slick.dbs.default.db")
  private val persons = TableQuery[PersonTable]

  def create(p: Person)(implicit mc: MarkerContext): Future[PersonData] = db.run {
  // Some operations on persons
  } 
}

I tried many different configurations but none seem to resolve the leaking connection issue that I'm facing.

Answer 1

You're calling Database.forConfig as a private val, when you need it to be a dependency. You should be leveraging play-slick to dependency inject the database config provider:

@Singleton
class PersonRepository @Inject() (dbConfigProvider: DatabaseConfigProvider)(implicit ec: ExecutionContext) {
  // We want the JdbcProfile for this provider
  private val dbConfig = dbConfigProvider.get[JdbcProfile]

   ...
}

https://github.com/playframework/play-samples/blob/2.8.x/play-scala-slick-example/app/models/PersonRepository.scala#L15

Also see the documentation:

While you can get the DatabaseConfig instance manually by accessing the SlickApi, we’ve provided some helpers for runtime DI users (Guice, Scaldi, Spring, etc.) for obtaining specific instances within your controller.

Here is an example of how to inject a DatabaseConfig instance for the default database (i.e., the database named default in your configuration):

class Application @Inject() (protected val dbConfigProvider: DatabaseConfigProvider, cc: ControllerComponents)(
    implicit ec: ExecutionContext
) extends AbstractController(cc)
    with HasDatabaseConfigProvider[JdbcProfile] {
}