Getting Started with next.jdbc

The next.jdbc library provides a simpler, faster alternative to the clojure.java.jdbc Contrib library and is the next step in the evolution of that library.

It is designed to work with Clojure 1.10 or later, supports datafy/nav, and by default produces hash maps with automatically qualified keywords, indicating source tables and column names (labels), if your database supports that.

Installation

You can add next.jdbc to your project with either:

seancorfield/next.jdbc {:mvn/version "1.1.547"}

for deps.edn or:

[seancorfield/next.jdbc "1.1.547"]

for project.clj or build.boot.

In addition, you will need to add dependencies for the JDBC drivers you wish to use for whatever databases you are using. You can see the drivers and versions that next.jdbc is tested against in the project's deps.edn file, but many other JDBC drivers for other databases should also work (e.g., Oracle, Red Shift).

An Example REPL Session

To start using next.jdbc, you need to create a datasource (an instance of javax.sql.DataSource). You can use next.jdbc/get-datasource with either a "db-spec" -- a hash map describing the database you wish to connect to -- or a JDBC URI string. Or you can construct a datasource from one of the connection pooling libraries out there, such as HikariCP or c3p0 -- see Connection Pooling below.

For the examples in this documentation, we will use a local H2 database on disk, and we'll use the Clojure CLI tools and deps.edn:

;; deps.edn
{:deps {org.clojure/clojure {:mvn/version "1.10.1"}
        seancorfield/next.jdbc {:mvn/version "1.1.547"}
        com.h2database/h2 {:mvn/version "1.4.199"}}}

Create & Populate a Database

In this REPL session, we'll define an H2 datasource, create a database with a simple table, and then add some data and query it:

