honey.sql.helpers — seancorfield/honeysql 2.0.0-alpha2

Helper functions for the built-in clauses in honey.sql.

add-column clj/s

(add-column & col-elems)

Add a single column to a table (see alter-table).

Accepts any number of SQL elements that describe a column:

(add-column :name [:varchar 32] [:not nil])

add-index clj/s

(add-index & args)

Like add-column, this accepts any number of SQL elements that describe a new index to be added:

(add-index :unique :name-key :first-name :last-name)

Produces: UNIQUE name_key(first_name, last_name)

alter-table clj/s≠

clj

(alter-table table & clauses)

cljs

(alter-table & args)

Alter table takes a SQL entity (the name of the table to modify) and any number of optional SQL clauses to be applied in a single statement.

(alter-table :foo (add-column :id :int nil))

If only the SQL entity is provided, the result needs to be combined with another SQL clause to modify the table.

(-> (alter-table :foo) (add-column :id :int nil))

columns clj/s

(columns & cols)

To be used with insert-into to specify the list of column names for the insert operation. Accepts any number of column names:

(-> (insert-into :foo) (columns :a :b :c) (values [[1 2 3] [2 4 6]]))

Produces: INSERT INTO foo (a, b, c) VALUES (?, ?, ?), (?, ?, ?) Parameters: 1 2 3 2 4 6

composite clj/s

(composite & args)

Accepts any number of SQL expressions and produces a composite value from them:

(composite :a 42)

Produces: (a, ?) Parameters: 42

create-extension clj/s≠

clj

(create-extension extension)

(create-extension extension if-not-exists)

cljs

(create-extension & args)

Accepts an extension name to create and optionally a flag to trigger IF NOT EXISTS in the SQL:

(create-extension :postgis) (create-extension :postgis :if-not-exists)

That second argument can be truthy value but using that keyword is recommended for clarity.

create-table clj/s≠

clj

(create-table table)

(create-table table if-not-exists)

cljs

(create-table & args)

Accepts a table name to create and optionally a flag to trigger IF NOT EXISTS in the SQL:

(create-table :foo) (create-table :foo :if-not-exists)

That second argument can be truthy value but using that keyword is recommended for clarity.

create-view clj/s

(create-view & args)

Accepts a single view name to create.

(-> (create-view :cities) (select :*) (from :city))

cross-join clj/s

(cross-join & args)

delete clj/s≠

clj

(delete table-coll)

cljs

(delete & args)

For deleting from multiple tables. Accepts a collection of table names to delete from.

(-> (delete [:films :directors]) (where [:= :id 1]))

delete-from clj/s≠

clj

(delete-from table)

cljs

(delete-from & args)

For deleting from a single table. Accepts a single table name to delete from.

(-> (delete-from :films) (where [:= :id 1]))

do-nothing clj/s≠

clj

(do-nothing)

cljs

(do-nothing & args)

Called with no arguments, produces DO NOTHING

do-update-set clj/s

(do-update-set & args)

drop-column clj/s≠

clj

(drop-column col)

cljs

(drop-column & args)

Takes a single column name (use with alter-table).

(alter-table :foo (drop-column :bar))

drop-extension clj/s

(drop-extension & extensions)

Accepts one or more extension names to drop.

drop-index clj/s

(drop-index & args)

Like drop-table, accepts a single index name:

(drop-index :name-key)

drop-table clj/s

(drop-table & tables)

Accepts one or more table names to drop.

(drop-table :foo)

except clj/s

(except & clauses)

Accepts any number of SQL clauses (queries) on which to perform a set except.

except-all clj/s

(except-all & clauses)

Accepts any number of SQL clauses (queries) on which to perform a set except all.

for clj/s

(for & args)

from clj/s

(from & tables)

Accepts one or more table names, or table/alias pairs.

(-> (select :*) (from [:foo :bar]))

Produces: SELECT * FROM foo AS bar

full-join clj/s

(full-join & args)

group-by clj/s

(group-by & args)

having clj/s

(having & exprs)

Like where, accepts one or more SQL expressions (conditions) and combines them with AND:

(having [:> :count 0] [:<> :name nil])

Produces: HAVING (count > ?) AND (name IS NOT NULL) Parameters: 0

inner-join clj/s

(inner-join & args)

insert-into clj/s≠

clj

(insert-into table)

(insert-into table cols)

(insert-into table statement)

(insert-into table cols statement)

cljs

(insert-into & args)

Accepts a table name or a table/alias pair. That can optionally be followed by a collection of column names. That can optionally be followed by a (select) statement clause.

(insert-into :table) (insert-into [:table :t]) (insert-into :table [:id :name :cost]) (insert-into :table (-> (select :) (from :other))) (insert-into [:table :t] [:id :name :cost] (-> (select :) (from :other)))

intersect clj/s

(intersect & clauses)

Accepts any number of SQL clauses (queries) on which to perform a set intersection.

join clj/s

(join & args)

left-join clj/s

(left-join & args)

limit clj/s≠

clj

(limit limit)

cljs

(limit & args)

Specific to MySQL, accepts a single SQL expression:

(limit 40)

Produces: LIMIT ? Parameters: 40

modify-column clj/s

(modify-column & col-elems)

Like add-column, accepts any number of SQL elements that describe the new column definition:

(modify-column :name [:varchar 64] [:not nil])

nest clj/s

