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?

Are you sure that this is the issue you are having? Unquoted identifiers are converted to lower case in PostgreSQL, in your example removing the quotes would result in an identical query. — Ants Aasma, Nov 14 '12 at 14:56
@AntsAasma, I'm sure the "assetxs.address" is the one that gives the headache. — Michael Krelin - hacker, Nov 14 '12 at 14:57
http://stackoverflow.com/questions/6720626/how-do-i-specify-a-postgresql-schema-in-scalaquery — Michael Krelin - hacker, Nov 14 '12 at 14:58
If you try to run this query in PostgreSQL tool UI it will not work. So what slick prints out it's not correct... If I try to run the query from scla/slick, it tells me: `ERROR: relation "assetxs.address" does not exist` — Cristian Boariu, Nov 14 '12 at 14:58
@MichaelKrelin-hacker If I not specify the schema there, it tells me `ERROR: relation "address" does not exist` — Cristian Boariu, Nov 14 '12 at 15:00
@CristianBoariu, you can, of course, extend search path to include the `assetxs` schema - this way you won't need to specify. The thing is - I have no idea about scala and slick, so I don't know how to work around it from that side. — Michael Krelin - hacker, Nov 14 '12 at 15:01
But the problem seems to have been acknowledged quite a while ago - https://groups.google.com/group/scalaquery/tree/browse_frm/month/2011-07/55b3f8f6e18a2cfa?rnum=31&_done=/group/scalaquery/browse_frm/month/2011-07?&pli=1 — Michael Krelin - hacker, Nov 14 '12 at 15:02

score 6 · Accepted Answer · answered Nov 15 '12 at 09:33

6

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.

answered Nov 15 '12 at 09:33

Cristian Boariu

9,331
12
87
159

Happy to see you found a workaround, and thanks for sharing :-) Have you tried this: jdbc:postgresql://localhost:5432/yourdatabase?searchpath=assetxs – Jack Nov 15 '12 at 13:06

score 5 · Answer 2 · answered Nov 14 '12 at 15:21

5

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")

answered Nov 14 '12 at 15:21

szeiger

1,386
8
10

1

Thanks but seems that in this way the schema is no longer included in sql statement: `SQL statement: select x2."id", x2."street", x2."number", x2."zipcode", x2."count ry" from "address" x2` – Cristian Boariu Nov 14 '12 at 15:28
@CristianBoariu If I'm not mistaking, you can specify the schema as part of the database URL, but I can't test that right now. – Jack Nov 15 '12 at 01:16
@JacobusR I've tried that too like `url?schema=assetx` but still it does not work... – Cristian Boariu Nov 15 '12 at 08:07
I found these two posts that may help: http://stackoverflow.com/a/4820666/828757 and http://stackoverflow.com/a/4185710/828757. The problem you are referring to was also a problem with ScalaQuery, but unfortunately the issue link is broken, so we won't know if it was fixed: http://stackoverflow.com/questions/6720626/how-do-i-specify-a-postgresql-schema-in-scalaquery – Jack Nov 15 '12 at 13:02
3

The new working link to the original issue is https://github.com/slick/slick/issues/19. The fact that the schema name from the table definition is ignored is a bug: https://github.com/slick/slick/issues/8. – szeiger Nov 21 '12 at 13:20

score 3 · Answer 3 · answered Jun 25 '15 at 01:22

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.

score 3 · Answer 4 · answered Sep 08 '16 at 22:46

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
}

score -2 · Answer 5 · answered Nov 14 '12 at 17:43

-2

You're just using the wrong driver, check your imports

import scala.slick.driver.PostgresDriver.simple._

answered Nov 14 '12 at 17:43

Somatik

4,599
2
35
47

I already use the import you specify: `import scala.slick.driver.PostgresDriver.simple._` – Cristian Boariu Nov 14 '12 at 17:49

Slick issue when going with PostgreSQL

5 Answers5

Linked