Fork me on GitHub

Hi all, I've been trying to make a schema-aware connection to postgres using java.jdbc but so far without any success . I have tried directly with (what I think it is) postgres' jdbc driver but still doesn't work as expected, here's what I'm doing:

  (import (java.sql DriverManager))
  (-> (DriverManager/getConnection "jdbc:)
      (.executeQuery "select * from users")
I'm expecting true as the result of the do-block, but it returns false, and the only way so far to make it return what I want is to prefix the users table in the query with the schema as in ... from ns1.users. Anyone has done this? ­čÖĆ


@inbox Does this not work?

(def db-spec {:dbtype "postgres" :dbname "ns1" :user "user" :password "password"})
(jdbc/query db-spec "select * from users")
That's pretty much what we use for MySQL to have schema-specific connections.


Or am I misunderstanding what you're trying to do?


Hi @seancorfield that didn't work ­čś× what I'm trying to do is a bit hacky, I have a test database roc_test, and I'm trying to run my integration tests in parallel, for that I want each db-connection fixture to create a new schema inside the roc_test db, re-connect to same db but targeting the new schema (cause I tried with (jdbc/db-do-commands db-spec ["SET search_path TO ns1"]) but didn't work) and run the migrations and the tests for each integration in isolation


in postgres a schema is sort of like a clojure namespace, and it is a way to organize names in a single database


then I tried to test the minimal example, which is a namespace:


and check if I can connect to it from jdbc


which postgres driver / version are you using?


When you do the SET search_path ... command, are you reusing that connection across the whole session? If not, I would not expect it to "stick" for subsequent queries...


dunno, I'm probably doing something wrong, but hard to know what when this is the first time I work with jdbc


(jdbc/with-db-connection [con-spec db-spec]
  (jdbc/db-do-commands con-spec ["SET search_path TO ns1"])
  (jdbc/query con-spec ["SELECT * FROM users"]))


meh, same thing ­čÖł


I'm uploading screenshots but slack keeps show me this message, I don't know if you're able to see them


just in case you're not:

(jdbc/with-db-connection [con-spec {:dbtype "postgresql"
                                    :dbname "roc_test"
                                    :user "roc"
                                    :password "roc"}]
  (jdbc/db-do-commands con-spec ["SET search_path TO ns1"])
  (jdbc/query con-spec ["SELECT * FROM users"]));; => ()

(jdbc/with-db-connection [con-spec {:dbtype "postgresql"
                                    :dbname "roc_test"
                                    :user "roc"
                                    :password "roc"}]
  (jdbc/db-do-commands con-spec ["SET search_path TO ns1"])
  (jdbc/query con-spec ["SELECT * FROM ns1.users"]))
;; => ({:id 1, :name "foo"} {:id 2, :name "bar"} {:id 3, :name "baz"})


I wasn't sure to be reusing the same connection when using that's why I went all the way down to java.sql but still the same happens


We see the screenshots, yes, but code snippets are easier to read.


I don't know what to suggest after that, sorry. I don't use Postgres (and I don't think MySQL has an equivalent to this sort of thing?).


nah, no worries, the whole idea is a bit crazy, I didn't have any expectations ­čśä


thanks a lot though!


I am not 100% sure which function you would use, but there must be someway to run the postgres command which shows you the search path


@hiredman jdbc/query seems to work:

(jdbc/with-db-connection [con-spec {:dbtype "postgresql"
                                    :dbname "roc_test"
                                    :user "roc"
                                    :password "roc"}]
  (jdbc/db-do-commands con-spec ["SET search_path TO ns1"])
  (jdbc/query con-spec ["SHOW search_path"]))
;; => ({:search_path "\"$user\",public"})
and as you can see, the search path stays with its initial value $user,public


Indeed ­čśů