repository·develop·Indexed 23 days ago
https://github.com/seancorfield/honeysqlA library for building SQL queries using Clojure data structures instead of string manipulation. It allows for programmatic query construction at runtime and supports multiple SQL dialects (including MySQL, Oracle, SQL Server, and NRQL) and runtimes such as Clojure, ClojureScript, Babashka, Jolt, and let-go. Features include a helper DSL via honey.sql.helpers, support for parameterized SQL strings via sql/format, and extensibility through register-op!, register-fn!, and register-clause!.
HoneySQL is compatible with:
It also provides basic support for:
honey.sql/formatv is omitted because clojure.template is not provided in let-go.io.github.jolt-lang/time and Jolt version 0.5.1 or higher.HoneySQL treats keywords and symbols as SQL entities. Their formatting depends on whether quoting is enabled (via :dialect or :quoted true) and how they are structured:
-) are converted to underscores (_)..): A symbol containing a dot is split into a table/alias name and a column name. Dots are respected during quoting./): A symbol containing a slash is treated as a qualified name (namespace/name). In the namespace portion, dashes (-) are converted to underscores (_) even when quoting is enabled.(require '[honey.sql :as sql])
;; Quoting enabled
(sql/format {:select :foo-bar} {:quoted true})
;;=> ["SELECT \"foo-bar\""]
;; Quoting disabled (dashes become underscores)
(sql/format {:select :foo-bar})
;;=> ["SELECT foo_bar"]
;; Dot notation (table.column)
(sql/format {:select :foo-bar.baz-quux} {:quoted true})
;;=> ["SELECT \"foo-bar\".\"baz-quux\""]
;; Slash notation (namespace/name)
(sql/format {:select :foo-bar/baz-quux} {:quoted true})
;;=> ["SELECT \"foo_bar\".\"baz-quux\""]
;; Strings as entities (no dash conversion, no slash splitting)
(sql/format {:update :table :set {"foo-bar" 1 "baz/quux" 2}})
;;=> ["UPDATE table SET \"foo-bar\" = ?, \"baz/quux\" = ?" 1 2]HoneySQL follows ANSI SQL precedence by default. Because different databases (like MySQL or SQLite) may have different precedence rules for SET clauses or set operations (UNION, EXCEPT, INTERSECT), you may need to force specific grouping.
You can use the :nest pseudo-clause in the DSL to wrap the generated SQL in parentheses ( ... ) to ensure the intended precedence is respected by the target database.
{:nest DSL}
;; will produce DSL wrapped in ( .. )HoneySQL supports New Relic's NRQL query language by using the :nrql dialect. When this dialect is selected, HoneySQL automatically sets :inline true for formatting, ensuring the generated SQL string is compatible with NRQL requirements.
Additionally, identifier stropping (quoting) uses backticks (similar to MySQL), but unlike MySQL, it does not split entities at / or . characters. For example, :foo/bar.baz will be rendered as `foo/bar.baz`.
(sql/format {:select [:mulog/timestamp :mulog/event-name]
:from :Log
:where [:= :mulog/data.account "foo-account-id"]
:since [2 :days :ago]
:limit 2000}
{:dialect :nrql :pretty true})HoneySQL 2.x features a streamlined codebase, a simpler DSL extension method, and out-of-the-box support for SQL dialects.
Because 1.x and 2.x use different group IDs and namespaces, you can use them side-by-side in the same project to facilitate a piecemeal migration. For a detailed comparison, refer to the doc/differences-from-1-x.md file in the repository.
Providing an atom containing a clojure.core.cache structure to the :cache option allows format to cache generated SQL strings based on the DSL data structure. This improves performance for repeated queries.
Important: When using :cache, you should use named parameters (starting with ?) instead of regular values to ensure the cache lookup works correctly.
Limitation: You cannot use named parameters with the :in clause when using :cache because :in unrolls the parameter, breaking cache lookup rules.
In HoneySQL 2.x, vectors and sequences are treated as function calls. To represent SQL composite values (tuples like (col1, col2)), use the :composite special syntax.
Alternatively, you can use the composite helper function.
(sql/format-expr [:composite :col1 :col2])
;;=> ["(col1, col2)"]
(sql/format-expr [:composite 13 42 "foo"])
;;=> ["(?, ?, ?)" 13 42 "foo"]
;; Using symbols
(sql/format-expr '(composite col1 col2))
;;=> ["(col1, col2)"]HoneySQL supports advanced SQL features for aggregations and window functions:
filter and within-group.:window, :partition-by, and :over is built-in.The :inline option (Boolean) suppresses the generation of ? placeholders and instead embeds values directly into the SQL string.
Warning: Using :inline true can introduce security vulnerabilities (SQL injection) because it bypasses parameterized statements. Use it only for trusted data or specific non-parameterized needs.
Inlining behavior:
nil $\rightarrow$ NULL'foo')- replaced by space)(str value)XTDB uses NEST_ONE and NEST_MANY to produce structured results from sub-queries. In HoneySQL, these are implemented as regular function calls within the :select clause.
Note: Function calls in :select clauses require three levels of nesting (parentheses/brackets) to ensure correct formatting: :select [:col-a [:col-b :alias-b] [[:fn-call :col-c] :alias-c]].
(sql/format '{select (a.* ((nest_many {select * from bar where (= foo_id a._id)}) b))
from ((foo a))})
;; => ["SELECT a.*, NEST_MANY (SELECT * FROM bar WHERE foo_id = a._id) AS b FROM foo AS a"]HoneySQL supports and and or as boolean operators. They can take any number of arguments.
Key Feature: Nil-safety
nil expressions are ignored within and/or blocks. This allows you to programmatically build queries by having conditional logic evaluate to nil when a clause should be omitted, rather than having to manually restructure the vector.
Operators can be specified as keywords or symbols. Use - in the operator name where the formatted SQL would have a space (e.g., :not-like becomes NOT LIKE).