HugSQL is a Clojure library for embracing SQL.
- 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
(e.g.,
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), next.jdbc, and clojure.jdbc
Installation
Leiningen dependency information:
[com.layerware/hugsql "0.5.1"]
You will also need to specify your JDBC driver dependency from one of the following:
For example, the Postgresql driver:
[org.postgresql/postgresql "42.2.2"]
HugSQL defaults to using
the clojure.java.jdbc
library to run underlying
database commands. If you would prefer to use another
underlying database library instead
of clojure.java.jdbc
, such
as clojure.jdbc
, please see HugSQL Adapters
Getting Started
This documentation make use
of The
Princess Bride example application in the HugSQL
repository. Feel free to view or clone the repo and run the
application with lein as-you-wish
.
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 resources
,
src
, or elsewhere in your classpath.
Our example application puts SQL files under src
:
.../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:
-- src/princess_bride/db/sql/characters.sql
-- The Princess Bride Characters
-- :name create-characters-table
-- :command :execute
-- :result :raw
-- :doc Create characters table
-- auto_increment and current_timestamp are
-- H2 Database specific (adjust to your DB)
create table characters (
id integer auto_increment primary key,
name varchar(40),
specialty varchar(40),
created_at timestamp not null default current_timestamp
)
/* ...snip... */
-- A :result value of :n below will return affected rows:
-- :name insert-character :! :n
-- :doc Insert a single character returning affected row count
insert into characters (name, specialty)
values (:name, :specialty)
-- :name insert-characters :! :n
-- :doc Insert multiple characters with :tuple* parameter type
insert into characters (name, specialty)
values :tuple*:characters
/* ...snip... */
-- A ":result" value of ":1" specifies a single record
-- (as a hashmap) will be returned
-- :name character-by-id :? :1
-- :doc Get character by id
select * from characters
where id = :id
-- Let's specify some columns with the
-- identifier list parameter type :i* and
-- use a value list parameter type :v* for IN()
-- :name characters-by-ids-specify-cols :? :*
-- :doc Characters with returned columns specified
select :i*:cols from characters
where id in (:v*:ids)
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.
(ns princess-bride.db.characters
(:require [hugsql.core :as hugsql]))
;; The path is relative to the classpath (not proj dir!),
;; so "src" is not included in the path.
;; The same would apply if the sql was under "resources/..."
;; Also, notice the under_scored path compliant with
;; Clojure file paths for hyphenated namespaces
(hugsql/def-db-fns "princess_bride/db/sql/characters.sql")
;; For most HugSQL usage, you will not need the sqlvec functions.
;; However, sqlvec versions are useful during development and
;; for advanced usage with database functions.
(hugsql/def-sqlvec-fns "princess_bride/db/sql/characters.sql")
The 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 characters-by-ids-specify-cols
function:
(characters/characters-by-ids-specify-cols-sqlvec
{:ids [1 2], :cols ["name" "specialty"]}) ;;=>
["select name, specialty from characters
where id in (?,?)"
,1
,2]
(characters/characters-by-ids-specify-cols db
{:ids [1 2], :cols ["name" "specialty"]}) ;;=>
({:name "Westley", :specialty "love"}
{:name "Buttercup", :specialty "beauty"})
You've only scratched the surface of HugSQL's functionality. Keep reading for full usage examples.
Using HugSQL
This section provides practical usage examples with HugSQL. Most examples come from The Princess Bride example application in the HugSQL repository.
def-db-fns
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.
hugsql.core/def-db-fns
doc:
=> (doc hugsql.core/def-db-fns)
-------------------------
hugsql.core/def-db-fns
([file] [file options])
Macro
Given a HugSQL SQL file, define the database
functions in the current namespace.
Usage:
(def-db-fns file options?)
where:
- file is a string file path in your classpath,
a resource object (java.net.URL),
or a file object (java.io.File)
- options (optional) hashmap:
{:quoting :off(default) | :ansi | :mysql | :mssql
:adapter adapter }
:quoting options for identifiers are:
:ansi double-quotes: "identifier"
:mysql backticks: `identifier`
:mssql square brackets: [identifier]
:off no quoting (default)
Identifiers containing a period/dot . are split, quoted separately,
and then rejoined. This supports myschema.mytable conventions.
:quoting can be overridden as an option in the calls to functions
created by def-db-fns.
:adapter specifies the HugSQL adapter to use for all defined
functions. The default adapter used is
(hugsql.adapter.clojure-java-jdbc/hugsql-adapter-clojure-java-jdbc)
when :adapter is not given.
See also hugsql.core/set-adapter! to set adapter for all def-db-fns
calls. Also, :adapter can be specified for individual function
calls (overriding set-adapter! and the :adapter option here).
The functions defined by def-db-fns
have the following arities:
[db]
[db params]
[db params options & command-options]
where:
db
is a db-spec, a connection, a connection pool, or a transaction objectparam-data
is a hashmap of parameter data where the keys match parameter placeholder names in your SQLoptions
is a hashmap of HugSQL-specific options (e.g.,:quoting
and:adapter
)& command-options
is a variable number of options to be passed down into the underlying adapter and database library functions. See Advanced Usage for more detail.
def-sqlvec-fns
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:
["select * from characters where id = ?", 2]
The sqlvec format is a convention used
by clojure.java.jdbc
and
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
parameters.
HugSQL provides the hugsql.core/def-sqlvec-fns
macro to create functions returning the sqlvec format.
The created functions have an -sqlvec
suffix by
default, though this is configurable with
the :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
queries.
hugsql.core/def-sqlvec-fns
doc:
=> (doc hugsql.core/def-sqlvec-fns)
-------------------------
hugsql.core/def-sqlvec-fns
([file] [file options])
Macro
Given a HugSQL SQL file, define the <name>-sqlvec functions in the
current namespace. Returns sqlvec format: a vector of SQL and
parameter values. (e.g., ["select * from test where id = ?" 42])
Usage:
(def-sqlvec-fns file options?)
where:
- file is a string file path in your classpath,
a resource object (java.net.URL),
or a file object (java.io.File)
- options (optional) hashmap:
{:quoting :off(default) | :ansi | :mysql | :mssql
:fn-suffix "-sqlvec" (default)
:quoting options for identifiers are:
:ansi double-quotes: "identifier"
:mysql backticks: `identifier`
:mssql square brackets: [identifier]
:off no quoting (default)
Identifiers containing a period/dot . are split, quoted separately,
and then rejoined. This supports myschema.mytable conventions.
:quoting can be overridden as an option in the calls to functions
created by def-db-fns.
:fn-suffix is appended to the defined function names to
differentiate them from the functions defined by def-db-fns.
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:
-
def-db-fns-from-string
➚ -
def-sqlvec-fns-from-string
➚ -
map-of-db-fns
➚ -
map-of-sqlvec-fns
➚ -
map-of-db-fns-from-string
➚ -
map-of-sqlvec-fns-from-string
➚ -
sqlvec
and aliassnip
➚ -
sqlvec-fn
and aliassnip-fn
➚ -
db-run
➚ -
db-fn
➚
Create,Drop,... (DDL)
SQL
-- :name create-characters-table
-- :command :execute
-- :result :raw
-- :doc Create characters table
-- auto_increment and current_timestamp are
-- H2 Database specific (adjust to your DB)
create table characters (
id integer auto_increment primary key,
name varchar(40),
specialty varchar(40),
created_at timestamp not null default current_timestamp
)
/* The create-character-table definition above uses the full,
long-hand "-- :key :value" syntax to specify the :command and
:result. We can save some typing by using the short-hand notation
as the second and (optionally) third values for the :name. Below, the
:! is equivalent to ":command :!", where :! is an alias for
:execute. The default :result is :raw when not specified, so
there is no need to specify it as the third value. */
-- :name drop-characters-table :!
-- :doc Drop characters table if exists
drop table if exists characters
Clojure:
(characters/create-characters-table-sqlvec) ;;=>
["create table characters (
id integer auto_increment primary key,
name varchar(40),
specialty varchar(40),
created_at timestamp not null default current_timestamp
)"]
(characters/create-characters-table db) ;;=>
[0]
(characters/drop-characters-table-sqlvec) ;;=>
["drop table if exists characters"]
(characters/drop-characters-table db) ;;=>
[0]
Insert
SQL
-- A :result value of :n below will return affected row count:
-- :name insert-character :! :n
-- :doc Insert a single character
insert into characters (name, specialty)
values (:name, :specialty)
-- :name insert-characters :! :n
-- :doc Insert multiple characters with :tuple* parameter type
insert into characters (name, specialty)
values :tuple*:characters
Clojure:
(characters/insert-character-sqlvec
{:name "Westley", :specialty "love"}) ;;=>
["insert into characters (name, specialty)
values (?, ?)"
,"Westley"
,"love"]
(characters/insert-character db
{:name "Westley", :specialty "love"}) ;;=>
1
(characters/insert-character db
{:name "Buttercup", :specialty "beauty"}) ;;=>
1
(characters/insert-characters-sqlvec
{:characters
[["Vizzini" "intelligence"]
["Fezzik" "strength"]
["Inigo Montoya" "swordmanship"]]}) ;;=>
["insert into characters (name, specialty)
values (?,?),(?,?),(?,?)"
,"Vizzini"
,"intelligence"
,"Fezzik"
,"strength"
,"Inigo Montoya"
,"swordmanship"]
(characters/insert-characters
db
{:characters
[["Vizzini" "intelligence"]
["Fezzik" "strength"]
["Inigo Montoya" "swordmanship"]]}) ;;=>
3
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
(e.g., Postgresql
supports this), you can specify your SQL insert statement
command type to be :returning-execute
, or :<!
for short:
-- :name insert-into-test-table-returning :<!
-- :doc insert with an sql returning clause
insert into test (id, name) values (:id, :name) returning id
OPTION #2: Get Generated Keys / Last Insert ID / Inserted Record
HugSQL's :insert
, or :i!
command
type indicates to the underlying adapter that the insert
should be performed, and then .getGeneratedKeys
called in the jdbc driver. The return value
of .getGeneratedKeys
varies greatly across
different databases and jdbc drivers. For example, see the
following code from the HugSQL test suite:
SQL
-- :name insert-into-test-table-return-keys :insert :raw
insert into test (id, name) values (:id, :name)
Clojure:
(testing "insert w/ return of .getGeneratedKeys"
;; return generated keys, which has varying support and return values
;; clojure.java.jdbc returns a hashmap, clojure.jdbc returns a vector of hashmaps
(when (= adapter-name :clojure.java.jdbc)
(condp = db-name
:postgresql
(is (= {:id 8 :name "H"}
(insert-into-test-table-return-keys db {:id 8 :name "H"} {})))
:mysql
(is (= {:generated_key 9}
(insert-into-test-table-return-keys db {:id 9 :name "I"})))
:sqlite
(is (= {(keyword "last_insert_rowid()") 10}
(insert-into-test-table-return-keys db {:id 10 :name "J"} {})))
:h2
(is (= {(keyword "scope_identity()") 11}
(insert-into-test-table-return-keys db {:id 11 :name "J"} {})))
;; hsql and derby don't seem to support .getGeneratedKeys
nil))
(when (= adapter-name :clojure.jdbc)
(condp = db-name
:postgresql
(is (= [{:id 8 :name "H"}]
(insert-into-test-table-return-keys db {:id 8 :name "H"} {})))
:mysql
(is (= [{:generated_key 9}]
(insert-into-test-table-return-keys db {:id 9 :name "I"})))
:sqlite
(is (= [{(keyword "last_insert_rowid()") 10}]
(insert-into-test-table-return-keys db {:id 10 :name "J"} {})))
:h2
(is (= [{(keyword "scope_identity()") 11}]
(insert-into-test-table-return-keys db {:id 11 :name "J"} {})))
;; hsql and derby don't seem to support .getGeneratedKeys
nil)))
Update
SQL
-- :name update-character-specialty :! :n
update characters
set specialty = :specialty
where id = :id
Clojure:
(let [vizzini (characters/character-by-name db {:name "vizzini"})]
(characters/update-character-specialty-sqlvec
{:id (:id vizzini), :specialty "boasting"})) ;;=>
["update characters
set specialty = ?
where id = ?"
,"boasting"
,3]
(let [vizzini (characters/character-by-name db {:name "vizzini"})]
(characters/update-character-specialty db
{:id (:id vizzini), :specialty "boasting"})) ;;=>
1
Delete
SQL
-- :name delete-character-by-id :! :n
delete from characters where id = :id
Clojure:
(let [vizzini (characters/character-by-name db {:name "vizzini"})]
(characters/delete-character-by-id-sqlvec {:id (:id vizzini)})) ;;=>
["delete from characters where id = ?",3]
(let [vizzini (characters/character-by-name db {:name "vizzini"})]
(characters/delete-character-by-id db {:id (:id vizzini)})) ;;=>
1
Select
SQL
-- A ":result" value of ":*" specifies a vector of records
-- (as hashmaps) will be returned
-- :name all-characters :? :*
-- :doc Get all characters
select * from characters
order by id
-- A ":result" value of ":1" specifies a single record
-- (as a hashmap) will be returned
-- :name character-by-id :? :1
-- :doc Get character by id
select * from characters
where id = :id
-- :name character-by-name :? :1
-- :doc Get character by case-insensitive name
select * from characters
where upper(name) = upper(:name)
-- :name characters-by-name-like :?
-- :doc Get characters by name like, :name-like should include % wildcards
select * from characters
where name like :name-like
-- Let's specify some columns with the
-- identifier list parameter type :i* and
-- use a value list parameter type :v* for SQL IN()
-- :name characters-by-ids-specify-cols :? :*
-- :doc Characters with returned columns specified
select :i*:cols from characters
where id in (:v*:ids)
Clojure:
(characters/all-characters-sqlvec) ;;=>
["select * from characters
order by id"]
(characters/all-characters db) ;;=>
({:id 1,
:name "Westley",
:specialty "love",
:created_at #inst "2015-11-09T19:33:58.472000000-00:00"}
{:id 2,
:name "Buttercup",
:specialty "beauty",
:created_at #inst "2015-11-09T19:33:58.492000000-00:00"}
{:id 3,
:name "Vizzini",
:specialty "boasting",
:created_at #inst "2015-11-09T19:33:58.530000000-00:00"}
{:id 4,
:name "Fezzik",
:specialty "strength",
:created_at #inst "2015-11-09T19:33:58.530000000-00:00"}
{:id 5,
:name "Inigo Montoya",
:specialty "swordmanship",
:created_at #inst "2015-11-09T19:33:58.530000000-00:00"})
(characters/character-by-id-sqlvec {:id 1}) ;;=>
["select * from characters
where id = ?"
,1]
(characters/character-by-id db {:id 1}) ;;=>
{:id 1,
:name "Westley",
:specialty "love",
:created_at #inst "2015-11-09T19:33:58.472000000-00:00"}
(characters/character-by-name-sqlvec {:name "buttercup"}) ;;=>
["select * from characters
where upper(name) = upper(?)"
,"buttercup"]
(characters/character-by-name db {:name "buttercup"}) ;;=>
{:id 2,
:name "Buttercup",
:specialty "beauty",
:created_at #inst "2015-11-09T19:33:58.492000000-00:00"}
(characters/characters-by-name-like-sqlvec {:name-like "%zz%"}) ;;=>
["select * from characters
where name like ?"
,"%zz%"]
(characters/characters-by-name-like db {:name-like "%zz%"}) ;;=>
({:id 3,
:name "Vizzini",
:specialty "boasting",
:created_at #inst "2015-11-09T19:33:58.530000000-00:00"}
{:id 4,
:name "Fezzik",
:specialty "strength",
:created_at #inst "2015-11-09T19:33:58.530000000-00:00"})
(characters/characters-by-ids-specify-cols-sqlvec
{:ids [1 2], :cols ["name" "specialty"]}) ;;=>
["select name, specialty from characters
where id in (?,?)"
,1
,2]
(characters/characters-by-ids-specify-cols
db
{:ids [1 2], :cols ["name" "specialty"]}) ;;=>
({:name "Westley", :specialty "love"}
{:name "Buttercup", :specialty "beauty"})
Transactions
The first argument of a function generated
by 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
an example
with clojure.java.jdbc/with-db-transaction
:
(clojure.java.jdbc/with-db-transaction [tx db]
(characters/insert-character tx
{:name "Miracle Max", :specialty "miracles"})
(characters/insert-character tx
{:name "Valerie", :specialty "speech interpreter"}))
Clojure Expressions
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
runtime: params
, which is the hashmap of parameter
data, and options
, which is the hashmap of
options.
A single-line comment expression starts
with --~
. Notice the tilde ~
. An
entire Clojure expression is expected to be in a single-line
comment.
-- :name clj-expr-single :? :1
select
--~ (if (seq (:cols params)) ":i*:cols" "*")
from test
order by id
A multi-line comment expression can have interspersed
SQL. The expression starts with /*~
, all
"continuing" parts also start with /*~
, and the
expression ends with ~*/
. When an expression needs
to represent advancing to the "next" Clojure form (like
the if
below), an empty separator /*~*/
is necessary:
-- :name clj-expr-multi :? :1
select
/*~ (if (seq (:cols params)) */
:i*:cols
/*~*/
*
/*~ ) ~*/
from test
order by id
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 :require
similar to usage in (ns ...):
-- :name clj-expr-generic-update :! :n
/* :require [clojure.string :as string]
[hugsql.parameters :refer [identifier-param-quote]] */
update :i:table set
/*~
(string/join ","
(for [[field _] (:updates params)]
(str (identifier-param-quote (name field) options)
" = :v:updates." (name field))))
~*/
where id = :id
(clj-expr-generic-update db {:table "test"
:updates {:name "X"}
:id 3})
Snippets
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 :name
):
-- :snip select-snip
select :i*:cols
-- :snip from-snip
from :i*:tables
Snippets can contain snippet
parameters. Below, :snip*:cond
is a Snippet List
Parameter Type, which specifies that :cond
is a
list/vector of snippets
-- :snip where-snip
where :snip*:cond
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:
-- :snip cond-snip
-- We could come up with something
-- quite elaborate here with some custom
-- parameter types that convert := to =, etc.,
-- Examples:
-- {:conj "and" :cond ["id" "=" 1]}
-- OR
-- {:conj "or" :cond ["id" "=" 1]}
-- note that :conj can be "", too
:sql:conj :i:cond.0 :sql:cond.1 :v:cond.2
Using the above snippets, we can now construct the full query. (With an optional where clause via a Clojure expression)
-- :name snip-query :? :*
:snip:select
:snip:from
--~ (when (:where params) ":snip:where")
(snip-query
db
{:select (select-snip {:cols ["id","name"]})
:from (from-snip {:tables ["test"]})
:where (where-snip {:cond [(cond-snip {:conj "" :cond ["id" "=" 1]})
(cond-snip {:conj "or" :cond ["id" "=" 2]})]})})
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.
Advanced Usage
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 :as-arrays?
option
to clojure.java.jdbc/query
. Please note the
required 3rd argument (the HugSQL-specific options) when using
this passthrough feature:
(is (= [[:name] ["A"] ["B"]]
(select-ordered db
{:cols ["name"] :sort-by ["name"]} {} {:as-arrays? true})))
Please note that as
of clojure.java.jdbc
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
the clojure.java.jdbc
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.
vacuum analyze
)
- determine the result type:
- one row (hash-map)
- many rows (vector of hash-maps)
- affected rows
- any other result you implement
- replace parameters for:
- Values:
where id = :id
- Value lists:
where id in (:v*:ids)
- Tuple lists (for multi-insert):
values :tuple*:my-records
- SQL identifiers:
from :i:table-name
- SQL identifier lists:
select :i*:column-names
- SQL keywords:
:sql:my-query
- Values:
SQL File Conventions
HugSQL SQL files contain special single-line comments and multi-line comments in the following forms:
-- :key value1 value2 value3
OR
/* :key
value1
value2
value3
*/
-- regular SQL comment ignored by hugsql
/*
regular SQL multi-line comment 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 recognizes the following keys:
:name
or: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
or: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.:snip
is used in place of:name
for snippets.:meta
metadata in the form of an EDN hashmap to attach to function:require
= namespace require and aliases for Clojure expression support
Command
The :command
specifies the underlying database command to run for
the given SQL. The built-in values are:
:query
or:?
= query with a result-set (default):execute
or:!
= any statement:returning-execute
or:<!
= support forINSERT ... RETURNING
:insert
or:i!
= support for insert and jdbc.getGeneratedKeys
:query
and :execute
mirror the
distinction between query
and execute!
in the
clojure.java.jdbc
library and fetch
and execute
in the
clojure.jdbc
library.
Read more about :returning-execute
and :insert
in the Insert
section
: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 :name
key:
-- :name all-characters :?
You can create command functions of your own by implementing a
hugsql.core/hugsql-command-fn
multimethod.
Result
The :result
specifies the expected result type for the given SQL.
The available built-in values are:
:one
or:1
= one row as a hash-map:many
or:*
= many rows as a vector of hash-maps:affected
or: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:
-- :name all-characters :? :*
You can create result functions of your own by implementing a
hugsql.core/hugsql-result-fn
multimethod.
Parameter Types
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:
:param-name
OR
:param-type:param-name
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
to the :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
period .
. A keyword is a key for a hashmap. An
integer is a vector index. For example:
-- first-employee :? :1
select * from employees where id = :value:employees.0.id
(first-employee db {:employees [{:id 1} {:id 2}]})
;=> {:id 1 :name "Al"}
Aside: Namespaced Keywords
As of HugSQL 0.5.1, namespaced keywords are supported for explicit namespaces, e.g.
:myproject.core.employee/name
or :employee/name
. However,
double-colon shorthands like ::employee/name
are not supported.
Aside: The Colon
Since HugSQL has commandeered the
colon :
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:
select my_arr[1\:3] from ...
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
double-colon:
select id::text ...
... where id = :id::bigint
-- with the param type specified
... where id = :v:id::bigint
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.
SQL Values
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 :value
, or :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.
An example:
--:name value-param :? :*
select * from characters where id = :id
--name value-param-with-param-type :? :*
select * from characters where id = :v:id
resulting sqlvec:
(value-param-sqlvec {:id 42})
;=> ["select * from characters where id = ?" 42]
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 :value*
, or :v*
for short.
The *
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.
--:name value-list-param :? :*
select * from characters where name in (:v*:names)
resulting sqlvec:
(value-list-param-sqlvec {:names ["Fezzik" "Vizzini"]})
;=> ["select * from characters where name in (?,?)" "Fezzik" "Vizzini"]
SQL Tuples
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 anin
()
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 :tuple
.
Each value in the list is treated as a Value Parameter. The list is joined with a comma and enclosed in parentheses.
-- :name tuple-param
-- :doc Tuple Param
select * from test
where (id, name) = :tuple:id-name
resulting sqlvec:
(tuple-param-sqlvec {:id-name [1 "A"]})
;=> ["select * from test\nwhere (id, name) = (?,?)" 1 "A"]
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 :tuple*
.
Each tuple in the list is treated as a Tuple Parameter. The list is joined with a comma.
-- :name tuple-param-list
-- :doc Tuple Param List
insert into test (id, name)
values :t*:people
resulting sqlvec:
(tuple-param-list-sqlvec {:people [[1 "Ed"] [2 "Al"] [3 "Bo"]]})
;=> ["insert into test (id, name)\nvalues (?,?),(?,?),(?,?)" 1 "Ed" 2 "Al" 3 "Bo"]
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 INSERT...VALUES
(...),(...),(...)
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,
you should map
or doseq
over your
HugSQL-generated insert function within
a transaction.
SQL Identifiers
Identifier Parameters are replaced at runtime with an optionally-quoted SQL identifier.
Identifier Parameters' type is :identifier
, or :i
for short.
--:name identifier-param :? :*
select * from :i:table-name
resulting sqlvec:
(identifier-param-sqlvec {:table-name "example"})
;=> ["select * from example"]
As of HugSQL 0.4.6, Identifier Parameters support SQL aliases:
(identifier-param-sqlvec {:table-name ["example" "my_example"]})
;=> ["select * from example as my_example"]
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 :quoting
option to properly quote and escape identifiers to prevent
SQL injection!
Valid :quoting
options are:
:ansi
double-quotes:"identifier"
:mysql
backticks:`identifier`
:mssql
square brackets:[identifier]
:off
no quoting (default)
Identifiers containing a period/dot .
are split, quoted separately,
and then rejoined. This supports myschema.mytable
conventions.
(hugsql.core/def-db-fns "path/to/good.sql" {:quoting :ansi})
(identifier-param-sqlvec {:table-name "example"})
;=> ["select * from \"example\""]
(identifier-param-sqlvec {:table-name "schema1.example"} {:quoting :mssql})
;=> ["select * from [schema1].[example]"]
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
in select
, group by
, order
by
clauses.
Identifier List Parameter's type
is :identifier*
, or :i*
for
short.
The *
indicates a sequence of zero or more
identifiers.
Each identifier in the list is treated as an identifier parameter, and the list is joined with a comma.
--:name identifier-list-param :? :*
select :i*:column-names, count(*) as population
from example
group by :i*:column-names
order by :i*:column-names
(identifier-list-param-sqlvec {:column-names ["state" "city"]})
;=> ["select state, city, count(*) as population\n
; from example\n
; group by state, city\n
; order by state, city"]
As of HugSQL 0.4.6, Identifier List Parameters support SQL aliases:
--:name identifier-list-param :? :*
select :i*:column-names-aliased, count(*) as population
from example
group by :i*:column-names
order by :i*:column-names
(let [columns [["state" "my_state"] ["city" "my_city"]]]
(identifier-list-param-sqlvec {:column-names-aliased columns
:column-names (mapv first columns)}))
;=> ["select state as my_state, city as my_city, count(*) as population\n
; from example\n
; group by state, city\n
; order by state, city"]
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 asc
or desc
on an 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 :sql
--:name sql-keyword-param :? :*
select * from example
order by last_name :sql:last_name_sort
(def user-input "asc")
(defn validated-asc-or-desc [x] (if (= x "desc") "desc" "asc"))
(sql-keyword-param-sqlvec {:last_name_sort (validated-asc-or-desc user-input)})
;=> ["select * from example\norder by last_name asc"]
Snippets
Snippet parameters are replaced at runtime with the provided snippet/sqlvec.
See Snippets for usage.
Snippet Lists
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 hugsql.parameters/hugsql-apply-param.
:
=> (doc hugsql.parameters/apply-hugsql-param)
-------------------------
hugsql.parameters/apply-hugsql-param
Implementations of this multimethod apply a hugsql parameter
for a specified parameter type. For example:
(defmethod apply-hugsql-param :value
[param data options]
(value-param param data options)
- :value keyword is the parameter type to match on.
- param is the parameter map as parsed from SQL
(e.g., {:type :value :name "id"} )
- data is the runtime parameter map data to be applied
(e.g., {:id 42} )
- options contain hugsql options (see hugsql.core/def-sqlvec-fns)
Implementations must return a vector containing any resulting SQL
in the first position and any values in the remaining positions.
(e.g., ["?" 42])
HugSQL Adapters
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 def-sqlvec-fns
.
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.
Default Adapter
HugSQL defaults to using the adapter for the
clojure.java.jdbc
library. If you would prefer
to use the adapter
for next.jdbc
, clojure.jdbc
, or
another adapter, you will need to configure your
dependencies and set the adapter.
Leiningen dependency information:
The hugsql
clojar is a meta clojar that pulls in hugsql-core
,
hugsql-adapter
, and the default adapter
hugsql-adapter-clojure-java-jdbc
, which uses
clojure.java.jdbc
to run database queries.
If you wish to use a different adapter, you should bypass
the hugsql
clojar and
specify hugsql-core
and the adapter clojar
you desire:
clojure.java.jdbc Adapter
Adapter for clojure.java.jdbc
Included here for reference, but this is unnecessary if you are using the hugsql
meta clojar.
[com.layerware/hugsql-core "0.5.1"]
[com.layerware/hugsql-adapter-clojure-java-jdbc "0.5.1"]
next.jdbc Adapter
Adapter for next.jdbc. (as of HugSQL 0.5.1
).
In order to maintain the closest behavior to
clojure.java.jdbc
, the adapter currently defaults to
setting the result set :builder-fn
option to
next.jdbc.result-set/as-unqualified-lower-maps
.
This behavior can be overridden when setting the adapter.
[com.layerware/hugsql-core "0.5.1"]
[com.layerware/hugsql-adapter-next-jdbc "0.5.1"]
clojure.jdbc Adapter
Adapter for clojure.jdbc
[com.layerware/hugsql-core "0.5.1"]
[com.layerware/hugsql-clojure-jdbc "0.5.1"]
Setting an adapter
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
the :adapter
as an option when defining your functions
with hugsql.core/def-db-fns
, or you can pass in
an :adapter
option when calling your defined function.
(ns my-app
(:require [hugsql.core :as hugsql]
[hugsql.adapter.next-jdbc :as next-adapter
[next.jdbc.result-set :as rs]]]))
(defn app-init []
(hugsql/set-adapter! (next-adapter/hugsql-adapter-next-jdbc)))
;; OR override the :builder-fn behavior
(defn app-init []
(hugsql/set-adapter! (next-adapter/hugsql-adapter-next-jdbc {:builder-fn result-set/as-unqualified-maps})))
OR
(ns my-db-stuff
(:require [hugsql.core :as hugsql]
[hugsql.adapter.next-jdbc :as next-adapter]))
(hugsql/def-db-fns "path/to/db.sql"
{:adapter (next-adapter/hugsql-adapter-next-jdbc)})
OR
(ns my-db-stuff
(:require [hugsql.core :as hugsql]
[hugsql.adapter.next-jdbc :as next-adapter]))
(def db ;;a db-spec here)
(hugsql/def-db-fns "path/to/db.sql")
(my-query db {:id 1}
{:adapter (next-adapter/hugsql-adapter-next-jdbc)})
Create an Adapter
Creating a HugSQL Adapter is as simple as implementing the hugsql.adapter/HugsqlAdapter protocol. For examples, see the implementations for clojure.java.jdbc, next.jdbc, and clojure.jdbc.
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
- HugSQL Adapter for ClickHouse
Did you write an adapter? Add an issue and we'll add your adapter here.
FAQ
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 forclojure.java.jdbc
andclojure.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
placeholders
?
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
name (
!
, and<!
suffixes) to indicate functionality. HugSQL prefers explicit configuration in the SQL file. - HugSQL features a
:result
configuration that indicates the expected return format (e.g.,:many
= vector of hashmaps,:one
= hashmap). Yesql supports a similar functionality by passing the:result-set-fn
option through toclojure.java.jdbc/query
. - 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
provides
map-of-db-fns
allowing 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?
Yes, as of the HugSQL 0.4.0 release, Clojure expressions and Snippets provide all of the power and flexibility to compose SQL statements.
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
and :snip*
can consume the sqlvec format output
from HoneySQL's format
function. It's the best of
both worlds!
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
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
Identifier Parameters
and Identifier List
Parameters support quoting and escaping of identifiers
with the :quoting
option. By
default, :quoting
is :off
, since
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
the :quoting
option to prevent SQL injection!
See Identifier Parameters for
details.
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.
Snippet Parameters
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
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.
License
Copyright © 2018 Layerware, Inc.
Distributed under the Apache License, Version 2.0