PGmacs

An Emacs-based browser and editor for the PostgreSQL database.

PGmacs provides an editing interface for the PostgreSQL 🐘 DBMS from Emacs. It allows you to:

• browse the list of tables in the database

• browse the contents of a table, row by row, in paginated mode for large tables

• edit the value of a column (type RET on the value you want to modify)

• delete a row (type DEL on the row you wish to delete)

• copy/paste rows of a database table (type k to copy, y to paste in a table display buffer)

• export the contents of a table to CSV or TSV format

PGmacs works with Emacs running in a terminal (TUI), or running in your platform’s GUI window system.

Production-ready?

PGmacs is in beta status. As of 2024-07, the author has sufficient confidence in the code to use it to modify real PostgreSQL databases used in production.

Supported versions

Emacs version: PGmacs requires Emacs version 29. It has also been tested on the pre-release v30. It has mostly been tested on Linux, but should work as expected on Microsoft Windows and MacOS. It works both in graphical mode and in the terminal.

PostgreSQL version: PGmacs is mostly tested with PostgreSQL versions 17.0 and 16.4, but should work with any PostgreSQL version supported by the pg-el library that it uses to communicate with PostgreSQL. For example, it works fine with PostgreSQL version 14 which was released in 2021.

PGmacs also works with some databases that implement the PostgreSQL frontend-backend protocol, but not with all of them. PGmacs queries various internal PostgreSQL tables for metainformation on the list of tables available, and these tables are not always present in PostgreSQL-compatible databases. PGmacs also uses some PostgreSQL-specific functions to display information such as the on-disk size of tables, and these functions are not always implemented. What we have tested so far:

• Neon “serverless PostgreSQL” works perfectly.

• ParadeDB v0.9.1 seems to work fine in limited testing (it’s really a PostgreSQL extension rather than a fully separate product).

• The Timescale DB extension for time series data works perfectly (tested with version 2.16.1).

• Xata “serverless PostgreSQL” has many limitations including lack of support for CREATE DATABASE, CREATE COLLATION, for XML processing, for temporary tables, for cursors, for EXPLAIN, for CREATE EXTENSION, for functions such as pg_notify.

• YugabyteDB v2.23 works to a limited extent: we are not able to run the SQL command that adds a PRIMARY KEY to an existing table, nor to display total database size on disk, for example.

• CrateDB v5.8.3 does not currently work; it does not implement PostgreSQL functions that we use to query table metainformation.

• CockroachDB version 24.2 does not work with PGmacs: our query for pg-table-owner triggers an internal error, there is no implementation of the function pg_size_pretty, and the database fails on basic SQL such as the boolean vector syntax b'1001000'.

• QuestDB: tested against version 6.5.4. This has very limited PostgreSQL support, and does not support the integer type for example.

• Google Spanner, or at least the Spanner emulator (that reports itself as PostgreSQL 14.1) and the PGAdapter library that enables support for the PostgreSQL wire protocol, do not work with PGmacs. Spanner has only limited PostgreSQL compatibility, for example refusing to create tables that do not have a primary key. It does not implement some functions we use to query the current user and database status, such as current_user, pg_backend_pid, pg_is_in_recovery.

• YDB by Yandex version 23-4 has very limited PostgreSQL compatibility and does not work with PGmacs. The system tables that we query to obtain the list of tables in the current database are not implemented.

• ClickHouse v24.5 does not work: its implementation of the wire protocol is very limited, with no support for the pg_type metadata and no support for basic PostgreSQL-flavoured SQL commands such as SET.

License

PGmacs is distributed under the terms of the GNU General Public License, version 3.

Copyright 2023-2024 Eric Marsden.

Quickstart

Installation

In your Emacs initialization file, include the following to check out the latest version of the code from the git repository, as well as the dependency pg-el:

