Basic Clojure MySQL wrappers, use at your own risk

gistfile1.clj

;ADD TO YOUR project.clj:
[org.clojure/java.jdbc "0.3.4"]
[mysql/mysql-connector-java "5.1.31"]
[com.jolbox/bonecp "0.8.0.RELEASE"]
;https://github.com/ptaoussanis/timbre for error logging, make sure to set your error log file
[com.taoensso/timbre "3.2.1"]
  
;SOURCE STARTS BELOW
(ns your-project.mysql-lib
  (:require [clojure.java.jdbc :as db-adapter]
            [taoensso.timbre :as timbre])
  (:import com.jolbox.bonecp.BoneCPDataSource))


;MYSQL CONFIG
(def db-spec {:classname   "com.mysql.jdbc.Driver"
              :subprotocol "mysql"
              :subname     "//mysql-host:3306/your-db-name"
              :user        "mysql-user"
              :password    "mysql-password"
              })
(defn pool
  "MySQL connection pool"
  [spec]
  (let [partitions 3
        cpds (doto (BoneCPDataSource.)
               (.setJdbcUrl (str "jdbc:" (:subprotocol spec) ":" (:subname spec)))
               (.setUsername (:user spec))
               (.setPassword (:password spec))
               (.setMinConnectionsPerPartition (inc (int (/ 4 partitions)))) ;min pool
               (.setMaxConnectionsPerPartition (inc (int (/ 20 partitions)))) ;max pool
               (.setPartitionCount partitions)
               (.setStatisticsEnabled true)
                                                                             ;; test connections every 25 mins (default is 240):
               (.setIdleConnectionTestPeriodInMinutes 25)
                                                                             ;; allow connections to be idle for 3 hours (default is 60 minutes):
               (.setIdleMaxAgeInMinutes (* 3 60))
                                                                             ;; consult the BoneCP documentation for your database:
               (.setConnectionTestStatement "/* ping *\\/ SELECT 1"))]
    {:datasource cpds}))
(def pooled-db (delay (pool db-spec)))
(defn db-connection [] @pooled-db)

(defn get-last-insert-id [db-connection]
  (-> (db-adapter/query db-connection
                        ["SELECT LAST_INSERT_ID() AS last_insert_id"])
      (first)
      :last_insert_id))

(defn execute-return-last-insert-id!
  ([sql-vector] (try (db-adapter/with-db-transaction [db-connection db-spec]
                                                     (db-adapter/execute! db-connection
                                                                          sql-vector)
                                                     (get-last-insert-id db-connection))
                     (catch Exception e
                            (timbre/error e "MySQL transaction error - going to retry")
                            (execute-return-last-insert-id! sql-vector true))))
  ([sql-vector second-attempt?]
   (try (db-adapter/with-db-transaction [db-connection db-spec]
                                        (db-adapter/execute! db-connection
                                                             sql-vector)
                                        (get-last-insert-id db-connection))
        (catch Exception e
               (timbre/error e "MySQL transaction error - failed after second attempt")))))


(defn execute!
  "Executes a query to MySQL, usually one that changes the database; uses Timbre to log any errors;
  sql-vector is in the format [query-string-with-?-params param1 param2 etc]
  If the operation fails with an exception, the function calls itself once to retry, might happen if the MySQL connection has timed out"
  ([sql-vector] (try (db-adapter/execute! (db-connection)
                                          sql-vector)
                     (catch Exception e
                            (timbre/error e "MySQL execute error - going to retry")
                            (execute! sql-vector true))))
  ([sql-vector second-attempt?]
   (try (db-adapter/execute! (db-connection)
                             sql-vector)
        (catch Exception e
               (timbre/error e "MySQL execute error - failed after second attempt")
               ;give up on retry
               '() ))))

(defn query
  "Executes a query to MySQL, usually one that doesn't change any data such as SELECT; uses Timbre to log any errors;
  sql-vector is in the format [query-string-with-?-params param1 param2 etc];
  If the operation fails with an exception, the function calls itself once to retry, might happen if the MySQL connection has timed out"
  ([sql-vector] (try (db-adapter/query (db-connection)
                                       sql-vector)
                     (catch Exception e
                            (timbre/error e "MySQL query error - going to retry")
                            (query sql-vector true))))
  ([sql-vector second-attempt?]
   (try (db-adapter/execute! (db-connection)
                             sql-vector)
        (catch Exception e
               (timbre/error e "MySQL query error - failed after second attempt")
               ;give up on retry
               '() ))))