clojure.jdbc documentation

Introduction

clojure.jdbc is a library for low level, jdbc-based database access.

Install

The simplest way to use clojure.jdbc in a clojure project, is by including it in the dependency vector on your project.clj file:

[funcool/clojure.jdbc "0.9.0"]

You also need to add an entry for the java driver that you need. For instance, for mysql:

---
[mysql/mysql-connector-java "5.1.6"]
---

The project is tested under JDK7 and JDK8.

Project Maturity

Since clojure.jdbc is a young project there may be some API breakage.

User Guide

Connecting to database

This section intends to explain everything that you should to know about how connect to the database.

JDBC is the default Java abstraction/interface for SQL databases. It’s like the Python DB-API and similar abstractions in other languages. Clojure, as a guest language on the JVM, benefits from having a good, well-tested abstraction like that.

Connection parameters are exposed in a simple hash-map and called dbspec. This is the simplest and most idiomatic way in Clojure to define configuration parameters.

This is a default aspect of one dbspec.

(def dbspec {:subprotocol "postgresql"
             :subname "//localhost:5432/dbname"
             :user "username"         ;; Optional
             :password "password"}    ;; Optional

Also, clojure.jdbc comes with alternative, more human comprehensible format, like this:

Pretty dbspec format

(def dbspec {:vendor "postgresql"
             :name "dbname"
             :host "localhost"      ;; Optional
             :port 5432             ;; Optional
             :user "username"       ;; Optional
             :password "password"}) ;; Optional

But it has some disadvantages: it does not supports all options of a default dbspec format.

Also, dbspec can be represented as a URI.

Same as the previous example but using URI format.

(def dbspec "postgresql://user:password@localhost:5432/dbname")

Creating a connection

With clojure.jdbc every function that interacts with a database explicitly requires one connection instance as parameter (no dynamic vars are used for it).

Create one connection using connection function:

(require '[jdbc.core :as jdbc])

(let [conn (jdbc/connection dbspec)]
  (do-something-with conn)
  (.close conn))

As you can see in previous example, you should explicltly close connection for proper resource management. You can use the with-open clojure macro for make code looks more clean and idiomatic.

(with-open [conn (jdbc/connection dbspec)]
  (do-something-with conn))

Execute Database Commands

Execute Raw SQL Statements

The simplest way to execute raw SQL is using the execute function. It requires an active connection as the first parameter followed by SQL sentence:

(with-open [conn (jdbc/connection dbspec)]
  (jdbc/execute conn "CREATE TABLE foo (id serial, name text);"))

Execute Parametrized SQL Statements

Raw SQL statements work well for creating tables and similar operations, but when you need to insert some data, especially if the data comes from untrusted sources. Using plain string concatenation is a very bat and unsafe approach.

For safe parameter bindings, it there a sqlvec format that consists in a vector with sql followed of parameters:

Example of sqlvec format

["select * from foo where age < ?" 20]

Almost all functions in clojure.jdbc accepts sqlvec as query parameter. Let see an example using it in execute function:

Execute a simple insert SQL statement.

(jdbc/execute conn ["insert into foo (name) values (?);" "Foo"]))

Returning Inserted Keys

In some circumstances, you want to use the "RETURNING id" or similar functionality on your queries for getting the primary keys of newly inserted records.

Example executing query with "returning" statement

(let [sql "insert into foo (name) values (?) returning id;"
      res (jdbc/fetch conn [sql "Foo"])]
  (println res))

;; This should print something like this to standard output:
[{:id 3}]

Make Queries

It is time to start executing queries and fetch results. For this purpose, clojure.jdbc exposes the fetch function:

(let [sql    ["SELECT id, name FROM people WHERE age > ?", 2]
      result (jdbc/fetch conn sql)]
  (doseq [row result]
    (println row))))

;; It should print somthing like this:
;; => {:id 1 :name "Foo"}
;; => {:id 2 :name "Bar"}

