PGmacs -- Emacs editing PostgreSQL databases

PGmacs provides an editing interface for the PostgreSQL 🐘 object-relational 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, and navigate between tables if they are linked by a foreign key

• edit the value of a column: type RET on the value you want to modify to edit the value in the minibuffer, or type w to edit the value in a widget-based buffer

• insert new rows into the table: type + in a table buffer to insert a row with new values obtained from the minibuffer, or type i to insert a new row with values obtained from a dedicated widget-based buffer.

• delete a row: type DEL in a table buffer to delete the row at point

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

• save the contents of a table in CSV or TSV format, or the contents of a row in JSON format

It works both in the terminal (TUI) and in GUI mode.

📖 You may be interested in the user manual.

Getting started

Tip

If you want to get a quick feel for what PGmacs can do before installing it, you can try out our prebuilt Podman/Docker container image which includes a terminal-only build of Emacs and the necessary dependencies.

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 pg-el dependency:

;; 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 these to the latest version with M-x package-vc-upgrade RET pgmacs RET. See the user manual for more installation methods (for example using use-package).

To load PGmacs, say

M-x pgmacs

which will open a widget-based buffer to collect connection information (database name, hostname, port, etc.). It will then open the PGmacs main 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 connection object from the pg.el library (function pgmacs-open). Check the user manual for more.

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 platforms

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 primarily tested with PostgreSQL versions 17.2 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, more or less, with some “PostgreSQL-compatible” databases. There are four main points where this compatibility may be problematic:

• Compatibility with the PostgreSQL wire protocol. This is the most basic form of compatibility.

• Compatibility with the PostgreSQL flavour of SQL, such as row expressions, non-standard functions such as CHR, data types such as BIT, VARBIT, JSON and JSONB, user-defined ENUMS and so on, functionality such as LISTEN. Some databases that claim to be “Postgres compatible” don’t even support foreign keys, views, triggers, sequences, tablespaces and temporary tables (looking at you, Amazon Aurora DSQL).

• Implementation of the system tables that are used by PGmacs to retrieve the list of tables in a database, their on-disk size, column metainformation, and the list of indexes present.

• Establishing encrypted TLS connection to hosted services. Most PostgreSQL client libraries (in particular the official client library libpq) use OpenSSL for TLS support, whereas Emacs uses GnuTLS, and you may encounter incompatibilities.

The following PostgreSQL-compatible databases have been tested:

• Neon “serverless PostgreSQL” works perfectly.

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

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

• The IvorySQL Oracle-compatible flavour of PostgreSQL works perfectly (last tested with version 3.4).

• The PgBouncer connection pooler for PostgreSQL works fine (last tested with version 1.23 in the default session pooling mode).

• 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 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. It does support some extensions such as pgvector, for example. Last tested with v2.23.

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

• CockroachDB 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'. Lasted tested with version 24.3.

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

• 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 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. Lasted tested with version 23-4.

• ClickHouse 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. Last tested with v24.5.

• Hosted PostgreSQL services that have been tested: Railway.app is running a Debian build of PostgreSQL 16.4, and works fine; Aiven.io is running a Red Hat build of PostgreSQL 16.4 on Linux/Aarch64 and works fine.

• Untested but likely to work: Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, Amazon Auroa, Google AlloyDB, Materialize. You may however encounter difficulties with TLS connections, as noted above.

License

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

Copyright 2023-2024 Eric Marsden.