honeysql-postgres

PostgreSQL extensions for the widely used honeysql.

This library aims to extend the features of honeysql to support postgres specific SQL clauses and some basic SQL DDL in addition to the ones supported by the parent library. This keeps honeysql clean and single-purpose, any vendor-specific additions can simply be separate libraries that work on top.

Index

• Usage
  • Leiningen
  • Maven
  • REPL
  • Breaking Change
  • distinct on
  • upsert
  • insert into with alias
  • over
  • create view
  • create table
  • drop table
  • alter table
  • pattern matching
  • except
  • SQL functions
• License

Usage

Leiningen

[nilenso/honeysql-postgres "0.2.6"]

Maven

<dependency>
  <groupId>nilenso</groupId>
  <artifactId>honeysql-postgres</artifactId>
  <version>0.2.6</version>
</dependency>

REPL

(require '[honeysql.core :as sql]
         '[honeysql.helpers :refer :all :as sqlh]
         '[honeysql-postgres.helpers :as psqlh])

Breaking Change

Implementation of over has been changed (from 0.2.2) to accept alias as an option and define the aggregator-function within the over clause and not in the select clause, this allows the inclusion of multiple window-function which was not possible in the previous implementation.

The query creation and usage is exactly the same as honeysql.

distinct-on

select can be written with a distinct on clause

(-> (select :column-1 :column-2 :column-3)
    (from :table-name)
    (modifiers :distinct-on :column-1 :column-2)
    (sql/format))
=> ["SELECT DISTINCT ON(column_1, column_2) column_1, column_2, column_3 FROM table_name"]

upsert

upsert can be written either way. You can make use of do-update-set! over do-update-set, if you want to modify the some column values in case of conflicts.

(-> (insert-into :distributors)
    (values [{:did 5 :dname "Gizmo Transglobal"}
             {:did 6 :dname "Associated Computing, Inc"}])
    (psqlh/upsert (-> (psqlh/on-conflict :did)
                      (psqlh/do-update-set :dname)))
    (psqlh/returning :*)
    sql/format)
=> ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *"
    5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]

(-> (insert-into :distributors)
    (values [{:did 23 :dname "Foo Distributors"}])
    (psqlh/on-conflict :did)
    (psqlh/do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || distributors.dname || ')'"] [:downer "EXCLUDED.downer"])
    sql/format)
=> ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = ?, downer = ?"
    23 "Foo Distributors" "EXCLUDED.dname || ' (formerly ' || distributors.dname || ')'" "EXCLUDED.downer"]

insert into with alias

insert-into-as can be used to write insert statements with table name aliased.

(-> (psqlh/insert-into-as :distributors :d)
    (values [{:did 5 :dname "Gizmo Transglobal"}
             {:did 6 :dname "Associated Computing, Inc"}])
    (psqlh/upsert (-> (psqlh/on-conflict :did)
                      (psqlh/do-update-set :dname)
                      (where [:<> :d.zipcode "21201"])))
    (psqlh/returning :d.*)
    sql/format)
=> ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*"
    5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]

over

You can make use of over to write window functions where it takes in vectors with aggregator functions and window functions along with optional alias like (over [aggregator-function window-function alias]), the can be coupled with the window clause to write window-function functions with alias that is later defines the window-function, like (-> (over [aggregator-function :w]) (window :w window-function)).

(-> (select :id)
    (psqlh/over
      [(sql/call :avg :salary) (-> (psqlh/partition-by :department) (order-by [:designation])) :Average]
      [(sql/call :max :salary) :w :MaxSalary])
    (from :employee)
    (psqlh/window :w (psqlh/partition-by :department))
    sql/format)
=> ["SELECT id , avg(salary) OVER (PARTITION BY department ORDER BY designation) AS Average, max(salary) OVER w AS MaxSalary FROM employee WINDOW w AS (PARTITION BY department)"]

create view

create-view can be used to create views

(-> (psqlh/create-view :metro)
    (select :*)
    (from :cities)
    (where [:= :metroflag "Y"])
    sql/format)
=> ["CREATE VIEW metro AS SELECT * FROM cities WHERE metroflag = ?" "Y"]

