honey.sql.helpers — com.github.seancorfield/honeysql 2.0.0-alpha3

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))

bulk-collect-into clj/s

(bulk-collect-into & args)

Accepts a variable name, optionally followed by a limit expression.

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)

create-materialized-view clj/s≠

clj

(create-materialized-view view)

cljs

(create-materialized-view & args)

Accepts a single view name to create.

(-> (create-materialized-view :cities) (select :*) (from :city)) (with-data true)

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)

create-table-as clj/s≠

clj

(create-table-as table)

(create-table-as table if-not-exists)

cljs

(create-table-as & args)

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

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

create-view clj/s≠

clj

(create-view view)

cljs

(create-view & args)

Accepts a single view name to create.

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

cross-join clj/s

(cross-join & args)

Accepts one or more CROSS JOIN expressions. Each cross join expression is specified as a table name (or a pair of table and alias):

(cross-join :table) (cross-join [:table :t])

Produces: CROSS JOIN table CROSS JOIN table AS t

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≠

clj

(do-update-set field-where-map)

(do-update-set column-value-map)

(do-update-set column* opt-where-clause)

cljs

(do-update-set & args)

Accepts one or more columns to update, or a hash map of column/value pairs (like set), optionally followed by a WHERE clause. Can also accept a single hash map with a :fields entry specifying the columns to update and a :where entry specifying the WHERE clause.

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-materialized-view clj/s

(drop-materialized-view & views)

Accepts one or more materialied view names to drop.

drop-table clj/s

(drop-table & tables)

Accepts one or more table names to drop.

(drop-table :foo)

drop-view clj/s

(drop-view & views)

Accepts one or more view names to drop.

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.

fetch clj/s≠

clj

(fetch offset)

cljs

(fetch & args)

Accepts a single SQL expression:

(fetch 10)

Produces: FETCH ? ONLY Parameters: 10

for clj/s≠

clj

(for lock-strength table* qualifier*)

cljs

(for & args)

Accepts a lock strength, optionally followed by one or more table names, optionally followed by a qualifier.

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)

Accepts one or more FULL JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition:

(full-join :table [:= :foo.id :table.foo_id]) (full-join [:table :t] [:= :foo.id :t.foo_id])

Produces: INNER JOIN table ON foo.id = table.foo_id INNER JOIN table AS t ON foo.id = t.foo_id

group-by clj/s

(group-by & args)

Accepts one or more SQL expressions to group by.

(group-by :foo :bar) (group-by [:date :baz])

Produces: GROUP BY foo, bar GROUP BY DATE(baz)

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)

An alternative name to join, this accepts one or more INNER JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition:

(inner-join :table [:= :foo.id :table.foo_id]) (inner-join [:table :t] [:= :foo.id :t.foo_id])

Produces: INNER JOIN table ON foo.id = table.foo_id INNER JOIN table AS t ON foo.id = t.foo_id

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.

into clj/s

(into & args)

Accepts table name, optionally followed a database name.

join clj/s

(join & args)

Accepts one or more (INNER) JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition:

(join :table [:= :foo.id :table.foo_id]) (join [:table :t] [:= :foo.id :t.foo_id])

Produces: INNER JOIN table ON foo.id = table.foo_id INNER JOIN table AS t ON foo.id = t.foo_id

join-by clj/s

(join-by & args)

Accepts a sequence of join clauses to be generated in a specific order.