We are know that almost all function in clojure.jdbc accepts a plain sql and sqlvec as query parameter. But is not entirely true, it also accepts as query parameter an instance of self crafted PreparedStatement or anythig that implements the ISQLStatement protocol.

Example creating a prepared statement and executing it

(let [stmt (jdbc/prepared-statement ["SELECT id, name FROM people WHERE age > ?", 2])
      result (jdbc/fetch conn stmt)]
  (println "Result: " result))

Note	The `fetch` method seems useful in most cases but may not work well with queries that returns a lot of results. For this purpose, cursor type queries exist that are explained in the Advanced usage section.

Transactions

Getting Started with Transactions

The most idiomatic way to wrap some code in a transaction, is by using the atomic macro:

(jdbc/atomic conn
  (do-thing-first conn)
  (do-thing-second conn))

Note	clojure.jdbc does not uses any dynamic thread-local vars to store the transaction state of a connection. Instead of that, it overwrites the lexical scope value of `conn` with a new connection that has transactional state.

Low-level Transaction Primitives

Behind the scenes of the atomic macro, clojure.jdbc uses the atomic-apply function.

Given an active connection as the first parameter and function that you want execute in a transaction as the second parameter, it executes the function inside a database transaction. The callback function should accept a connection as its first parameter.

Example executing a function in transaction context

(jdbc/atomic-apply conn
  (fn [conn]
    (do-something-with conn)))

Note	clojure.jdbc, in contrast to java.jdbc, handles nested transactions well. Thus making all code wrapped in transaction blocks truly atomic independently of transaction nesting. If you want extend or change a default transaction strategy, see Transaction Strategy section.

Isolation Level

clojure.jdbc by default does nothing with the isolation level and keeps it to default values.

You can set the isolation level when creating a connection by specifying it in your dbspec.

(def dbspec {:subprotocol "h2"
             :subname "mem:"
             :isolation-level :serializable})

(with-open [conn (jdbc/connection dbspec)]
  ;; The just created connection has the isolation
  ;; level set to :serializable
  (do-things conn))

An other way to set the isolation level is in moment of declaring a transaction, using the atomic-apply function or atomic macro:

(jdbc/atomic-apply conn do-something {:isolation-level :serializable})

(jdbc/atomic conn {:isolation-level :serializable}
  (do-something conn))

This is a list of supported options:

:read-uncommitted - Set read uncommitted isolation level
:read-committed - Set read committed isolation level
:repeatable-read - Set repeatable reads isolation level
:serializable - Set serializable isolation level
:none - Use this option to indicate to clojure.jdbc to do nothing and keep default behavior.

You can read more about it on wikipedia.

Warning

not all JDBC providers support the above isolation levels.

Read-Only Transactions

In some circumstances, mainly when you are using the strictest isolation-level, you may want to indicate to database that a query is actually read-only, allowing the database server to make some optimizations for this operation.

You can set transaction read-only using transaction options

(jdbc/atomic conn {:isolation-level :serializable
                   :read-only true}
  (query-something conn))

Advanced usage

Server Side Cursors

By default, most JDBC drivers prefetch all results into memory make the use of lazy structures totally useless for fetching data. Luckily, some databases implement server-side cursors that avoid this behavior.

If you have an extremely large resultset and you want retrieve it and process each item, this is exactly what you need.

For this purpose, clojure.jdbc exposes the fetch-lazy function, that returns a some kind of cursor instance. At the moment of creating cursor, no query is executed.

The cursor can be used converting it into clojure lazyseq using cursor→lazyseq function:

