Intro Due to the nature of my work, I operate in a variety of environments, databases, and languages. Emacs is very good for this. As an example of this, I just found myself needing to verify if an update had worked on the PostGres database of a remote server. First I logged in to the server with better-shell-remote-open1, and then I sudoed and changed to the postgres user, then I quickly fired up my bookmarked myPostgres.

Intro 🖼1 There are several copy-pasted articles2 out there on setting up project-local postgres instances. Which one came first or whether they are both copies of another, I don’t know; but in any case, I found the goals good, and was enlightened by the concept. There are several technical errors, but they served to get the juices flowing. I am using the Postgres software version I got from GUIX, which is postgres 14.

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.

My project design includes creating a file/namespace for each database table, with a suite of CRUD operations applying to each table. The end result is that I have functions like db.my-table/CREATE, READ, UPDATE, and DELETE available for each table. The strategy is as follows: Establish the template clj file that has all your CRUD operations Obtain all table names (e.g. from init.sql) Use a script to create a matching clj file based on template (1) customized to refer to the tables from (2) 1.

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.