Fork me on GitHub
#sql
<
2020-10-17
>
Aviv Kotek08:10:36

hi, not an sql expert, using next-jdbc with MYSQL, I would like to switch my data-source/connection to another database. so i'd have methods running on "default" database ("dbname" in spec), but other methods to run on another db ("dbname2" in spec). something like that: => (def db1 {:dbtype "h2" :dbname "db1" :host localhost :port 3306}) (def ds (jdbc/get-datasource db1)) (jdbc/execute! ds ........) -> triggers "db1" (def db2 {:dbtype "h2" :dbname "db2" :host localhost :port 3306}) (def ds2 (jdbc/get-datasource db2) --> i'd like to skip this step - assuming it creates a new connection (jdbc/execute! ds2 .....)-> triggers "db2" i'd like to have: "USE db2" (jdbc/execute! ds .....) -> triggers "db2" without creating ds2 Is there any way I can switch "dbnames" aka "USE db2" but modifying correct connection? does creating a new datasource means new connection? do I have to create new connection for such task? is there any alternative? ty

hiredman18:10:47

The solution to this to not use globals(defs), pass the database to use as an argument

Aviv Kotek09:10:27

yes but it's in a context of stateful app (using "sierra component"), so my db-conn is reused

Aviv Kotek09:10:39

I'd like to "modify" it, or alternatively just hold another conn

dharrigan10:10:39

I too had the need to use multiple databases in a few of my applications. What I do, is this. I use Juxt Clip (https://github.com/juxt/clip) to maintain (let's say) two connections, i.e., a connection to one database and a connection to another database

dharrigan10:10:01

Then, in order for me to do the right thing, I simply pass into the function that does the sql lookup, which database to use

dharrigan10:10:05

a bit like this:

dharrigan10:10:15

So, in this file: you see where I define a starttrek-db key that when Clip is initiated in your app, it will invoke the appropriate start (and post-start and stop) functions

dharrigan10:10:52

at the end, the startrek-db key has a handle on the connection that is returned from the /connection-pool-start function

dharrigan10:10:28

imagine, if you were to simply andd in a vulcan-db key with a near-duplicate of the connetion-pool-start, then you would have two keys pointing to two different dbs

dharrigan10:10:48

so then, when you get to your db and decide upon which to invoke, you simply do:

dharrigan10:10:35

(defn select
  ([sql datasource] (select sql datasource {}))
  ([sql datasource opts]
   (log/debugf "Executing JDBC '%s'." sql)
   (try
    (let [results (jdbc/execute-one! datasource sql opts)]
      (when (seq results)
        (log/tracef "JDBC Result '%s'." results))
      results)
    (catch Exception e
      (log/error e)
      (throw e)))))

(let [{:keys [startrek-db]} app-config]
  (select "select 1 from bar" startrek-db))

or...

(let [{:keys [vulcan-db]} app-config]
  (select "select 1 from bar" vulcan-db))

dharrigan10:10:46

simples 🙂