remodoy/clj-postgresql

README

clj-postgresql

A Clojure library designed to help using more advanced PostgreSQL features in Clojure projects.

Make use of PGHOST, PGUSER and PGDATABASE when available
Can use ~/.pgpass for passwords
Implement clojure.java.jdbc's ISQLValue and IResultSetReadColumn to coerce clojure types

Releases

Add the following to the :dependencies section of your project.clj file:

Leiningen dependency information:

[clj-postgresql "0.7.0"]

Maven dependency information:

<dependency>
  <groupId>clj-postgresql</groupId>
  <artifactId>clj-postgresql</artifactId>
  <version>0.7.0-SNAPSHOT</version>
</dependency>

Documentation

Connecting to database

The pg/spec and pg/pool functions use PGHOST, PGPORT, PGUSER and PGDATABASE environment variables and the ~/.pgpass file by default. The function arguments can be used to override the connection parameters in the environment. E.g.:

(require '[clj-postgresql.core :as pg])
(require '[clojure.java.jdbc :as jdbc])

(def db (pg/spec))
(jdbc/query db ["SELECT true"])

(def pooled-db (pg/pool))
(jdbc/query pooled-db ["SELECT 'hello from db'"])

(def db2 (pg/pool :host "db1.example.com" :user "myaccount" :dbname "anotherdb" :password "foobar"))
(jdbc/query db2 ["SELECT 'test'"])

You can pass hikari-specific options via the hikari keyword:

(def db2 (pg/pool :host "db1.example.com"
                  :user "myaccount"
                  :dbname "anotherdb"
                  :password "foobar"
                  :hikari {:read-only true}))

The pool can be closed with:

(pg/close! db)

ACTUALLY DO THIS (prevent compile time resolution of connection params and initialization of the pool):

(def db (delay (pg/pool)))
(jdbc/query @db ["SELECT 1"])

Under the hood, pg/spec uses the following logic:

Default :dbtype is "postgresql". Current username is used for :dbname and :user as with psql command.
PGHOST, PGPORT, PGUSER and PGDATABASE environment variables override default :host, :port, :user and :dbname.
pg/spec function arguments override params.
If there is no :password, a ~/.pgpass lookup is made.

Automatic type conversions

With clj-postgresql, clojure.java.jdbc is extended to accept native clojure maps, vectors and sequences as parameter values. Conversion from clojure type to native SQL type is done based on the parameter type information returned by PostgreSQL.

(require '[clj-postgresql :as pg])
(require '[clojure.java.jdbc :as jdbc])
(def db (pg/spec))
(jdbc/query db ["SELECT ?::int[] AS arr", [1 2 3 4]])
; => ({:arr [1 2 3 4]})
(jdbc/query db ["SELECT ?::json AS jsonobj" {"foo" "bar"}])
; => ({:jsonobj {"foo" "bar"}})
(jdbc/query db ["SELECT ?::timestamptz AS epoch" 1])
; => ({:epoch #inst "1970-01-01T00:00:00.001000000-00:00"})

Clojure maps

json type parameters accept any Clojure maps
geometry columns accept GeoJSON-like Clojure maps
hstore works as before
Extendable multimethod to convert map to custom PostgreSQL types e.g. (defmethod map->parameter :mytype [m _] ...).

Clojure vectors

PostgreSQL array types like int[], and text[] (internally _int, _text, ...) accept clojure vectors as arguments.
inet type also accepts address as [192 168 1 11]
Extendable multimethod to convert vector to custom PostgreSQL types e.g. (defmethod vec->parameter :mytype [v _] ...).

Sequables (e.g. lists)

Are converted to vectors

Numbers

Numeric values to timestampt and timestamptz columns are converted to java.sql.Timestamp.
Extendable multimethod to convert numeric values to custom PostgreSQL types e.g. (defmethod num->parameter :mytype [num _] ...).

PostGIS types

The org.postgis.Point, etc. are of org.postgis.Geometry type. They cannot be directly used as query parameters without first wrapping them to PGgeometry. This library extends clojure.java.jdbc to automatically convert Geometry objects into PGgeometry when inserting and automatically convert PGgeometries to specific Geometry objects when reading from database.

(require '[clj-postgresql.spatial :as st])

(st/point 1 2)
;=> #<Point POINT(1 2)>
(st/point 1 2 3)
;=> #<Point POINT(1 2 3)>
(st/point [1 2])
;=> #<Point POINT(1 2)>

(st/multi-point [[1 2] [3 4] [5 6 7]])
;=> #<MultiPoint MULTIPOINT(1 2,3 4,5 6 7)>

(st/line-string [[1 2] [3 4] [5 6 7]])
;=> #<LineString LINESTRING(1 2,3 4,5 6 7)>

(st/multi-line-string [[[1 2] [3 4]] [[5 6] [7 8]]])
;=> #<MultiLineString MULTILINESTRING((1 2,3 4),(5 6,7 8))>
(st/multi-line-string [[[1 2] [3 4]] (st/line-string [[5 6] [7 8]])])
;=> #<MultiLineString MULTILINESTRING((1 2,3 4),(5 6,7 8))>

(st/linear-ring [[1 2] [3 4]])
;=> #<LinearRing (1 2,3 4)>

(st/polygon [[[1 2] [3 4] [5 6]]])
;=> #<Polygon POLYGON((1 2,3 4,5 6))>
(st/polygon [(st/linear-ring [[1 2] [3 4] [5 6]]) (st/linear-ring [[7 8] [9 10] [11 12]])])
;=> #<Polygon POLYGON((1 2,3 4,5 6),(7 8,9 10,11 12))>
(st/polygon [ [[1 2] [3 4] [5 6]] [[7 8] [9 10] [11 12]] ])
;=> #<Polygon POLYGON((1 2,3 4,5 6),(7 8,9 10,11 12))>

(st/multi-polygon [[[[1 2] [3 4] [5 6]] [[7 8] [9 10] [11 12]]] [[[1 2] [3 4] [5 6]] [[7 8] [9 10] [11 12]]]])
;=> #<MultiPolygon MULTIPOLYGON(((1 2,3 4,5 6),(7 8,9 10,11 12)),((1 2,3 4,5 6),(7 8,9 10,11 12)))>

PostgreSQL geometric types

(require '[clj-postgresql.core :as pg])

;; point [x y], [pgpoint-or-str]
(pg/point 1 2)
;=> #<PGpoint (1.0,2.0)>
(pg/point [3.0 4])
;=> #<PGpoint (3.0,4.0)>
(pg/point (pg/point 1 2))
;=> #<PGpoint (1.0,2.0)>
(pg/point (PGpoint. 1 2))
;=> #<PGpoint (1.0,2.0)>
(str (pg/point 1 2))
;=> "(1.0,2.0)"

;; box [x1 y1 x2 y2], [p1 p2], [pgbox-or-str]
(pg/box 1 2 3 4)
;=> #<PGbox (1.0,2.0),(3.0,4.0)>
(pg/box (pg/point 1 2) (pg/point 3 4))
;=> #<PGbox (1.0,2.0),(3.0,4.0)>
(pg/box [1 2] [3 4])
;=> #<PGbox (1.0,2.0),(3.0,4.0)>

;; circle [x y r], [point r], [pgcircle-or-str]
(pg/circle 25.0 30.0 5)
;=> #<PGcircle <(25.0,30.0),5.0>>
(pg/circle [25 30] 5)
;=> #<PGcircle <(25.0,30.0),5.0>>
(pg/circle (pg/point 25 30) 5)
;=> #<PGcircle <(25.0,30.0),5.0>>

;; line [x1 y1 x2 y2], [p1 p2], [pgline-or-str]
(pg/line 1 2 3 4)
;=> #<PGline [(1.0,2.0),(3.0,4.0)]>
(pg/line [1 2] [3 4])
;=> #<PGline [(1.0,2.0),(3.0,4.0)]>
(pg/line (pg/point 1 2) (pg/point 3 4))
;=> #<PGline [(1.0,2.0),(3.0,4.0)]>

;; lseg [x1 y1 x2 y2], [p1 p2], [pglseg-or-str]
(pg/lseg 1 2 10 20)
;=> #<PGlseg [(1.0,2.0),(10.0,20.0)]>
(pg/lseg [1 2] [10 20])
;=> #<PGlseg [(1.0,2.0),(10.0,20.0)]>
(pg/lseg (pg/point 1 2) (pg/point 10 20))
;=> #<PGlseg [(1.0,2.0),(10.0,20.0)]>

;; path [points-coll open?]
(pg/path [[1 2] [10 20] [50 100]] true)
;=> #<PGpath [(1.0,2.0),(10.0,20.0),(50.0,100.0)]>
(pg/path [(pg/point 1 2) (pg/point 10 20) (pg/point 50 100)] true)
;=> #<PGpath [(1.0,2.0),(10.0,20.0),(50.0,100.0)]>
(pg/path [[1 2] [3 4] [5 6]] false) ; closed path
;=> #<PGpath ((1.0,2.0),(3.0,4.0),(5.0,6.0))>

;; polygon [points-or-pgpolygon-or-str]
(pg/polygon [[1 2] [3 4] [5 6]])
;=> #<PGpolygon ((1.0,2.0),(3.0,4.0),(5.0,6.0))>
(pg/polygon "((1.0,2.0),(3.0,4.0),(5.0,6.0))")
;=> #<PGpolygon ((1.0,2.0),(3.0,4.0),(5.0,6.0))>

License

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.