SQL, Clojure, and editor support

When you’re writing code with a modern editor or an IDE, you can count on having a number of convenient features such as syntax highlighting, autocomplete, and code navigation. Unless you’re writing SQL, of course!

A lot of editors and IDEs, such as IntelliJ IDEA, have really nice support for SQL. However, it’s not so easy to benefit from it in practice. If you’re writing Clojure and you want to use SQL to query a database, you have a few options:

1. Embed SQL into strings in your Clojure code.
2. Put SQL into .sql files and import them with HugSQL or similar.
3. Use a low-level query builder such as HoneySQL.
4. Use a high-level query builder or an ORM such as Toucan.

Let’s look into each of them in more detail.

Embed SQL into strings. A basic query would look like this:

(jdbc/query db ["SELECT title, rating FROM movies WHERE movie_id = ?" movie-id]))

This is simple, but most likely you aren’t getting syntax highlighting or other features for the string. For example, IntelliJ IDEA does support SQL inside Java strings, but it does not work in Cursive. Furthermore, if you’d need to parametrize query in a way that is not allowed by the ? placeholder, you’ll have to resort to string templating, which is prone to SQL injection bugs.

Put SQL into .sql files. With HugSQL, the SQL file would look something like this:

-- :name get-movie-by-id
SELECT title, rating FROM movies WHERE movie_id = :id

When you load this with HugSQL, it defines a function called get-movie-by-id which executes the query.

(hugsql/def-db-fns "my_queries.sql")
(get-movie-by-id db {:id movie-id})

What is great about this approach is that now you get the full editor support for SQL. In Cursive, however, you can’t jump from Clojure code that refers to get-movie-by-id to its definition in SQL. Jumping to definition works with normal Clojure functions, but Cursive does not know how to deal with functions defined by HugSQL.¹

HugSQL has advanced support for parametrizing your queries using snippets and Clojure expressions. If you use Clojure expressions, though, you now have the opposite problem: there’s Clojure code embedded into your SQL comments and there’s no editor support for it.

Use a low-level query builder. With HoneySQL, the query would be built like this:

(->> (sql/format {:select [:title :rating]
                  :from   [:movies]
                  :where  [:= :movies.movie_id movie-id]})
     (jdbc/execute db))

There’s a lot to like about this. Your editor’s Clojure support works with this. You won’t get autocomplete for database identifiers, but completion for commonly used keywords can be good enough. Queries are Clojure data, so you can use the full power of Clojure to generate them.

The main problem is that you need to learn a new, non-canonical syntax for your SQL queries. You know how to write the query you want in SQL, but now you need to figure out how to map it to HoneySQL. It shouldn’t be too hard, but over the years me and my colleagues have struggled with it.

Use a high-level query builder or an ORM. With Toucan, the query would look like this:

(defmodel Movie :movies)
(db/select Movie :movie-id movie-id)

From the editor support perspective, this is about the same as using HoneySQL. It does save you quite a bit of boilerplate. Toucan relies on HoneySQL for advanced paremetrization, so the syntax problem remains.

None of the approaches seems like an obvious winner. In practice, every big project I’ve seen has used a mix of them.

Contrast this with Datomic and MongoDB: the query languages of both can be represented cleanly enough as Clojure data and so that’s what you use. This assortment of options does not exist for them because it’s not needed.

In his article Against SQL, Jamie Brandon argues that SQL’s drawbacks cause “a massive drag on quality and innovation in runtime and tooling”. He does not mention editor support, but I can’t help but think that it’s an example of the effects of that drag.