HugSQL is a Clojure library for embracing SQL.

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 object
  • param-data is a hashmap of parameter data where the keys match parameter placeholder names in your SQL
  • options 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:

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

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
 */
Examples: 
-- 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 for INSERT ... 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 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 :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!

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 for clojure.java.jdbc and 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 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 to clojure.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