---
title: Using Yesql in Clojure
teaser: Integrate Clojure with PostgreSQL using Yesql.
tags: web,clojure
author: Keith Smiley
published_on: 2014-11-19
---
We [previously wrote][liberator-intro] about writing webapps with
[Liberator][liberator] in [Clojure][clojure]. This time I want to look at how we
chose to integrate our JSON
API with
[PostgreSQL][postgres].
[liberator-intro]: https://thoughtbot.com/blog/getting-started-with-liberator
[liberator]: https://clojure-liberator.github.io/liberator/
[clojure]: http://clojure.org/
[postgres]: http://www.postgresql.org/
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][jdbc] of the JDBC database wrapper, is to provide database queries
in-line.
[jdbc]: https://github.com/clojure/java.jdbc#example-usage
Here is a high level example:
```clojure
(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][honey].
[honey]: https://github.com/jkk/honeysql
Here is what the previous query example looks like with this approach:
```clojure
(->
(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][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.
[yesql]: https://github.com/krisajenkins/yesql
Here is an example of how this works:
```sql
-- find_bot.sql
SELECT *
FROM bots
WHERE name = ?
```
```clojure
; 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][ragtime] which provides similar raw SQL migrations. These two libraries make a great pair for
interacting with a database from our Clojure code.
[ragtime]: https://github.com/weavejester/ragtime
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.