(jdbc/atomic conn
  (with-open [cursor (jdbc/fetch-lazy conn "SELECT id, name FROM people;")]
    (doseq [row (jdbc/cursor->lazyseq cursor)]
      (println row)))

In some databases, it requires that cursor should be evaluated in a context of one transaction.

Transaction Strategy

Transaction strategies in clojure.jdbc are implemented using protocols having default implementation explained in the previous sections. This approach allows an easy way to extend, customize or completely change a transaction strategy for your application.

If you want another strategy, you should create a new type and implement the ITransactionStrategy protocol.

Sample dummy transaction strategy.

(require '[jdbc.proto :as proto])

(def dummy-tx-strategy
  (reify
    proto/ITransactionStrategy
    (begin! [_ conn opts] conn)
    (rollback! [_ conn opts] conn)
    (commit! [_ conn opts] conn)))

clojure.jdbc has different ways to specify that transaction strategy shouldbe used. The most common is setting it in your dbspec:

(def dbspec {:subprotocol "postgresql"
             :subname "//localhost:5432/dbname"
             :tx-strategy dummy-tx-strategy})
(with-open [conn (jdbc/connection dbspec)]
  (jdbc/atomic conn
    ;; In this transaction block, the dummy transaction
    ;; strategy will be used.
    (do-somthing conn)))

Internally, clojure.jdbc maintains an instance of default transaction strategy stored in a dynamic var. You can use the clojure facilities for alter that var for set an other default transaction stragegy:

Overwritting with alter-var-root

(alter-var-root #'jdbc/*default-tx-strategy* (fn [_] dummy-tx-strategy))

Overwritting it with dynamic scope

(binding [jdbc/*default-tx-strategy* dummy-tx-strategy]
  (some-func-that-uses-transactions))

Extend SQL Types

If you want to extend some type/class to use it as JDBC parameter without explicit conversion to an SQL-compatible type, you should extend your type with the jdbc.proto/ISQLType protocol.

Here is an example which extends Java’s String[] (string array) in order to pass it as a query parameter that corresponds to PostgreSQL text array in the database:

(require '[jdbc.proto :as proto])

(extend-protocol ISQLType
  ;; Obtain a class for string array
  (class (into-array String []))

  (set-stmt-parameter! [this conn stmt index]
    (let [value (proto/as-sql-type this conn)
          array (.createArrayOf conn "text" value)]
      (.setArray stmt index array)))

  (as-sql-type [this conn] this))

In this way you can pass a string array as a JDBC parameter that is automatically converted to an SQL array and assigned properly in a prepared statement:

(with-open [conn (jdbc/connection pg-dbspec)]
  (jdbc/execute conn "CREATE TABLE arrayfoo (id integer, data text[]);")
  (let [mystringarray (into-array String ["foo" "bar"])]
    (jdbc/execute conn ["INSERT INTO arrayfoo VALUES (?, ?);" 1 mystringarray])))

clojure.jdbc also exposes the jdbc.proto/ISQLResultSetReadColumn protocol that encapsulates reverse conversions from SQL types to user-defined types.

You can read more about that in this blog post: http://www.niwi.be/2014/04/13/postgresql-json-field-with-clojure-and-jdbc/

Connection pool

DataSource is the preferd way to connect to the database in production enviroments, and is usually used for implement connection pools.

To make good use of resourses is much recommendable use some kind of connection pool implementation. This can avoid continuosly creating and destroying connections, that in the majority of time is a slow operation.

Java ecosystem comes with various of it. This is a list of most used:

HikariCP: https://github.com/brettwooldridge/HikariCP
c3p0: http://www.mchange.com/projects/c3p0/
Apache DBCP2: http://commons.apache.org/proper/commons-dbcp/

clojure.jdbc is compatible with any other connection pool implemenetation, simply pass a javax.sql.DataSource instance to jdbc/connection function.

c3p0

c3p0, a mature, highly concurrent JDBC connection pooling library for clojure.jdbc.

Dependency entry

[com.mchange/c3p0 "0.9.5"]

In order to use this connection pool, previously you should create a DataSource instance. Here an little example on how it can be done:

(import 'com.mchange.v2.c3p0.ComboPooledDataSource)

(def ds (doto (ComboPooledDataSource.)
          (.setJdbcUrl (str "jdbc:"
                            (:subprotocol dbspec)
                            (:subname dbspec)))
          (.setUser (:user dbspec nil))
          (.setPassword (:password dbspec nil))

          ;; Pool Size Management
          (.setMinPoolSize 3)
          (.setMaxPoolSize 15)

          ;; Connection eviction
          (.setMaxConnectionAge  3600) ; 1 hour
          (.setMaxIdleTime 1800)       ; 1/2 hour
          (.setMaxIdleTimeExcessConnections 120)

          ;; Connection testing
          (.setTestConnectionOnCheckin false)
          (.setTestConnectionOnCheckout false)
          (.setIdleConnectionTestPeriod 600)))

You can found all configuration parameters here: http://www.mchange.com/projects/c3p0/#configuration

Now, the new created datasource should be used like a plain dbspec for creating connections:

(with-open [conn (jdbc/connection ds)]
  (do-stuff conn))

dbcp2

Apache commons DBCP (JDBC) connection pool implementation for clojure.jdbc

Dependency entry

[org.apache.commons/commons-dbcp2 "2.0.1"]

In order to use this connection pool, previously you should create a DataSource instance. Here an little example on how it can be done:

(import 'org.apache.commons.dbcp2.BasicDataSource)

(def ds (doto (BasicDataSource.)
          (.setJdbcUrl (str "jdbc:"
                            (:subprotocol dbspec)
                            (:subname dbspec)))
          (.setUser (:user dbspec nil))
          (.setPassword (:password dbspec nil))

          ;; Pool Size Management
          (.setInitialSize 0)
          (.setMaxIdle 3)
          (.setMaxTotal 15)

          ;; Connection eviction
          (.setMaxConnLifetimeMillis 3600000) ; 1 hour

          ;; Connection testing
          (.setTestOnBorrow false)
          (.setTestOnReturn false)
          (.setTestWhileIdle true)
          (.setTimeBetweenEvictionRunsMillis 600000) ;; 10 minutes
          (.setNumTestsPerEvictionRun 4)
          (.setMinEvictableIdleTimeMillis 1800000))) ;; 1/2 hours

You can found all configuration parameters here: http://commons.apache.org/proper/commons-dbcp/configuration.html

Now, the new created datasource should be used like a plain dbspec for creating connections:

(with-open [conn (jdbc/connection ds)]
  (do-stuff conn))

HikariCP

Fast, simple, reliable. HikariCP is a "zero-overhead" production ready JDBC connection pool.

Dependency entry for Java8

[hikari-cp "1.2.4"]

Dependency entry for Java7 or Java6

[hikari-cp "1.2.4" :exclusions [com.zaxxer/HikariCP]]
[com.zaxxer/HikariCP-java6 "2.3.9"]

In order to use this connection pool, previously you should create a DataSource instance. Here an little example on how it can be done:

(require '[hikari-cp.core :as hikari])

(def ds (hikari/make-datasource
         {:connection-timeout 30000
          :idle-timeout 600000
          :max-lifetime 1800000
          :minimum-idle 10
          :maximum-pool-size  10
          :adapter "postgresql"
          :username "username"
          :password "password"
          :database-name "database"
          :server-name "localhost"
          :port-number 5432}))

HikariCP, unlike other datasource implementations, requires to setup explicitly that adapter should be used. This is a list of supported adapters:

Table 1. List of adapters supported by HikariCP
Adapter	Datasource class name
`:derby`	`org.apache.derby.jdbc.ClientDataSource`
`:firebird`	`org.firebirdsql.pool.FBSimpleDataSource`
`:db2`	`com.ibm.db2.jcc.DB2SimpleDataSource`
`:h2`	`org.h2.jdbcx.JdbcDataSource`
`:hsqldb`	`org.hsqldb.jdbc.JDBCDataSource`
`:mariadb`	`org.mariadb.jdbc.MySQLDataSource`
`:mysql`	`com.mysql.jdbc.jdbc2.optional.MysqlDataSource`
`:sqlserver-jtds`	`net.sourceforge.jtds.jdbcx.JtdsDataSource`
`:sqlserver`	`com.microsoft.sqlserver.jdbc.SQLServerDataSource`
`:oracle`	`oracle.jdbc.pool.OracleDataSource`
`:pgjdbc-ng`	`com.impossibl.postgres.jdbc.PGDataSource`
`:postgresql`	`org.postgresql.ds.PGSimpleDataSource`
`:sybase`	`com.sybase.jdbcx.SybDataSource`

You can found more information and documentation about hikari-cp here: https://github.com/tomekw/hikari-cp

Now, the new created datasource should be used like a plain dbspec for creating connections:

(with-open [conn (jdbc/connection ds)]
  (do-stuff conn))

FAQ

Why another JDBC wrapper?

This is an incomplete list of reasons:

Connection management in clojure.jdbc is simple and explicit. However, java.jdbc does not make diferentiation between a connection or dbspec hash map. At first glace it seems more flexible, but it requires huge additional complexity and boilerplate code on each function that receives a connection. If you are curious, take a look at the with-db-connection implementation of java.jdbc and compare it with clojure.jdbc: it will get you a good idea of the hidden and unnecessary complexity found in java.jdbc.
clojure.jdbc comes with proper transaction management with full support for nested transactions, and plugable transaction strategies. In contrast, java.jdbc comes with one unique strategy, that does not support subtransactions (it flattens all nested transactions in one unique giving you false security when you wraps you code in a transaction).
clojure.jdbc has native support for connection pools.
clojure.jdbc has first class documentation.
clojure.jdbc has a simpler implementation than java.jdbc without unnecesary code duplication. A good example are the crud methods of java.jdbc: all them repeats the transaction logic implicitly. In contrast, clojure.jdbc, if you want a transaction, you should wrap your code in a transaction context explicitly.

Does clojure.jdbc have better performance than java.jdbc?

Mostly Yes, clojure.jdbc by default has better performance than java.jdbc. You can run the micro benchmark code in your environment with: lein with-profile bench run

In my environments, the results are:

[3/5.0.5]niwi@niwi:~/clojure.jdbc> lein with-profile bench run
Simple query without connection overhead.
java.jdbc:
"Elapsed time: 673.890131 msecs"
clojure.jdbc:
"Elapsed time: 450.329706 msecs"
Simple query with connection overhead.
java.jdbc:
"Elapsed time: 2490.233925 msecs"
clojure.jdbc:
"Elapsed time: 2239.524395 msecs"
Simple query with transaction.
java.jdbc:
"Elapsed time: 532.151667 msecs"
clojure.jdbc:
"Elapsed time: 602.482932 msecs"

Why does clojure.jdbc not include a DSL for working with SQL as java.jdbc 0.3 does?

clojure.jdbc is a wrapper for the Java JDBC interface. It doesn’t intend to provide helpers to avoid SQL usage. There are already plenty of DSLs for working with SQL. clojure.jdbc will not reinvent the wheel.

This is an incomplete list of Clojure DSLs for SQL:

Is this a fork of java.jdbc?

No. It is an alternative implementation.

Developers Guide

Philosophy

Five most important rules:

Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Readability counts.

All contributions to clojure.jdbc should keep these important rules in mind.

Contributing

Unlike Clojure and other Clojure contributed libraries clojure.jdbc does not have many restrictions for contributions. Just open an issue or pull request.

Source Code

clojure.jdbc is open source and can be found on github.

You can clone the public repository with this command:

git clone https://github.com/funcool/clojure.jdbc

Run tests

For running tests just execute this:

lein test

You should have postgresql up and running with a current user created with trust access mode activated for this user and test db already created.

License

clojure.jdbc is writen from scratch and is licensed under Apache 2.0 license:

Copyright (c) 2013-2015 Andrey Antukh <niwi@niwi.nz>

Licensed under the Apache License, Version 2.0 (the "License")
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

You can see the full license in the LICENSE file located in the root of the project repo.

Additionaly, I want to give thanks to the java.jdbc developers for their good initial work. Some intial ideas for clojure.jdbc are taken from that project.