This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-02-09
Channels
- # beginners (205)
- # boot (6)
- # cider (22)
- # cljs-dev (41)
- # cljsrn (4)
- # clojure (97)
- # clojure-dev (61)
- # clojure-greece (40)
- # clojure-italy (8)
- # clojure-russia (16)
- # clojure-spec (18)
- # clojure-uk (34)
- # clojurescript (14)
- # community-development (1)
- # cursive (45)
- # datomic (39)
- # fulcro (66)
- # jobs (2)
- # lein-figwheel (1)
- # lumo (9)
- # off-topic (8)
- # parinfer (98)
- # portkey (8)
- # re-frame (81)
- # reagent (54)
- # remote-jobs (17)
- # ring (2)
- # shadow-cljs (217)
- # spacemacs (32)
- # sql (24)
- # test-check (6)
- # unrepl (73)
- # yada (12)
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? 🙏@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
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"]))
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 clojure.java.jdbc
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?).
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
@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