honey.sql.helpers — com.github.seancorfield/honeysql 2.6.1230

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

or conditionally like this:

  (-> (select :a :b :c)
      (from :table)
      (cond->
        id (where [:= :id id]))
      (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).

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 & index-elems)

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/s

(alter-column & col-elems)

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/s

(alter-table table & clauses)

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

(bulk-collect-into varname n)

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

compare-with clj/s

(compare-with & args)

Accepts a time interval such as:

(compare-with 1 :week :ago)

Produces: COMPARE WITH 1 WEEK AGO

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

(create-extension extension)

(create-extension extension if-not-exists)

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/s

(create-index & args)

Accepts an index spexification and a column specification. The column specification consists of table name and one or more columns.

(create-index :name-of-idx [:table :col]) (create-index :name-of-idx [:table :col1 :col2]) (create-index [:unique :name-of-idx] [:table :col])

PostgreSQL also supports :if-not-exists and expressions instead of columns.

(create-index [:name-of-idx :if-not-exists] [:table :%lower.col])

create-materialized-view clj/s

(create-materialized-view view)

Accepts a single view name to create.

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

create-or-replace-view clj/s

(create-or-replace-view view)

Accepts a single view name to create.

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

create-table clj/s

(create-table table)

(create-table table if-not-exists)

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

(create-table-as table)

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

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

(create-view view)

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

(delete table-coll)

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

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

delete-from clj/s

(delete-from table)

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

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

distinct clj/s

(distinct & args)

Like select-distinct but produces DISTINCT...

do-nothing clj/s

(do-nothing)

Called with no arguments, produces DO NOTHING

do-update-set clj/s

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

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

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

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

(drop-column col)

Takes one or more column names (use with alter-table).

Accepts an IF EXISTS flag (keyword or symbol) before any column names.

(alter-table :foo (drop-column :bar :if-exists :quux))

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.

erase-from clj/s

(erase-from table)

For erasing (hard delete) from a single table (XTDB). Accepts a single table name to erase from.

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

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.

exclude clj/s

(exclude & args)

Accepts one or more column names to exclude from a select list.

expr clj/s

(expr & args)

Like distinct but produces ... (i.e., just the expression that follows).

facet clj/s

(facet & args)

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/s

(fetch limit)

Accepts a single SQL expression:

(fetch 10)

Produces: FETCH ? ONLY Parameters: 10

filter clj/s

(filter expr1 clause1 & more)

Accepts alternating expressions and clauses and produces a FILTER expression:

(filter :%count.* (where :> i 5))

Produces: COUNT(*) FILTER (WHERE i > ?) Parameters: 5

for clj/s

(for lock-strength table* qualifier*)

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: FULL JOIN table ON foo.id = table.foo_id FULL JOIN table AS t ON foo.id = t.foo_id

generic-helper-unary clj/s

(generic-helper-unary k args)

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/s

(generic-helper-variadic k args)

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/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 (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/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

(insert-into table)

(insert-into table cols)

(insert-into table statement)

(insert-into table cols statement)

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

(into table dbname)

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

(lateral clause-or-expression)

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

(limit limit)

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

(lock lock-mode)

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

MySQL-specific, deprecated. Use alter-column and specify the MySQL dialect to get MODIFY COLUMN.

offset clj/s

(offset offset)

Accepts a single SQL expression:

(offset 10)

Produces: OFFSET ? Parameters: 10

on-conflict clj/s

(on-conflict column* where-clause)

Accepts zero or more SQL entities (keywords or symbols), optionally followed by a single SQL clause ({:where <condition>}). Ex.: (on-conflict :mom :dad {:where [:= :race "human"]}

on-constraint clj/s

(on-constraint constraint)

Accepts a single constraint name.

on-duplicate-key-update clj/s

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

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.

records clj/s

(records & args)

Produces RECORDS {...}, {...}, ...

refresh-materialized-view clj/s

(refresh-materialized-view view)

Accepts a materialied view name to refresh.

rename clj/s

(rename & args)

Accepts one or more column names with aliases to rename in a select list.

rename-column clj/s

(rename-column old-col new-col)

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

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/s

(replace-into table)

(replace-into table cols)

(replace-into table statement)

(replace-into table cols statement)

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.

The arguments are identical to insert-into. The REPLACE INTO statement is only supported by MySQL and SQLite.

returning clj/s

(returning & cols)

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

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

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

(set col-set-map)

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/s

(since & args)

Accepts a time interval such as:

(since 2 :days :ago)

Produces: SINCE 2 DAYS AGO

table clj/s

(table name)

Accepts a single table name and produces TABLE name

This is equivalent to: SELECT * FROM name

timeseries clj/s

(timeseries & args)

Accepts a time interval such as:

(timeseries 1 :day)

or:

(timeseries :auto)

Produces: TIMESERIES 1 DAY Or: TIMESERIES AUTO

truncate clj/s

(truncate table)

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.

until clj/s

(until & args)

Accepts a time interval such as:

(until 1 :month :ago)

Produces: UNTIL 1 MONTH AGO

update clj/s

(update table)

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

(values row-value-coll)

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 (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/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

(with-columns col-spec-coll)

(with-columns & col-specs)

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

(with-data data?)

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.

within-group clj/s

(within-group expr1 clause1 & more)

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