HugSQL Documentation

HugSQL is a Clojure library designed to bridge the gap between Clojure and SQL by using simple conventions within SQL files to define database functions in your Clojure namespaces. This approach maintains a clean separation between your application logic (Clojure) and your data access logic (SQL).

Key capabilities include:

• Runtime Parameter Replacement: Dynamically inject values, identifiers, lists, and tuples into your SQL.
• Composability: Use Clojure expressions and SQL snippets to build complex queries.
• Database Adapters: Supports multiple database libraries via protocol-based adapters, including clojure.java.jdbc (default), next.jdbc, and clojure.jdbc.

HugSQL protects against SQL injection by using standard SQL parameter binding for value-based parameters. When you use these parameter types, HugSQL converts Clojure data types to SQL and defers the actual binding to your underlying database library. This is the safest way to handle user-provided data.

Supported value parameter types include:

• Value Parameters
• Value List Parameters
• Tuple Parameters
• Tuple List Parameters

HugSQL uses special single-line and multi-line comments within .sql files to define the metadata for the Clojure functions it generates. These comments must follow specific syntax patterns so the HugSQL parser can identify them.

Single-line syntax

Use a double dash followed by a colon and the key: -- :key value

Multi-line syntax

Use a C-style comment block containing the key and values:

/* :key
 value
 */

Standard SQL comments that do not follow the :key pattern are ignored by HugSQL.

-- :name query-get-many :? :*
-- :doc My query doc string to end of this line
select * from my_table;

-- :name query-get-one :? :1
/* :doc
My multi-line
comment doc string
*/
select * from my_table limit 1

HugSQL allows you to use Clojure expressions to conditionally compose portions of your SQL statements at runtime. These expressions are written inside SQL comments, maintaining a SQL-first workflow.

Expressions are compiled after the initial parse and must return either a String or nil. The returned string can include HugSQL-specific parameter syntax (like :i:, :v:, etc.).

At runtime, two symbols are available within your expressions:

• params: A hashmap containing your parameter data.
• options: A hashmap containing configuration options.

-- :name clj-expr-single :? :1
select
--~ (if (seq (:cols params)) ":i*:cols" "*")
from test
order by id

HugSQL and HoneySQL are not mutually exclusive and can be used together to combine SQL-first development with Clojure-based SQL generation. You can use HoneySQL to generate a sqlvec format output and then pass that output into HugSQL snippets.

To achieve this, use HugSQL Snippet Parameter Types :snip or :snip*, which are designed to consume the sqlvec format produced by HoneySQL's format function.

;; Conceptual workflow:
;; 1. Use HoneySQL to generate sqlvec
(def query-vec (honey.sql/format {:select [:id :name] :from [:users]}))

;; 2. Pass the result into a HugSQL snippet using :snip or :snip*
(hugsql/execute-snippet my-snippet :snip query-vec)

Raw SQL Parameters allow you to perform un-quoted, direct text replacement in your SQL statements. Unlike standard parameters that are treated as values, :sql parameters allow you to parameterize SQL keywords, such as ASC or DESC in an ORDER BY clause, or to compose multiple statements into one.

Security Warning: Because these parameters perform direct text replacement without quoting, they are highly susceptible to SQL injection. Always validate and sanitize any user-provided input before passing it to a :sql parameter.

--:name sql-keyword-param :? :*
select * from example
order by last_name :sql:last_name_sort

Tuple Parameters allow you to pass a list of values that are enclosed in parentheses and joined by commas. They are useful for multi-column comparisons, such as (col1, col2) = :tuple:name.

Unlike Value List Parameters (which expect a list of the same data type), Tuple Parameters can contain values of different data types. Each element in the provided list is treated as an individual Value Parameter.

Supported Databases:

• PostgreSQL
• MySQL
• H2

Unsupported Databases:

• Derby
• HSQLDB
• SQLite

-- :name tuple-param
-- :doc Tuple Param
select * from test
where (id, name) = :tuple:id-name

(tuple-param-sqlvec {:id-name [1 "A"]})
;=> ["select * from test\nwhere (id, name) = (?,?)" 1 "A"]