;; Requires Emacs 29 and git
(unless (package-installed-p 'pg)
   (package-vc-install "https://github.com/emarsden/pg-el/" nil nil 'pg))
(unless (package-installed-p 'pgmacs)
   (package-vc-install "https://github.com/emarsden/pgmacs/"))

(require 'pgmacs)

You can later upgrade PGmacs to the latest version with M-x package-vc-upgrade RET pgmacs RET.

With use-package: with the vc support in the use-package macro (available from Emacs 29), you can say

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

With quelpa:

(use-package pgmacs
  :ensure nil
  :defer t
  :quelpa (pgmacs :fetcher github :repo "emarsden/pgmacs"))

Manual installation:

Clone this repository, ensure that directory is on your load path, as described on EmacsWiki, and say (require 'pgmacs).

git clone https://github.com/emarsden/pgmacs/

CREATE USER pgmacs_readonly_user WITH PASSWORD 'changeme';
GRANT pg_read_all_data TO pgmacs_readonly_user;

Connecting to a PostgreSQL database

With the pgmacs.el library loaded, say

M-x pgmacs

This will open a widget-based buffer to collect connection information (database name, hostname, port, etc.). It reads initial values for these variables from the following environment variables, if they are set:

• POSTGRES_DATABASE, POSTGRESQL_DATABASE, POSTGRES_DB, PGDATABASE
• POSTGRES_HOSTNAME, PGHOST
• POSTGRES_PORT_NUMBER, POSTGRESQL_PORT_NUMBER, PGPORT
• POSTGRES_USER, POSTGRESQL_USERNAME, PGUSER
• POSTGRES_PASSWORD, POSTGRESQL_PASSWORD, PGPASSWORD

These semi-standardized variable names are used by the official Docker image for PostgreSQL, the more sophisticated Bitnami PostgreSQL image) and by the official psql PostgreSQL client.

It will then open the main PGmacs table-list buffer, which will show you a list of the tables available in the database.

You can also open PGmacs with a PostgreSQL connection string

M-x pgmacs-open-string RET user=myself port=5432 dbname=mydb

or with a PostgreSQL connection URI

M-x pgmacs-open-uri RET postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

or with a PostgreSQL connection object from the pg.el library, using function pgmacs-open.

Prebuilt container image

If you want safely to get a quick feel for what PGmacs can do before checking the source code and installing it, you can try out our prebuilt Podman/Docker container image and run PGmacs in terminal mode. It includes Emacs, PGmacs and the necessary dependencies preinstalled.

You will need either Podman (free software that’s available for Linux, Microsoft Windows and MacOS) or Docker installed.

podman run --network host -ti ghcr.io/emarsden/pgmacs:latest

The container image is based on a lightweight Alpine Linux image, and is rebuilt for each new PGmacs release. It’s built for the following platforms:

• Linux/AMD64
• Linux/Aarch64 (64-bit ARM)
• Linux/armv7 (32-bit ARM)
• Linux/riscv64

Note that you need to run the container with --network host or a similar commandline option that allows network access to the PostgreSQL database. Since this container runs Emacs in terminal mode, rather than GUI mode, some PGmacs functionality is not available (in particular, the SchemaSpy support).

The table list buffer

The table list buffer is the main PGmacs buffer. It shows some metainformation concerning the PostgreSQL backend that you are connected to (version, database size on disk, etc.), followed by a tabulated list of all the tables in the database (at least the tables which are visible to your current PostgreSQL user).

The following keys are bound when the point is located in the table list buffer:

The More backend information button will open a buffer that displays further information concerning the PostgreSQL backend that you are connected to, including the list of available extensions.

Displaying a database table: the row-list buffer

A row-list buffer shows some metainformation on the table and its columns, then the row data in tabular form, as illustrated below.

If the table contains a large number of rows, the contents will be paginated, with Next and Previous buttons to move page by page. The number of rows in each page is determined by the variable pgmacs-row-limit.

A column which is a part of a primary key will be displayed using the customizable pgmacs-column-primary-key face, which defaults to a bold version of the pgmacs-table-data face. A column which references a foreign key will be displayed using the pgmacs-column-foreign-key face, which defaults to a blue color.

The following keys are bound when the point is located in the row-list table:

Run a shell command or an external application on cell value

There are two methods for running an external (non-Emacs) command on the current cell value:

• Run a Unix filter shell command with the cell value as input: press !. This works similarly to the standard Emacs shell-command command, which is bound to M-!. If called with a prefix argument, it will update the database value to the result of the shell command.

• Run a program asynchronously with the cell value as its first commandline argument: press &. This works similarly to the standard Emacs async-shell-command, which is bound to M-&.

For example, to count the number of characters in the current cell, type

! wc -c

To downcase the value of a text cell (and modify the value in the database) use

C-u ! tr '[:upper:]' '[:lower]'

To reverse the order of the characters in the cell (and modify the value in the database), use

C-u ! rev

On a Microsoft Windows machine, you may need to install the MSYS2 commandline tools for these examples to work.

If the current cell contains a file name, you can launch your system’s default application for that filename extension by typing & then entering xdg-open (or open on a MacOS machine).

Follow foreign key references

A column that references data in a foreign table (FOREIGN KEY) will be shown in blue. If you type RET when point is located on a foreign key reference, PGmacs will jump to the referenced row and column in the other table. A new row-list buffer is opened; type q to come back to the original row-list buffer.

Filtering rows displayed using a WHERE expression

You may wish only to display the rows in a table that match an SQL WHERE filter. If you press W in a row-list buffer, PGmacs will prompt you for a WHERE expression to use as a filter. In the example illustrated by the video below, table temperatures includes a column measurement. If you type a WHERE filter of measurement < -5, PGmacs will display rows corresponding to the following SQL query:

SELECT * FROM temperatures WHERE measurement < -5

You can type W again to enter a new filter. To cancel the filter, type W and enter an empty string (enter W RET).

The screen recording below illustrates the use of a WHERE filter on a large table that contains various attributes concerning French inhabited locations. It starts by using a WHERE filter to display only the row corresponding to the city of Toulouse based on its zip code, copies that row as JSON with j, then with the copied latitude and longitude prepares a PostGIS query to select (with an updated WHERE filter) rows that are within a certain geographic distance of Toulouse, using the PostGIS function ST_DistanceSphere.

Dired-like multi-row delete

You can “mark” rows for later deletion by pressing d. Each row marked for deletion will be highlighted in a red color. You can then delete the marked rows by pressing x (for “expunge”, as in dired-mode). To unmark a row, press u.

The screen capture below illustrates this functionality in operation, combined with the WHERE filtering described above.

Editing data in a PostgreSQL table

Editing takes place in a row-list buffer that displays information from a particular database table.

Editing a column value

If your table has a primary key, you can edit the contents of the table. To modify a value, move the cursor to the relevant column value and type RET. This will prompt you for the new value, and immediately update the cell to the value you specified (it sends PostgreSQL an SQL command similar to UPDATE table_name SET column_name to X WHERE pk_col1 = value1 AND pk_col2 = value2, where pk_col1 and pk_col2 are the names of the columns that comprise the primary key constraint). Please note that there is no undo support for editing operations; you’ll have to re-execute an update operation.

Note that PGmacs tells you the column type when prompting for the new value. You must specify a value in the format accepted by PostgreSQL for that type (check the current value as displayed in the minibuffer if you’re unsure of the expected format).

For certain column types and very long column values, it may be more convenient to use the widget-based editing interface to edit a column value. Move the cursor to the relevant column value and type w. This will open a dedicated buffer with an editing widget suitable for that column’s type, as illustrated below for an HSTORE key->value map.

If you wish to abort editing, simply kill this editing buffer.

Inserting, copying and deleting rows

To insert a new row into a table, press + in the row-list buffer. You will be prompted for the values of each column for which a default value is not specified (in the minibuffer), then the new row will be inserted. You can also insert a new row by entering new values in a widget-based buffer by pressing i (this may be more convenient if the table contains many rows, or the values to enter are very long).

To delete the row at point, press Del or Backspace in a table buffer and confirm. Please note that this deletes the current row in the PostgreSQL database, as well as in the Emacs buffer. Deleting rows is only possible on tables that include a primary key constraint (this can be a single column specified as PRIMARY KEY, or a constraint across a group of columns).

To copy/paste rows, press k to copy the row to the PGmacs kill buffer (this only copies, without deleting the row), then y to insert a new row with the same values. Any columns that have a default value specified (for example, primary key rows that pull a value from an integer sequence, or are specified as SERIAL, or timestamp values that default to now) will be inserted with a new generated value, rather than the value in the copied row.

All updates, insertions and deletions are immediately synced with the PostgreSQL server by sending it the appropriate SQL UPDATE TABLE, DELETE FROM or INSERT INTO commands.

Running a shell command on a cell value

Typing ! in a row-list buffer runs a shell-command on the current cell value, and displays the output in the echo area. The cell value is sent as standard input to the shell command, as for the Emacs function shell-command-on-region. If called with a prefix argument, the current cell value is replaced by the output of the cell command, and the PostgreSQL database is updated.

For example, to count the length in characters of the current cell value (or of its displayed representation, when it is not a text field), type

! wc -c

To reverse the characters in the current cell, and also update the database:

C-u ! rev

To downcase the characters in the current cell, and also update the database:

C-u ! tr '[:upper:]' '[:lower:']

Running SQL queries

You can also view (but obviously not edit!) the output from an SQL query you enter. Type e in a PGmacs buffer, which will prompt you for an SQL query in the minibuffer, then display the output in a dedicated temporary buffer. Type q to kill the temporary buffer.

Likewise, PGmacs can display the output from an SQL query in an Emacs buffer. Edit your SQL query in a dedicated buffer, that you set to sql-mode or your favorite alternative. In the main PGmacs table-list buffer or a row-list buffer, type E and PGmacs will prompt for the buffer name, then display the query output in a dedicated temporary buffer.

Running SchemaSpy

The SchemaSpy application is able to generate useful illustrations documenting the tables present in a database, the links between them (entity relationship diagrams) and their schema structure. PGmacs includes functionality to run SchemaSpy on the database and table you are viewing, and display the relevant images in an Emacs buffer. This functionality only works in graphical mode (not in the terminal) and requires your Emacs to support SVG images; PGmacs will inform you if these conditions are not met.

SchemaSpy is free software distributed under the terms of the GNU LGPL version 3.

In the main table-list buffer, type S to run SchemaSpy on the current database and display the relationships between the tables in a dedicated buffer.

In a row-list buffer, type S to run SchemaSpy the on the current table and view the table structure diagram in a dedicated buffer.

See the customizable variable pgmacs-schemaspy-cmdline to adjust the commandline which runs SchemaSpy to your local installation.

Running in a software container (Podman/Docker)

The default setting for pgmacs-schemaspy-cmdline runs SchemaSpy in a prebuilt Docker/Podman software container. You will need to have Podman or Docker installed (we recommend Podman, because it’s fully free software and it runs well in rootless mode, which is better for security). This is the easiest way of running SchemaSpy, because all necessary dependencies are preinstalled. The container image is around 380MB in size.

podman run -v %D:/output --network=host docker.io/schemaspy/schemaspy:latest -t pgsql11 -host %h
-port %P -u %u -p %p -db %d -imageformat svg

Some notes on customizing this commandline:

• Replace podman by docker if that is your preference.

• The official SchemaSpy container docker.io/schemaspy/schemaspy will need network access to the host where PostgreSQL is running. (In its default configuration, SchemaSpy is not able to connect to PostgreSQL over a Unix socket.) This will require a Podman setting such as --network=host.

• In this commandline, %d is replaced by the database name, %h by the hostname on which PostgreSQL is running, %P by the port it is running on, %u by the user, %p by the PostgreSQL password for that user, %s by the current table schema name, %t by the current table name and %D by the directory (which will be created in the system temporary directory) in which output files are created by SchemaSpy. The %s and %t values are only used in the table view, and not in the database view, and are added automatically to the commandline in the form

  -s %s -i %t
  

Running SchemaSpy installed locally

You can also run the SchemaSpy java application natively, using a setting for pgmacs-schemaspy-cmdline similar to the following:

java -jar ~/lib/schemaspy.jar -dp /usr/share/java/postgresql-jdbc4.jar -t pgsql11 -host %h -port %P -u %u -p %p -db %d -imageformat svg -o /tmp/schema

This requires the following software to be installed:

• SchemaSpy, in this example installed to ~/lib/schemaspy.jar

• Java (available as java here)

• GraphViz, installable on Debian using sudo apt install graphviz and on Microsoft Windows using choco install graphviz for example.

• JDBC support for PostgreSQL, here installed in /usr/share/java/postgresql-jdbc4.jar, installable on Debian for example using sudo apt install libpostgresql-jdbc-java

Extending and customizing PGmacs

A big advantage of building on the Emacs platform is that PGmacs is automatically quite easy to extend and customize. You can extend it using the same programming language (Emacs Lisp) and APIs that are used to build PGmacs itself.

Here’s a simple example where we add a key binding that applies in row-list buffers to implement a web search on the content of the current cell (where the cursor is located). This is easy to implement using the function pgmacs-funcall-cell, which calls an Emacs Lisp function on the content of the current cell.

We define a function that executes a web search, here using the EWW browser built into Emacs with the “html” interface of the DuckDuckGo search engine.

(defun my/ddg-query (q)
  (eww-browse-url (format "http://ddg.gg/html/?%s"
                          (url-build-query-string `(("q" ,q))))))

Then we define a function that calls our ddg-query function on the current cell contents, and define a keybinding for it in the pgmacs-row-list-map keymap which is active in a row-list buffer.

(require 'pgmacs)

(defun my/pgmacs-ddg-cell ()
  (interactive)
  (pgmacs-funcall-cell #'my/ddg-query))

(define-key pgmacs-row-list-map (kbd "D") #'my/pgmacs-ddg-cell)

This is what the functionality looks like once you’ve included this code in your Emacs initialization file:

Customizing display of specific table columns

The function pgmacs-register-column-displayer allows you to register a dedicated display function for a specific column in a particular table. It takes the following arguments:

• a table name (potentially schema-qualified)

• a column name

• a display function, which takes three arguments: the cell value, the cell max-width and the current table.

Illustration: display BYTEA images inline

As an illustration, below we register a column display function for a column that contains images stored in the database as BYTEA. The display function displays the images inline in the row-list.

Suppose we have the following SQL schema:

CREATE TABLE inlineimg(
  id SERIAL PRIMARY KEY,
  image BYTEA,
  created TIMESTAMP DEFAULT now())

Here is some sample Emacs Lisp code to insert some example rows in the table, using the pg-el library:

(dotimes (i 20)
   (let* ((img (with-temp-buffer
                 (url-insert-file-contents "https://picsum.photos/120/40")
                    (buffer-string)))
          (res (pg-exec-prepared con "INSERT INTO inlineimg(image) VALUES($1)"
                                 `((,img . "bytea")))))
     (message "Inserted image: %s" (pg-result res :status))))

and here is how to defined a custom display function that displays each image inline:

(defun my-inline-image-displayer (value _max-width _table)
  (let* ((img (create-image value nil t))
         (txt (propertize " " 'display img 'rear-nonsticky t)))
    (or txt "<invalid img>")))

(pgmacs-register-column-displayer "inlineimg" "image" #'my-inline-image-displayer)

and finally, this is what the row-list buffer for that table looks once the display function has been registered:

Illustration: highlight anomalous values in a column

A second illustration of the use of this function to highlight values in a column that are higher than a particular threshold by displaying them in a particular color. Suppose we have the following SQL schema:

CREATE TABLE temperatures(
  id SERIAL PRIMARY KEY,
  measurement FLOAT,
  created TIMESTAMP DEFAULT now())

Some Emacs Lisp code to insert fake measurement data in the table, using the pg-el library:

(dotimes (i 500)
   (let* ((rnd (* 0.5 (1+ (/ (random) (float most-positive-fixnum)))))
          (temp (+ -8 (* rnd 50))))
      (pg-exec-prepared con "INSERT INTO temperatures(measurement) VALUES ($1)"
                        `((,temp . "float")))))))

A custom display function that sets the foreground color for values above 40 to dark

(defun my-highlight-anomaly (value _max-width _table)
  (if (> (string-to-number value) 40.0)
      (propertize value 'face '(:foreground "darkred"))
    value))

(pgmacs-register-column-displayer "temperatures" "measurement" #'my-highlight-anomaly)

Your feedback

Bug reports should be filed as issues on our GitHub project page.

Patches and pull requests are also welcome!