Wrap Slick queries in Futures

Question

I am trying to query a MySQL database asynchronously using Slick. The following code template, which I use to query about 90k rows in a for comprehension, seems to be working initially, but the program consumes several gigabytes of RAM and fails without warning after around 200 queries.

import scala.slick.jdbc.{StaticQuery => Q}
def doQuery(): Future[List[String]] = future {
  val q = "select name from person"
  db withSession {
    Q.query[String](q).list
  }
}

I have tried setting up connections both using the fromURL method and also using a c3p0 connection pool. My question is: Is this the way to do asynchronous calls to the database?

Answer 1

Async is still an open issue for Slick.

You could try using Iterables and stream data instead of storing it in memory with a solution similar to this: Treating an SQL ResultSet like a Scala Stream

Although please omit the .toStream call at the end. It will cache the data in memory, while Iterable will not.

If you want an async version of iterable you could look into Observables.

Answer 2

It turns out that this is a non issue (actually a bug in my code, which opened a new database connection for each query). In my experience, you can wrap DB queries in Futures as shown above and compose them later with Scala Async or Rx, as shown here. All is required for good performance is a large thread pool (x2 the CPUs in my case) and an equally large connection pool.

Answer 3

Slick 3 (Reactive Slick) looks like it might address this.