Honey SQL

SQL as Clojure data structures. Build queries programmatically -- even at runtime -- without having to bash strings together.

Build

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 the develop 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 that snapshot 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 the legacy 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 this summary of differences between 1.x and 2.x if you are migrating from 1.x!

Try HoneySQL Online!

John Shaffer has created this awesome HoneySQL web app, written in ClojureScript, so you can experiment with HoneySQL in a browser, including setting different options so you can generate pretty SQL with inline values (via :inline true) for copying and pasting directly into your SQL tool of choice!

Note on code samples

Sample code in this documentation is verified via lread/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.

Usage

This section includes a number of usage examples but does not dive deep into the way 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 the Getting Started section of the documentation before trying to use HoneySQL to build your own queries!

From Clojure:

(refer-clojure :exclude '[assert distinct filter for 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 '[filter for 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
                                     where for 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:

(def sqlmap {: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`

format turns maps into next.jdbc-compatible (and clojure.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 connection or run queries for you, it simply generates SQL strings. You can then pass them to a JDBC library, such as next.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 to sql/format:

(sql/format sqlmap {:inline true})
=> ["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 {:numbered true})
=> ["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 becomes foo.bar, except in contexts where that would be illegal (such as the list of columns in an INSERT statement). This approach is likely to be more compatible with code that uses libraries like next.jdbc and seql, as well as being more convenient in a world of namespace-qualified keywords, following the example of clojure.spec etc.

(def q-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 symbolic queries (that are purely literal, not programmatically constructed) to provide "escape hatches" for certain symbols that you want to be treated as locally bound symbols (and, hence, their values):