[[!meta title="PGmacs: browsing and editing PostgreSQL databases from Emacs"]] [[!meta copyright="Copyright © 2024 Eric Marsden"]] [[!inline pages="internal(2024/info/pgmacs-nav)" raw="yes"]] # PGmacs: browsing and editing PostgreSQL databases from Emacs Eric Marsden (he/him) - , [@emarsden@mastodon.social](https://mastodon.social/@emarsden) [[!inline pages="internal(2024/info/pgmacs-before)" raw="yes"]] PGmacs provides an Emacs-based browsing and editing interface for the PostgreSQL DBMS. It allows you to browse the contents of database tables, in paginated mode for large tables, to edit column values in the minibuffer or using a widget-based interface, to delete, copy and insert new rows, and to save the contents of a table in CSV or JSON format. You can run shell commands on column values. You can also rename tables and columns, add SQL comments, and generate SchemaSpy diagrams. PGmacs works both in the terminal and in GUI mode. It uses the pg-el library, which implements the PostgreSQL wire protocol, to connect to PostgreSQL over the network or over a Unix socket. The talk will provide an overview of the functionality of PGmacs, then show how it can be easily extended to display images stored as database BLOBs inlined in a row-list buffer. About the speaker: Eric Marsden developed his first Emacs packages pg-el, coffee.el (an implementation of the essential Hyper Text Coffee Pot Control Protocol) and ipp.el as a procrastination exercise to avoid working on his PhD. He continues to use Emacs Lisp in his spare time. # Discussion ## Questions and answers - Q: This is brilliant, thank you! Do you know if PGmacs works with TRAMP? I often use TRAMP multi-hop to access databases - both remotely when accessing via a 'bastion server' and locally when using OCI containers - A: There is no TRAMP support, I'm afraid. PGmacs is directly connecting to the database server over the network. You can set up ssh tunnels, but that would be done separately from Tramp. Thanks :) - \ you can tunnel arbitrary data over ssh - \ ssh subsystems are the thing to look at, I think\... I might look at it - \ (sftp works that way: basically you can put arbitrary progams at the remote end\... like, oh, say, pgsql. transparently to the ssher.) - \ Possibly PGmacs could setup the tunnel itself, using TRAMP? - With docker.el, kubel, etc, it's often possible to for example select a container/pod/whatever that is hosted on a machine you've connected to via TRAMP (such as /podman:\:/path/), and trigger a terminal/eshell as well as port-forwards and other similar things. It'd be nice to be able to use this tool in a similar way since it would open up the ability to use it with complex connection configurations. Doing SSH tunnels manually is ofc totally fine in practice :) - Q: \Great work! I'm impressed. How did you come up with this brilliant idea? - A: First got the idea by looking at sqlite-mode - Q: Is sqlite-mode also capable of all of this functionality (table relations, etc)? If not, will it be possible to abstract out this functionality from pgmacs somehow? - A: I'm not veyr familiar with sqlite-mode but  it looks more basic. There are differences between the sql dialects so it will be difficult to abstract it out. - Q: Would it be possible to move it into Emacs tree? Are the maintainers interested in it? - A: Currently its at a very early stage and is being updated regularly. I also have some philosophical reasons to not do it regarding copyright transfer to the FSF. - Q: Almost missed this one, so glad I didn't, but this may have been answered already: what do you use for the in-buffer tables? vtable? - A: vtable but "forked" (some changes/improvements, may consider "merging" back with vtable core work) - Q: - A: ## Notes - I have got 270 tables, and it stays there forever "loading" tables, and nothing appears. - Thanks for checking it out! You may have an authentication failure, and PGmacs is not very good at showing that in the connect phase. Switch to the \*Messages\* buffer and see whether there's an error message there. - This is happening with me as well. I noticed this error message in Postgres Logs. It works if I open the postgres database. - db-1  \| 2024-12-08 18:58:41.524 UTC \[48\] STATEMENT:  CREATE EXTENSION IF NOT EXISTS vector - db-1  \| 2024-12-08 18:59:13.831 UTC \[50\] ERROR:  could not open extension control file "/usr/share/postgresql/14/extension/vector.control": No such file or directory - In \*Messages\* buffer, this is what I see - pg-bind: Wrong type argument: stringp, 97 - OK, thanks for this feedback. It looks like your Postgres vector extension is not well set up, but that really shouldn't be causing a complete connection failure. You can comment out the line (pg-vector-setup con) in function pgmacs-open in pgmacs.el and see whether that helps - - Let me try that and report back. - I got this error: pg-do-startup: Process postgres not running: failed with code 111 - OK, that means that the network connection failed. There is probably more error information in the backtrace - Ah sorry, had stopped the pg. This time I got the same error - pg-bind: Wrong type argument: stringp, 97 - But it didn't try to load vector extensions (from pg logs). - With toggle-debug-on-error, this is the backtrace - [https://gist.github.com/ankitrgadiya/d9ae038489e4f680e3037e2e61584312](https://gist.github.com/ankitrgadiya/d9ae038489e4f680e3037e2e61584312){rel="noreferrer noopener"} - OK, thanks for that backtrace. I don't immediately see what is wrong here. There is something strange about that shared_urls table, it seems. Are you using pg-el from the github repo?  - I created the github issue to continue the discussion: [https://github.com/emarsden/pgmacs/issues/9](https://github.com/emarsden/pgmacs/issues/9){rel="noreferrer noopener"} - - - BTW I'm using the postgres docker image. - This is amazing! Image inline :o !! - I'm impressed by the inline images.  Just sprinkle a little elisp and voila. - A PostGIS point field -\> osm.el integration would be very cool - Interesting idea, will look into that. - \ emarsden: Super slick Emacs/PG hacking and presentation! Eat your heart out, MS Access - \ another package I never knew I needed - \ Very cool, I'm currently using PG on a small project and it's always a pain to leave emacs to do checks in the database. - \ I just wish it had existed when I was doing massive db work in the 90s/2000s - \ Just installed it, to prevent me from forgetting about it. \ [[!inline pages="internal(2024/info/pgmacs-after)" raw="yes"]] [[!inline pages="internal(2024/info/pgmacs-nav)" raw="yes"]]