Table of Content

PG2 version 0.1.3 is out. One of its new features is a module which mimics Next.JDBC API. Of course, it doesn’t cover 100% of Next.JDBC features yet most of the functions and macros are there. It will help you to introduce PG2 into the project without rewriting all the database-related code from scratch.

Obtaining a Connection

In Next.JDBC, all the functions and macros accept something that implements the Connectable protocol. It might be a plain Clojure map, an existing connection, or a connection pool. The PG2 wrapper follows this design. It works with either a map, a connection, or a pool.

Import the namespace and declare a config:

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

(def config
  {:host "127.0.0.1"
   :port 10140
   :user "test"
   :password "test"
   :dbname "test"})

Having a config map, obtain a connection by passing it into the get-connection function:

(def conn
  (jdbc/get-connection config))

This approach, although is a part of the Next.JDBC design, is not recommended to use. Once you’ve established a connection, you must either close it or, if it was borrowed from a pool, return it to the pool. There is a special macro on-connection that covers this logic:

(jdbc/on-connection [bind source]
  ...)

If the source was a map, a new connection is spawned and gets closed afterwards. If the source is a pool, the connection gets returned to the pool. When the source is a connection, nothing happens when exiting the macro.

(jdbc/on-connection [conn config]
  (println conn))

A brief example with a connection pool and a couple of futures. Each future borrows a connection from a pool, and returns it afterwards.

(pool/with-pool [pool config]
  (let [f1
        (future
          (jdbc/on-connection [conn1 pool]
            (println
             (jdbc/execute-one! conn1 ["select 'hoho' as message"]))))
        f2
        (future
          (jdbc/on-connection [conn2 pool]
            (println
             (jdbc/execute-one! conn2 ["select 'haha' as message"]))))]
    @f1
    @f2))

;; {:message hoho}
;; {:message haha}

Executing Queries

Two functions execute! and execute-one! send queries to the database. Each of them takes a source, a SQL vector, and a map of options. The SQL vector is a sequence where the first item is either a string or a prepared statement, and the rest values are parameters.

(jdbc/on-connection [conn config]
  (jdbc/execute! conn ["select $1 as num" 42]))
;; [{:num 42}]

Pay attention that parameters use a dollar sign with a number but not a question mark.

The execute-one! function acts like execute! but returns the first row only. Internaly, this is done by passing the {:first? true} parameter that enables the First reducer.

(jdbc/on-connection [conn config]
  (jdbc/execute-one! conn ["select $1 as num" 42]))
;; {:num 42}

To prepare a statement, pass a SQL-vector into the prepare function. The result will be an instance of the PreparedStatement class. To execute a statement, put it into a SQL-vector followed by the parameters:

(jdbc/on-connection [conn config]
  (let [stmt
        (jdbc/prepare conn
                      ["select $1::int4 + 1 as num"])
        res1
        (jdbc/execute-one! conn [stmt 1])

        res2
        (jdbc/execute-one! conn [stmt 2])]

    [res1 res2]))

;; [{:num 2} {:num 3}]

Above, the same stmt statement is executed twice with different parameters.

More realistic example with inserting data into a table. Let’s prepare the table first:

(jdbc/execute! config ["create table test2 (id serial primary key, name text not null)"])

Insert a couple of rows returning the result:

(jdbc/on-connection [conn config]
  (let [stmt
        (jdbc/prepare conn
                      ["insert into test2 (name) values ($1) returning *"])

        res1
        (jdbc/execute-one! conn [stmt "Ivan"])

        res2
        (jdbc/execute-one! conn [stmt "Huan"])]

    [res1 res2]))

;; [{:name "Ivan", :id 1} {:name "Huan", :id 2}]

As it was mentioned above, in Postgres, a prepared statement is always bound to a certain connection. Thus, use the prepare function only inside the on-connection macro to ensure that all the underlying database interaction is made within the same connection.

Transactions

The with-transaction macro wraps a block of code into a transaction. Before entering the block, the macro emits the BEGIN expression, and COMMIT afterwards, if there was no an exception. Should an exception pop up, the transaction gets rolled back with ROLLBACK, and the exception is re-thrown.

The macro takes a binding symbol which a connection is bound to, a source, an a map of options. The standard Next.JDBC transaction options are supported, namely:

  • :isolation
  • :read-only
  • :rollback-only

Here is an example of inserting a couple of rows in a transaction:

(jdbc/on-connection [conn config]

  (let [stmt
        (jdbc/prepare conn
                      ["insert into test2 (name) values ($1) returning *"])]

    (jdbc/with-transaction [TX conn {:isolation :serializable
                                     :read-only false
                                     :rollback-only false}]

      (let [res1
            (jdbc/execute-one! conn [stmt "Snip"])

            res2
            (jdbc/execute-one! conn [stmt "Snap"])]

        [res1 res2]))))

;; [{:name "Snip", :id 3} {:name "Snap", :id 4}]

The Postgres log:

BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
insert into test2 (name) values ($1) returning *
  $1 = 'Snip'
insert into test2 (name) values ($1) returning *
  $1 = 'Snap'
COMMIT

The :isolation parameter might be one of the following:

  • :read-uncommitted
  • :read-committed
  • :repeatable-read
  • :serializable

To know more about transaction isolation, refer to the official [Postgres documentation][transaction-iso].

When read-only is true, any mutable query will trigger an error response from Postgres:

(jdbc/with-transaction [TX config {:read-only true}]
  (jdbc/execute! TX ["delete from test2"]))

;; Execution error (PGErrorResponse) at org.pg.Accum/maybeThrowError (Accum.java:207).
;; Server error response: {severity=ERROR, message=cannot execute DELETE in a read-only transaction, verbosity=ERROR}

When :rollback-only is true, the transaction gets rolled back even there was no an exception. This is useful for tests and experiments:

(jdbc/with-transaction [TX config {:rollback-only true}]
  (jdbc/execute! TX ["delete from test2"]))

The logs:

statement: BEGIN
execute s1/p2: delete from test2
statement: ROLLBACK

The table still has its data:

(jdbc/execute! config ["select * from test2"])

;; [{:name "Ivan", :id 1} ...]

The function active-tx? helps to determine if you’re in the middle of a transaction:

(jdbc/on-connection [conn config]
  (let [res1 (jdbc/active-tx? conn)]
    (jdbc/with-transaction [TX conn]
      (let [res2 (jdbc/active-tx? TX)]
        [res1 res2]))))

;; [false true]

It returns true for transactions tha are in the error state as well.

Keys and Namespaces

The pg.jdbc wrapper tries to mimic Next.JDBC and thus uses kebab-case-keys when building maps:

(jdbc/on-connection [conn config]
  (jdbc/execute-one! conn ["select 42 as the_answer"]))

;; {:the-answer 42}

To change that behaviour and use snake_case_keys, pass the {:kebab-keys? false} option map:

(jdbc/on-connection [conn config]
  (jdbc/execute-one! conn
                     ["select 42 as the_answer"]
                     {:kebab-keys? false}))

;; {:the_answer 42}

By default, Next.JDBC returns full-qualified keys where namespaces are table names, for example :user/profile-id or :order/created-at. At the moment, namespaces are not supported by the wrapper.

For more information, please refer to the official README file.

← PG2 release 0.1.2: more performance, benchmarks, part 3

PG2 release 0.1.4: HoneySQL API and shortcuts →