gem-hunter
Language: Clojure
PostgreSQL helpers for Clojure projects
A Clojure library designed to help using more advanced PostgreSQL features in Clojure projects.
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>
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:
:dbtype is "postgresql". Current username is used for :dbname and :user as with psql command.:host, :port, :user and :dbname.pg/spec function arguments override params.:password, a ~/.pgpass lookup is made.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"})
json type parameters accept any Clojure mapsgeometry columns accept GeoJSON-like Clojure mapshstore works as before(defmethod map->parameter :mytype [m _] ...).int[], and text[] (internally _int, _text, ...) accept clojure vectors as arguments.inet type also accepts address as [192 168 1 11](defmethod vec->parameter :mytype [v _] ...).timestampt and timestamptz columns are converted to java.sql.Timestamp.(defmethod num->parameter :mytype [num _] ...).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)))>
(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))>
Copyright © 2014, Remod Oy All rights reserved.
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.