Basic Clojure interaction with SQL

database.clj

(ns bookkeeper.database
  (:require [clojure.java.jdbc :as sql]
            [clj-time.format :as f]))

(def db {:classname "com.mysql.jdbc.Driver"
         :subprotocol "mysql"
         :subname "//database-server:3306/database"
         :user "user"
         :password "password"})

;; ----------------------------------------------------------------------------
;;  Explicit connections prevent composition.  So we wrap all db functions in
;;  a fn that checks if a connection exists, and only if not does it make one
;;  that way the outermost fn that needs a db creates the connection and those
;;  internal use it.

(defmacro wrap-connection [& body]
  `(if (sql/find-connection)
    ~@body
    (sql/with-connection db ~@body)))

(defmacro transaction [& body]
  `(if (sql/find-connection)
     (sql/transaction ~@body)
     (sql/with-connection db (sql/transaction ~@body))))

;; ---------------------------------------------------------------------------
;;  Utilities
(def mysql-formatter
  (f/formatter "YYYYMMddHHmmss"))

(defn fmt-time [t]
  (f/unparse mysql-formatter t))

;; ---------------------------------------------------------------------------
;;  A protocal that must be followed by anything DB persistable
(defprotocol DB-ENTRY
  (insert [entry])
  (delete [entry]))

;; ----------------------------------------------------------------------------
(defn- convert-time
  "If the map contains a :time key then convert it to SQL time"
  [m]
  (if (contains? m :time)
    (assoc m :time (fmt-time (:time m)))
    m))

(defn- format-for-ins
  "Preformat a map for insertion into the database.  Removes id field and
   converts time"
  [m]
  (-> m
      (dissoc :id)
      (convert-time)))

;; ----------------------------------------------------------------------------
(defn ins
  "Inserts a map into the database. The keys of the map must map onto the fields
of the database.  In most cases we have an
autoincrement field.  So fetch and return the map with this included."
  [in-m table]
  (wrap-connection
   (let [m (format-for-ins in-m)
         ins (sql/insert-values table (keys m) (vals m))]
     ;; Bind in the returned primary key
     (assoc in-m :id (:generated_key ins)))))

;; ----------------------------------------------------------------------------
(defn del
  "Remove a map from the database via the passed in primary key"
  [m table]
  (wrap-connection
   (sql/delete-rows
    table
    ["id=?" (:id m)])))

;; ----------------------------------------------------------------------------
;; Playing to see where this should go
(defn select-first [query]
  "Return the first result of query in a vector."
  (wrap-connection
   (sql/with-query-results res
     query
     (into {} (first res)))))

;; ----------------------------------------------------------------------------
(defn select-all [query]
  "Return all results of query in a vector.  No ordering guarantees are made."
  (wrap-connection
   (sql/with-query-results res
     query
     (vec (map (partial into {}) res)))))