- Notifications
You must be signed in to change notification settings - Fork176
Turn Clojure data structures into SQL
seancorfield/honeysql
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
SQL as Clojure data structures. Build queries programmatically -- even at runtime -- without having to bash strings together.
This project follows the version scheme MAJOR.MINOR.COMMITS where MAJOR and MINOR provide some relative indication of the size of the change, but do not follow semantic versioning. In general, all changes endeavor to be non-breaking (by moving to new names rather than by breaking existing names). COMMITS is an ever-increasing counter of commits since the beginning of this repository.
Note: every commit to thedevelop branch runs CI (GitHub Actions) and successful runs push a MAJOR.MINOR.9999-SNAPSHOT build to Clojars so the very latest version of HoneySQL is always available either via thatsnapshot on Clojars or via a git dependency on the latest SHA.
HoneySQL 2.7.y requires Clojure 1.10.3 or later.Earlier versions of HoneySQL support Clojure 1.9.0.It also supports recent versions of ClojureScript and Babashka.
Compared to thelegacy 1.x version, HoneySQL 2.x provides a streamlined codebase and a simpler method for extending the DSL. It also supports SQL dialects out-of-the-box and will be extended to support vendor-specific language features over time (unlike 1.x).
Note: you can use 1.x and 2.x side-by-side as they use different group IDs and different namespaces. This allows for a piecemeal migration. See thissummary of differences between 1.x and 2.x if you are migrating from 1.x!
John Shaffer has created this awesomeHoneySQL web app, written in ClojureScript,so you can experiment with HoneySQL in a browser, including setting differentoptions so you can generate pretty SQL with inline values (via:inline true)for copying and pasting directly into your SQL tool of choice!
Sample code in this documentation is verified vialread/test-doc-blocks.
Some of these samples show pretty-printed SQL: HoneySQL 2.x supports:pretty true which inserts newlines between clauses in the generated SQL strings.
This section includes a number of usage examples but does not dive deep into theway the data structure acts as a DSL that can specify SQL statements (as hash maps)and SQL expressions and function calls (as vectors). It is recommended that you read theGetting Startedsection of the documentation before trying to use HoneySQL to build your own queries!
From Clojure:
(refer-clojure:exclude '[assert distinct filterfor group-by into partition-by set update])(require '[honey.sql:as sql];; CAUTION: this overwrites several clojure.core fns:;;;; distinct, filter, for, group-by, into, partition-by, set, and update;;;; you should generally only refer in the specific;; helpers that you want to use! '[honey.sql.helpers:refer:all:as h];; so we can still get at clojure.core functions: '[clojure.core:as c])
From ClojureScript, we don't have:refer :all. If we want to use:refer, we have no choice but to be specific:
(refer-clojure:exclude '[filterfor group-by into partition-by set update])(require '[honey.sql:as sql] '[honey.sql.helpers:refer [select select-distinct from join left-join right-join wherefor group-by having union order-by limit offset values columns update insert-into set composite delete delete-from truncate]:as h] '[clojure.core:as c])
Everything is built on top of maps representing SQL queries:
(defsqlmap {:select [:a:b:c]:from [:foo]:where [:=:foo.a"baz"]})
Column names can be provided as keywords or symbols (but not strings -- HoneySQL treats strings as values that should be lifted out of the SQL as parameters).
format turns maps intonext.jdbc-compatible (andclojure.java.jdbc-compatible), parameterized SQL:
(sql/format sqlmap)=> ["SELECT a, b, c FROM foo WHERE foo.a = ?""baz"];; sqlmap as symbols instead of keywords:(-> '{select (a, b, c) from (foo) where (= foo.a"baz")} (sql/format))=> ["SELECT a, b, c FROM foo WHERE foo.a = ?""baz"]
HoneySQL is a relatively "pure" library, it does not manage your JDBC connectionor run queries for you, it simply generates SQL strings. You can then pass themto a JDBC library, such asnext.jdbc:
(jdbc/execute! conn (sql/format sqlmap))
Note: you'll need to add your preferred JDBC library as a dependency in your project -- HoneySQL deliberately does not make that choice for you.
If you want to format the query as a string with no parameters (e.g. to use the SQL statement in a SQL console), pass:inline true as an option tosql/format:
(sql/format sqlmap {:inlinetrue})=> ["SELECT a, b, c FROM foo WHERE foo.a = 'baz'"]
As seen above, the default parameterization uses positional parameters (?) with the order of values in the generated vector matching the order of those placeholders in the SQL. As of 2.4.962, you can specified:numbered true as an option to produce numbered parameters ($1,$2, etc):
(sql/format sqlmap {:numberedtrue})=> ["SELECT a, b, c FROM foo WHERE foo.a = $1""baz"]
Namespace-qualified keywords (and symbols) are generally treated as table-qualified columns::foo/bar becomesfoo.bar, except in contexts where that would be illegal (such as the list of columns in anINSERT statement). This approach is likely to be more compatible with code that uses libraries likenext.jdbc andseql, as well as being more convenient in a world of namespace-qualified keywords, following the example ofclojure.spec etc.
(defq-sqlmap {:select [:foo/a:foo/b:foo/c]:from [:foo]:where [:=:foo/a"baz"]})(sql/format q-sqlmap)=> ["SELECT foo.a, foo.b, foo.c FROM foo WHERE foo.a = ?""baz"];; this also works with symbols instead of keywords:(-> '{select (foo/a, foo/b, foo/c) from (foo) where (= foo/a"baz")} (sql/format))=> ["SELECT foo.a, foo.b, foo.c FROM foo WHERE foo.a = ?""baz"]
As of 2.6.1126, there is a helper macro you can use with quoted symbolicqueries (that are purely literal, not programmatically constructed) toprovide "escape hatches" for certain symbols that you want to be treatedas locally bound symbols (and, hence, their values):
;; quoted symbolic query with local substitution:(let [search-value"baz"] (sql/formatv [search-value] '{select (foo/a, foo/b, foo/c) from (foo) where (= foo/a search-value)}))=> ["SELECT foo.a, foo.b, foo.c FROM foo WHERE foo.a = ?""baz"]
Note: this is a Clojure-only feature and is not available in ClojureScript, and it is intended for literal, inline symbolic queries only, not for programmatically constructed queries (where you would be able to substitute the values directly, as you build the query).
Documentation for the entire data DSL can be found in theClause Reference, theOperator Reference, and theSpecial Syntax reference.
For every single SQL clause supported by HoneySQL (as keywords or symbolsin the data structure that is the DSL), there is also a correspondingfunction in thehoney.sql.helpers namespace:
(-> (select:a:b:c) (from:foo) (where [:=:foo.a"baz"]))=> {:select [:a:b:c]:from [:foo]:where [:=:foo.a"baz"]}
In general,(helper :foo expr) will produce{:helper [:foo expr]}(with a few exceptions -- see the docstring of the helper functionfor details).
Order doesn't matter (for independent clauses):
(= (-> (select:*) (from:foo)) (-> (from:foo) (select:*)))=>true
When using the vanilla helper functions, repeated clauses will be merged into existing clauses, in the natural evaluation order (where that makes sense):
(-> sqlmap (select:d))=> {:from [:foo],:where [:=:foo.a"baz"],:select [:a:b:c:d]}
If you want to replace a clause, you candissoc the existing clause first, since this is all data:
(-> sqlmap (dissoc:select) (select:*) (where [:>:b10]) sql/format)=> ["SELECT * FROM foo WHERE (foo.a = ?) AND (b > ?)""baz"10]
Note: the helpers always produce keywords so you can rely on
dissocwith the desired keyword to remove. If you are building the data DSL "manually" and using symbols instead of keywords, you'll need todissocthe symbol form instead.
where will combine multiple clauses together using SQL'sAND:
(-> (select:*) (from:foo) (where [:=:a1] [:<:b100]) sql/format)=> ["SELECT * FROM foo WHERE (a = ?) AND (b < ?)"1100]
The power of this approach comes from the abiliity to programmatically andconditionally build up queries:
(defnfetch-user [& {:keys [id name]}] (-> (select:*) (from:users) (cond-> id (where [:=:id id]) name (where [:=:name name])) sql/format))
You can callfetch-user with either:id or:nameor both and get backa query with the appropriateWHERE clause, since the helpers will merge theconditions into the query DSL.
Column and table names may be aliased by using a vector pair of the originalname and the desired alias:
(-> (select:a [:b:bar]:c [:d:x]) (from [:foo:quux]) (where [:=:quux.a1] [:<:bar100]) sql/format)=> ["SELECT a, b AS bar, c, d AS x FROM foo AS quux WHERE (quux.a = ?) AND (bar < ?)"1100]
or conditionally:
(-> (select:a [:b:bar]) (cond-> need-c (select:c) x-val (select [:d:x])) (from [:foo:quux]) (where [:=:quux.a1] [:<:bar100]) (cond-> x-val (where [:>:x x-val])) sql/format)
In particular, note that(select [:a :b]) meansSELECT a AS b rather thanSELECT a, b -- helpers likeselect are generally variadic and do not takea collection of column names.
The examples in this README use a mixture of data structures and the helperfunctions interchangably. For any example using the helpers, you could evaluateit (without the call tosql/format) to see what the equivalent data structurewould be.
Documentation for all the helpers can be found in thehoney.sql.helpers API reference.
Inserts are supported in two patterns.In the first pattern, you must explicitly specify the columns to insert,then provide a collection of rows, each a collection of column values:
(-> (insert-into:properties) (columns:name:surname:age) (values [["Jon""Smith"34] ["Andrew""Cooper"12] ["Jane""Daniels"56]]) (sql/format {:prettytrue}))=> ["INSERT INTO properties (name, surname, age)VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)""Jon""Smith"34"Andrew""Cooper"12"Jane""Daniels"56];; or as pure data DSL:(-> {:insert-into [:properties]:columns [:name:surname:age]:values [["Jon""Smith"34] ["Andrew""Cooper"12] ["Jane""Daniels"56]]} (sql/format {:prettytrue}))=> ["INSERT INTO properties (name, surname, age)VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)""Jon""Smith"34"Andrew""Cooper"12"Jane""Daniels"56]
If the rows are of unequal lengths, they will be padded withNULL values to make them consistent.
Alternately, you can simply specify the values as maps:
(-> (insert-into:properties) (values [{:name"John":surname"Smith":age34} {:name"Andrew":surname"Cooper":age12} {:name"Jane":surname"Daniels":age56}]) (sql/format {:prettytrue}))=> ["INSERT INTO properties (name, surname, age)VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)""John""Smith"34"Andrew""Cooper"12"Jane""Daniels"56];; or as pure data DSL:(-> {:insert-into [:properties]:values [{:name"John",:surname"Smith",:age34} {:name"Andrew",:surname"Cooper",:age12} {:name"Jane",:surname"Daniels",:age56}]} (sql/format {:prettytrue}))=> ["INSERT INTO properties (name, surname, age)VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)""John""Smith"34"Andrew""Cooper"12"Jane""Daniels"56]
The set of columns used in the insert will be the union of all column names from allthe hash maps: columns that are missing from any rows will haveNULL as their valueunless you specify those columns in the:values-default-columns option, which takesa set of column names that should get the valueDEFAULT instead ofNULL:
(-> (insert-into:properties) (values [{:name"John":surname"Smith":age34} {:name"Andrew":age12} {:name"Jane":surname"Daniels"}]) (sql/format {:prettytrue}))=> ["INSERT INTO properties (name, surname, age)VALUES (?, ?, ?), (?, NULL, ?), (?, ?, NULL)""John""Smith"34"Andrew"12"Jane""Daniels"](-> (insert-into:properties) (values [{:name"John":surname"Smith":age34} {:name"Andrew":age12} {:name"Jane":surname"Daniels"}]) (sql/format {:prettytrue:values-default-columns #{:age}}))=> ["INSERT INTO properties (name, surname, age)VALUES (?, ?, ?), (?, NULL, ?), (?, ?, DEFAULT)""John""Smith"34"Andrew"12"Jane""Daniels"]
The column values do not have to be literals, they can be nested queries:
(let [user-id12345 role-name"user"] (-> (insert-into:user_profile_to_role) (values [{:user_profile_id user-id:role_id (-> (select:id) (from:role) (where [:=:name role-name]))}]) (sql/format {:prettytrue})))=> ["INSERT INTO user_profile_to_role (user_profile_id, role_id)VALUES (?, (SELECT id FROM role WHERE name = ?))"12345"user"];; or as pure data DSL:(let [user-id12345 role-name"user"] (-> {:insert-into [:user_profile_to_role]:values [{:user_profile_id12345,:role_id {:select [:id],:from [:role],:where [:=:name"user"]}}]} (sql/format {:prettytrue})))=> ["INSERT INTO user_profile_to_role (user_profile_id, role_id)VALUES (?, (SELECT id FROM role WHERE name = ?))"12345"user"]
(-> (select:*) (from:foo) (where [:in:foo.a (-> (select:a) (from:bar))]) (sql/format))=> ["SELECT * FROM foo WHERE foo.a IN (SELECT a FROM bar)"];; or as pure data DSL:(-> {:select [:*],:from [:foo],:where [:in:foo.a {:select [:a],:from [:bar]}]} (sql/format))=> ["SELECT * FROM foo WHERE foo.a IN (SELECT a FROM bar)"]
Because values can be nested queries -- and also because values can be function calls --whenever you are working with values that are, themselves, structured data, you willneed to tell HoneySQL not to interpret that structured data as part of the DSL. Thisespecially affects using JSON values with HoneySQL (e.g., targeting PostgreSQL). Thereare two possible approaches:
- Use named parameters instead of having the values directly in the DSL structure (see
:paramunderMiscellaneous below), or - Use
[:lift ..]wrapped around any structured values which tells HoneySQL not to interpret the vector or hash map value as a DSL.
Composite types are supported:
(-> (insert-into:comp_table) (columns:name:comp_column) (values [["small" (composite1"inch")] ["large" (composite10"feet")]]) (sql/format {:prettytrue}))=> ["INSERT INTO comp_table (name, comp_column)VALUES (?, (?, ?)), (?, (?, ?))""small"1"inch""large"10"feet"];; with numbered parameters:(-> (insert-into:comp_table) (columns:name:comp_column) (values [["small" (composite1"inch")] ["large" (composite10"feet")]]) (sql/format {:prettytrue:numberedtrue}))=> ["INSERT INTO comp_table (name, comp_column)VALUES ($1, ($2, $3)), ($4, ($5, $6))""small"1"inch""large"10"feet"];; or as pure data DSL:(-> {:insert-into [:comp_table],:columns [:name:comp_column],:values [["small" [:composite1"inch"]] ["large" [:composite10"feet"]]]} (sql/format {:prettytrue}))=> ["INSERT INTO comp_table (name, comp_column)VALUES (?, (?, ?)), (?, (?, ?))""small"1"inch""large"10"feet"]
Updates are possible too:
(-> (update:films) (set {:kind"dramatic":watched [:+:watched1]}) (where [:=:kind"drama"]) (sql/format {:prettytrue}))=> ["UPDATE filmsSET kind = ?, watched = watched + ?WHERE kind = ?""dramatic"1"drama"];; or as pure data DSL:(-> {:update:films,:set {:kind"dramatic",:watched [:+:watched1]},:where [:=:kind"drama"]} (sql/format {:prettytrue}))=> ["UPDATE filmsSET kind = ?, watched = watched + ?WHERE kind = ?""dramatic"1"drama"]
If you are trying to build a compound update statement (withfrom orjoin),be aware that different databases have slightly different syntax in terms ofwhereSET should appear. The default above is to putSET beforeFROM whichis how PostgreSQL (and other ANSI-SQL dialects work). If you are using MySQL,you will need to select the:mysql dialect in order to put theSET afteranyJOIN clause.
Deletes look as you would expect:
(-> (delete-from:films) (where [:<>:kind"musical"]) (sql/format))=> ["DELETE FROM films WHERE kind <> ?""musical"];; or as pure data DSL:(-> {:delete-from [:films],:where [:<>:kind"musical"]} (sql/format))=> ["DELETE FROM films WHERE kind <> ?""musical"]
If your database supports it, you can also delete from multiple tables:
(-> (delete [:films:directors]) (from:films) (join:directors [:=:films.director_id:directors.id]) (where [:<>:kind"musical"]) (sql/format {:prettytrue}))=> ["DELETE films, directorsFROM filmsINNER JOIN directors ON films.director_id = directors.idWHERE kind <> ?""musical"];; or pure data DSL:(-> {:delete [:films:directors],:from [:films],:join [:directors [:=:films.director_id:directors.id]],:where [:<>:kind"musical"]} (sql/format {:prettytrue}))=> ["DELETE films, directorsFROM filmsINNER JOIN directors ON films.director_id = directors.idWHERE kind <> ?""musical"]
If you want to delete everything from a table, you can usetruncate:
(-> (truncate:films) (sql/format))=> ["TRUNCATE TABLE films"];; or as pure data DSL:(-> {:truncate:films} (sql/format))=> ["TRUNCATE TABLE films"]
Queries may be combined with a:union,:union-all,:intersect or:except keyword:
(sql/format {:union [(-> (select:*) (from:foo)) (-> (select:*) (from:bar))]})=> ["SELECT * FROM foo UNION SELECT * FROM bar"]
There are also helpers for each of those:
(sql/format (union (-> (select:*) (from:foo)) (-> (select:*) (from:bar))))=> ["SELECT * FROM foo UNION SELECT * FROM bar"]
Note: different databases have different precedence rules for these set operations when used in combination -- you may need to use
:nestto add(..)in order to combine these operations in a single SQL statement, if the natural order produced by HoneySQL does not work "as expected" for your database.
Function calls (and expressions with operators) can be specified asvectors where the first element is either a keyword or a symbol:
(-> (select:*) (from:foo) (where [:>:date_created [:date_add [:now] [:interval24:hours]]]) (sql/format))=> ["SELECT * FROM foo WHERE date_created > DATE_ADD(NOW(), INTERVAL ? HOURS)"24]
Note: The above example may be specific to MySQL but the general principle of vectors for function calls applies to all dialects.
A shorthand syntax also exists for simple function calls:keywords that begin with% are interpreted as SQL function calls:
(-> (select :%count.*) (from:foo) sql/format)=> ["SELECT COUNT(*) FROM foo"]
;; with an alias:(-> (select [:%count.*:total]) (from:foo) sql/format)=> ["SELECT COUNT(*) AS total FROM foo"]
(-> (select :%max.id) (from:foo) sql/format)=> ["SELECT MAX(id) FROM foo"]
Since regular function calls are indicated with vectors and so are aliased pairs,this shorthand can be more convenient due to the extra wrapping needed for theregular function calls in a select:
(-> (select [[:count:*]]) (from:foo) sql/format)=> ["SELECT COUNT(*) FROM foo"]
(-> (select [[:count:*]:total]) (from:foo) sql/format)=> ["SELECT COUNT(*) AS total FROM foo"]
(-> (select [:%count.*]) (from:foo) sql/format)=> ["SELECT COUNT(*) FROM foo"];; or even:(-> (select :%count.*) (from:foo) sql/format)=> ["SELECT COUNT(*) FROM foo"]
(-> (select [[:max:id]]) (from:foo) sql/format)=> ["SELECT MAX(id) FROM foo"](-> (select [[:max:id]:highest]) (from:foo) sql/format)=> ["SELECT MAX(id) AS highest FROM foo"];; the pure data DSL requires an extra level of brackets:(-> {:select [[[:max:id]]],:from [:foo]} sql/format)=> ["SELECT MAX(id) FROM foo"](-> {:select [[[:max:id]:highest]],:from [:foo]} sql/format)=> ["SELECT MAX(id) AS highest FROM foo"];; the shorthand makes this simpler:(-> {:select [[:%max.id]],:from [:foo]} sql/format)=> ["SELECT MAX(id) FROM foo"](-> {:select [[:%max.id:highest]],:from [:foo]} sql/format)=> ["SELECT MAX(id) AS highest FROM foo"];; or even (no alias):(-> {:select [:%max.id],:from [:foo]} sql/format)=> ["SELECT MAX(id) FROM foo"];; or even (no alias, no other columns):(-> {:select :%max.id,:from:foo} sql/format)=> ["SELECT MAX(id) FROM foo"]
Custom columns using functions are built with the same vector format.Be sure to properly nest the vectors so that the first element in the selectionis the custom function and the second is the column alias.
(sql/format {:select [:job_name;; A bare field selection [[:avg [:/ [:-:end_time:start_time]1000.0]];; A custom function:avg_exec_time_seconds;; The column alias ]]:from [:job_data]:group-by:job_name})=> ["SELECT job_name, AVG((end_time - start_time) / ?) AS avg_exec_time_seconds FROM job_data GROUP BY job_name"1000.0]
If a keyword begins with', the function name is formatted as a SQLentity rather than being converted to uppercase and having hyphens-converted to spaces). That means that hyphens- will become underscores_unless you have quoting enabled:
(-> (select:*) (from:foo) (where [:'my-schema.SomeFunction:bar0]) (sql/format))=> ["SELECT * FROM foo WHERE my_schema.SomeFunction(bar, ?)"0](-> (select:*) (from:foo) (where [:'my-schema.SomeFunction:bar0]) (sql/format:quotedtrue))=> ["SELECT * FROM\"foo\" WHERE\"my-schema\".\"SomeFunction\"(\"bar\", ?)"0](-> (select:*) (from:foo) (where [:'my-schema.SomeFunction:bar0]) (sql/format:dialect:mysql))=> ["SELECT * FROM `foo` WHERE `my-schema`.`SomeFunction`(`bar`, ?)"0]
Note: in non-function contexts, if a keyword begins with
', it is transcribed into the SQL exactly as-is, with no case or character conversion at all.
Keywords that begin with? are interpreted as bindable parameters:
(-> (select:id) (from:foo) (where [:=:a:?baz]) (sql/format {:params {:baz"BAZ"}}))=> ["SELECT id FROM foo WHERE a = ?""BAZ"];; or with numbered parameters:(-> (select:id) (from:foo) (where [:=:a:?baz]) (sql/format {:params {:baz"BAZ"}:numberedtrue}))=> ["SELECT id FROM foo WHERE a = $1""BAZ"];; or as pure data DSL:(-> {:select [:id],:from [:foo],:where [:=:a:?baz]} (sql/format {:params {:baz"BAZ"}}))=> ["SELECT id FROM foo WHERE a = ?""BAZ"]
Sometimes you want to provide SQL fragments directly or have certain valuesplaced into the SQL string rather than turned into a parameter.
The:raw syntax lets you embed SQL fragments directly into a HoneySQL expression.It accepts either a single string to embed or a vector of expressions that will beconverted to strings and embedded as a single string.
The:inline syntax attempts to turn a Clojure value into a SQL value and thenembeds that string, e.g.,[:inline "foo"] produces'foo' (a SQL string).
The:param syntax identifies a named parameter whose value will be suppliedvia the:params argument toformat.
The:lift syntax will prevent interpretation of Clojure data structures aspart of the DSL and instead turn such values into parameters (useful when youwant to pass a vector or a hash map directly as a positional parameter value,for example when you have extendednext.jdbc'sSettableParameter protocolto a data structure -- as is common when working with PostgreSQL's JSON/JSONB types).
Finally, the:nest syntax will cause an extra set of parentheses to bewrapped around its argument, after formatting that argument as a SQL expression.
These can be combined to allow more fine-grained control over SQL generation:
(defcall-qualify-map (-> (select [[:foo:bar]] [[:raw"@var := foo.bar"]]) (from:foo) (where [:=:a [:param:baz]] [:=:b [:inline42]])))
call-qualify-map=> {:where [:and [:=:a [:param:baz]] [:=:b [:inline42]]]:from (:foo):select [[[:foo:bar]] [[:raw"@var := foo.bar"]]]}(sql/format call-qualify-map {:params {:baz"BAZ"}})=> ["SELECT FOO(bar), @var := foo.bar FROM foo WHERE (a = ?) AND (b = 42)""BAZ"]
(-> (select:*) (from:foo) (where [:<:expired_at [:raw ["now() - '"5" seconds'"]]]) (sql/format))=> ["SELECT * FROM foo WHERE expired_at < now() - '5 seconds'"]
(-> (select:*) (from:foo) (where [:<:expired_at [:raw ["now() - '" [:lift5]" seconds'"]]]) (sql/format))=> ["SELECT * FROM foo WHERE expired_at < now() - '? seconds'"5]
(-> (select:*) (from:foo) (where [:<:expired_at [:raw ["now() - '" [:param:t]" seconds'"]]]) (sql/format {:params {:t5}}))=> ["SELECT * FROM foo WHERE expired_at < now() - '? seconds'"5]
(-> (select:*) (from:foo) (where [:<:expired_at [:raw ["now() -" [:inline (str5" seconds")]]]]) (sql/format))=> ["SELECT * FROM foo WHERE expired_at < now() - '5 seconds'"]
A common example in the wild is the PostGIS extension to PostgreSQL where youhave a lot of function calls needed in code:
(-> (insert-into:sample) (values [{:location [:ST_SetSRID [:ST_MakePoint0.29132.621] [:cast4325:integer]]}]) (sql/format {:prettytrue}))=> ["INSERT INTO sample (location)VALUES (ST_SETSRID(ST_MAKEPOINT(?, ?), CAST(? AS INTEGER)))"0.29132.6214325]
To quote SQL entity names, pass the:quoted true option toformat and they willbe quoted according to the selected dialect. If you override the dialect in aformat call, by passing the:dialect option, SQL entity names will be automaticallyquoted. You can override the dialect and turn off quoting by passing:quoted false.Valid:dialect options are:ansi (the default, use this for PostgreSQL),:mysql,:oracle, or:sqlserver. As of 2.5.1091,:nrql is also supported:
(-> (select:foo.a) (from:foo) (where [:=:foo.a"baz"]) (sql/format {:dialect:mysql}))=> ["SELECT `foo`.`a` FROM `foo` WHERE `foo`.`a` = ?""baz"]
(-> (select:foo.a) (from:foo) (where [:=:foo.a"baz"]) (sql/format {:dialect:nrql}))=> ["SELECT `foo.a` FROM foo WHERE `foo.a` = 'baz'"]
SeeNew Relic NRQL Support for more details of the NRQL dialect.
The ANSI/PostgreSQL/SQLServer dialects support locking selects via aFOR clause as follows:
:for [<lock-strength> <table(s)> <qualifier>]where<lock-strength>is required and may be one of::update:no-key-update:share:key-share
- Both
<table(s)>and<qualifier>are optional but if present,<table(s)>must either be:- a single table name (as a keyword) or
- a sequence of table names (as keywords)
<qualifier>can be:nowait,:wait,:skip-lockedetc.
If<table(s)> and<qualifier> are both omitted, you may also omit the[..] and just say:for :update etc.
(-> (select:foo.a) (from:foo) (where [:=:foo.a"baz"]) (for:update) (sql/format))=> ["SELECT foo.a FROM foo WHERE foo.a = ? FOR UPDATE""baz"]
If the:mysql dialect is selected, an additional locking clause is available::lock :in-share-mode.
(sql/format {:select [:*]:from:foo:where [:=:name [:inline"Jones"]]:lock [:in-share-mode]} {:dialect:mysql:quotedfalse})=> ["SELECT * FROM foo WHERE name = 'Jones' LOCK IN SHARE MODE"]
Dashes are allowed in quoted names:
(sql/format {:select [:f.foo-id:f.foo-name]:from [[:foo-bar:f]]:where [:=:f.foo-id12345]} {:quotedtrue})=> ["SELECT\"f\".\"foo-id\",\"f\".\"foo-name\" FROM\"foo-bar\" AS\"f\" WHERE\"f\".\"foo-id\" = ?"12345]
Here's a big, complicated query. Note that HoneySQL makes no attempt to verify that your queries make any sense. It merely renders surface syntax.
(defbig-complicated-map (-> (select-distinct:f.*:b.baz:c.quux [:b.bla"bla-bla"] [[:now]] [[:raw"@x := 10"]]) (from [:foo:f] [:baz:b]) (join:draq [:=:f.b:draq.x]:eldr [:=:f.e:eldr.t]) (left-join [:clod:c] [:=:f.a:c.d]) (right-join:bock [:=:bock.z:c.e]) (where [:or [:and [:=:f.a"bort"] [:not=:b.baz [:param:param1]]] [:and [:<12] [:<23]] [:in:f.e [1 [:param:param2]3]] [:between:f.e1020]]) (group-by:f.a:c.e) (having [:<0:f.e]) (order-by [:b.baz:desc]:c.quux [:f.a:nulls-first]) (limit50) (offset10)))
big-complicated-map=> {:select-distinct [:f.*:b.baz:c.quux [:b.bla"bla-bla"] [[:now]] [[:raw"@x := 10"]]]:from [[:foo:f] [:baz:b]]:join [:draq [:=:f.b:draq.x]:eldr [:=:f.e:eldr.t]]:left-join [[:clod:c] [:=:f.a:c.d]]:right-join [:bock [:=:bock.z:c.e]]:where [:or [:and [:=:f.a"bort"] [:not=:b.baz [:param:param1]]] [:and [:<12] [:<23]] [:in:f.e [1 [:param:param2]3]] [:between:f.e1020]]:group-by [:f.a:c.e]:having [:<0:f.e]:order-by [[:b.baz:desc]:c.quux [:f.a:nulls-first]]:limit50:offset10}(sql/format big-complicated-map {:params {:param1"gabba":param22}:prettytrue})=> ["SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS\"bla-bla\", NOW(), @x := 10FROM foo AS f, baz AS bINNER JOIN draq ON f.b = draq.x INNER JOIN eldr ON f.e = eldr.tLEFT JOIN clod AS c ON f.a = c.dRIGHT JOIN bock ON bock.z = c.eWHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ?GROUP BY f.a, c.eHAVING ? < f.eORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRSTLIMIT ?OFFSET ?""bort""gabba"1223123102005010];; with numbered parameters:(sql/format big-complicated-map {:params {:param1"gabba":param22}:prettytrue:numberedtrue})=> ["SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS\"bla-bla\", NOW(), @x := 10FROM foo AS f, baz AS bINNER JOIN draq ON f.b = draq.x INNER JOIN eldr ON f.e = eldr.tLEFT JOIN clod AS c ON f.a = c.dRIGHT JOIN bock ON bock.z = c.eWHERE ((f.a = $1) AND (b.baz <> $2)) OR (($3 < $4) AND ($5 < $6)) OR (f.e IN ($7, $8, $9)) OR f.e BETWEEN $10 AND $11GROUP BY f.a, c.eHAVING $12 < f.eORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRSTLIMIT $13OFFSET $14""bort""gabba"1223123102005010]
;; Printable and readable(require '[clojure.edn:as edn])(= big-complicated-map (edn/read-string (pr-str big-complicated-map)))=>true
Any keyword (or symbol) that appears as the first element of a vector will be treated as a generic function unless it is declared to be an operator or "special syntax". Any keyword (or symbol) that appears as a key in a hash map will be treated as a SQL clause -- and must either be built-in or must be registered as a new clause.
If your database supports<=> as an operator, you can tell HoneySQL about it using theregister-op! function (which should be called before the first call tohoney.sql/format):
(sql/register-op!:<=>);; all operators are assumed to be variadic:(-> (select:a) (where [:<=>:a"foo"]) sql/format)=> ["SELECT a WHERE a <=> ?""foo"](-> (select:a) (where [:<=>"food":a"fool"]) sql/format)=> ["SELECT a WHERE ? <=> a <=> ?""food""fool"]
Sometimes you want an operator to ignorenil clauses (:and and:or are declared that way):
(sql/register-op!:<=>:ignore-niltrue)
Or perhaps your database supports syntax likea BETWIXT b AND c, in which case you can useregister-fn! to tell HoneySQL about it (again, called before the first call tohoney.sql/format):
;; the formatter will be passed your new operator (function) and a;; sequence of the arguments provided to it (so you can write any arity ops):(sql/register-fn!:betwixt (fn [op [a b c]] (let [[sql-a & params-a] (sql/format-expr a) [sql-b & params-b] (sql/format-expr b) [sql-c & params-c] (sql/format-expr c)] (-> [(str sql-a"" (sql/sql-kw op)"" sql-b" AND" sql-c)] (c/into params-a) (c/into params-b) (c/into params-c)))));; example usage:(-> (select:a) (where [:betwixt:a110]) sql/format)=> ["SELECT a WHERE a BETWIXT ? AND ?"110];; with numbered parameters:(-> (select:a) (where [:betwixt:a110]) (sql/format {:numberedtrue}))=> ["SELECT a WHERE a BETWIXT $1 AND $2"110]
Note: the generation of positional placeholders (
?) or numbered placeholders ($1,$2, etc) is handled automatically byformat-exprso you get this behavior "for free" in your extensions, as long as you use the public API forhoney.sql. You should avoid writing extensions that generate placeholders directly if you want them to work with numbered parameters.
You can also register SQL clauses, specifying the keyword, the formatting function, and an existing clause that this new clause should be processed before:
;; the formatter will be passed your new clause and the value associated;; with that clause in the DSL (which is often a sequence but does not;; need to be -- it can be whatever syntax you desire in the DSL):(sql/register-clause!:foobar (fn [clause x] (let [[sql & params] (if (ident? x) (sql/format-expr x) (sql/format-dsl x))] (c/into [(str (sql/sql-kw clause)"" sql)] params))):from); SELECT ... FOOBAR ... FROM ...;; example usage:(sql/format {:select [:a:b]:foobar:baz})=> ["SELECT a, b FOOBAR baz"](sql/format {:select [:a:b]:foobar {:where [:=:id1]}})=> ["SELECT a, b FOOBAR WHERE id = ?"1]
If you find yourself registering an operator, a function (syntax), or a new clause, consider submitting apull request to HoneySQL so others can use it, too. If it is dialect-specific, let me know in the pull request.
## HoneySQL 1.x (legacy)
HoneySQL 1.x will continue to get critical security fixes but otherwise should be considered "legacy" at this point.
Copyright (c) 2020-2024 Sean Corfield. HoneySQL 1.x was copyright (c) 2012-2020 Justin Kramer and Sean Corfield.
Distributed under the Eclipse Public License, the same as Clojure.
About
Turn Clojure data structures into SQL