There are lots of Clojure libraries available for working with SQL. The core libraries that provide the low level functionality are clojure.java.jdbc and the more recent clojure.jdbc. Some of the more popular libraries built on top of them are Korma, Honey SQL, and Yesql.
I've been a huge fan of the approach that Yesql takes since it was released. Every time I've worked with a DSL for SQL, I've found that I'd always run into cases where I knew exactly what to do if I was writing plain SQL, but I couldn't find a clean way to express using the abstraction on top of it. Since Yesql approach lets you keeps your SQL as SQL the problem largely goes away.
Luminus has been using Yesql since it came out and I think it made the framework much more approachable. Unfortunately, Yesql doesn't appear to be actively developed, and I found myself falling back to using clojure.java.jdbc directly for things like batch inserts.
Another problem from Luminus perspective is that Yesql API defines query functions directly. Luminus uses conman for connection management, and it creates its own connection-aware functions. This required an ugly hack of using a shadow namespace for interning the functions generated by Yesql.
I recently learned about the HugSQL library that is inspired by Yesql, and addresses all the issues I've run into using it. The official site does a good job enumerating the major differences from Yesql. Some of the highlights for HugSQL are:
The latest version of HugSQL provides an API that returns a map of query functions keyed on their names as well as the ability to define the functions directly.
I think this is a very useful feature even if you're not using conman or Luminus. Having a map of the query functions allows the user to decide what they want to do with them explicitly. For example, you're able to do things like the following:
(def queries (hugsql.core/map-of-db-fns "queries.sql")
(defn get-user [db opts]
((-> queries :get-user :fn) db opts))
Let's take a look at the basic usage of HugSQL and differences from Yesql.
The core syntax in HugSQL is quite similar to Yesql. Both Yesql and HugSQL use comments with a special format to provide the metadata for generating the functions to work with queries.
-- name: create-user!
-- creates a new user record
INSERT INTO users
(id, pass)
VALUES (:id, :pass)
-- name: get-users
-- retrieve all users
SELECT * FROM users
-- name: get-user
-- retrieve a user given the id.
SELECT * FROM users
WHERE id = :id
Yesql uses the -- name: fn-name syntax to specify the function name, the comment below the function name is implicitly used as the doc for the function. The ! at the end of the function name is used as a convention to indicate that it mutates the data. The query parameter placeholders are identified using the : prefix.
-- :name create-user! :! :n
-- :doc creates a new user record
INSERT INTO users
(id, pass)
VALUES (:id, :pass)
-- :name get-users :? :*
-- :doc retrieve all users
SELECT * FROM users
-- :name get-user :? :1
-- :doc retrieve a user given the id
SELECT * FROM users
WHERE id = :id
The HugSQL version uses the -- :name syntax instead that mirrors the Clojure keyword syntax. The function name is followed by two additional flags. The first flag indicates the SQL command type and the second indicates the result.
This provides more flexibility for handling the results. For example, the get-users query indicates that it selects multiple records, while the get-user indicates that it selects exactly one record. This helps document the intent of the query and cuts down on boilerplate, as you'd have to write a wrapper otherwise that gets the first result from the query.
: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: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)In HugSQL, all the comments that represent metadata start with a key describing the type of metadata. In the examples above, the doc string is explicitly specified using the -- :doc prefix.
HugSQL also supports additional syntax within its queries. For example, if we wanted to insert multiple records using a single query, then we could use a vector of records as follows:
-- :name add-users! :! :n
-- :doc add multiple users
INSERT INTO users
(id, pass)
VALUES :t*:users
(add-users! db {:users
[["bob" "Bob"]
["alice" "Alice"]]})
The syntax for for in-list queries is also a bit different from Yesql. The SQL query uses the :v* flag to indicate the value list parameter.
-- :name find-users :? :*
-- :doc find users with a matching ID
SELECT *
FROM users
WHERE id IN (:v*:ids)
The function parameters will now consist of a map with the key :ids that points to a vector of ids that we would like to match on.
(find-users db {:ids ["foo" "bar" "baz"]})
As you can see, the syntactic differences for basic queries are very minor. I've migrated a number of projects to HugSQL already, and found the process to be completely painless.
I haven't covered the advanced features of HugSQL, but I highly recommend looking over the official documentation to see what's available.