I’ve been experimenting with the Clojure programming language, and it’s been a lot of fun. But being rather new to the language, it took me a bit of trial and error to figure out how to implement transactions for an SQL database using the clojure.java.jdbc database connector library. Here’s what I found.

First, let’s look at a standard database call without transactions.

(ns vidbmk.model
  (:require [clojure.java.jdbc :as jdbc]))

(def db-spec {:subprotocol "postgresql"
              :subname "//localhost:5432/testdb"
              :user "test"
              :password "test"})

(jdbc/insert! db-spec :mytable {:id 1, :name "test data"})

• The first line is the namespace declaration, which also pulls in the clojure.java.jdbc library, to be referenced by “jdbc” prefix.
• Then we define db-spec as a map of the database connection details.
• Finally, we run the command to insert a row of test data into a table called “mytable”. Note that we pass in the db-spec map as the first argument for jdbc/insert!.

That’s fine, but we would really like to be able to roll back that insert call in the case of an exception, or for testing purposes. To do this, you just need to wrap all your database calls in db-transaction, like so (the namespace and db-spec remain the same).

(jdbc/db-transaction [t-con db-spec]
  (jdbc/insert! t-con :mytable {:id 1, :name "test data"}))

Notice that the addition of t-con, and its replacement of db-spec in the call to insert!. t-con can be any arbitrary name you want, it is bound to the database connection created by db-transaction, and whatever you choose for a name will replace db-spec in all database calls nested within the db-transaction.

For example, to apply a transaction bound to “blahblah” to multiple database calls…

(jdbc/db-transaction [blahblah db-spec]
  (jdbc/insert! blahblah :mytable {:id 1, :name "test data"})
  (jdbc/insert! blahblah :mytable {:id 2, :name "more test data"}))

If anything within the db-transaction scope throws an exception, the transaction will be rolled back. Or, you can force a rollback by explicitly calling db-set-rollback-only! within the transaction scope.

(jdbc/db-transaction [blahblah db-spec]
  (jdbc/insert! blahblah :mytable {:id 1, :name "test data"})
  (jdbc/insert! blahblah :mytable {:id 2, :name "more test data"})
  (jdbc/db-set-rollback-only! blahblah))

More examples here: https://github.com/clojure/java.jdbc/blob/master/doc/clojure/java/jdbc/UsingSQL.md