2024-06-24 PGmacs - editing PostgreSQL databases from Emacs

As you probably know, I’m not only a fan of Emacs – I also like PostgreSQL very much. It is then no surprise that upon learning about PGmacs, a new tool by Eric Marsden to access PostgreSQL databases from within Emacs, I wanted to try it out.

The installation went (almost) smoothly – it turned out that PGmacs is not (yet) available on Melpa, so I needed to install it directly from GitHub. The readme suggests using package-vc-install, but since I use use-package, I just added these two lines to my init file:

(use-package pg :vc (:url "https://github.com/emarsden/pg-el"))
(use-package pgmacs :vc (:url "https://github.com/emarsden/pgmacs"))

I’m not sure if PGmacs will be automatically upgraded when a new version is available, though – I’ll probably read more about use-package in a spare moment.

I then ran M-x pgmacs and was greeted with a form (created using the Emacs Widget Library) with fields like database name, port number and other stuff. I was nice, although it would be even nicer to be able to just paste a connection string. I looked at the documentation and it turned out that it’s indeed possible – just use M-x pgmacs-open-uri. That’s great! The first impression is definitely “this doesn’t look like a proof of concept or work in progress, it feels like a production-grade tool”!

One thing that put me off a little bit was that there is no Info manual for PGmacs. Surprisingly, the user manual is written in Markdown, not in Org mode. Maybe I will try to convert it to TeXinfo format with pandoc and see if that helps.

Of course, these are all just first impressions. I started actually using PGmacs, and I’ve got a few second impressions, too.

It’s capabilities are fairly simple. You can browse tables (both the list of them and individual tables), insert, delete and update rows (the last two are only possible when a table has a primary key, which makes sense), and do a few more things like exporting a table to csv – and that’s pretty much it. I am so used to psql at this point that I don’t expect to use PGmacs a lot, especially that it worked rather slowly on my machine (this is not its fault though, probably – I used it to access a database on a virtual machine and I suspect that at least some of the overhead was caused by this). Still, I plan to start using it, since even in the first 30 minutes I encountered quite a few quirks (I don’t want to call them “bugs” – they didn’t anything nasty to my data nor resulted in wrong answers to queries – but they were not entirely expected) and I’d love to help the author fix them. For example, I expect C-h m to show me the list of keybindings for that mode; nothing like that happens in PGmacs. I expect q to bury the buffer in special modes; in PGmacs, it killed the buffer without aking me if I’m sure that’s what I want. The pgmacs-open-uri variable does not use a dedicated history variable. These are definitely not show-stoppers, but they can be annoying.

Another thing that bugged me a bit was how PGmacs handles JSONB columns. PGmacs uses pg.el by the same author, and one of its advertised features is that it translates PostgreSQL types to Elisp native types whenever possible. One natural representation for a JSON value is a hash table (an alist or a plist are other possibilities), and that’s what pg.el uses. I deal with JSONB columns pretty often, and I’d prefer to be able to view and edit them as JSON (possibly in json-navigator by Damien Cassou). However, since JSONB columns are somewhat against the relational database structure and hence frowned upon by some people (including me, at least usually), this is not something I’d even suggest to change, though.

I really regret that I do not have the time these days to contribute a lot to PGmacs. I’ll do my best and try, but I can only fix a minor issue here and there and definitely I won’t be able to do bigger stuff. One thing that I’d love to see in PGmacs is this: when the point is on a value in a column which references a column in some other table, pressing RET on it could open that other table on the corresponding row. That alone would be immensely useful and would instantly convince me to start using PGmacs a lot more.

All in all, even though it’s indeed in beta status and some things do not work as smoothly as they could, PGmacs is a great piece of software. It’s main advantage seems to be that it does not use any command-line tool like psql to connect to the database, but uses sockets instead (this is of course a feature of pg.el which PGmacs is built on).

CategoryEnglish, CategoryBlog, CategoryEmacs, CategoryPostgreSQL