(nest & args)

offset clj/s≠

clj

(offset offset)

cljs

(offset & args)

Specific to MySQL, accepts a single SQL expression:

(offset 10)

Produces: OFFSET ? Parameters: 10

on-conflict clj/s

(on-conflict & args)

on-constraint clj/s≠

clj

(on-constraint constraint)

cljs

(on-constraint & args)

Accepts a single constraint name.

order-by clj/s

(order-by & args)

outer-join clj/s

(outer-join & args)

over clj/s

(over & args)

Accepts any number of OVER clauses, each of which is a pair of an aggregate function and a window function or a triple of an aggregate function, a window function, and an alias:

(select :id (over [[:avg :salary] (partition-by :department)]))

Produces: SELECT id, AVG(salary) OVER ()PARTITION BY department)

partition-by clj/s

(partition-by & args)

rename-column clj/s≠

clj

(rename-column old-col new-col)

cljs

(rename-column & args)

Accepts two column names: the original name and the new name to which it should be renamed:

(rename-column :name :full-name)

rename-table clj/s

(rename-table & args)

Accepts a single table name and, despite its name, actually means RENAME TO:

(alter-table :foo (rename-table :bar))

Produces: ALTER TABLE foo RENAME TO bar

returning clj/s

(returning & cols)

Accepts any number of column names to return from an insert operation:

(returning :*)

Produces: RETURNING *

right-join clj/s

(right-join & args)

select clj/s

(select & exprs)

Accepts any number of column names, or column/alias pairs, or SQL expressions (optionally aliased):

(select :id [:foo :bar] [[:max :quux]])

Produces: SELECT id, foo AS bar, MAX(quux)

The special column name :* produces * for 'all columns'. You can also specify :t.* for 'all columns' from the table (or alias) t.

select-distinct clj/s

(select-distinct & args)

Like select but produces SELECT DISTINCT.

select-distinct-on clj/s≠

clj

(select-distinct-on distinct-cols & exprs)

cljs

(select-distinct-on & args)

Accepts a sequence of one or more columns for the distinct clause, followed by any number of column names, or column/alias pairs, or SQL expressions (optionally aliased), as for select:

(select-distinct-on [:a :b] :c [:d :dd])

Produces: SELECT DISTINCT ON(a, b) c, d AS dd

set clj/s≠

clj

(set col-set-map)

cljs

(set & args)

Accepts a hash map specifying column names and the values to be assigned to them, as part of update:

(-> (update :foo) (set {:a 1 :b nil}))

Produces: UPDATE foo SET a = ?, b = NULL

truncate clj/s≠

clj

(truncate table)

cljs

(truncate & args)

Accepts a single table name to truncate.

union clj/s

(union & clauses)

Accepts any number of SQL clauses (queries) on which to perform a set union.

union-all clj/s

(union-all & clauses)

Accepts any number of SQL clauses (queries) on which to perform a set union all.

update clj/s≠

clj

(update table)

cljs

(update & args)

Accepts either a table name or a table/alias pair.

(-> (update :table) (set {:id 1 :cost 32.1}))

upsert clj/s

(upsert clause)

(upsert data clause)

Provided purely to ease migration from nilenso/honeysql-postgres this accepts a single clause, constructed from on-conflict, do-nothing or do-update-set, and where. Any of those are optional.

This helper unpacks that clause and turns it into what HoneySQL 2.x expects, with any where clause being an argument to the do-update-set helper, along with the :fields.

nilenso/honeysql-postgres:

(-> ... (upsert (-> (on-conflict :col) do-nothing))) (-> ... (upsert (-> (on-conflict :col) (do-update-set :x) (where [:<> :x nil]))))

HoneySQL 2.x:

(-> ... (on-conflict :col) do-nothing) (-> ... (on-conflict :col) (do-update-set {:fields [:x] :where [:<> :x nil]}))

Alternative structure for that second one:

(-> ... (on-conflict :col) (do-update-set :x {:where [:<> :x nil]}))

using clj/s

(using & args)

values clj/s≠

clj

(values row-value-coll)

cljs

(values & args)

Accepts a single argument: a collection of row values. Each row value can be either a sequence of column values or a hash map of column name/column value pairs.

Used with insert-into.

(-> (insert-into :foo) (values [{:id 1, :name "John"} {:id 2, :name "Fred"}]))

Produces: INSERT INTO foo (id, name) VALUES (?, ?), (?, ?) Parameters: 1 "John" 2 "Fred"

where clj/s

(where & exprs)

Accepts one or more SQL expressions (conditions) and combines them with AND:

(where [:= :status 0] [:<> :task "backup"])

Produces: WHERE (status = ?) AND (task <> ?) Parameters: 0 "backup"

window clj/s

(window & args)

with clj/s

(with & args)

with-columns clj/s≠

clj

(with-columns col-spec-coll)

(with-columns & col-specs)

cljs

(with-columns & args)

Accepts any number of column descriptions. Each column description is a sequence of SQL elements that specify the name and the attributes.

(with-columns [:id :int [:not nil]] [:name [:varchar 32] [:default ""]])

Produces: id INT NOT NULL, name VARCHAR(32) DEFAULT ''

Can also accept a single argument which is a collection of column descriptions (mostly for compatibility with nilenso/honeysql-postgres which used to be needed for DDL).

with-recursive clj/s

(with-recursive & args)