(-> (select :*) (from :foo) (join-by :left :bar [:= :foo.id :bar.id] :join :quux [:= :bar.qid = :quux.id])

This produces a LEFT JOIN followed by an INNER JOIN even though the 'natural' order for left-join and join would be to generate the INNER JOIN first, followed by the LEFT JOIN.

lateral clj/s≠

clj

(lateral clause-or-expression)

cljs

(lateral & args)

Accepts a SQL clause or a SQL expression:

(lateral (-> (select '*) (from 'foo))) (lateral '(calc_value bar))

Produces: LATERAL (SELECT * FROM foo) LATERAL CALC_VALUE(bar)

left-join clj/s

(left-join & args)

Accepts one or more LEFT JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition:

(left-join :table [:= :foo.id :table.foo_id]) (left-join [:table :t] [:= :foo.id :t.foo_id])

Produces: LEFT JOIN table ON foo.id = table.foo_id LEFT JOIN table AS t ON foo.id = t.foo_id

limit clj/s≠

clj

(limit limit)

cljs

(limit & args)

Specific to some databases (notabley MySQL), accepts a single SQL expression:

(limit 40)

Produces: LIMIT ? Parameters: 40

The two-argument syntax is not supported: use offset instead:

LIMIT 20,10 is equivalent to LIMIT 10 OFFSET 20

(-> (limit 10) (offset 20))

lock clj/s≠

clj

(lock lock-mode)

cljs

(lock & args)

Intended for MySQL, this accepts a lock mode.

It will accept the same type of syntax as for even though MySQL's lock clause is less powerful.

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≠

clj

(nest clause)

cljs

(nest & args)

A pseudo clause that exists purely to cause nesting in parentheses. Should only be needed very rarely in cases where HoneySQL doesn't do the right thing for your specific database dialect.

Wraps a single clause.

offset clj/s≠

clj

(offset offset)

cljs

(offset & args)

Accepts a single SQL expression:

(offset 10)

Produces: OFFSET ? Parameters: 10

on-conflict clj/s≠

clj

(on-conflict column)

(on-conflict column where-clause)

cljs

(on-conflict & args)

Accepts a single column name to detect conflicts during an upsert, optionally followed by a WHERE clause.

on-constraint clj/s≠

clj

(on-constraint constraint)

cljs

(on-constraint & args)

Accepts a single constraint name.

on-duplicate-key-update clj/s≠

clj

(on-duplicate-key-update column-value-map)

cljs

(on-duplicate-key-update & args)

MySQL's upsert facility. Accepts a hash map of column/value pairs to be updated (like set does).

order-by clj/s

(order-by & args)

Accepts one or more expressions to order by.

An ordering expression may be a simple column name which is assumed to be ordered ASC, or a pair of an expression and a direction (:asc or :desc):

(order-by :foo) (order-by [:bar :desc]) (order-by [[:date :baz] :asc])

Produces: ORDER BY foo ASC ORDER BY bar DESC ORDER BY DATE(baz) ASC

outer-join clj/s

(outer-join & args)

Accepts one or more OUTER JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition:

(outer-join :table [:= :foo.id :table.foo_id]) (outer-join [:table :t] [:= :foo.id :t.foo_id])

Produces: OUTER JOIN table ON foo.id = table.foo_id OUTER JOIN table AS t ON foo.id = t.foo_id

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)

Accepts one or more columns or SQL expressions to partition by as part of a WINDOW expression.

refresh-materialized-view clj/s≠

clj

(refresh-materialized-view view)

cljs

(refresh-materialized-view & views)

Accepts a materialied view name to refresh.

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)

Accepts one or more RIGHT JOIN expressions. Each join expression is specified as a pair of arguments, where the first one is the table name (or a pair of table and alias) and the second one is the join condition:

(right-join :table [:= :foo.id :table.foo_id]) (right-join [:table :t] [:= :foo.id :t.foo_id])

Produces: RIGHT JOIN table ON foo.id = table.foo_id RIGHT JOIN table AS t ON foo.id = t.foo_id

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

select-distinct-top clj/s

(select-distinct-top & args)

Like select-top but produces SELECT DISTINCT TOP...

select-top clj/s

(select-top & args)

Accepts a TOP expression, followed by any number of column names, or column/alias pairs, or SQL expressions (optionally aliased), as for select. The TOP expression can be a simple numeric expression, or a sequence with a numeric expression followed by keywords (or symbols) for PERCENT and/or WITH TIES.

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)

Accepts similar arguments to select as part of a SQL USING clause.

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)

Accepts a window name followed by a partition by clause.

with clj/s

(with & args)

Accepts one or more CTE definitions.

See the documentation for the :with clause.

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-data clj/s≠

clj

(with-data data?)

cljs

(with-data & args)

Accepts a Boolean determining WITH DATA vs WITH NO DATA.

with-recursive clj/s

(with-recursive & args)

Accepts one or more CTE definitions.

See the documentation for the :with clause.