[[!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"]]
<!-- Initially generated with emacsconf-publish-talk-page and then left alone for manual editing -->
<!-- You can manually edit this file to update the abstract, add links, etc. --->
# PGmacs: browsing and editing PostgreSQL databases from Emacs
Eric Marsden (he/him) - <mailto:eric.marsden@risk-engineering.org>, [@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.
<https://github.com/emarsden/pgmacs>
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:\<image\>:/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: \<Donovan\>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)
- 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)
- - - 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.
- 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.
- I just wish it had existed when I was doing massive db work in the 90s/2000s
- Better 20+ years late than never 😆
- there is a grantback if you assign copyright to the FSF: you get all the rights you gave away right back again
- Thanks Eric, looks fantastic, and quite eager to look into those vtable enhancements myself (thanks for answering)
- YouTube comment: Very impressive, thanks!
- YouTube comment: Finally a perspective on a potential real excel killer.
- YouTube comment: How is the duckdb more coming along?
[[!inline pages="internal(2024/info/pgmacs-after)" raw="yes"]]
[[!inline pages="internal(2024/info/pgmacs-nav)" raw="yes"]]
