Fork me on GitHub
#sql
<
2018-02-09
>
anler08:02:09

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:

(do
  (import (java.sql DriverManager))
  (-> (DriverManager/getConnection "jdbc:)
      (.createStatement)
      (.executeQuery "select * from users")
      (.next)))
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? 🙏

seancorfield18:02:48

@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.

seancorfield18:02:00

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

anler18:02:13

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

hiredman18:02:22

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

anler18:02:49

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

anler18:02:10

and check if I can connect to it from jdbc

hiredman18:02:14

which postgres driver / version are you using?

seancorfield18:02:15

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...

anler18:02:15

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

seancorfield18:02:12

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

anler18:02:58

meh, same thing 🙈

anler18:02:32

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

anler18:02:06

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

anler18:02:18

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

seancorfield18:02:57

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

seancorfield19:02:13

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?).

anler19:02:19

nah, no worries, the whole idea is a bit crazy, I didn't have any expectations 😄

anler19:02:30

thanks a lot though!

hiredman19:02:23

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

anler19:02:34

@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

anler19:02:35

Indeed 😅