explicitly close db connection in slick

Question

Here is code I am trying to optimise:

object UserRepo
{
  val users = TableQuery[Users]

  val dbName = "db"

  lazy val queryAllUsers = for (user <- users) yield user

  type UserRow =  (Int, String, String, String)

  def getAll() : Future[ Seq[UserRow] ] =
  {
    val db = Database.forConfig( dbName )

    val f: Future[Seq[UserRow]] = db.run( queryAllUsers.result )

    f.onComplete {
      case Success(_) => { db.close() }
      case Failure(_) => { db.close() }
    }

    f
  }

}

I going to have number of query to the DB I am trying to get rid of string where I am creating DB connection. Is there any execution context I can use to close connection explicitly ?? so code will look more concise ?

Is there option to get used db connection within Future.onComplete scope??

Thanks

So what you are saying is that you want to create (and later close) db connection for this single operation? Shouldn't you have connection being opened/closed on some kind of startup/shutdown of your application and resused whenever you perform query? — Paul Dolega, Feb 19 '17 at 14:52
Good point, you mean to manage db connection on application level? Yes, this could be nice, but as I understand I will have to create this code manually? is there something build in in the slick? — Pavel, Feb 19 '17 at 14:54
I've created separate project to manage all work related to database/data and planning to reuse this in Play project as also in the data processing services — Pavel, Feb 19 '17 at 15:05
If you are using play it may be useful to take a look at `play-slick` and simply use `DI`. Let me update the answer with `Play` style `DI`. — Paul Dolega, Feb 19 '17 at 15:06

score 5 · Accepted Answer · edited May 23 '17 at 12:02

As for your comment ( explicitly close db connection in slick ) normally what you do is to create a connection on an application startup (or lazily on first use) and then closing it at the application end.

This obviously all depends what kind of application you are running:

if you are having DI container you would probably manage some of this in your DI mechanisms (like Modules in Guice)
if you are having web application, specifically e.g. Play - you would probably use play-slick that does this initialization / shutting down for you (kind of).

General way (no DI)

The easiest general way (assuming you are not using DI or play-slick) of doing this would be perhaps something like this:

object DbManager {
  lazy val db = createDb

  private def createDb = {
      Database.forConfig("db")
  }

  def close {
      db.close
  }
}

Then your code would be:

object UserRepo
{
  val users = TableQuery[Users]

  lazy val queryAllUsers = for (user <- users) yield user

  type UserRow =  (Int, String, String, String)

  def getAll() : Future[ Seq[UserRow] ] =
  {
    DbManager.db.run( queryAllUsers.result )
  }   
}

Above code doesn't do any cleaning up - this would need to be added to some kind of hook when application is closing (in case e.g. of web application) or you would need to manually call DbManager.close at some specified time (when you are closing the application).

Play slick

You would probably need to start from here: https://github.com/playframework/play-slick/tree/master/samples/basic (most basic sample showing play-slick configuration).

Updating your answer with this would be:

class UserRepo @Inject() (dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile])
{
  import driver.api._

  val users = TableQuery[Users]

  lazy val queryAllUsers = for (user <- users) yield user

  type UserRow =  (Int, String, String, String)

  def getAll() : Future[ Seq[UserRow] ] =
  {
    db.run( queryAllUsers.result )
  }   
}

In this scenario you wouldn't call:

UserRepo.getAll

but you would rather need to inject it:

class MyClientCode @Inject() (userRepo: UserRepo) {
  ...
  userRepo.getAll
  ...
}

You would need to obviously configure it in your configuration but this should be very straightforward to do with the sample provided above.

So in short your Play application will have database connection configuration and would do all initialization / cleaning up. Your external modules (like the one you described in your comment) would simply pull DatabaseConfigProvider as Guice managed dependency (as show above).

well, considering how concise scala code could be I am considering to use play-slick for my project: (Play project + Akk processing services). So, I will have separate project for data models/table mapping etc. Thanks, — Pavel, Feb 19 '17 at 15:17

explicitly close db connection in slick

1 Answers1