0.6.7 docs





    Apr 21, 2018

    Shantanu Kumar
    Bangalore, India


    Index of all namespaces

    « Project + dependencies

    A Clojure library for JDBC access

    Transaction handling and strategy (via `transaction propagation`). The following propagations are implemented:
    :mandatory, :nested, :never, :not-supported, :required, :requires-new, :supports
    The README below is fetched from the published project artifact. Some relative links may be broken.


    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


    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
      "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
      "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.


    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)


    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


    Copyright © 2015-2018 Shantanu Kumar (,

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