Awesome

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:

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

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