conman

Luminus database connection management and SQL query generation library

The library provides pooled connections using the HikariCP library.

The queries are generated using HugSQL and wrapped with connection aware functions.

Usage

Conman relies on a dynamic variable to manage the connection. The dynamic variable allows the connection to be rebound contextually for transactions. When working with multiple databases, a separate var is required to track each database connection.

Defining Queries

SQL statements should be populated in files that are accessible on the resource path. For example, we could create a file resources/sql/queries.sql with the following content:

-- :name create-user! :! :n
-- :doc creates a new user record
INSERT INTO users
(id, first_name, last_name, email, pass)
VALUES (:id, :first_name, :last_name, :email, :pass)

-- :name get-user :? :1
-- :doc retrieve a user given the id.
SELECT * FROM users
WHERE id = :id

-- :name get-all-users :? :*
-- :doc retrieve all users.
SELECT * FROM users

See the official HugSQL docs for further examples.

The queries are bound to the connection using the bind-connection macro. This macro accepts the connection var followed by one or more strings representing SQL query files.

The lifecycle of the connection is expected to be managed using a library such as mount. The full list of options hat can be passed to pool-spec can be found here.

(ns myapp.db
  (:require [mount.core :refer [defstate]
            [conman.core :as conman]))

(def pool-spec
  {:jdbc-url "jdbc:postgresql://localhost/myapp?user=user&password=pass"})

(defstate ^:dynamic *db*
          :start (conman/connect! pool-spec)
          :stop (conman/disconnect! *db*))

(conman/bind-connection *db* "sql/queries.sql")

The bind-connection generates create-user! and get-user functions in the current namespace. These functions can be called in four different ways:

;; when called with no argument then the HugSQL generated function
;; will be called with an empty parameter map and the connection specified in the *db* var
(get-all-users)

;; when a parameter map is passed as the argument, then the map and the connection specified
;; in the *db* var will be passed to the HugSQL generated function
(create-user! {:id "foo" :first_name "Bob" :last_name "Bobberton" :email nil :pass nil})

;; an explicit connection and a parameter map can be
;; passed to the function
(get-user some-other-conn {:id "foo"})

;; finally, an explicit connection and a parameter map, options, and optional command options
;; can be passed to the function
(get-user some-other-conn {:id "foo"} opts)
(get-user some-other-conn {:id "foo"} opts cmd-opt1 cmd-opt2)

Next, the connect! function should be called to initialize the database connection. The function accepts a map with the database specification.

(def pool-spec
  {:jdbc-url   "jdbc:postgresql://localhost/myapp?user=user&password=pass"})

(connect! pool-spec)

For the complete list of configuration options refer to the official hikari-cp library documentation. Conman supports the following additional options:

:datasource
:datasource-classname

The connection can be terminated by running the disconnect! function:

(disconnect! conn)

A connection can be reset using the reconnect! function:

(reconnect! conn pool-spec)

When using a dynamic connection, it's possible to use the with-transaction macro to rebind it to the transaction connection. The SQL query functions generated by the bind-connection macro will automatically use the transaction connection in that case:

(with-transaction [conn]
  (jdbc/db-set-rollback-only! conn)
  (create-user!
    {:id         "foo"
     :first_name "Sam"
     :last_name  "Smith"
     :email      "sam.smith@example.com"})
  (get-user {:id "foo"}))

The isolation level and readonly status of the transaction may be specified using the :isolation and :read-only? keys respectively:

(with-transaction
    [conn :isolation :serializable]
    (= java.sql.Connection/TRANSACTION_SERIALIZABLE
       (.getTransactionIsolation (sql/db-connection conn))))
(with-transaction
  [conn :isolation :read-uncommitted]
  (= java.sql.Connection/TRANSACTION_READ_UNCOMMITTED
     (.getTransactionIsolation (sql/db-connection conn))))

License

Distributed under the Eclipse Public License either version 1.0 or (at your option) any later version.

dhruvbhatia/conman

conman

Usage

Defining Queries

License