create table

create-table and with-columns can be used to create tables along with the SQL functions, where create-table takes a table name as argument and with-columns takes a vector of vectors as argument, where the vectors describe the column properties as [:column-name :datatype :constraints ... ].

(-> (psqlh/create-table :films)
    (psqlh/with-columns [[:code (sql/call :char 5) (sql/call :constraint :firstkey) (sql/call :primary-key)]
                         [:title (sql/call :varchar 40) (sql/call :not nil)]
                         [:did :integer (sql/call :not nil)]
                         [:date_prod :date]
                         [:kind (sql/call :varchar 10)]])
    sql/format)
=> ["CREATE TABLE films (code char(?) CONSTRAINT firstkey PRIMARY KEY, title varchar(?) NOT NULL, did integer NOT NULL, date_prod date, kind varchar(?))"
    5 40 10]

drop table

drop-table is used to drop tables

(sql/format (psqlh/drop-table :cities :towns :vilages))
=> ["DROP TABLE cities, towns, vilages"]

alter table

use alter-table along with add-column & drop-column to modify table level details

(-> (psqlh/alter-table :employees)
    (psqlh/add-column :address :text)
    sql/format)
=> ["ALTER TABLE employees ADD COLUMN address text"]

(-> (psqlh/alter-table :employees)
    (psqlh/drop-column :address)
    sql/format)
=> ["ALTER TABLE employees DROP COLUMN address"]

create-extension

create-extension can be used to create extensions with a given keyword.

(-> (psqlh/create-extension :uuid-ossp :if-not-exists? true)
    (sql/format :allow-dashed-names? true
                :quoting :ansi))
=> ["CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""]

drop-extension

drop-extension is used to drop extensions.

(-> (psqlh/drop-extension :uuid-ossp)
    (sql/format :allow-dashed-names? true
                :quoting :ansi))
=> ["DROP EXTENSION \"uuid-ossp\""]

pattern matching

The ilike and not-ilike operators can be used to query data using a pattern matching technique.

• like

(-> (select :name)
    (from :products)
    (where [:ilike :name "%name%"])
    sql/format)
=> ["SELECT name FROM products WHERE name ILIKE ?" "%name%"]

• not-ilike

(-> (select :name)
    (from :products)
    (where [:not-ilike :name "%name%"])
    sql/format)
=> ["SELECT name FROM products WHERE name NOT ILIKE ?" "%name%"]

except

(sql/format
  {:except
    [{:select [:ip]}
     {:select [:ip] :from [:ip_location]}]})
=> ["SELECT ip EXCEPT SELECT ip FROM ip_location"]

except-all works the same way as except.

SQL functions

The following are the SQL functions added in honeysql-postgres

• not

(sql/format (sql/call :not nil))
=> ["NOT NULL"]

• primary-key

(sql/format (sql/call :primary-key))
=> ["PRIMARY KEY"]

(sql/format (sql/call :primary-key :arg1 :arg2))
=> ["PRIMARY KEY(arg1, arg2)"]

• unique

(sql/format (sql/call :unique))
=> ["UNIQUE"]

(sql/format (sql/call :unique :arg1 :arg2))
=> ["UNIQUE(arg1, arg2)"]

• foreign-key

(sql/format (sql/call :foreign-key))
=> ["FOREIGN KEY"]

(sql/format (sql/call :foreign-key :arg1 :arg2))
=> ["FOREIGN KEY(arg1, arg2)"]

• references

(sql/format (sql/call :references :reftable :refcolumn))
=> ["REFERENCES reftable(refcolumn)"]

• constraint

(sql/format (sql/call :constraint :name))
=> ["CONSTRAINT name"]

• default

(sql/format (sql/call :default :value))
=> ["DEFAULT value"]

• nextval

(sql/format (sql/call :nextval :value))
=> ["nextval('value')"]

• check

(sql/format (sql/call :check [:= :a :b]))
=> ["CHECK(a = b)"]

(sql/format (sql/call :check [:= :a :b] [:= :c :d]))
["CHECK(a = b AND c = d)"]

License

Copyright © 2020 Nilenso

Distributed under the Eclipse Public License, the same as Clojure.