Play Scala Slick example (v.2.8.x) with Postgres

Question

for weeks I'm trying to get slick running with evolutions for Postgres (and eventually with codegen for the Tables.scala file) and it is extremely frustrating because the official documentation stops after the essential basic setup and anything that can be found elsewhere on the internet what goes a bit deeper is outdated. It seems that the framework changes a lot after every release and you cannot use older code snippets.

In order to keep things simple, I ended up in cloning the official play-samples repo and step by step change that to work with Postgres instead of the used H2 in-memory db.

Here is a link to the play-scala-slick-example

The only thing I changed is in the build.sbt#L11:

• I replaced the h2 dependency with a postgres dependency: "org.postgresql" % "postgresql" % "42.2.19",
• and I added the jdbc dependency (according to their documentation) - but this causes a binding issue, so I'm not sure that this dependency should be really used

Then I changed the config to connect to local dev DB at the application.conf#L66-L68

slick.dbs.default.profile="slick.jdbc.PostgresProfile$"
slick.dbs.default.db.dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
slick.dbs.default.db.driver="org.postgresql.Driver"
slick.dbs.default.db.url="jdbc:postgresql://localhost:5432/my_local_test_db?currentSchema=play_example&user=postgres&password="

When I run the application with the JDBC connection and try to access it in the browser it reports this exception:

CreationException: Unable to create injector, see the following errors:

1) A binding to play.api.db.DBApi was already configured at play.api.db.DBModule$$anonfun$$lessinit$greater$1.apply(DBModule.scala:39):
Binding(interface play.api.db.DBApi to ProviderConstructionTarget(class play.api.db.DBApiProvider)) (via modules: com.google.inject.util.Modules$OverrideModule -> play.api.inject.guice.GuiceableModuleConversions$$anon$4).
  at play.api.db.slick.evolutions.EvolutionsModule.bindings(EvolutionsModule.scala:15):
Binding(interface play.api.db.DBApi to ConstructionTarget(class play.api.db.slick.evolutions.internal.DBApiAdapter) in interface javax.inject.Singleton) (via modules: com.google.inject.util.Modules$OverrideModule -> play.api.inject.guice.GuiceableModuleConversions$$anon$4)

And when I run it without the jdbc dependency (I saw some hints on stackoverflow that this might cause the exception above) then it displays a warning and eventually runs into a timeout.

[warn] c.z.h.HikariConfig - db - using dataSourceClassName and ignoring jdbcUrl.

Does anyone know what is missing here to tell it to use the JDBC URL?

Answer 1

I finally found a solution. Thanks to user who pointed me in the right direction, where I found what the actual 2 problems are.

First in my application.conf had the wrong parameters and second I indeed needed to get rid of the jdbc dependency.

When the slick db config looks like this, then it will work:

slick.dbs.default.profile="slick.jdbc.PostgresProfile$"
slick.dbs.default.db.dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
slick.dbs.default.db.properties.driver="org.postgresql.Driver"
slick.dbs.default.db.properties.url="jdbc:postgresql://localhost:5432/fugu_test?currentSchema=play_example&user=postgres&password="

The "...properties..." was missing in both 2 last lines for the driver and url. It seems that what I had was from an older slick version.

Answer 2

I was going through similar problems for getting the Play to work with Postgres.

The connections to the database should be configured with a pool from HikariCP. The pasted configuration, as it can be implied from the build file, gets Slick to use HikariCP.

Here you go with my base and let me know if it works:

Contents of build.sbt:

import com.typesafe.sbt.SbtScalariform._

import scalariform.formatter.preferences._

val SlickVersion = "3.3.2"

name := “play-slick"

version := "6.0.0"

//val PlayVersion = "2.8.5"

scalaVersion := "2.13.1"

resolvers += Resolver.jcenterRepo

resolvers += "Sonatype snapshots" at "https://oss.sonatype.org/content/repositories/snapshots/"

libraryDependencies ++= Seq(
  jdbc,
  "org.postgresql" % "postgresql" % "42.2.18",
  "com.typesafe.slick" %% "slick-hikaricp" % SlickVersion,
  "org.webjars" %% "webjars-play" % "2.8.0",
  "org.webjars" % "bootstrap" % "4.4.1" exclude("org.webjars", "jquery"),
  "org.webjars" % "jquery" % "3.2.1",
  "net.codingwell" %% "scala-guice" % "4.2.6",
  "com.iheart" %% "ficus" % "1.4.7",
  "com.typesafe.play" %% "play-mailer" % "8.0.1",
  "com.typesafe.play" %% "play-mailer-guice" % "8.0.1",
//"com.enragedginger" %% "akka-quartz-scheduler" % "1.8.2-akka-2.6.x",
  "com.enragedginger" %% "akka-quartz-scheduler" % "1.8.3-akka-2.6.x",
  "com.adrianhurt" %% "play-bootstrap" % "1.5.1-P27-B4",
  specs2 % Test,
  ehcache,
  guice,
  jdbc,
  filters
)

