Slick issue when going with PostgreSQL

Question

I'm using slick in a scala project to query some tables.

    //define table
object Addresses extends Table[Address]("assetxs.address") {
  def id = column[Int]("id", O.PrimaryKey)
  def street = column[String]("street")
  def number = column[String]("number")
  def zipcode = column[String]("zipcode")
  def country = column[String]("country")
  def * = id ~ street ~ number ~ zipcode ~ country <> (Address, Address.unapply _)
}

If I use any query of this table it does not work (it says it cannot find my table) so I went further and print out the query like:

implicit val session = Database.forURL("jdbc:postgresql://localhost:5432/postgres", driver = "org.postgresql.Driver", user="postgres", password="postgres").createSession()
      session.withTransaction{
        val query = Query(Addresses)
        println("Addresses: " + query.selectStatement)
}

I noticed that the name of the schema.table appears in "" so the statement is:

select x2."id", x2."street", x2."number", x2."zipcode", x2."country"
from "assetxs.address" x2

which of course does not work (I've tried to run it in PostgreSQL tool and I needed to remove "" from table name in order to have it working.

Can you please tell me if there is any slick option to not include "" in any query when using table names?

Answer 1

You've put the schema into the table name. A (quoted) table name containing a dot character is valid in SQL but it's not what you want here. You have to specify the schema separately:

object Addresses extends Table[Address](Some("assetxs"), "address")

Answer 2

In the end I was able to solve this issue.

I specify the table name only:

object Addresses extends Table[Address]("address")

and change my postgresql conf to include my schema when searching (it seems that slick is looking on public schema only):

search_path = '"$user",assetxs,public'

and now it works.

Answer 3

The solution I found when wanting to work with both H2 (testing) and Postgres (production) using liquibase and slick.

• Stick with lowercase in your Slick Table objects

class MyTable(tag: Tag) extends Table[MyRecord](tag, Some("my_schema"), "my_table")

• In your H2 url config you'll need to specify DATABASE_TO_UPPER=false (this prevents the table and column names from being upper cased) and put quotation marks around the INIT schema (this prevents the schema from being upper cased)

url = jdbc:h2:mem:test;MODE=PostgreSQL;DATABASE_TO_UPPER=false;INIT=create schema if not exists \"my_schema\"\;SET SCHEMA \"my_schema\""

• When specifying schema names in liquibase scripts it must also be quoted so that H2 won't try to capitalize it.

Answer 4

Since this problem is still bothering Scala newcomers (like me), I've performed small research and found that such an application.conf was successful with Slick 3.1.1 and PostgreSQL 9.5:

postgres.devenv = {
  url = "jdbc:postgresql://localhost:5432/dbname?currentSchema=customSchema"
  user = "user"
  password = "password"
  driver = org.postgresql.Driver
}