Why does Play action fail with "no suitable driver found" with Slick and PostgreSQL?

Question

I'm writing a Scala web app using Play Framework 2.1.1 using a local Postgres database along with Slick 1.0.0, and I'm running into what seems to be a contradiction here.

This is the error I'm running into:

[SQLException: No suitable driver found for postgres://user:password@localhost:5432/postgres]
56  
57  def instance = Action {
58    Database.forURL("postgres://user:password@localhost:5432/postgres", driver = "org.postgresql.Driver") withSession {
59      val q = Retailer.map(_.name)
60      Ok(views.html.instance(q.list, newRForm))
61    }
62  }
63

Where user and password are respectively the username and password of the Postgres database.

In java error (No suitable driver found) I found:

1. You'll need to load the driver somewhere using Class.forName("org.postgresql.Driver");
2. You'll need the PostgreSQL driver's jar file in the classpath of your program.

In Application.scala I have the following block of code:

{
  println(ConfigFactory.load().getString("db.default.url"))
  println(Class.forName("org.postgresql.Driver"))
} 

Rerunning play compile results in:

(Server started, use Ctrl+D to stop and go back to the console...)

[info] play - database [default] connected at jdbc:postgresql://localhost:5432/postgres
[info] play - Application started (Dev)
postgres://user:password@localhost:5432/postgres
class org.postgresql.Driver
[error] application -

! @6ei1nhkop - Internal server error, for (GET) [/instance] ->

play.api.Application$$anon$1: Execution exception[[SQLException: No suitable driver found for jdbc:postgresql://user:password@localhost:5432/postgres]]
        at play.api.Application$class.handleError(Application.scala:289) ~[play_2.10.jar:2.1.1]
        at play.api.DefaultApplication.handleError(Application.scala:383) [play_2.10.jar:2.1.1]
        at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$17$$anonfun$apply$24.apply(PlayDefaultUpstreamHandler.scala:326) [play_2.10.jar:2.1.1]
        at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$17$$anonfun$apply$24.apply(PlayDefaultUpstreamHandler.scala:324) [play_2.10.jar:2.1.1]
        at play.api.libs.concurrent.PlayPromise$$anonfun$extend1$1.apply(Promise.scala:113) [play_2.10.jar:2.1.1]
        at play.api.libs.concurrent.PlayPromise$$anonfun$extend1$1.apply(Promise.scala:113) [play_2.10.jar:2.1.1]
java.sql.SQLException: No suitable driver found for jdbc:postgresql://user:password@localhost:5432/postgres
        at java.sql.DriverManager.getConnection(Unknown Source) ~[na:1.7.0_21]
        at java.sql.DriverManager.getConnection(Unknown Source) ~[na:1.7.0_21]
        at scala.slick.session.Database$$anon$2.createConnection(Database.scala:105) ~[slick_2.10-1.0.0.jar:1.0.0]
        at scala.slick.session.BaseSession.conn$lzycompute(Session.scala:207) ~[slick_2.10-1.0.0.jar:1.0.0]
        at scala.slick.session.BaseSession.conn(Session.scala:207) ~[slick_2.10-1.0.0.jar:1.0.0]
        at scala.slick.session.BaseSession.close(Session.scala:221) ~[slick_2.10-1.0.0.jar:1.0.0]

Then I run play dependencies and the postgres .jar is resolved!

Here are the resolved dependencies of your application:
+--------------------------------------------------------+--------------------------------------------------------+-----------------------------------+
| ←[32mpostgresql:postgresql:9.1-901-1.jdbc4←[0m         | ←[37mats:ats_2.10:1.0-SNAPSHOT←[0m                     | ←[37mAs postgresql-9.1-901-1.jdbc4.jar←[0m |
+--------------------------------------------------------+--------------------------------------------------------+-----------------------------------+

Why can't a suitable driver be found?

conf/application.conf

# Database configuration
db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgres://user:password@localhost:5432/postgres"
db.default.user=user    
db.default.password=password

project/Build.scala

import sbt._
import Keys._
import play.Project._

object ApplicationBuild extends Build {

  val appName    = "ats"
  val appVersion = "1.0-SNAPSHOT"

  val appDependencies = Seq(
    // Add your project dependencies here, 
    jdbc,
    "com.typesafe.slick" %% "slick"      % "1.0.0",
    "postgresql"         %  "postgresql" % "9.1-901-1.jdbc4"
  )

  val main = play.Project(appName, appVersion, appDependencies).settings(
    // Add your own project settings here    
  )

I also have postgresql-9.2-1002.jdbc4.jar and slick_2.10-1.0.1-RC1.jar in my /lib file, and my local Postgres version from doing a SELECT version(); is 9.2.4 The postgres driver resolution seems to be resolving to the 9.1 .jar though, and when I comment out the app dependency to let /lib be included on its own, /lib doesn't seem to be on Play's CLASSPATH.

I know that the Postgres url is correct, and I'm able to connect to my database when my application first launches.

Answer 1

You are mixing things up.

So then I looked at this question which says...

1. You'll need to load the driver somewhere. Class.forName("org.postgresql.Driver");
2. You'll need the postgresql driver .jar file in the classpath of your program.

This is not applicable in this case. You have a framework that takes care of these things for you. That question you refer to is describing how to access a database with "raw" jdbc.

---

Here's how you should do it.

First of all you can simplify the configuration part. 5432 is the default port for postresql and localhost is also the default host. Username and password should be placed outside of the url.

# Database configuration
db.default.driver=org.postgresql.Driver
db.default.url=jdbc:postgres:postgres
db.default.user=user    
db.default.password=password

Now define proper sbt dependencies. Just remove the jar files from the /lib folder and update your dependencies to get the latest PostgreSQL driver (9.2) by changing your Build.scala appDependencies. Be aware that the groupId has changed from postgresql to org.postgresql.

val appDependencies = Seq(
  // Add your project dependencies here, 
  jdbc,
  "com.typesafe.slick" %% "slick" % "1.0.0",
  "org.postgresql" % "postgresql" % "9.3-1102-jdbc41"
)

And finally you should change your controller to resolve the datasource from configuration:

def instance = Action {
  Database.forDataSource(DB.getDataSource()) withSession {
    val q = Retailer.map(_.name)
    Ok(views.html.instance(q.list, newRForm))
  }
}