lazy val root = (project in file(".")).enablePlugins(PlayScala)

routesImport += "utils.route.Binders._"

// https://github.com/playframework/twirl/issues/105
TwirlKeys.templateImports := Seq()

scalacOptions ++= Seq(
  "-deprecation", // Emit warning and location for usages of deprecated APIs.
  "-feature", // Emit warning and location for usages of features that should be imported explicitly.
  "-unchecked", // Enable additional warnings where generated code depends on assumptions.
  "-Xfatal-warnings", // Fail the compilation if there are any warnings.
  //"-Xlint", // Enable recommended additional warnings.
  "-Ywarn-dead-code", // Warn when dead code is identified.
  "-Ywarn-numeric-widen", // Warn when numerics are widened.
  // Play has a lot of issues with unused imports and unsued params
  // https://github.com/playframework/playframework/issues/6690
  // https://github.com/playframework/twirl/issues/105
  "-Xlint:-unused,_"
)

//********************************************************
// Scalariform settings
//********************************************************

scalariformAutoformat := true

ScalariformKeys.preferences := ScalariformKeys.preferences.value
  .setPreference(FormatXml, false)
  .setPreference(DoubleIndentConstructorArguments, false)
  .setPreference(DanglingCloseParenthesis, Preserve)

The database configuration which should be added to application.conf, provided that Postgres is configured to use encrypted connection. I used letsencrypt.

#include "database.conf"



db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://localhost/users_db?ssl=true&sslmode=require"
db.default.username=${?DATABASE_USER}
db.default.password=${?DATABASE_PASSWORD}

db.default.hikaricp.connectionTestQuery = "SELECT 1"




fixedConnectionPool = 5

database.dispatcher {
  executor = "thread-pool-executor"
  throughput = 1
  thread-pool-executor {
    fixed-pool-size = ${fixedConnectionPool}
  }
}

Sample from UserDAOImpl, for the signature and the imports:

package models.daos

import java.util.UUID

import javax.inject.Inject
import models.User
import play.api.db.Database

import scala.concurrent.{ ExecutionContext, Future }
import scala.util.{ Failure, Success }

/**
 * Give access to the user object.
 */
class UserDAOImpl @Inject() (db: Database)(implicit executionContext: DatabaseExecutionContext) extends UserDAO {


  /**
   * Finds a user by its user info.
   *
   * @param userInfo The user info of the user to find.
   * @return The found user or None if no user for the given user info could be found.
   */
  def find(userInfo: UserInfo) =
    
    Future {
      val c = db.getConnection()
      val statement = c.prepareStatement("SELECT * FROM users WHERE email = ?;")
      statement.setString(1, userInfo.email)
      if (statement.execute()) {
        val resultSet = statement.getResultSet
        if (resultSet.next()) {
          val userID = resultSet.getString("userid")
          val firstName = resultSet.getString("firstName")
          val lastName = resultSet.getString("lastName")
          val affiliation = resultSet.getString("affiliation")
          val roleTitle = resultSet.getString("roleTitle")
          val fullName = resultSet.getString("fullName")
          val email = resultSet.getString("email")
          val avatarURL = resultSet.getString("avatarURL")
          val activatedStr = resultSet.getString("activated")
          val activated: Boolean = activatedStr match {
            case "f" => false
            case "t" => true
            case _ => false
          }
          statement.close()
          c.close()
          Some(
            User(
              UUID.fromString(userID),
              firstName = Some(firstName),
              lastName = Some(lastName),
              affiliation = Some(affiliation),
              roleTitle = Some(roleTitle),
              fullName = Some(fullName),
              email = Some(email),
              avatarURL = Some(avatarURL),
              activated))

        } else {
          statement.close()
          c.close()
          None
        }
      } else {
        statement.close()
        c.close()
        None
      }
    }

}

Place this DatabaseExecutionContext file in the same directory as your dao files.

package models.daos

import javax.inject._

import akka.actor.ActorSystem
import play.api.libs.concurrent.CustomExecutionContext

/**
 * This class is a pointer to an execution context configured to point to "database.dispatcher"
 * in the "application.conf" file.
 */
@Singleton
class DatabaseExecutionContext @Inject() (system: ActorSystem) extends CustomExecutionContext(system, "database.dispatcher")