honey.sql.helpers
(source)Helper functions for the built-in clauses in honey.sql.
All helper functions are inherently variadic.
In general, `(helper :foo expr)` will produce `{:helper [:foo expr]}`,
with a few exceptions: see the docstring of the helper function for details.
Typical usage is threaded, like this:
```
(-> (select :a :b :c)
(from :table)
(where [:= :id 42])
(sql/format))
```
Therefore all helpers can take an existing DSL expression
as their first argument or, if the first argument is not
a hash map, an empty DSL is assumed -- an empty hash map.
The above is therefore equivalent to:
```
(-> {}
(select :a :b :c)
(from :table)
(where [:= :id 42])
(sql/format))
```
Some of the helper functions here have `:arglists` metadata
in an attempt to provide better hints for auto-complete in
editors but those `:arglists` _always omit the DSL argument_
to avoid duplicating the various argument lists. When you
see an auto-complete suggestion like:
bulk-collect-into [varname] [varname n]
bear in mind that a DSL hash map can always be threaded in
so the following (pseudo) arities are also available:
bulk-collect-into [dsl varname] [dsl varname n]
The actual arguments are:
bulk-collect-info [& args]
(as they are for all helper functions).
For more info about this library see:
https://cljdoc.org/d/com.github.seancorfield/honeysql/2.5.1103/doc/getting-startedPublic Variable | Short Description |
---|---|
add-column (clj) | Add a single column to a table (see `alter-table`). |
add-index (clj) | 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-column (clj) | Like add-column, accepts any number of SQL elements that describe the new column definition: (alter-column :name [:varchar 64] [:not nil]). |
alter-table (clj) | 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. |
bulk-collect-into (clj) | Accepts a variable name, optionally followed by a limit expression. |
columns (clj) | To be used with `insert-into` to specify the list of column names for the insert operation. |
compare-with (clj) | Accepts a time interval such as: (compare-with 1 :week :ago) Produces: COMPARE WITH 1 WEEK AGO. |
composite (clj) | Accepts any number of SQL expressions and produces a composite value from them: (composite :a 42) Produces: (a, ?) Parameters: 42. |
create-extension (clj) | 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-index (clj) | Accepts an index spexification and a column specification. |
create-materialized-view (clj) | Accepts a single view name to create. |
create-or-replace-view (clj) | Accepts a single view name to create. |
create-table (clj) | 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) | 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) | Accepts a single view name to create. |
cross-join (clj) | Accepts one or more CROSS JOIN expressions. |
delete (clj) | For deleting from multiple tables. |
delete-from (clj) | For deleting from a single table. |
distinct (clj) | Like `select-distinct` but produces DISTINCT... |
do-nothing (clj) | Called with no arguments, produces DO NOTHING. |
do-update-set (clj) | Accepts one or more columns to update, or a hash map of column/value pairs (like `set`), optionally followed by a `WHERE` clause. |
drop-column (clj) | Takes one or more column names (use with `alter-table`). |
drop-extension (clj) | Accepts one or more extension names to drop. |
drop-index (clj) | Like drop-table, accepts a single index name: (drop-index :name-key). |
drop-materialized-view (clj) | Accepts one or more materialied view names to drop. |
drop-table (clj) | Accepts one or more table names to drop. |
drop-view (clj) | Accepts one or more view names to drop. |
except (clj) | Accepts any number of SQL clauses (queries) on which to perform a set except. |
except-all (clj) | Accepts any number of SQL clauses (queries) on which to perform a set except all. |
expr (clj) | Like `distinct` but produces ... |
facet (clj) | Accepts any number of column names, or column/alias pairs, or SQL expressions (optionally aliased): (facet :id [:foo :bar] [[:max :quux]]) Produces: FACET id, foo AS bar, MAX(quux). |
fetch (clj) | Accepts a single SQL expression: (fetch 10) Produces: FETCH ? ONLY Parameters: 10. |
filter (clj) | Accepts alternating expressions and clauses and produces a FILTER expression: (filter :%count.* (where :> i 5)) Produces: COUNT(*) FILTER (WHERE i > ?) Parameters: 5. |
for (clj) | Accepts a lock strength, optionally followed by one or more table names, optionally followed by a qualifier. |
from (clj) | Accepts one or more table names, or table/alias pairs. |
full-join (clj) | Accepts one or more FULL JOIN expressions. |
generic-helper-unary (clj) | Clauses that accept only a single item can be implemented using this helper, as: (defn my-helper [& args] (generic-helper-unary :my-clause args)) Even though your helper is designed for clauses that accept only a single item, you should still define it as variadic, because that is the convention all helpers use here. |
generic-helper-variadic (clj) | Most clauses that accept a sequence of items can be implemented using this helper, as: (defn my-helper [& args] (generic-helper-variadic :my-clause args)). |
group-by (clj) | Accepts one or more SQL expressions to group by. |
having (clj) | Like `where`, accepts one or more SQL expressions (conditions) and combines them with AND (by default): (having [:> :count 0] [:<> :name nil]) or: (having :and [:> :count 0] [:<> :name nil]) Produces: HAVING (count > ?) AND (name IS NOT NULL) Parameters: 0 (having :> :count 0) Produces: HAVING count > ? Parameters: 0 (having :or [:> :count 0] [:= :name ""]) Produces: HAVING (count > ?) OR (name = ?) Parameters: 0 "". |
inner-join (clj) | An alternative name to `join`, this accepts one or more INNER JOIN expressions. |
insert-into (clj) | Accepts a table name or a table/alias pair. |
intersect (clj) | Accepts any number of SQL clauses (queries) on which to perform a set intersection. |
into (clj) | Accepts table name, optionally followed a database name. |
join (clj) | Accepts one or more (INNER) JOIN expressions. |
join-by (clj) | Accepts a sequence of join clauses to be generated in a specific order. |
lateral (clj) | 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) | Accepts one or more LEFT JOIN expressions. |
limit (clj) | 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) | Intended for MySQL, this accepts a lock mode. |
modify-column (clj) | Like add-column, accepts any number of SQL elements that describe the new column definition: (modify-column :name [:varchar 64] [:not nil]) MySQL-specific, deprecated. |
offset (clj) | Accepts a single SQL expression: (offset 10) Produces: OFFSET ? Parameters: 10. |
on-conflict (clj) | Accepts zero or more SQL entities (keywords or symbols), optionally followed by a single SQL clause (`{:where <condition>}`). |
on-constraint (clj) | Accepts a single constraint name. |
on-duplicate-key-update (clj) | MySQL's upsert facility. |
order-by (clj) | Accepts one or more expressions to order by. |
outer-join (clj) | Accepts one or more OUTER JOIN expressions. |
over (clj) | 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) | Accepts one or more columns or SQL expressions to partition by as part of a `WINDOW` expression. |
refresh-materialized-view (clj) | Accepts a materialied view name to refresh. |
rename-column (clj) | 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) | 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. |
replace-into (clj) | Accepts a table name or a table/alias pair. |
returning (clj) | Accepts any number of column names to return from an insert operation: (returning :*) and (returning :a :b) Produce: RETURNING * and RETURNING a, b respectively. |
right-join (clj) | Accepts one or more RIGHT JOIN expressions. |
select (clj) | 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'. |
select-distinct (clj) | Like `select` but produces SELECT DISTINCT. |
select-distinct-on (clj) | 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) | Like `select-top` but produces SELECT DISTINCT TOP... |
select-top (clj) | Accepts a TOP expression, followed by any number of column names, or column/alias pairs, or SQL expressions (optionally aliased), as for `select`. |
set (clj) | 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. |
since (clj) | Accepts a time interval such as: (since 2 :days :ago) Produces: SINCE 2 DAYS AGO. |
table (clj) | Accepts a single table name and produces TABLE name This is equivalent to: SELECT * FROM name. |
timeseries (clj) | Accepts a time interval such as: (timeseries 1 :day) or: (timeseries :auto) Produces: TIMESERIES 1 DAY Or: TIMESERIES AUTO. |
truncate (clj) | Accepts a single table name to truncate. |
union (clj) | Accepts any number of SQL clauses (queries) on which to perform a set union. |
union-all (clj) | Accepts any number of SQL clauses (queries) on which to perform a set union all. |
until (clj) | Accepts a time interval such as: (until 1 :month :ago) Produces: UNTIL 1 MONTH AGO. |
update (clj) | Accepts either a table name or a table/alias pair. |
upsert (clj) | 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. |
using (clj) | Accepts similar arguments to `select` as part of a SQL `USING` clause. |
values (clj) | Accepts a single argument: a collection of row values. |
where (clj) | Accepts one or more SQL expressions (conditions) and combines them with AND (by default): (where [:= :status 0] [:<> :task "backup"]) or: (where :and [:= :status 0] [:<> :task "backup"]) Produces: WHERE (status = ?) AND (task <> ?) Parameters: 0 "backup" For a single expression, the brackets can be omitted: (where := :status 0) ; same as (where [:= :status 0]) With multiple expressions, the conjunction may be specified as a leading symbol: (where :or [:= :status 0] [:= :task "stop"]) Produces: WHERE (status = 0) OR (task = ?) Parameters: 0 "stop". |
window (clj) | Accepts a window name followed by a partition by clause. |
with (clj) | Accepts one or more CTE definitions. |
with-columns (clj) | Accepts any number of column descriptions. |
with-data (clj) | Accepts a Boolean determining WITH DATA vs WITH NO DATA. |
with-recursive (clj) | Accepts one or more CTE definitions. |
within-group (clj) | Accepts alternating expressions and clauses and produces a WITHIN GROUP expression: (within-group :%count.* (where :> i 5)) Produces: COUNT(*) WITHIN GROUP (WHERE i > ?) Parameters: 5. |