One of the most meaningful testaments to Clojure’s data-driven and lisp-powered design is the ability to use it to refine any of its transpiling processes, here SQL generation (elsewhere route generation, CSS generation, and of course its actual hosted languages, including Javascript or Java generation*). In this post I was tasked with refactoring a 3-arg function that reads from the database into a map-taking function that works with one, two, or three items in the map.

I thought I’d share a snippet of what I love about my HoneySQL + Postgres setup (this is plain Honey without the Postgres extras, which never seem to cover my needs). I don’t take any pride in the actual database model, which is in-use and was not well designed. Nonetheless, the two things that worked out nicely here are: table applications has a one-to-many relationship with validations, so PG’s “distinct on” was the perfect tool for just matching the most recent of an application’s validations.

Turns out that Postgres has beautiful support for “array” field types. In one database of mine, I use this to keep track of the recipients of emails: DROP TABLE IF EXISTS "email_log" CASCADE; CREATE TABLE "email_log" ( id SERIAL PRIMARY KEY, sender TEXT NOT NULL, recipients TEXT[] NOT NULL, message TEXT, sent_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, info JSONB); -- Note the recipients TEXT[] definition: that's an array of texts. Now for the use-case: a user might have multiple email addresses, and the email_log tracks recipient ADDRESSES not user-ids.

HoneySQL is a wonderful Clojure library that lets use the full power of Clojure collections to generate your SQL statements. Postgres supports json operators and data types, which is one of the main features for which I use it; however, since this is a non-SQL-standard feature, HoneySQL doesn’t support it directly, and I didn’t see that support in HoneySQL-Postgres yet. Fortunately, HoneySQL makes it easy to extend this way. (ns myapp.