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)

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)

Run a query and return a collection of records.

Depending on the types of arguments provided, all! will have different behaviors.

Arity 1

?table - either a simple-keyword representing a Gungnir which will return all rows of that table. Or it a HoneySQL form query the database using that.

(all :user)

(-> (select :*)
    (from :user)
    (all))

Arity 2

?form - either a HoneySQL form which will extend the query. Or a qualified-keyword representing a model field.

args - a collection of keys and values. Where the keys are qualified-keywords representing a model fields which will be matched with the values. If no key value pairs are provided then you must supply a model name as a simple-keyword.

(all :user/validated false
     :user/type :user/pro)

(-> (where [:> :user/date expiration-date])
    (all :user))

Same as gungnir.query/all but executes the query with the global datasource.

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

(alter-column :name :varchar 64 :not nil)

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

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

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

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

(composite :a 42)

Produces: (a, ?) Parameters: 42

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)

Accepts a single view name to create.

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

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)

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)

Accepts a single view name to create.

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

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

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

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

Delete a row from the database based on record which can either be a namespaced map or relational atom. The row will be deleted based on it’s primary-key. Return true on deletion. If no match is found return false.

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

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

Called with no arguments, produces DO NOTHING

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.

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

Accepts one or more extension names to drop.

Like drop-table, accepts a single index name:

(drop-index :name-key)

Accepts one or more materialied view names to drop.

Accepts one or more table names to drop.

(drop-table :foo)

Accepts one or more view names to drop.

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

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

Accepts a single SQL expression:

(fetch 10)

Produces: FETCH ? ONLY Parameters: 10

Accepts alternating expressions and clauses and produces a FILTER expression:

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

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

Run a query and return a single record or nil.

Depending on the types of arguments provided, find will have different behaviors.

Arity 1

Use form to query the database and return a single record or nil. Will not find a record by it’s primary-key.

form - HoneySQL form which will be used to query the database.

(-> (select :*)
    (from :user)
    (where [:= :user/id user-id])
    (find))

Arity 2

Find a record by it’s primary-key from the table represented by the model-key.

model-key - Model key which will identify which table to read from.

primary-key-value - The value of the primary key to match with.

(find :user user-id)

Arity 3

Find a record by it’s primary-key from the table represented by the model-key. Extended with the HoneySQL form.

form - HoneySQL form which will be used to query the database.

model-key - Model key which will identify which table to read from.

primary-key-value - The value of the primary key to match with.

Find a single record by its primary-key-value from table. Optionally extend the query using a HoneySQL form.

(-> (select :user/email)
    (where [:= :user/active false])
    (find :user user-id))

Same as gungnir.query/find but executes the query with the global datasource.

Run a query and return a single record or nil, based on matching keys and values.

(find-by :user/email "user@test.com"
        :user/validated true)

Optionally extend the queries using HoneySQL

(-> (select :user/username)
    (find-by :user/email "user@test.com"
             :user/validated true))

Same as gungnir.query/find-by but executes the query with the global datasource.

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

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

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

Produces: SELECT * FROM foo AS bar

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

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.

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

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)

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

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 the value of :changeset/result in changeset.

If :changeset/errors is not nil this function will have no side effects. Instead it will return the changeset as is.

If during insertion an error occurs, the changeset will be returned with the errors inserted in the :changeset/errors key.

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

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

Accepts table name, optionally followed a database name.

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

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.

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)

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

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

Load the relations field-keys of record, but retain the structure.

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.

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.

Accepts a single SQL expression:

(offset 10)

Produces: OFFSET ? Parameters: 10

Accepts zero or more SQL entities (keywords or symbols), optionally followed by a single SQL clause (hash map).

Accepts a single constraint name.

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

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

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

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)

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

Accepts a materialied view name to refresh.

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

(rename-column :name :full-name)

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

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.

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

(returning :*) and (returning :a :b)

Produce: RETURNING * and RETURNING a, b respectively.

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

Insert or update the value of :changeset/result in changeset. If no primary-key is present, the record will be inserted, otherwise the existing record will be updated based on the :changeset/diff fields.

If :changeset/errors is not nil this function will have no side effects. Instead it will return the changeset as is.

If during insert / update an error occurs, the changeset will be returned with the errors inserted in the :changeset/errors key.

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.

Like select but produces SELECT DISTINCT.

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

Like select-top but produces SELECT DISTINCT TOP…

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.

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

Accepts a single table name and produces TABLE name

This is equivalent to: SELECT * FROM name

Accepts a single table name to truncate.

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

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

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

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

Insert the value of :changeset/result in changeset.

If :changeset/errors is not nil this function will have no side effects. Instead it will return the changeset as is.

If during the update an error occurs, the changeset will be returned with the errors updated in the :changeset/errors key.

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

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

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”

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”

Accepts a window name followed by a partition by clause.

Accepts one or more CTE definitions.

See the documentation for the :with clause.

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

Accepts a Boolean determining WITH DATA vs WITH NO DATA.

Accepts one or more CTE definitions.

See the documentation for the :with clause.

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