Connecting Slick to Postgres

Libraries
1

Hi folks,

I’m quite new to Scala and am working on a REST API project that connects to a Postgres database via Slick, or at least that’s what I’m stuck trying to achieve.

I’m trying to open a connection to the database, set up the users schema and populate it with some sample user data.

package com.foram

import com.foram.db._
import scala.concurrent.{Await, Future}
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
import slick.basic.DatabasePublisher
import slick.jdbc.PostgresProfile.api._

object SlickConnect extends App{
  val db = Database.forConfig("postgresDB")

  try {
    val users = TableQuery[Users]

    val setupAction = DBIO.seq(
      users.schema.create,

      users += (1, "User One", "user1", "[email protected]"),
      users += (2, "User Two", "user2", "[email protected]"),
      users += (3, "User Three", "user3", "[email protected]")
    )

    val setupFuture = db.run(setupAction)

    Await.result(setupFuture, Duration.Inf)
  } finally db.close
}

But every time I try to connect to the database I get the following error:

Exception in thread "main" java.sql.SQLTransientConnectionException: postgresDB - Connection is not available, request timed out after 30006ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
	at slick.jdbc.hikaricp.HikariCPJdbcDataSource.createConnection(HikariCPJdbcDataSource.scala:14)
	at slick.jdbc.JdbcBackend$BaseSession.<init>(JdbcBackend.scala:494)
	at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:46)
	at slick.jdbc.JdbcBackend$DatabaseDef.createSession(JdbcBackend.scala:37)
	at slick.basic.BasicBackend$DatabaseDef.acquireSession(BasicBackend.scala:250)
	at slick.basic.BasicBackend$DatabaseDef.acquireSession$(BasicBackend.scala:249)
	at slick.jdbc.JdbcBackend$DatabaseDef.acquireSession(JdbcBackend.scala:37)
	at slick.basic.BasicBackend$DatabaseDef$$anon$3.run(BasicBackend.scala:275)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:750)
Caused by: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.
	at org.postgresql.Driver$ConnectThread.getResult(Driver.java:365)
	at org.postgresql.Driver.connect(Driver.java:288)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:88)
	at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:71)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
	at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:706)
	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:692)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	... 3 more

Process finished with exit code 1

I’ve followed the docs and set up my build.sbt and application.conf according to their directions.

build.sbt

name := "foram-rest-api"

version := "0.1"

scalaVersion := "2.12.8"

val akkaVersion = "2.6.8"
val akkaHttpVersion = "10.2.8"
val scalaTestVersion = "3.0.5"

libraryDependencies ++= Seq(
  "com.typesafe.akka" %% "akka-actor-typed" % akkaVersion,
  // akka streams
  "com.typesafe.akka" %% "akka-stream" % akkaVersion,
  // akka http
  "com.typesafe.akka" %% "akka-http" % akkaHttpVersion,
  "com.typesafe.akka" %% "akka-http-spray-json" % akkaHttpVersion,
  "com.typesafe.akka" %% "akka-http-testkit" % akkaHttpVersion,
  // testing
  "com.typesafe.akka" %% "akka-testkit" % akkaVersion,
  "org.scalatest" %% "scalatest" % scalaTestVersion,
  // JWT
  "com.pauldijou" %% "jwt-spray-json" % "2.1.0",
  // Slick and Postgres
  "com.typesafe.slick" %% "slick" % "3.3.3",
  "org.slf4j" % "slf4j-nop" % "1.6.4",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.3.3",
  "org.postgresql" % "postgresql" % "9.4-1206-jdbc42"
)

application.conf

postgresDB = {
  connectionPool = "HikariCP"
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    serverName = "localhost"
    portNumber = "5432"
    databaseName = "pgtest"
    user = "postgres"
    password = "postgres"
  }
  numThreads = 10
}

The postgres database is up and running locally and I can connect to it via psql, just not via Slick.

The full source code is in the project’s GitHub repo.

I’m sure I’m missing something obvious but if anyone has any suggestions, I would be very grateful to hear them.

2

No recent experience with slick here, but my guess would be that the JDBC driver version is outdated/doesn’t match your Postgres server version. (I have the postgresql:42.2.20 jar, pulled by doobie.) Try updating the driver dependency to a more recent version matching your server. More suggestions for this symptom e.g. here.

2 Likes
3

Thank you so much! Updating the JDBC driver to 42.3.3 got the connection to work.

I had been stuck on this for absolutely ages.

4

Im new to Scala aswell, and was taking a look at both slick and doobie: is there a reason you switched to doobie?

How would you describe your experiences thus far with Slick?

5

It’s been quite some time since I’ve used Slick, so I cannot really comment on it, and I wouldn’t say I have switched, it’s rather that doobie seemed to be a better match for the stuff I’ve been working on recently - rather simple database schema with recurring schema changes where I prefer to have full control at the SQL level, anyway, and a cats-effect based stack where doobie blends in seamlessly. With a different project profile, I might look into Slick again, but for my current requirements I’m quite happy with doobie. (Ok, it’d be really nice if the cats-effect 3 based v1.0 went from RC to stable eventually. :slight_smile:)

2 Likes