Using Yesql in Clojure

Keith Smiley

We previously wrote about writing webapps with Liberator in Clojure. This time I want to look at how we chose to integrate our JSON API with PostgreSQL.

Without trying to overgeneralize, there seems to be a few common ways to integrate Clojure with databases. The first, as shown in the example usage of the JDBC database wrapper, is to provide database queries in-line.

Here is a high level example:

(query connection ["SELECT * FROM robots WHERE name = ?" "ralph"])

This can end up littering our code with in-line SQL statements that are not easily reusable or easy to reason about in the case of complex queries.

The second approach revolves around creating Clojure functions and composing them together to make a data structure that compiles down to a database query. An example of a library that does this is Honey SQL.

Here is what the previous query example looks like with this approach:

(->
  (select :*)
  (from :robots)
  (where [:= :name "ralph"]))

Since this directly translates to raw SQL, this doesn’t make creating queries any easier than the first approach. This approach is actually harder to read than if we were just working with raw SQL queries.

The third approach, that Yesql takes, provides a different way to do things. When using Yesql, we create raw .sql files that contain our queries. Then, we execute these from Clojure.

Here is an example of how this works:

-- find_bot.sql
SELECT *
FROM bots
WHERE name = ?

; queries.clj
(defquery find-bot "queries/find_bot.sql")
(defn find-ralph []
  (find-bot (connection) "ralph"))

Another possible advantage of this is we can reuse our queries across different services that have similar entities. I find this separation between the queries and Clojure extremely refreshing. We can easily write our SQL and completely ignore it in our Clojure code. This also helps us keep much of this logic complete separate in ourSQL. That way we can focus on the rest of our logic in the Clojure code. We are using this alongside ragtime which provides similar raw SQL migrations. These two libraries make a great pair for interacting with a database from our Clojure code.

We have been happy to have a variety of libraries that approach our problems in very different ways as we progress on this Clojure API. We are excited to continue to watch and participate as the Clojure community grows and comes up with great new ways to solve problems.