Hi, this is a short presentation about PGmacs,
which is a browsing and editing interface for
Postgres databases.
My name is Eric Marsden, and I'm the
developer of this Emacs Lisp library.
PGmacs was inspired by sqlite-mode, which is a
great feature available from Emacs 29 onwards.
It allows you to view and modify SQLite
databases you might have lying around.
It's a really great feature.
It would be even better, however, if it
were able to use a real database.
And I happen to know that was possible
because a few years ago, I wrote an
Emacs Lisp library, pg.el, which implements the wire
protocol used for communication over the network between
a Postgres client and the Postgres backend, the
Postgres server.
I've included here an example of what it
looks like to use this library.
You connect to the database by specifying the
database name, your username, your password, potentially the
hostname that you're connecting to.
Once you're connected, you can then execute SQL
statements, for example, to create a new table,
to insert values into that table, and to
count the number of rows in a table.
And pg.el does automatic type conversion to
and from Emacs Lisp types and Postgres types for
you.
Probably the best is to jump straight into
a demo of PGmacs.
When we connect to a database, we see
up the top of the buffer, some information
about the backend that we're connected to, some
version information and the total database size on
disk.
There are some shortcuts to some commands we
can run on the database, and there's a
list of tables that we have access to,
with again, metainformation about their size on
disk and their owner.
Now, we can enter a table by pressing
return.
Here we again see some metainformation about
the table, such as the list of columns,
the SQL type of each column, any defaults
that might be present and any SQL constraints.
We see any indexes that might be present
on this table, and then we see the
list of rows of data in the table.
If we see any information which is incorrect
or incomplete, we can easily fix it by
pressing enter again, and this updates the information
present in the database.
PGmacs shows us this in the minibuffer
it has updated one row.
We can look at help for the key
bindings that are present in this type of
buffer.
If we scroll down, we can see that
we can upcase the value of a cell
with M-u.
Let's try that out.
Okay, here we have updated the value of
the cell in the database.
We can now lowercase it, and we can
put it back to the initial
title case of the value.
We can also run a shell command on
a cell value, to count the number of
characters, for example.
We can run a shell command with a
prefix argument, which updates the value in the
database with the output from the shell command.
So that has updated the database.
If we do that again, that will reverse
the value. If we come back
to the list of tables, we see
that there is a table which is called
deleteme.
We can try renaming this table with R,
we can look at what is in this
table.
Okay, there is only one row of information
that doesn't seem very important, so let's delete
that.
Let's now delete the table with DEL.
PGmacs asks for confirmation, and we've deleted the
table.
Now when we have a big table with
a lot of data, PGmacs is going to
show us the results paginated, so we can
go chunk by chunk through the table.
We can implement a where filter on the
rows to only show the rows that match
a certain SQL clause.
So for example, here we have some temperature
measurements.
We want to focus on measurements that are
more than 40 degrees Celsius, for example.
Okay, we have now filtered on a certain
number of rows.
The filter is shown to us just up
here, and we see here that we have
some values that look anomalous.
There's one, 140, and one is 61.
We probably want to delete them, they represent
errors.
We can mark them for deletion with d,
and then really delete them, expunge them, as
in Dired, with x.
We can export this table in CSV format,
thanks to this little button up here.
Here we have our table conveniently formatted as
CSV.
We can also export a particular row to
JSON by typing j, let's look at
what that looks like.
Okay, here's the JSON for one of the
rows in the table.
Now let's look at a table that contains
a column of type HStore.
This is a Postgres-specific key-value map.
Then the attributes column is of type
JSON, which can be stored natively in Postgres.
It's possible to edit these values using a
widget-based interface by typing w, and here
we have a convenient interface for modifying these
types of values.
Likewise, for the JSON type parameter, we can
update using a widget-based interface.
Let's connect to a different database.
We do that with a M-x pgmacs, and
then enter our username and password.
Here we have the list of tables present
in this database.
PGmacs has some support for running the SchemaSpy
utility, which generates a graphical representation of the
relationships between tables in this database, which can
be useful when you're discovering the database.
Here is the SVG that's output by this
utility.
This is a test database containing information about
a digital media store.
It has a table with information about the
tracks that are available.
If we look at this table, we
see that we have the name of the
track, a reference to the album, album ID.
This is a reference to a foreign key,
a key in the table which is called
album.
Now we can follow this foreign key reference
simply by pressing Enter, and here we find
it's the album called For Those About To
Rock.
And in the same way, here we have
a foreign key reference to the relevant artist
which we can follow.
That's, of course, AC/DC.
And finally, PGmacs has some convenience queries that
allows us to list the procedures which are
defined in this database.
Here we have the built-in procedures, mostly
used by the PostGIS extension.
We can also display some more information about
our backend.
Here we have the list of extensions which
are available.
We can load one of these extensions if
we have the rights to do that.
Here we've loaded the relevant extension.
We can show some information about the Postgres
settings also, and update them if we have
the rights to do that.
Now I'm preaching to the converted, but Emacs
is a really great application development platform.
PGmacs is currently around 3000 lines of code.
A first point of comparison, PGCLI, which is
a text user interface for accessing Postgres implemented
in Python, is 17,000 lines of code.
PGAdmin4, which is a well-known Python GUI
for administrating Postgres databases, is almost half a
million lines of code.
And DBeaver, implemented in Java, is almost a
million lines of code.
Developing things on Emacs, you get for
free portability between different platforms.
As I'm showing you, it works great on
Linux, which is where I developed this library.
It also works perfectly in the terminal, except
for some of the functionality I showed, which
requires SVG support.
It also works well, if you're into that
kind of thing, on MacOS and on Windows.
And it even works perfectly on Haiku, which
is a free BeOS clone.
Emacs is actually really pretty on this operating
system, so congratulations to the people who did
the port there.
Now, of course, the main advantage of building
on the Emacs development platform, is that the
application is easy for the user to extend.
To illustrate that, previously we were looking at
a table of temperature measurements.
Imagine we want to highlight rows in this
table, which look anomalous, where the value looks
a bit extreme.
That's quite easy to do with a bit
of Emacs Lisp.
We define a function, which, if the cell
value is bigger than 40, is going to
display it in a face which has a
dark red foreground.
We can now register this display function for
the measurement column in the temperatures table.
And if we reopen the table now, we
see that the anomalous measurements are indeed highlighted
in red.
Another example, imagine we have a table which
contains image data.
There's a column which is of BYTEA type,
which contains images in binary form.
We can display these inline as follows.
We create an inline image display function, using
Emacs' image support.
And then we'd register this function to display
the image column in the inline image table.
If we now reopen the inline image table,
we see the images are displayed inline.
And as a final example of customisation, here's
how to bind a key to a specific
function, in the row-list buffer.
Let's define a function that does a DuckDuckGo
lookup for a particular value in
the Emacs web browser.
We can define a function which does a
funcall on the cell value for this
DuckDuckGo lookup function.
And finally we can define a key, the
capital D key, in the row-list map,
which calls this function that does a
DuckDuckGo lookup.
And now if I'm browsing information in a
table that I want to do a web
search on, I can use my D shortcut
and see some web results concerning the cell.
So to conclude, the source and installation instructions
for PGmacs are available on GitHub.
It requires Emacs 29.
There is a prebuilt container image which you
can use for testing.
It's a Docker image.
The Docker image only works in terminal mode.
And of course that's a recommended way of
testing Emacs Lisp code that you load
into your Emacs before having read it.
It works with any recent version of Postgres
over the past 10 years, including the latest
release, Postgres 17.
It does work with a certain number of
databases that are compatible with Postgres, such as
ParadeDB, TimescaleDB and IvorySQL.
However, it doesn't work with all databases that
claim to be Postgres compatible.
It doesn't work, for example, with CrateDB or
with CockroachDB or some others that you see
listed here.
The reason for that is that these databases
don't implement the system tables that PGmacs queries
to obtain information about the columns present in
a table, the size on disk, the ownership
of tables, etc.
PGmacs supports TLS encrypted connections to the database,
as well as local Unix socket connections.
And in terms of stability, I would classify
it as beta status.
I do use it myself in production on
some not very important data.
Really, it works quite well.
Thanks for your attention.