asphalt

A Clojure library for JDBC access.

Why Asphalt?

• Simple (as in separation of concerns)
  • Extensible connection mechanism (via a protocol)
  • Extensible SQL source (via a protocol)
  • SQL params setter is orthogonal to SQL and operation
  • Retrieving data from query result is orthogonal to SQL query and operation
  • Extensible transaction strategy (via a protocol)
• Performance and Control
  • Aspects can be overridden independent of each other
  • Support for type hints in SQL
• Rich transaction support
  • Transaction propagation (borrowed from EJB, Spring)
  • Fine-grained control over commit and rollback
  • Transaction isolation
  • Declarative transaction

Usage

Clojars coordinates: [asphalt "0.6.7"] (requires Java 7 or higher, Clojure 1.6 or higher)

(require '[asphalt.core :as a])        ; for most common operations
(require '[asphalt.transaction :as t]) ; for transactions

Connection source

You need a valid JDBC connection source (instance of asphalt.type.IConnectionSource protocol) to work with Asphalt. The following are supported by default:

• A map containing connection parameters (any of the following key sets)
  • :connection (java.sql.Connection instance)
  • :factory (fn that accepts a map and returns a JDBC connection)
  • :classname (JDBC driver classname), :connection-uri (JDBC URL string)
  • :subprotocol (sub-protocol portion of JDBC URL string), :subname (rest of the JDBC URL string)
  • :datasource (javax.sql.DataSource instance) with following optional attributes
    • :username or :user (database user name)
    • :password (database password)
  • :name (JNDI name) with optional attributes
    • :context (javax.naming.Context)
    • :environment (environment map)
• A JDBC URL string
• JDBC datasource (javax.sql.DataSource instance)
• JDBC connection (java.sql.Connection instance)

For development you may define a map based connection source:

(def conn-source {:subprotocol "mysql"
                  :subname "//localhost/testdb"
                  :username "testdb_user"
                  :password "secret"})

Typically one would create a connection-pooled datasource as connection source for production use:

• clj-dbcp
• c3p0
• bone-cp
• hikari-cp

Simple usage

This section covers the minimal examples only. Advanced features are covered in subsequent sections.

;; insert row, returning auto-generated keys
(a/genkey conn-source
  "INSERT INTO emp (name, salary, dept) VALUES (?, ?, ?)"
  ["Joe Coder" 100000 "Accounts"])

;; update rows, returning the number of rows updated
;; used for `INSERT`, `UPDATE`, `DELETE` statements, or DDL statements such as `ALTER TABLE`, `CREATE INDEX` etc.
(a/update conn-source "UPDATE emp SET salary = ? WHERE dept = ?" [110000 "Accounts"])

;; query zero (nil) or more rows (vector of rows)
(a/query a/fetch-rows
  conn-source
  "SELECT name, salary, dept FROM emp" [])

;; query zero (nil) or one row (exception is thrown if result-set has more than one row)
(a/query a/fetch-optional-row
  conn-source
  "SELECT name, salary, dept FROM emp" [])

;; query one row, and work with column values via de-structuring
(let [[name salary dept] (a/query a/fetch-single-row ...)]
  ;; work with the column values
  ...)

SQL templates

Ordinary SQL with ? place-holders may be boring and tedious to work with. Asphalt uses SQL-templates to fix that.

(a/defsql sql-insert "INSERT INTO emp (name, salary, dept) VALUES ($name, $salary, $dept)")

(a/defsql sql-update "UPDATE emp SET salary = $new-salary WHERE dept = $dept")

With SQL-templates, you can pass param maps with keys as param names:

(a/genkey conn-source sql-insert
  {:name "Joe Coder" :salary 100000 :dept "Accounts"})

(a/update conn-source sql-update {:new-salary 110000 :dept "Accounts"})

SQL-templates are functions

SQL-templates defined with defsql are invokable as functions:

;; defsql infers connection worker as either update or query
(sql-update conn-source {:new-salary 110000 :dept "Accounts"})

;; for genkey we need to specify the :conn-worker option as such
(a/defsql sql-insert "INSERT INTO emp (name, salary, dept) VALUES ($name, $salary, $dept)"
  {:conn-worker a/genkey})

(sql-insert conn-source {:name "Joe Coder" :salary 100000 :dept "Accounts"})

;; same as above, but using positional params
(sql-insert conn-source ["Joe Coder" 100000 "Accounts"])

SQL templates with type hints

The examples we saw above read and write values as objects, which means we depend on the JDBC driver for the conversion. SQL-templates let you optionally specify the types of params and also the result columns in a query:

(a/defsql sql-insert
  "INSERT INTO emp (name, salary, dept) VALUES (^string $name, ^int $salary, ^string $dept)")

(a/defsql sql-select "SELECT ^string name, ^int salary, ^string dept FROM emp")

;; multi-value param
(a/defsql sql-update "UPDATE emp SET salary = ^int $new-salary WHERE dept IN (^strings $depts)")

The operations on the type-hinted SQL-templates remain the same as non type-hinted SQL templates, but internally the appropriate types are used when communicating with the JDBC driver.

Starting with version 0.6.6, dynamic non-parameter variable substitution is supported. See the snippet below:

;; notice the ^sql type hint
(a/defsql insert-into
  "INSERT INTO ^sql $table (id, dept) VALUES ($id, $dept)")

;; value is passed like normal parameters
(insert-into conn-source {:table "emp_697"
                          :id "F69-2239-AX"
                          :dept "Accounts"})

;; the above SQL is treated as: INSERT INTO emp_697 (id, dept) VALUES ($id, $dept)

Supported type hints

The following types are supported as type hints:

Type	Comments	Multi-value	Result on NULL
sql	Non-param substitution	none	not applicable
nil	Dynamic/slow discovery	none	nil
array		arrays	nil
ascii-stream		ascii-streams	nil
big-decimal		big-decimals	nil
binary-stream		binary-streams	nil
blob		blobs	nil
bool	Duplicate of boolean	bools	false
boolean		booleans	false
byte		bytes	0
byte-array		byte-arrays	nil
character-stream		character-stream	nil
clob		clobs	nil
date		dates	nil
double		doubles	0.0
float		floats	0.0
int		ints	0
integer	Duplicate of int	integers	0
long		longs	0
ncharacter-stream		ncharacter-streams	nil
nclob		nclobs	nil
nstring		nstrings	nil
object	Catch-all type	objects	nil
ref		refs	nil
row-id		row-ids	nil
string		strings	nil
sql-xml		sql-xmls	nil
time		times	nil
timestamp		timestamps	nil
url		urls	nil

Note on type hints in result columns:

• Primitive type hints for result columns coerce NULL value as primitive default values as shown in the table.
• You may specify ^^ (shortcut) as type hint to imply default or no type hint, e.g. SELECT ^^ name, ^^ age, ^string join_date FROM emp WHERE id = ^int $id

Note on multi-value types:

• Only applicable for SQL params, not for query result types
• Corresponding param must be a vector of values
• Every multi-value param expands into comma-separated ? placeholders

Caveats with SQL-template type hints

• Type hints are optional at each param level.
• When type-hinting result columns in a query either type-hint every column, or do not type-hint any column at all.
• Wildcards (e.g. SELECT *) in return columns are tricky to use with return column type hints. You should hint every return column type as in SELECT * ^int ^string ^int ^date if the return columns are of that type.
• Queries that use UNION are also tricky to use with return column type hints. You should hint only one set of return columns, not in every UNION sub-query.

Transactions

Simple example:

(t/with-transaction [txn conn-source] {}
  (a/update txn sql-insert ["Joe Coder" 100000 "accounts"])
  (a/update txn sql-update {:new-salary new-salary :id id}))

By default, if the code doesn't throw any exception the transaction would be committed and on all exceptions the transaction would be rolled back.

Advanced example

(a/with-transaction [txn data-source] {:isolation :read-committed
                                       :propagation t/tp-mandatory}
  (let [[id salary dept] (a/query a/fetch-single-row txn sql-select-with-id [])
        new-salary (compute-new-salary salary dept)]
    (a/update txn sql-update {:new-salary new-salary :id id})))

Supported isolation levels:

• :none
• :read-committed
• :read-uncommitted
• :repeatable-read
• :serializable

Supported transaction propagation types:

• t/tp-mandatory
• t/tp-nested
• t/tp-never
• t/tp-not-supported
• t/tp-required
• t/tp-requires-new
• t/tp-supports

Declarative transaction

Given a fn that accepts a connection source as its first argument, it is possible to wrap it with transaction options such that the fn is invoked in a transaction.

(defn foo
  [conn-source emp-id]
  ..)


(def bar (t/wrap-transaction-options foo {:isolation :read-committed
                                          :propagation t/tp-requires-new}))

;; call foo in a transaction as per the transaction options
(bar conn-source emp-id)

Development

Running tests: lein do clean, test or lein with-profile c18,dev,dbcp test

Running performance benchmarks: lein with-profile c18,dev,dbcp,perf test

License

Copyright © 2015-2018 Shantanu Kumar (kumar.shantanu@gmail.com, shantanu.kumar@concur.com)

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