with-connection: What happened?

Question

In my Clojure project, I changed the dependency from [org.clojure/java.jdbc "0.2.3"] to [org.clojure/java.jdbc "0.3.3"]

I have received the following error:

clojure.lang.Compiler$CompilerException: java.lang.RuntimeException: No such var: sql/with-connection, compiling:(/Volumes/HD2/env/prj/restore/src/restore/db.clj:80:5)

What happened? Is this function deprecated?

Background: I needed execute! 0.2.3 didn't have it. 0.3.3 has it, but it lacks with-connection ?!?

Please help.

NielsK NielsK · Accepted Answer · 2014-03-23T13:38:08

with-connection was not only considered harmful for reasons leonardoborges mentioned, it also makes working with connection pools harder. Decoupling function database access from specific connections makes for a much easier model. Forcing queries to use the same connection should be the exception, not the rule. So clojure.core/java.jdbc 0.3.0 was designed specifically to deprecate with-connection. To accomodate that, the whole API needed to be changed.

Each database access function now takes the db spec as a parameter. If the db-spec is a connection pool the function will be executed on one of it's connections, otherwise an implicit new connection is made from the db-spec. So all database access functions result in a connection each when connectionpools are not used.

This also means resultsets can no longer be returned lazily. Formerly, processing lazy sequences could be postponed while still inside the with-connection block. Now they need to be realized during function execution, or its connection could be closed or a new connection could be returned from the pool for the next access function.

So processing can now be done within the scope of the functions themselves, through two new named parameters: :row-fn and :result-set-fn. The first transforms each row, the second the collection of rows. If the :result-set-fn returns a lazy sequence, you will get a connection or resultset closed exception when using it later. The default :result-set-fn is doall. When using your own, make sure it is realized.

So much for the general case, where access and connections are decoupled. Now for the exception: Needing functions to use the same connection.

The most common of these is transaction use, which uses scope to indicate the beginning and the end of the transaction. The old transaction only provided this scope. The new with-db-transaction function takes a binding of a new var and the dp-spec.

This var will be bound to one specific connection from a pool, or when no connection pools are used, a newly created connection. All db access functions used inside the block should use the var instead of the db-spec parameter.

(def db {..})

(with-db-transaction
  [c db]
  (let [from 1111
        to 2222
        sum 10
        saldo-from (query c ["select saldo from account where id=?" from]
                          :row-fn :saldo
                          :result-set-fn first)
        saldo-to (query c ["select saldo from account where id=?" to]
                        :row-fn :saldo
                        :result-set-fn first)]
    (update! c :account {:saldo (- saldo-from sum)} ["id=?" from])
    (update! c :account {:saldo (+ saldo-to sum)} ["id=?" to])))

A begin transaction command will be issued at the beginning. All access will use the same connection that's now specifically passed to the functions instead of through dynamic scoping magic. When no exceptions are generated, a commit will be given at the end of the scope.

When one specific connection, but no transaction mechanic is needed, there's the with-db-connection function which has the same semantics. So if you want to execute a command to set a session setting, and do some queries on that connection, you can do the following:

(def db {..})

(with-db-connection [c db]
                    (execute! c ["alter session set NLS_SORT='ITALIAN'"])
                    (query c ["select * from person where name=?" "Mario"]
                           :row-fn (comp concat (juxt :name :surname))))

Connection pools usually have specific on-open and on-close commands that are part of their specs. Using those all connections from that pool will have the same session settings set, and with-db-connection is not even needed at all.

with-connection: What happened?

3 Answers