A Clojure library for embracing SQL.
- HugSQL is of the opinion that SQL is the right tool for the job when working with a relational database.
- HugSQL uses simple conventions in your SQL files to define (at compile time) database functions in your Clojure namespace, creating a clean separation of Clojure and SQL code.
- HugSQL supports runtime replacement
of SQL Value Parameters
where id = :id), SQL Identifiers (i.e. table/column names), and SQL Keywords. You can also implement your own parameter types.
- HugSQL features Clojure Expressions and Snippets providing the full expressiveness of Clojure and the composability of partial SQL statements when constructing complex SQL queries.
- HugSQL has protocol-based adapters supporting multiple database libraries and ships with adapters for clojure.java.jdbc (default) and clojure.jdbc
Leiningen dependency information:
You will also need to specify your JDBC driver dependency from one of the following:
For example, the Postgresql driver:
HugSQL defaults to using
clojure.java.jdbc library to run underlying
database commands. If you would prefer to use another
underlying database library instead
clojure.jdbc, please see HugSQL Adapters
This documentation make use
Princess Bride example application in the HugSQL
repository. Feel free to view or clone the repo and run the
Start with SQL
HugSQL provides a clean separation of SQL and Clojure code. You can start developing with HugSQL by deciding where to put your SQL files in your application.
HugSQL can find any SQL file in your classpath. You can
place your SQL files under
src, or elsewhere in your classpath.
Our example application puts SQL files under
.../examples/princess-bride $ tree . ├── LICENSE ├── project.clj ├── README.md ├── resources ├── src │ └── princess_bride │ ├── core.clj │ ├── db │ │ ├── characters.clj │ │ ├── quotes.clj │ │ └── sql │ │ ├── characters.sql │ │ └── quotes.sql │ └── db.clj
Our SQL for The Princess Bride characters is as follows:
HugSQL uses special SQL comments to accomplish its work. These conventions are explained later in this document. Keep reading!
Now Some Clojure
Now we write a bit of Clojure to define our database functions.
princess-bride.db.characters namespace now
has several functions defined based on the SQL statements in the
SQL file. Here's an example of the sqlvec output and a sample
run of the
You've only scratched the surface of HugSQL's functionality. Keep reading for full usage examples.
This section provides practical usage examples with HugSQL. Most examples come from The Princess Bride example application in the HugSQL repository.
hugsql.core/def-db-fns is a macro that creates
functions in your Clojure namespace based on the SQL queries and
statements in your HugSQL-flavored SQL file.
The functions defined by
def-db-fns have the following arities:
dbis a db-spec, a connection, a connection pool, or a transaction object
param-datais a hashmap of parameter data where the keys match parameter placeholder names in your SQL
optionsis a hashmap of HugSQL-specific options (e.g.,
& command-optionsis a variable number of options to be passed down into the underlying adapter and database library functions. See Advanced Usage for more detail.
HugSQL generates a format internally known
as sqlvec. The sqlvec format is a vector with an
SQL string in the first position containing any
placeholders, followed by any number of parameter values to be
applied to the SQL in positional order. For example:
The sqlvec format is a convention used
clojure.jdbc for value parameter replacement.
Because of the underlying support in these libraries and the
JDBC-driver-specific issues for data type handling, HugSQL
also uses the sqlvec format by default for value
HugSQL provides the
macro to create functions returning the sqlvec format.
The created functions have an
-sqlvec suffix by
default, though this is configurable with
:fn-suffix option. These functions are
helpful during development/debugging and for the purpose of
using the parameter-replacing functionality of HugSQL without
using the built-in adapter database functions to execute
Other Useful Functions
HugSQL has several other useful functions that take HugSQL-flavored SQL and can be used in other contexts (like the REPL). See the API docs ➚ for more information on the following:
Retrieving Last Inserted ID or Record
It is often the case that you want to return the record just inserted or at least the auto-generated ID. This functionality varies greatly across databases and JDBC drivers. HugSQL attempts to help where it can. You will need to choose an option that fits your database.
OPTION #1: INSERT ... RETURNING
If your database supports the RETURNING clause of INSERT
supports this), you can specify your SQL insert statement
command type to be
:<! for short:
OPTION #2: Get Generated Keys / Last Insert ID / Inserted Record
type indicates to the underlying adapter that the insert
should be performed, and then
called in the jdbc driver. The return value
.getGeneratedKeys varies greatly across
different databases and jdbc drivers. For example, see the
following code from the HugSQL test suite:
The first argument of a function generated
def-db-fns can take a database spec, a
connection, a connection pool, or a transaction object. Use your
underlying database library to get a transaction object. Here's
Clojure Expressions give HugSQL SQL statements the full power of Clojure to conditionally compose portions of SQL during runtime.
Expressions are written within SQL comments (in keeping with the SQL-first workflow).
Expressions should return a string or nil. The string returned may contain HugSQL-specific parameter syntax.
Expressions have two bound symbols available at
params, which is the hashmap of parameter
options, which is the hashmap of
A single-line comment expression starts
--~. Notice the tilde
entire Clojure expression is expected to be in a single-line
A multi-line comment expression can have interspersed
SQL. The expression starts with
"continuing" parts also start with
/*~, and the
expression ends with
~*/. When an expression needs
to represent advancing to the "next" Clojure form (like
if below), an empty separator
/*~*/ is necessary:
Expressions are defined and compiled just after the initial file/string parse.
Expressions needing to access Clojure namespaces other than
the included clojure.core can specify a
similar to usage in
Snippets allow query composition by defining and generating portions of SQL statements and then using the Snippet Parameter Types to place these snippets into a full SQL statement.
You can define a snippet in your SQL file with
-- :snip my-snippet (as opposed to
Snippets can contain snippet
:snip*:cond is a Snippet List
Parameter Type, which specifies that
:cond is a
list/vector of snippets
Snippets can get very elaborate (creating an entire DSL) if
you want. The
cond-snip snippet below uses
deep-get parameter name
access to construct a where clause condition:
Using the above snippets, we can now construct the full query. (With an optional where clause via a Clojure expression)
It's worth noting that a snippet returns an sqlvec. This small detail gives you a great deal of flexibility in providing snippets to your HugSQL queries. Why? Because you don't necessarily need to create your own snippet DSL: you could use another library for this. It is the best of both worlds! This exercise is left to the reader.
Each underlying database library and corresponding HugSQL
adapter may support additional options for the execute/query
commands. Functions defined by
def-db-fns have a
variable-arity 4th argument that passes any options through to
the underlying database library.
Below is an assertion from the HugSQL test suite showing a
query passing along the
clojure.java.jdbc/query. Please note the
required 3rd argument (the HugSQL-specific options) when using
this passthrough feature:
Please note that as
0.5.8 and HugSQL
0.4.7, the above additional options are now required to be a
hashmap instead of keyword arguments as in previous versions. In
clojure.java.jdbc 0.5.8 the deprecated usage will emit a
warning. In clojure.java.jdbc 0.6.0 the usage is deprecated and
not allowed. See
changelog for details.
HugSQL in Detail
HugSQL encourages SQL, DDL, and DML statements to be stored in SQL files such that you are not concatenating large strings or needing to use leaky-abstraction DSLs in your Clojure projects.
In order to generate the Clojure functions from your SQL statements, HugSQL requires a simple set of conventions in your SQL files. These conventions allow HugSQL to:
- define functions by name
- add docstrings to defined functions
- determine how to execute (the command type):
- SQL select
- DDL create table/index/view, drop ...
- DML insert, update, delete
- any other statements (e.g.
- determine the result type:
- one row (hash-map)
- many rows (vector of hash-maps)
- affected rows
- any other result you implement
- replace parameters for:
where id = :id
- Value lists:
where id in (:v*:ids)
- Tuple lists (for multi-insert):
- SQL identifiers:
- SQL identifier lists:
- SQL keywords:
SQL File Conventions
HugSQL SQL files contain special single-line comments and multi-line comments in the following forms:Examples:
HugSQL recognizes the following keys:
:name-(private fn) = name of the function to create and, optionally, the command and result as a shorthand in place of providing these as separate key/value pairs
:doc= docstring for the created function
:command= underlying database command to run
:result= expected result type
:snip-(private fn) = name of the function to create and, optionally, the command and result as a shorthand in place of providing these as separate key/value pairs.
:snipis used in place of
:metametadata in the form of an EDN hashmap to attach to function
:require= namespace require and aliases for Clojure expression support
:command specifies the underlying database command to run for
the given SQL. The built-in values are:
:?= query with a result-set (default)
:!= any statement
:<!= support for
INSERT ... RETURNING
:i!= support for insert and jdbc
:execute mirror the
clojure.java.jdbc library and
execute in the
Read more about
:insert in the Insert
:query is the default command when no command is specified.
To save some typing, the command can be specified as the
second value for the
You can create command functions of your own by implementing a
:result specifies the expected result type for the given SQL.
The available built-in values are:
:1= one row as a hash-map
:*= many rows as a vector of hash-maps
:n= number of rows affected (inserted/updated/deleted)
:raw= passthrough an untouched result (default)
:raw is the default when no result is specified.
To save some typing, the result function can be specified as the third
value for the
:name key. You must supply a second command value in
order to use this shorthand convention:
You can create result functions of your own by implementing a
Within an SQL statement itself, HugSQL understands several types of parameters that can be passed in during the function call. All parameter types take the form:
When a HugSQL-generated function is called, the parameters in an SQL
statement are replaced at runtime with the hash-map data passed in as
the function's second argument. The keys of the hash-map are matched
:param-name portion of the parameter. Parameters can be
repeated throughout an SQL statement, and all instances of a parameter
will be replaced.
Aside: Param Name Deep Get
The parameter name can optionally use a "deep get" syntax
to drill down into a parameter data structure. This syntax
consists of keywords and integers joined by a
.. A keyword is a key for a hashmap. An
integer is a vector index. For example:
Aside: The Colon
Since HugSQL has commandeered the
: for use, you will need to escape a
colon with a backslash if you actually need a colon in your
SQL. Escaping a colon will prevent HugSQL from interpreting
the colon as the start of a HugSQL parameter. For example,
Postgresql array ranges use colons:
BUT, HugSQL does make one exception to the colon-escaping
necessity in regard to the Postgresql historical type-casting syntax
that uses a double-colon
:: to indicate a
Postgresql data type. So, HugSQL properly leaves alone a
Now back to the Parameter Types...
HugSQL recognizes the need for different types of parameters in SQL
statements. Specifically, SQL treats data values differently from
identifiers and SQL keywords. If you are building dynamic queries to
select specific columns or choose the
order by direction, then a
simple value parameter is not sufficient. HugSQL supports value
parameters, identifier parameters, and raw sql (keyword) parameters.
The built-in parameter types are detailed below along with information on extending these types and creating new types.
Value Parameters are replaced at runtime with an appropriate SQL data type for the given Clojure data type.
HugSQL defers the Clojure-to-SQL conversion to the underlying database driver using the sqlvec format.
Value Parameters' type is
:v for short.
Value Parameters are the default parameter type, so you can omit the type portion of the parameter placeholder in your SQL statements.
SQL Value Lists
Value List Parameters are similar to Value Parameters, but work on
lists of values needed for
in (...) queries.
Value List Parameters' type is
:v* for short.
* indicates a sequence of zero or more values.
Each value in the list is treated as a value parameter, and the list is joined with a comma.
Tuple Parameters are similar to Value List Parameters in that they both work with lists of values.Tuple Parameters differ in that they enclose their values in parentheses. Additionally, while a Tuple Parameter can be used like a Value List Parameter (e.g., for an
in ()clause), it is generally understood that a Tuple Parameter's data values may have different data types, but Value Parameters values are of the same data type.
Tuple Parameters' type is
Each value in the list is treated as a Value Parameter. The list is joined with a comma and enclosed in parentheses.
The use of a tuple in the above manner is not supported by all databases. Postgresql, MySQL, and H2 support it. Derby, HSQLDB, and SQLite do not support it.
SQL Tuple Lists
Tuple List Parameters support lists of tuples. This is specifically useful for multi-record insert.
Tuple List Parameters' type is
Each tuple in the list is treated as a Tuple Parameter. The list is joined with a comma.
The use of a tuple list in the above manner is not supported by all databases. Postgresql, MySQL, H2, Derby, and SQLite support it. HSQLDB does not support it.
BATCH INSERTS: It should be noted that Tuple List
Parameter support is only support for SQL
(...),(...),(...) syntax. This is appropriate for
small-ish multi-record inserts. However, this is different than
large batch support. The underlying JDBC driver for your
database has a limit to the size of the SQL and the number of
allowed bind parameters. If you are doing large batch inserts,
doseq over your
HugSQL-generated insert function within
Identifier Parameters are replaced at runtime with an optionally-quoted SQL identifier.
Identifier Parameters' type is
:i for short.
As of HugSQL 0.4.6, Identifier Parameters support SQL aliases:
By default, identifiers are not quoted. You can specify your desired quoting as an option when defining your functions or as an option when calling your function.
If you are taking identifiers
from user input, you should use the
option to properly quote and escape identifiers to prevent
:quoting options are:
:offno quoting (default)
Identifiers containing a period/dot
. are split, quoted separately,
and then rejoined. This supports
SQL Identifier Lists
Identifier List Parameters are similar to identifier
parameters, but work on lists of identifiers. You might use
these to replace column lists found
Identifier List Parameter's type
* indicates a sequence of zero or more
Each identifier in the list is treated as an identifier parameter, and the list is joined with a comma.
As of HugSQL 0.4.6, Identifier List Parameters support SQL aliases:
Raw SQL (Keywords)
Raw SQL Parameters allow full, un-quoted,
parameter replacement with raw SQL, allowing you to
parameterize SQL keywords (and any other SQL parts). You
might use this to set
order by column clause, or you can use
this to compose many SQL statements into a single statement.
You should take special care to always properly validate any incoming user input before using Raw SQL Parameters to prevent an SQL injection security issue.
SQL Parameters' type is
Snippet parameters are replaced at runtime with the provided snippet/sqlvec.
See Snippets for usage.
Snippet List parameters are replaced at runtime with the provided list of snippets/sqlvecs.
See Snippets for usage.
Custom Parameter Types
You can create your own parameter types by implementing a
method for the multimethod
One of HugSQL's design goals is to balance the coupling of an
SQL-template library (itself) with an underlying database
library of the developer's choosing. We could concede to no
coupling at all--providing only
However, wrapping a few underlying database library functions in
a protocol provides a nice easy-path for most use cases. And,
where necessary to use the underlying database libraries
directly, HugSQL attempts to stay out of the way and give you
the tools to do what you want to do.
In the spirit of the above thoughts, HugSQL provides an adapter protocol to allow your choice of underlying database library.
Using Other Adapters
HugSQL will default to using the adapter for the
clojure.java.jdbc library. If you would prefer
to use the adapter for
clojure.jdbc or another
adapter, you will need to configure your dependencies and
set the adapter.
Leiningen dependency information:
hugsql clojar is a meta clojar that pulls in
hugsql-adapter, and the default adapter
hugsql-adapter-clojure-java-jdbc, which uses
to run database queries.
If you wish to use a different adapter, you should bypass the
clojar and specify
hugsql-core and the adapter clojar you desire:
Within your Clojure code, you will need to explicitly set the adapter.
You can do this globally (i.e., at app startup) with
hugsql.core/set-adapter!, or you can specify
:adapter as an option when defining your functions
hugsql.core/def-db-fns, or you can pass in
:adapter option when calling your defined function.
Create an Adapter
Adapters from the Community
HugSQL's adapter protocol encourages alternative adapters to support your preferred database library. Write your own!
- HugSQL Adapter for postgres.async by Robin Heggelund Hansen
Did you write an adapter? Add an issue and we'll add your adapter here.
Comparison with Yesql
Yesql is a Clojure library written by Kris Jenkins. It has a similar take on using SQL that HugSQL embraces whole-heartedly. Certainly, HugSQL would not exist were it not for Kris' great library.
So why build a another similar library? A recent project with some fairly hairy SQL required me to generate dynamically-named tables and views with variable columns, and I found myself having to revert back to string concatenation for building up my SQL. I realized I needed something similar to Yesql, but with support for different types of parameter placeholders: namely, SQL Identifiers and SQL Keywords. This was the seed that grew into HugSQL, and the two libraries have quite a few differences now.
Differences between Yesql and HugSQL:
- Yesql is coupled to
clojure.java.jdbc. HugSQL has protocol-based adapters to allow multiple database backend libraries and ships with support for
clojure.jdbc. This functionality has enabled the adapter for postgres.async. See Hugsql Adapters for more information.
- Yesql only supports SQL Value parameters. HugSQL supports SQL Values, SQL Identifiers, SQL Tuples, SQL Keywords (Raw SQL), and you can create your own custom parameter types. See HugSQL in Detail for more info.
- Yesql supports positional parameter
?and named parameter placeholders
:id. HugSQL only supports named parameter placeholders and there are no plans to support positional placeholders.
- Yesql tends to favor naming conventions of the function
<!suffixes) to indicate functionality. HugSQL prefers explicit configuration in the SQL file.
- HugSQL features a
:resultconfiguration that indicates the expected return format (e.g.,
:many= vector of hashmaps,
:one= hashmap). Yesql supports a similar functionality by passing the
:result-set-fnoption through to
- Yesql (as of 0.5.x) supports setting a default database
connection at the time of function definition, and optionally
overriding this connection at function call time. HugSQL
expects a database spec, connection, or transaction object as
the first argument to your function call. However, as of
version 0.4.1, HugSQL
map-of-db-fnsallowing other libraries to wrap HugSQL-created functions and set a default database connection. This is precisely what the Luminus web framework's conman library does.
- As of HugSQL 0.4.0, HugSQL supports Clojure expressions and Snippets for composing SQL from smaller parts.
--Curtis Summers (Feb 2016)
P.S. I'd like to keep the above comparison accurate as both libraries grow. See something that's not right? Add an issue!
Does HugSQL support my wacky SQL?
Yes, HugSQL's parsing strategy is to extract the parts it cares about and leave the rest of your SQL as is. If you find some SQL is not being handled properly by HugSQL, it may be a bug. File an issue.
What about DSLs for SQL?
Can I get the same flexible SQL generation like I can in Honey SQL?
HugSQL encourages you to think in SQL first, then
sprinkle in the power of Clojure where necessary. HoneySQL
starts on the Clojure side first. Both are valid workflows and
a matter of developer preference and situation. It's important
to realize that HugSQL and HoneySQL are not mutually exclusive:
HugSQL Snippet Parameter Types
:snip* can consume the sqlvec format output
format function. It's the best of
Preventing SQL Injection
How does HugSQL help protect against SQL injection?
HugSQL attempts to provide a set of tools that help protect against SQL injection where possible without taking away power from the developer. Below are a few potential SQL injection attack vectors and HugSQL's response to each:
Value Parameters, Value List Parameters, Tuple Parameters, and Tuple List Parameters are all variations on SQL value parameters that convert a Clojure data type to SQL. By default, all of these parameter types defer to the underlying database library to perform SQL parameter binding to prevent SQL injection issues.
Identifier Parameters and Identifier List Parameters support quoting and escaping of identifiers with the
:quoting option. By
HugSQL makes no assumptions about your given database. This
may be fine for your use case if you are not taking
identifiers from user input. If you
are taking identifiers from user input, you should use
:quoting option to prevent SQL injection!
See Identifier Parameters for
Raw SQL (Keyword) Parameters
Raw SQL (Keyword) Parameters are exactly what they seem, and it is your responsibility to sanitize any usage of this parameter type when using user input.
Snippets generate sqlvecs and Snippet Parameter Types consume sqlvecs. For snippets containing any HugSQL parameter types, the same rules as above apply. If you are consuming a snippet (or sqlvec) from your own code or another library (say, HoneySQL), then other rules might apply.
Custom Parameter Types
Custom Parameter Types allow you to create your own parameter types. It is your responsibility to ensure your implementation protects against SQL injection by properly escaping your data.
Clojure Expressions should return either a string or nil, and strings returned from expressions are parsed at runtime to support HugSQL parameters. The same rules apply for the above parameter types.
Copyright © 2017 Layerware, Inc.
Distributed under the Apache License, Version 2.0