Clojure establishing multiple database connections

Question

We have a Clojure web application that is used by multiple projects (>20) that have multiple users logging in simultaneously. All projects have their own MySQL database. We have tried to figure out a way to use one application instance to serve requests from users that are delivered from their project's database.

The following script shows the principles of our multiple connections and should be executable in REPL (with correct database setup).

(ns testmultiple.core
  (:require
    [clojure.java.jdbc :as jdbc]
    [compojure.core :refer [defroutes GET ANY routes context]]
    [conman.core :as conman]
    [mount.core :refer [defstate]]))

(def database-urls {:DB1 "jdbc:mysql://localhost:3306/DB1?user=DB1_user&password=DB1_password"
                    :DB2 "jdbc:mysql://localhost:3306/DB2?user=DB2_user&password=DB2_password"})

;; Connects to all databases in pool-specs
(defn connect!
  [pool-specs]
  (reduce merge (map (fn [pool-spec]
                       {(keyword (key pool-spec)) (conman/connect! {:jdbc-url (val pool-spec)})}) pool-specs)))

;; Disconnect from all databases in db-connections
(defn disconnect!
  [db-connections]
  (map (fn [db] (conman/disconnect! (val db))) db-connections))

;; Establish connections to all databases
;; and store connections in *dbs*
(defstate ^:dynamic *dbs*
          :start (connect!
                   database-urls)
          :stop (disconnect! *dbs*))

;; Bind queries to *db* dynamic variable which is bound
;; to each clients database before executing queries
;; The queries file defines the query get-user which
;; returns user by user id
(def ^:dynamic *db* nil)
(conman/bind-connection *db* "sql/queries.sql")

(mount.core/start)

; Define function that executes in current *db* binding
(defn getuser [id] (get-user {:id id}))

; Works, the user with Id 670 is returned from DB1
(with-bindings {#'*db* (:DB1 *dbs*)} (getuser 670))

; Works, the user with Id 670 is returned from DB2
(with-bindings {#'*db* (:DB2 *dbs*)} (getuser 670))

More specifically, the project is inferred from the URL request in the router. The following code shows the principle for the router. Accessing www.example.com/DB1/page1 and www.example.com/DB2/page2 will show page1 with data from DB1 and page2 with data from DB2, respectively.

(defn serve-page1 [] (str "page1" (getuser 670)))
(defn serve-page2 [] (str "page2" (getuser 670)))

(def home-routes
  (context "/:project" [project]
    (if (contains? *dbs* (keyword project))
      (routes
        (GET "/page1" []
          (with-bindings {#'*db* ((keyword project) *dbs*)}
            (serve-page1)))
        (GET "/page2" []
          (with-bindings {#'*db* ((keyword project) *dbs*)}
            (serve-page2))))
      (ANY "*" [] (str "Project not found")))))

This will be an application with considerable traffic. Notably, we are still in development phase and have thus not been able to test this solution with more than a couple of databases running on localhost. Our questions are

• Is establishing multiple connections like this reasonable, stable and scalable?
• Are there other better methods for the routing and dynamic binding of the project's database?

Answer 1

Is establishing multiple connections like this reasonable, stable and scalable?

Yes, this is a very reasonable approach. Very few database systems are limited by the number of outgoing connections. Both JDBC and Korma will handle this just fine in clojure. You do need to be aware of which requests are dependent on which DB when building the monitoring and ops related components of course. So you can tell which DB is causing problems.

Are there other better methods for the routing and dynamic binding of the project's database?

My only suggestion would be to explicitly pass the DB to each function rather than using a binding, though this is a personal style opinion and your approach will clearly work.