> clj
Clojure 1.10.1
user=> (require '[next.jdbc :as jdbc])
nil
user=> (def db {:dbtype "h2" :dbname "example"})
#'user/db
user=> (def ds (jdbc/get-datasource db))
#'user/ds
user=> (jdbc/execute! ds ["
create table address (
  id int auto_increment primary key,
  name varchar(32),
  email varchar(255)
)"])
[#:next.jdbc{:update-count 0}]
user=> (jdbc/execute! ds ["
insert into address(name,email)
  values('Sean Corfield','sean@corfield.org')"])
[#:next.jdbc{:update-count 1}]
user=> (jdbc/execute! ds ["select * from address"])
[#:ADDRESS{:ID 1, :NAME "Sean Corfield", :EMAIL "sean@corfield.org"}]
user=>

The "db-spec" hash map

We described the database with just :dbtype and :dbname because it is created as a local file and needs no authentication. For most databases, you would need :user and :password for authentication, and if the database is running on a remote machine you would need :host and possibly :port (next.jdbc tries to guess the correct port based on the :dbtype).

Note: You can see the full list of :dbtype values supported in next.jdbc/get-datasource's docstring. If you need this programmatically, you can get it from the next.jdbc.connection/dbtypes hash map. If those lists differ, the hash map is the definitive list (and I'll need to fix the docstring!). The docstring of that Var explains how to tell next.jdbc about additional databases.

If you already have a JDBC URL (string), you can use that as-is instead of the db-spec hash map. If you have a JDBC URL and still need additional options passed into the JDBC driver, you can use a hash map with the :jdbcUrl key specifying the string and whatever additional options you need.

execute! & execute-one!

We used execute! to create the address table, to insert a new row into it, and to query it. In all three cases, execute! returns a vector of hash maps with namespace-qualified keys, representing the result set from the operation, if available. If the result set contains no rows, execute! returns an empty vector []. When no result set is available, next.jdbc returns a "result set" containing the "update count" from the operation (which is usually the number of rows affected; note that :builder-fn does not affect this fake "result set"). By default, H2 uses uppercase names and next.jdbc returns these as-is.

If you only want a single row back -- the first row of any result set, generated keys, or update counts -- you can use execute-one! instead. Continuing the REPL session, we'll insert another address and ask for the generated keys to be returned, and then we'll query for a single row:

user=> (jdbc/execute-one! ds ["
insert into address(name,email)
  values('Someone Else','some@elsewhere.com')
"] {:return-keys true})
#:ADDRESS{:ID 2}
user=> (jdbc/execute-one! ds ["select * from address where id = ?" 2])
#:ADDRESS{:ID 2, :NAME "Someone Else", :EMAIL "some@elsewhere.com"}
user=>

Since we used execute-one!, we get just one row back (a hash map). This also shows how you provide parameters to SQL statements -- with ? in the SQL and then the corresponding parameter values in the vector after the SQL string. If the result set contains no rows, execute-one! returns nil. When no result is available, and next.jdbc returns a fake "result set" containing the "update count", execute-one! returns just a single hash map with the key next.jdbc/update-count and the number of rows updated.

In the same way that you would use execute-one! if you only want one row or one update count, compared to execute! for multiple rows or a vector containing an update count, you can also ask execute! to return multiple result sets -- such as might be returned from a stored procedure call, or a T-SQL script (for SQL Server) -- instead of just one. If you pass the :multi-rs true option to execute!, you will get back a vector of results sets, instead of just one result set: a vector of zero or more vectors. The result may well be a mix of vectors containing realized rows and vectors containing update counts, reflecting the results from specific SQL operations in the stored procedure or script.

Note: In general, you should use execute-one! for DDL operations since you will only get back an update count. If you have a SQL statement that you know will only return an update count, execute-one! is the right choice. If you have a SQL statement that you know will only return a single row in the result set, you probably want to use execute-one!. If you use execute-one! for a SQL statement that would return multiple rows in a result set, even though you will only get the first row back (as a hash map), the full result set will still be retrieved from the database -- it does not limit the SQL in any way.

Options & Result Set Builders

All functions in next.jdbc (except get-datasource) can accept, as the optional last argument, a hash map containing a variety of options that control the behavior of the next.jdbc functions.

We saw :return-keys provided as an option to the execute-one! function above and mentioned the :builder-fn option just above that. As noted, the default behavior it to return rows as hash maps with namespace-qualified keywords identifying the column names with the table name as the qualifier. There's a whole chapter on result set builders but here's a quick example showing how to get unqualified, lower case keywords instead:

user=> (require '[next.jdbc.result-set :as rs])
nil
user=> (jdbc/execute-one! ds ["
insert into address(name,email)
  values('Someone Else','some@elsewhere.com')
"] {:return-keys true :builder-fn rs/as-unqualified-lower-maps})
{:id 3}
user=> (jdbc/execute-one! ds ["select * from address where id = ?" 3]
                          {:builder-fn rs/as-unqualified-lower-maps})
{:id 3, :name "Someone Else", :email "some@elsewhere.com"}
user=>

Relying on the default result set builder -- and table-qualified column names -- is the recommended approach to take, if possible, with a few caveats:

• MS SQL Server produces unqualified column names by default (see Tips & Tricks for how to get table names back from MS SQL Server),
• Oracle's JDBC driver doesn't support .getTableName() so it will only produce unqualified column names (also mentioned in Tips & Tricks),
• If your SQL query joins tables in a way that produces duplicate column names, and you use unqualified column names, then those duplicated column names will conflict and you will get only one of them in your result -- use aliases in SQL (as) to make the column names distinct,
• If your SQL query joins a table to itself under different aliases, the qualified column names will conflict because they are based on the underlying table name provided by the JDBC driver rather the alias you used in your query -- again, use aliases in SQL to make those column names distinct.

If you want to pass the same set of options into several operations, you can use next.jdbc/with-options to wrap your datasource (or connection) in a way that will pass "default options". Here's the example above rewritten with that:

user=> (require '[next.jdbc.result-set :as rs])
nil
user=> (def ds-opts (jdbc/with-options ds {:builder-fn rs/as-unqualified-lower-maps}))
#'user/ds-opts
user=> (jdbc/execute-one! ds-opts ["
insert into address(name,email)
  values('Someone Else','some@elsewhere.com')
"] {:return-keys true})
{:id 4}
user=> (jdbc/execute-one! ds-opts ["select * from address where id = ?" 4])
{:id 4, :name "Someone Else", :email "some@elsewhere.com"}
user=>

plan & Reducing Result Sets

While the execute! and execute-one! functions are fine for retrieving result sets as data, most of the time you want to process that data efficiently without necessarily converting the entire result set into a Clojure data structure, so next.jdbc provides a SQL execution function that works with reduce and with transducers to consume the result set without the intermediate overhead of creating Clojure data structures for every row.

We're going to create a new table that contains invoice items so we can see how to use plan without producing data structures:

user=> (jdbc/execute-one! ds ["
create table invoice (
  id int auto_increment primary key,
  product varchar(32),
  unit_price decimal(10,2),
  unit_count int unsigned,
  customer_id int unsigned
)"])
#:next.jdbc{:update-count 0}
user=> (jdbc/execute-one! ds ["
insert into invoice (product, unit_price, unit_count, customer_id)
values ('apple', 0.99, 6, 100),
       ('banana', 1.25, 3, 100),
       ('cucumber', 2.49, 2, 100)
"])
#:next.jdbc{:update-count 3}
user=> (reduce
         (fn [cost row]
           (+ cost (* (:unit_price row)
                      (:unit_count row))))
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
14.67M

The call to jdbc/plan returns an IReduceInit object but does not actually run the SQL. Only when the returned object is reduced is the connection obtained from the data source, the SQL executed, and the computation performed. The connection is closed automatically when the reduction is complete. The row in the reduction is an abstraction over the underlying (mutable) ResultSet object -- it is not a Clojure data structure. Because of that, you can simply access the columns via their SQL labels as shown -- you do not need to use the column-qualified name, and you do not need to worry about the database returning uppercase column names (SQL labels are not case sensitive).

Here's the same computation rewritten using transduce:

user=> (transduce
         (map #(* (:unit_price %) (:unit_count %)))
         +
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
14.67M

or composing the transforms:

user=> (transduce
         (comp (map (juxt :unit_price :unit_count))
               (map #(apply * %)))
         +
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
14.67M

If you just wanted the total item count:

user=> (transduce
         (map :unit_count)
         +
         0
         (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
11

You can use other functions that perform reductions to process the result of plan, such as obtaining a set of unique products from an invoice:

user=> (into #{}
             (map :product)
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
#{"apple" "banana" "cucumber"}

Any operation that can perform key-based lookup can be used here without creating hash maps from the rows: get, contains?, find (returns a MapEntry of whatever key you requested and the corresponding column value), or direct keyword access as shown above. Any operation that would require a Clojure hash map, such as assoc or anything that invokes seq (keys, vals), will cause the full row to be expanded into a hash map, such as produced by execute! or execute-one!, which implements Datafiable and Navigable and supports lazy navigation via foreign keys, explained in datafy, nav, and the :schema option.

This means that select-keys can be used to create regular Clojure hash map from (a subset of) columns in the row, without realizing the row, and it will not implement Datafiable or Navigable.

If you wish to create a Clojure hash map that supports that lazy navigation, you can call next.jdbc.result-set/datafiable-row, passing in the current row, a connectable, and an options hash map, just as you passed into plan. Compare the difference in output between these three expressions:

user=> (into []
             (map #(select-keys % [:id :product :unit_price :unit_cost :customer_id]))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
user=> (into []
             (map #(select-keys % [:invoice/id :invoice/product
                                   :invoice/unit_price :invoice/unit_cost
                                   :invoice/customer_id]))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))
user=> (into []
             (map #(rs/datafiable-row % ds {}))
             (jdbc/plan ds ["select * from invoice where customer_id = ?" 100]))

The latter produces a vector of hash maps, just like the result of execute!, where each "row" follows the case conventions of the database, the keys are qualified by the table name, and the hash map is datafiable and navigable.

In addition to the hash map operations described above, the abstraction over the ResultSet can also respond to a couple of functions in next.jdbc.result-set:

• next.jdbc.result-set/row-number - returns the 1-based row number, by calling .getRow() on the ResultSet,
• next.jdbc.result-set/column-names - returns a vector of column names from the ResultSet, as created by the result set builder specified.

Note: Apache Derby requires the following options to be provided in order to call .getRow() (and therefore row-number): {:concurrency :read-only, :cursors :close, :result-type :scroll-insensitive}

If you realize a row, by calling datafiable-row on the abstract row passed into the reducing function, you can still call row-number and column-names on that realized row. These functions are not available on the realized rows returned from execute! or execute-one!, only within reductions over plan.

The order of the column names returned by column-names matches SQL's natural order, based on the operation performed, and will also match the order of column values provided in the reduction when using an array-based result set builder (plan provides just the column values, one row at a time, when using an array-based builder, without the leading vector of column names that you would get from execute!: if you call datafiable-row on such a row, you will get a realized vector of column values).

Note: since plan expects you to process the result set via reduction, you should not use it for DDL or for SQL statements that only produce update counts.

Datasources, Connections & Transactions

In the examples above, we created a datasource and then passed it into each function call. When next.jdbc is given a datasource, it creates a java.sql.Connection from it, uses it for the SQL operation (by creating and populating a java.sql.PreparedStatement from the connection and the SQL string and parameters passed in), and then closes it. If you're not using a connection pooling datasource (see below), that can be quite an overhead: setting up database connections to remote servers is not cheap!

If you want to run multiple SQL operations without that overhead each time, you can create the connection yourself and reuse it across several operations using with-open and next.jdbc/get-connection:

(with-open [con (jdbc/get-connection ds)]
  (jdbc/execute! con ...)
  (jdbc/execute! con ...)
  (into [] (map :column) (jdbc/plan con ...)))

If any of these operations throws an exception, the connection will still be closed but operations prior to the exception will have already been committed to the database. If you want to reuse a connection across multiple operations but have them all rollback if an exception occurs, you can use next.jdbc/with-transaction:

(jdbc/with-transaction [tx ds]
  (jdbc/execute! tx ...)
  (jdbc/execute! tx ...)
  (into [] (map :column) (jdbc/plan tx ...)))

If with-transaction is given a datasource, it will create and close the connection for you. If you pass in an existing connection, with-transaction will set up a transaction on that connection and, after either committing or rolling back the transaction, will restore the state of the connection and leave it open:

(with-open [con (jdbc/get-connection ds)]
  (jdbc/execute! con ...) ; committed
  (jdbc/with-transaction [tx con] ; will commit or rollback this group:
    (jdbc/execute! tx ...)
    (jdbc/execute! tx ...)
    (into [] (map :column) (jdbc/plan tx ...)))
  (jdbc/execute! con ...)) ; committed

You can read more about working with transactions further on in the documentation.

Note: Because get-datasource and get-connection return plain JDBC objects (javax.sql.DataSource and java.sql.Connection respectively), next.jdbc/with-options cannot flow options across those calls, so if you are explicitly managing connections or transactions as above, you would need to have local bindings for the wrapped versions:

(with-open [con (jdbc/get-connection ds)]
  (let [con-opts (jdbc/with-options con some-options)]
    (jdbc/execute! con-opts ...) ; committed
    ;; either use unwrapped version here or (:connectable con-opts)
    (jdbc/with-transaction [tx con] ; will commit or rollback this group:
      (let [tx-opts (jdbc/with-options tx some-options)]
        (jdbc/execute! tx-opts ...)
        (jdbc/execute! tx-opts ...)
        (into [] (map :column) (jdbc/plan tx-opts ...))))
    (jdbc/execute! con-opts ...))) ; committed

Prepared Statement Caveat

Not all databases support using a PreparedStatement for every type of SQL operation. You might have to create a java.sql.Statement instead, directly from a java.sql.Connection and use that, without parameters, in plan, execute!, or execute-one!. See the following example:

(require '[next.jdbc.prepare :as prep])

(with-open [con (jdbc/get-connection ds)]
  (jdbc/execute! (prep/statement con) ["...just a SQL string..."])
  (jdbc/execute! con ["...some SQL..." "and" "parameters"]) ; uses PreparedStatement
  (into [] (map :column) (jdbc/plan (prep/statement con) ["..."])))

Connection Pooling

next.jdbc makes it easy to use either HikariCP or c3p0 for connection pooling.

First, you need to add the connection pooling library as a dependency, e.g.,

com.zaxxer/HikariCP {:mvn/version "3.3.1"}
;; or:
com.mchange/c3p0 {:mvn/version "0.9.5.4"}

Check those libraries' documentation for the latest version to use!

Then import the appropriate classes into your code:

(ns my.main
  (:require [next.jdbc :as jdbc]
            [next.jdbc.connection :as connection])
  (:import (com.zaxxer.hikari HikariDataSource)
           ;; or:
           (com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)))

Finally, create the connection pooled datasource. db-spec here contains the regular next.jdbc options (:dbtype, :dbname, and maybe :host, :port, :classname etc -- or the :jdbcUrl format mentioned above). Those are used to construct the JDBC URL that is passed into the datasource object (by calling .setJdbcUrl on it). You can also specify any of the connection pooling library's options, as mixed case keywords corresponding to any simple setter methods on the class being passed in, e.g., :connectionTestQuery, :maximumPoolSize (HikariCP), :maxPoolSize, :preferredTestQuery (c3p0).

Some important notes regarding HikariCP:

• Authentication credentials must use :username (if you are using c3p0 or regular, non-pooled, connections, then the db-spec hash map must contain :user).
• When using :dbtype "jtds", you must specify :connectionTestQuery "SELECT 1" (or some other query to verify the health of a connection) because the jTDS JDBC driver does not implement .isValid() so HikariCP requires a specific test query instead (c3p0 does not rely on this method so it works with jTDS without needing :preferredTestQuery).
• When using PostgreSQL, and trying to set a default :schema via HikariCP, you will need to specify :connectionInitSql "COMMIT;" until this HikariCP issue is addressed.

You will generally want to create the connection pooled datasource at the start of your program (and close it before you exit, although that's not really important since it'll be cleaned up when the JVM shuts down):

(defn -main [& args]
  (with-open [^HikariDataSource ds (connection/->pool HikariDataSource db-spec)]
    (jdbc/execute! ds ...)
    (jdbc/execute! ds ...)
    (do-other-stuff ds args)
    (into [] (map :column) (jdbc/plan ds ...))))
;; or:
(defn -main [& args]
  (with-open [^PooledDataSource ds (connection/->pool ComboPooledDataSource db-spec)]
    (jdbc/execute! ds ...)
    (jdbc/execute! ds ...)
    (do-other-stuff ds args)
    (into [] (map :column) (jdbc/plan ds ...))))

You only need the type hints on ds if you plan to call methods on it via Java interop, such as .close (or using with-open to auto-close it) and you want to avoid reflection.

If you are using Component, a connection pooled datasource is a good candidate since it has a start/stop lifecycle. next.jdbc has support for Component built-in, via the next.jdbc.connection/component function which creates a Component-compatible entity which you can start and then invoke as a function with no arguments to obtain the DataSource within.

(ns my.data.program
  (:require [com.stuartsierra.component :as component]
            [next.jdbc :as jdbc]
            [next.jdbc.connection :as connection])
  (:import (com.zaxxer.hikari HikariDataSource)))

;; HikariCP requires :username instead of :user in the db-spec:
(def ^:private db-spec {:dbtype "..." :dbname "..." :username "..." :password "..."})

(defn -main [& args]
  ;; connection/component takes the same arguments as connection/->pool:
  (let [ds (component/start (connection/component HikariDataSource db-spec))]
    (try
      ;; "invoke" the data source component to get the javax.sql.DataSource:
      (jdbc/execute! (ds) ...)
      (jdbc/execute! (ds) ...)
      ;; can pass the data source component around other code:
      (do-other-stuff ds args)
      (into [] (map :column) (jdbc/plan (ds) ...))
      (finally
        ;; stopping the component will close the connection pool:
        (component/stop ds)))))

Working with Additional Data Types

By default, next.jdbc relies on the JDBC driver to handle all data type conversions when reading from a result set (to produce Clojure values from SQL values) or setting parameters (to produce SQL values from Clojure values). Sometimes that means that you will get back a database-specific Java object that would need to be manually converted to a Clojure data structure, or that certain database column types require you to manually construct the appropriate database-specific Java object to pass into a SQL operation. You can usually automate those conversions using either the ReadableColumn protocol (for converting database-specific types to Clojure values) or the SettableParameter protocol (for converting Clojure values to database-specific types).

In particular, PostgreSQL does not seem to perform a conversion from java.util.Date to a SQL data type automatically. You can require the next.jdbc.date-time namespace to enable that conversion.

If you are working with Java Time, some JDBC drivers will automatically convert java.time.Instant (and java.time.LocalDate and java.time.LocalDateTime) to a SQL data type automatically, but others will not. Requiring next.jdbc.date-time will enable those automatic conversions for all databases.

Note: next.jdbc.date-time also provides functions you can call to enable automatic conversion of SQL date/timestamp types to Clojure data types when reading result sets. If you need specific conversions beyond that to happen automatically, consider extending the ReadableColumn protocol, mentioned above.

The next.jdbc.types namespace provides over three dozen convenience functions for "type hinting" values so that the JDBC driver might automatically handle some conversions that the default parameter setting function does not. Each function is named for the corresponding SQL type, prefixed by as-: as-bigint, as-other, as-real, etc. An example of where this helps is when dealing with PostgreSQL enumerated types: the default behavior, when passed a string that should correspond to an enumerated type, is to throw an exception that column "..." is of type ... but expression is of type character varying. You can wrap such strings with (as-other "...") which tells PostgreSQL to treat this as java.sql.Types/OTHER when setting the parameter.

Processing Database Metadata

JDBC provides several features that let you introspect the database to obtain lists of tables, views, and so on. next.jdbc does not provide any specific functions for this but you can easily get this metadata from a java.sql.Connection and turn it into Clojure data as follows:

(with-open [con (p/get-connection ds opts)]
  (-> (.getMetaData con) ; produces java.sql.DatabaseMetaData
      ;; return a java.sql.ResultSet describing all tables and views:
      (.getTables nil nil nil (into-array ["TABLE" "VIEW"]))
      (rs/datafiable-result-set ds opts)))

Several methods on DatabaseMetaData return a ResultSet object, e.g., .getCatalogs(), .getClientInfoProperties(), .getSchemas(). All of those can be handled in a similar manner to the above. See the Oracle documentation for java.sql.DatabaseMetaData (Java 11) for more details.

Support from Specs

As you are developing with next.jdbc, it can be useful to have assistance from clojure.spec in checking calls to next.jdbc's functions, to provide explicit argument checking and/or better error messages for some common mistakes, e.g., trying to pass a plain SQL string where a vector (containing a SQL string, and no parameters) is expected.

You can enable argument checking for functions in next.jdbc, next.jdbc.connection, next.jdbc.prepare, and next.jdbc.sql by requiring the next.jdbc.specs namespace and instrumenting the functions. A convenience function is provided:

(require '[next.jdbc.specs :as specs])
(specs/instrument) ; instruments all next.jdbc API functions

(jdbc/execute! ds "SELECT * FROM fruit")
Call to #'next.jdbc/execute! did not conform to spec.

In the :problems output, you'll see the :path [:sql :sql-params] and :pred vector? for the :val "SELECT * FROM fruit". Without the specs' assistance, this mistake would produce a more cryptic error, a ClassCastException, that a Character cannot be cast to a String, from inside next.jdbc.prepare.

A convenience function also exists to revert that instrumentation:

(specs/unstrument) ; undoes the instrumentation of all next.jdbc API functions

Friendly SQL Functions :>