Fork me on GitHub
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


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


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


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


a bit like this:


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


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


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


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


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

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


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


simples 🙂