For the English part of the blog, see Content AND Presentation.
2024-05-04 Pretty printing SQL
I work with PostgreSQL on a daily basis, and I often share queries with my teammates. I like to show them nicely formatted queries instead of the mess I sometimes write
. A bit surprisingly, I had troubles finding a nice SQL pretty-printer/formatter.
For a few years I used sqlformat, a tool written in Python. I was quite happy with it, until I noticed that it didn’t work with queries referring to jsonb columns properly. For example, this query
select jsonb_column->'property',
jsonb_column->'another'->'subproperty',
jsonb_column->'yet'->'another'
becomes this mess.
select jsonb_column->'property',
jsonb_column->'another'->'subproperty',
jsonb_column->'yet'->'another'
After I had to deal with a query which selected well over twenty or thirty properties from jsonb columns, I decided to stop and look for another solution.
This time, I found SQL Formatter. This one is written in JavaScript, and can be used both from JS code and command line. Since I’m primarily working with Node.js, this looks very good – there is a potential for logging formatted queries instead of one-line monstrosities, for example. My first use, however, is this quick-and-dirty Emacs command.
(defun sql-format-region ()
"Format SQL in region using `sql-formatter'."
(interactive)
(shell-command-on-region (region-beginning)
(region-end)
"sql-formatter -c ~/.sql-formatter.json"
nil
t))
enabling me to quickly format an SQL query in the active region. The ~/.sql-formatter.json file looks like this:
{
"language": "postgresql",
"keywordCase": "lower",
"dataTypeCase": "lower",
"functionCase": "lower"
}
As you can see, I’m not fond of screaming at my database.
(My simple command probably should use the r interactive code, but I never had the need to call it non-interactively, so there’s no benefit for me to refactoring it into something nicer.) The above query looks after formatting looks like this:
select jsonb_column -> 'property', jsonb_column -> 'another' -> 'subproperty', jsonb_column -> 'yet' -> 'another'
Maybe it’s not my favorite style, but it’s legible and consistent. Here is a more complicated query (which I shamelessly asked an LLM to write for me):
WITH cte AS ( SELECT id, json_column->>'key1' AS value1, json_column->>'key2' AS value2 FROM table1 ) SELECT cte.id, table2.column1, SUM(table2.column2) AS total FROM cte JOIN table2 ON cte.value1 = table2.value1 JOIN table3 ON cte.value2 = table3.value2 WHERE (cte.value1 = 'some_value' AND table2.column3 = 'another_value') OR (cte.value2 = 'another_value' AND table3.column4 = 'some_value') GROUP BY cte.id, table2.column1 HAVING SUM(table2.column2) > 100 ORDER BY cte.id ASC;
and after reformatting, it looks like this:
with
cte as (
select
id,
json_column ->> 'key1' as value1,
json_column ->> 'key2' as value2
from
table1
)
select
cte.id,
table2.column1,
sum(table2.column2) as total
from
cte
join table2 on cte.value1 = table2.value1
join table3 on cte.value2 = table3.value2
where
(
cte.value1 = 'some_value'
and table2.column3 = 'another_value'
)
or (
cte.value2 = 'another_value'
and table3.column4 = 'some_value'
)
group by
cte.id,
table2.column1
having
sum(table2.column2) > 100
order by
cte.id asc;
After I found SQL Formatter, I stumbled upon Steve Purcell’s sqlformat.el package, which is way more advanced – for example, it supports several SQL formatters. It’s entirely possible that one day I’ll ditch my command in favor of sqlformat.el, but for now it is sufficient for me. (One reason I might want to use it is that it can use even more formatters than the two I’ve mentioned, and some of them look pretty advanced and configurable.)
CategoryEnglish, CategoryBlog, CategoryPostgreSQL, CategoryEmacs
2024-04-27 Emacs everywhere
A few years ago I mentioned that I use Textern so that I can easily edit input boxes and text areas in Emacs. While I still use it, it seems less and less great. More and more web apps do crazy JavaScript things with their inputs and textareas, and they sometimes interfere with Textern in unpredictable ways.
Recently, I found something similar but possibly better: Emacs everywhere. One of its advantages is that it works, well, everywhere in X.org, not only in Firefox. (I’m not sure how, but it supposedly also works on Macs. Maybe they use X.org, too? I don’t know.) At first it seemed to me that it doesn’t matter much – I hardly ever use anything but Emacs and Firefox. Then it dawned on me: Emacs everywhere works in the terminal, too! (You may be surprised that I don’t use Eshell or at least M-x term. Well, I don’t. Old habits die slowly, plus I am a tad paranoid and I’m slightly afraid of my Emacs (or more probably, some rogue package) doing something unpleasant like saving sessions or my keypresses to the disk. Since I often type various passwords in the terminal, I’d prefer this not to happen.
The way Emacs everywhere does its job is (at least conceptually) pretty simple. It spawns an Emacs frame, creates an empty buffer, inserts the primary selection into it if it exists, and lets me edit the buffer. When I’m done, I can press C-c C-c and Emacs everywhere pastes what I’ve written into the program that was running before I started it. (In fact, there is a bit more to that. You can also press C-c C-k to cancel, and DEL (that is, backspace) as the first keypress deletes everything.)
I made a few quick experiments with various nasty apps like Jira and Discord, and Emacs everywhere seems to work very well! I also wrote a short script to launch it, emacs-everywhere.sh:
#!/bin/bash emacsclient --eval "(emacs-everywhere)"
and bound it to s-e (that is, Super-e) in my window manager of choice:
globalkeys = gears.table.join(
-- Emacs everywhere
awful.key({ modkey, }, "e", function() awful.util.spawn("emacs-everywhere.sh") end),
-- Emacs everywhere ends here
-- ...
)
Now, the only thing left is that I need to remember to press s-e to edit anything in Emacs – unlike Textern, Emacs everywhere does not (and cannot) put the “current” content of whatever I’m editing in the Emacs buffer it creates. (Technically, it could simulate pressing C-a C-x which in many applications means “mark everything and kill” (or “select all and paste”, as the youth of today calls it), but that would break e.g. in a terminal.) In fact, even if I forget about it, I can for example select everything with C-a (in Firefox at least), and then the selected text will get copied to the Emacs everywhere buffer – and when it gets pasted again, the selection will still be active (unless I deactivated it manually), so what I wrote in Emacs will effectively replace what I had earlier. Very nice!
That’s it for today, see you next week!
2024-04-22 Next-Error-Follow mode
A few weeks ago I wrote about Emacs’ Follow mode. It turns out that searching for follow-mode on my blog reveals an old post about Ibuffer which is very short and unfortunately a bit misleading. It seems that the mention of Follow mode there was really meant to mean Next-Error-Follow mode. It is a completely unrelated, but also useful concept. When you open a buffer with references to various places (like an Occur or Diff buffer), you can say M-x
next-error-follow-minor-mode, and moving around in that buffer will automatically jump to the relevant position in another window. It’s a great way to quickly see the results of a search, differences between files, compilation errors etc. in context.