I often have to write more or less complex SQL queries. In case of the easy ones, there is no problem – I just write them and that’s it. With more complex queries, however, it is very useful to adopt an incremental technique – writing them piece by piece. One of the best tools for such “incremental programming” is of course the REPL. In case of SQL (PostgreSQL in my case), this is usually solved with psql. While it has lots of nice features, editing a multiline query within it is not the nicest experience in the world.
My first attempt to solving this problem was to use psql‘s \e command. If given in the line on its own, it launches the default system editor with the previous query. While the readline library used by psql is fine, it is not very helpful in case of long, multiline queries – in such a case, a real editor like Emacs or Vim (or even a toy editor like everything else
) seems much better.
After some time, however, I had an even better idea. Why bother running a separate program myself when I could just write SQL queries in the comfort of my editor? The canonical Emacs way is the Comint mode, which is like shell-mode for non-shell programs (like REPLs of various languages, for instance).
There is, however, an even better alternative – Org-mode. For those of you who don’t know it (but under what rock have you been living for the past ten years?), it is like an interactive Markdown editor on steroids: you can write structured text in a plain text markup, and interact with it in various ways. (Of course, it is also much more, e.g. an advanced PIM/task manager, a spreadsheet etc.) Much like Markdown, you can have “source blocks” in Org-mode, containing code in various languages (including SQL), and that code gets properly syntax-highlighted. For instance, you can have this (assuming you run Postgres in Docker as explained in my previous post):
#+begin_src sql :engine postgresql :dbhost localhost :dbuser postgres :dbpassword docker :database postgres :dbport 5432
create table testtable (
id int,
content text
);
insert into testtable(id, content)
values (1, 'aqq'), (2, 'bum'), (3, 'cyk');
#+end_src
And – unlike in this very blog post – in Emacs the query has the font-lock (the Emacs term for syntax highlighting) turned on.
Unlike your typical Markdown editor, however, this code is actually executable. This is why I included all the information about the database server in the above snippet. This means that pressing C-c
C-c (i.e., control-c twice) when on the source block makes Org-mode run it under the hood and give you the results. (By default, after pressing C-c C-c you have also to confirm that you really want to execute the code, since executing unknown code with your user’s permissions is a potentially dangerous thing. This confirmation step can be turned off if you trust the code you want to run.) Note that “giving the results” may mean a lot of things in Org-mode, like saving them to a file or putting them verbatim in the Org-mode file. In case of SQL queries, however, probably the best way is to lay them out in an Org-mode table. (Unlike Markdown, Org-mode has first-class support for tables. This means not only syntax to describe them, but also good support for editing them, and even support for spreadsheet-like calculations.)
Of course, the “table” you get from creating an SQL relation and inserting two rows in it is not very interesting. How about a SELECT?
#+begin_src sql :engine postgresql :dbhost localhost :dbuser postgres :dbpassword docker :database postgres :dbport 5432 select id, content from testtable; #+end_src #+RESULTS: | id | content | |----+---------| | 1 | aqq | | 2 | bum | | 3 | cyk |
Here, I included the #+RESULTS keyword (inserted automatically by Org-mode after executing the code block) and the actual results (ditto). Notice that this output is different from (although similar to) the default ASCII-art-ish table produced by psql. This is Org-mode’s table syntax in action.
Now I have to say that this is my preferred way of experimenting with SQL queries REPL-style. I can edit the query using my favorite keybindings in my favorite editor, and the results are always two keystrokes away (and I could easily bind the command org-babel-execute-src-block to e.g. f5 or whatever key I like best) – what’s even more, after pressing those keystrokes, I am still in the same place of the query so that I can continue editing. This way, incremental building of queries becomes a pleasure.
CategoryEnglish, CategoryBlog, CategoryEmacs, CategoryOrgMode, CategoryPostgreSQL