Liking cljdoc? Tell your friends :D

clojure.jdbc documentation

1. Introduction

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

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

2. Project Maturity

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

3. Install

This section covers a installing clojure.jdbc and its requirements.

3.1. Requirements

clojure.jdbc is tested with these platforms:

  • JDK7

  • JDK8

3.2. Leiningen

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

on project.clj
[clojure.jdbc "0.4.0-beta1"]

3.3. Gradle

If you are using gradle, this is a dependency line for gradle dsl:

compile "clojure.jdbc:clojure.jdbc:0.4.0-beta1"

3.4. Get the Code

clojure.jdbc is open source and you can found the source on github.

You can clone the public repository with this command:

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

4. User Guide

4.1. Connecting to database

4.1.1. Connection parameters

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

4.1.2. 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))

4.2. Execute Database Commands

4.2.1. 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 sentences:

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

4.2.2. 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, the execute! function is not a good option.

For this problem, clojure.jdbc exposes the execute-prepared! function. It accepts parametrized SQL and a list of groups of parameters that allow the repeated execution of the same operations with distinct parameters.

Execute a simple insert SQL statement.
(let [sql "INSERT INTO foo (name) VALUES (?)"]
  (jdbc/execute-prepared! conn [sql "Foo"]))
Bulk insert example.
(let [sql "INSERT INTO foo (name) VALUES (?)"]
  (jdbc/execute-prepared! conn sql ["Foo"] ["Bar"]))

;; This should emit this sql:
;;   INSERT INTO foo (name) VALUES ('Foo');
;;   INSERT INTO foo (name) VALUES ('Bar');

4.2.3. Returning Inserted Keys

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

This is how you can do it using clojure.jdbc:

(let [sql "INSERT INTO foo (name) VALUES (?);"
      res (jdbc/execute-prepared! conn sql ["Foo"] ["Bar"] {:returning [:id]})]
  (println res))

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

Also, you can use :all keyword in case of you need statement with "RETURNING *".

(jdbc/execute-prepared! conn sql ["foo"] {:returning :all})

4.3. Make Queries

The basic way to query a database is using the query function:

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

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

Parametrized sql can be:

  • A vector with first element a sql string following with parameters

  • A native string (SQL query without parameters)

  • An instance of PreparedStatement

  • An instance of any type that implements the ISQLStatement protocol.

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

4.4. Transactions

4.4.1. Getting Started with Transactions

All transaction related functions in clojure.jdbc are exposed under the jdbc.transaction namespace and if you need transactions in your code, you should import it:

(require '[jdbc.transaction :as tx])

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

(tx/with-transaction conn
  (do-thing-first conn)
  (do-thing-second conn))

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.

4.4.2. Low-level Transaction Primitives

Behind the scenes of the with-transaction macro, clojure.jdbc uses the call-in-transaction 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 function should accept a connection as its first parameter.

(tx/call-in-transaction conn (fn [conn] (do-something-with conn)))

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.

4.4.3. 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})
Or set it when executing a transaction
(tx/call-in-transaction conn do-something {:isolation-level :serializable})

;; Or...

(tx/with-transaction conn {:isolation-level :serializable}
  (do-something conn))

This is a list of supported options:

  • :read-uncommited - Set read uncommited isolation level

  • :read-commited - 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.

not all JDBC providers support the above isolation levels.

4.4.4. 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
(tx/with-transaction conn {:isolation-level :serializable
                           :read-only true}
  (query-something conn))

5. Advanced usage

5.1. 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 lazy-query 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:

(with-transaction conn
  (with-open [cursor (jdbc/lazy-query 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.

5.2. 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.
(def dummy-tx-strategy
  (reify tx/ITransactionStrategy
    (begin! [_ conn opts] conn)
    (rollback! [_ conn opts] conn)
    (commit! [_ conn opts] conn)))

You can specify the transaction strategy to use in these ways:

Using the with-transaction-strategy macro.
(with-open [conn (jdbc/connection conn)]
  (tx/with-transaction-strategy conn dummy-tx-strategy
    (do-some-thing conn)))
with-transaction-strategy does not use dynamic vars, it simple associate the strategy to connection and exposes it.
Using the wrap-transaction-strategy function:
(with-open [conn (-> (jdbc/connection dbspec)
                     (jdbc/wrap-transaction-strategy dummy-tx-strategy))]
  (do-some-thing conn))
Using dynamic vars
;; Overwritting the default value
(alter-var-root #'tx/*default-tx-strategy* (fn [_] dummy-tx-strategy))

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

5.3. Extend SQL Types

Everything related to type handling/conversion is exposed in the jdbc.proto namespace.

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 [rconn (proto/get-connection conn)
          value (proto/as-sql-type this conn)
          array (.createArrayOf raw-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-prepared! 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/

6. 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:

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

6.1. 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))

6.2. 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))

6.3. HikariCP

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

Dependency entry for Java8
[hikari-cp "0.13.0"]
Dependency entry for Java7 or Java6
[hikari-cp "0.13.0" :exclusions [com.zaxxer/HikariCP]]
[com.zaxxer/HikariCP-java6 "2.2.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:

(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
AdapterDatasource 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))

7. How to Contribute?

clojure.jdbc unlike Clojure and other Clojure contrib libs, does not have many restrictions for contributions. Just follow the following steps depending on the situation:

Bugfix:

  • Fork the GitHub repo.

  • Fix a bug/typo on a new branch.

  • Make a pull-request to master.

New feature:

  • Open new issue with the new feature proposal.

  • If it is accepted, follow the same steps as "bugfix".

8. FAQ

8.1. Why another JDBC wrapper?

This is an incomplete list of reasons:

  • Connection management should be explicit. clojure.jdbc has a clear differentiation between connection and dbspec without unnecessary nesting controls and with explicit resource management (using with-open or other specific macros for it, see the examples).

  • clojure.jdbc has full support for the whole transactions API, with the ability to set the database isolation level and use nested transactions (savepoints). It creates a new transaction if no other transaction is active but, when invoked within the context of an already-existing transaction, it creates a savepoint.

  • clojure.jdbc supports extension or substitution of transaction management if a default behavior is not sufficient for you.

  • clojure.jdbc has native support for connection pools.

  • clojure.jdbc has a simpler implementation than java.jdbc. It has no more complexity than necessary for each available function in the public API.
    As an example:

    • java.jdbc has a lot boilerplate connection management around all functions that receive dbspec. It doesn’t have well designed connection management.
      Ex: functions like create! can receive plain a dbspec or 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 unnecessary complexity found in java.jdbc.
      java.jdbc has inconsistent connection management. In contrast, with clojure.jdbc a connection should be created explicitly before using any other function that requires one connection.

    • java.jdbc has repeated transaction handling on each CRUD method (insert!, drop!, etc…​). With clojure.jdbc, if you want that some code to run in a transaction, you should wrap it in a transaction context explicitly, using the with-transaction macro (see the transactions section for more information).

  • Much more documentation ;) (a project without documentation is a project that doesn’t really exist).

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

8.3. 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:

8.4. Is this a fork of java.jdbc?

No. It is an alternative implementation.

9. License

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

Copyright (c) 2013-2014 Andrey Antukh <niwi@niwi.be>

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.

Can you improve this documentation? These fine people already did:
Andrey Antukh & Adam Miller
Edit on GitHub

cljdoc is a website building & hosting documentation for Clojure/Script libraries

× close