YeSQL with connection pool?

Question

I'm checking if YeSQL may help in my Clojure project, but I cannot find any example of YeSQL using a connection pool.

does this mean that YeSQL creates a new connection to every statement?

I also found an example on how to use transactions using clojure.java.jdbc/with-db-transaction, but I feel it is outdated (I haven't tried yet).

does this mean that YeSQL depends on clojure.java.jdbc to commit/rollback control? in this case, shouldn't I just use clojure.java.jdbc alone, since YeSQL doesn't offer too much more (other than naming my queries and externalizing them)?

thanks in advance

Answer 1

YeSQL doesn't handle connections or connection pooling. You need to handle it externally and provide a connection instance to the query function.

As you can see from the YeSQL example from README:

; Define a database connection spec. (This is standard clojure.java.jdbc.)
(def db-spec {:classname "org.postgresql.Driver"
              :subprotocol "postgresql"
              :subname "//localhost:5432/demo"
              :user "me"})

; Use it standalone. Note that the first argument is the db-spec.
(users-by-country db-spec "GB")
;=> ({:count 58})

; Use it in a clojure.java.jdbc transaction.
(require '[clojure.java.jdbc :as jdbc])
(jdbc/with-db-transaction [connection db-spec]
   {:limeys (users-by-country connection "GB")
    :yanks  (users-by-country connection "US")})

If you ask how to add connection pool handling you might check an example from Clojure Cookbook.

As for the transaction handling, YeSQL documentation is none, but source is quite easy to understand:

(defn- emit-query-fn
  "Emit function to run a query.
   - If the query name ends in `!` it will call `clojure.java.jdbc/execute!`,
   - If the query name ends in `<!` it will call `clojure.java.jdbc/insert!`,
   - otherwise `clojure.java.jdbc/query` will be used."
  [{:keys [name docstring statement]}]
  (let [split-query (split-at-parameters statement)
        {:keys [query-args display-args function-args]} (split-query->args split-query)
        jdbc-fn (cond
                 (= [\< \!] (take-last 2 name)) `insert-handler
                 (= \! (last name)) `execute-handler
                 :else `jdbc/query)]
    `(def ~(fn-symbol (symbol name) docstring statement display-args)
       (fn [db# ~@function-args]
         (~jdbc-fn db#
                   (reassemble-query '~split-query
                                     ~query-args))))))

So it will just generate a function that will either call clojure.java.jdbc/execute! or clojure.java.jdbc/insert! with the generated query. You might need to refer to those functions' documentation for further details.

Answer 2

When doing transactions using YesQL, I wrap the YesQL calls in a clojure.java.jdbc/with-db-transation invocation, and pass the generated connection details in to the YesQL function call, which they'll use instead of the standard non-transaction connection if supplied. For example:

;; supply a name for the transaction connection, t-con, based on the existing db connection
(jdbc/with-db-transaction [t-con db-spec] 
  ;; this is the yesql call with a second map specifying the transaction connection
  (create-client-order<! {...} {:connection t-con}))

All wrapped YesQL calls using the {:connection t-con} map will be part of the same transaction.