Slick Connection Pool on per requests

Question

How can use slick connection pool ?
For example :
with this config :

database {
  dataSourceClass = org.postgresql.ds.PGSimpleDataSource
  driver = org.postgresql.Driver
  properties = {
    url = "jdbc:postgresql://172.17.0.2/sampleDB"
    user = "user"
    password = "userpass"
  }
  minConnections = 10
  maxConnections = 20
  numThreads = 10
}

I have only one client and this client with web browser request to get all persons from API .
now slick generate 10 connection to database .
second step client refresh browser and slick generate new 10 connection to database without using previous connections .
and then new refresh in browser and slick generate another 10 connection to database . (Now I have about 30 connection on DB with only one client!)

Why ? This is normal ?
Why maxConnections not work ?
I must close connection after requests ?
Or forget some configuration about that ?

Update
This is my sample API :

trait PersonsApi extends DatabaseConfig with JsonMapper {

  val getAllPersons = (path("persons") & get) {
    complete(db.run(PersonDao.findAll))
  }

  val getPersonById = (path("persons" / IntNumber) & get) {
    num => complete(db.run(PersonDao.findById(num)))
  }


  val personsApi =
    getAllPersons ~ 
    getPersonById
}

This is my example entity class (DAO Pattern) :

class PersonTable(tag: Tag) extends Table[Person](tag, "persons") {
  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)

  def name = column[String]("name")

  def family = column[String]("family")

  override def * : ProvenShape[Person] = (id.?, name, family) <> (Person.tupled, Person.unapply)
}


object PersonDao extends BaseDao {
  def findAll = personTable.result

  def findById(id: Long) = personTable.filter(_.id === id).result

}

This DatabaseConfig interface :

trait DatabaseConfig extends Config {
  val driver = slick.driver.PostgresDriver

  import driver.api._

  def db = Database.forConfig("database")

}

Note : I don't use play framework .

Configuration is fine. Your problem is definitely how you create db in your code. Could you show me the code where you create db (from this configuration)? I bet you create it on request and that's the whole point. — Paul Dolega

Paul Dolega Paul Dolega · Accepted Answer · 2016-12-20T11:37:04

Your configuration seems to be fine. It's impossible to say without further code samples from your application but my bet is you are creating your db on each and every request to your application.

Just make sure that this code:

Database.forConfig("database")

is executed once perhaps by:

putting it as a Singleton injected dependency or
by using play-slick and it's way of dealing with Slick configuration (if you are using Play which is, again, not possible to say from your question, though I assumed it as you mentioned web requests).

EDIT (after question update): And we have an answer. Each time you call db method new Database object (together with connection pool is created). Just move it as I suggested above (created once per application lifecycle). The easiest way possible (not necessarily the best one) would be to change this line:

def db = Database.forConfig("database")

to this:

lazy val db = Database.forConfig("database")

Above would immediately solve your problem (assuming that there is only one instance of PersonsApi created in your application.

Other solution (better perhaps) would be to create something like this:

object DatabaseConfig extends Config {
  val driver = slick.driver.PostgresDriver

  import driver.api._

  lazy val db = Database.forConfig("database")
}

and then change your API to this:

trait PersonsApi extends JsonMapper {

  val getAllPersons = (path("persons") & get) {
    complete(DatabaseConfig.db.run(PersonDao.findAll))
  }

  val getPersonById = (path("persons" / IntNumber) & get) {
    num => complete(DatabaseConfig.db.run(PersonDao.findById(num)))
  }


  val personsApi =
    getAllPersons ~ 
    getPersonById
}

Slick Connection Pool on per requests

1 Answers