pgsql.el

pgsql.el is a native PostgreSQL wire-protocol client written in Emacs Lisp. It is intended to provide a small, public protocol API without an external psql process, JDBC bridge, or user-interface dependency.

Status

The focused 0.1 protocol surface is released and covered by deterministic transcript tests, PostgreSQL 16 integration tests, and a self-contained real-TLS suite. It is also exercised by Clutch’s native PostgreSQL adapter. The API remains pre-1.0 and may evolve as additional callers establish concrete requirements.

Goals

The initial release is focused on the protocol surface needed by database clients:

• TCP and PostgreSQL SSL negotiation
• clear-text, MD5, and SCRAM-SHA-256 authentication
• simple and parameterized query execution
• structured result and error data
• an explicit SQL NULL representation
• transaction status reported by ReadyForQuery
• timeouts and PostgreSQL query cancellation

The package is protocol-only. Query consoles, result rendering, completion, schema browsers, saved connections, SQL rewriting, connection pooling, and automatic SQL replay belong to applications built on top of it.

Relationship to pg.el

MELPA already provides pg.el, a mature and broad PostgreSQL client with COPY support, asynchronous idle LISTEN/NOTIFY handling, Unix-domain sockets, extensible type decoding, and a large server-compatibility matrix. pgsql.el is not a fork or drop-in replacement and does not try to match that feature breadth; applications that need those capabilities should use pg.el.

pgsql.el provides a narrower protocol-library boundary for synchronous application clients: no third-party runtime dependency, opaque connection and result values, one request in flight, completion only after the matching ReadyForQuery has been consumed, authoritative public transaction state, a dedicated SQL NULL value distinct from PostgreSQL false, and bounded cancellation that drains the original connection before reuse. Those lifecycle guarantees let callers such as Clutch depend on public protocol state without reaching into transport internals or maintaining caller-side synchronization workarounds.

Requirements

• Emacs 29.1 or newer
• PostgreSQL is required only for the live test suite

The package has no third-party runtime dependency.

Installation

pgsql is available from MELPA. After configuring MELPA, install it with M-x package-install RET pgsql RET, or declare it with use-package:

(use-package pgsql
  :ensure t)

For a source checkout, clone the repository and add it to load-path:

(add-to-list 'load-path "/path/to/pgsql.el")
(require 'pgsql)

Usage

Connect with keyword arguments and always close the connection when its owner is finished:

(setq connection
      (pgsql-connect :host "127.0.0.1"
                     :port 5432
                     :user "app"
                     :password "secret"
                     :database "app"
                     :sslmode 'prefer))

(unwind-protect
    (pgsql-result-rows
     (pgsql-exec connection "SELECT current_database()"))
  (pgsql-disconnect connection))

pgsql-connect supports disable, prefer, require, and verify-full SSL modes. prefer falls back to plaintext only when the server explicitly rejects the PostgreSQL SSL request; TLS handshake and certificate failures are never hidden. SCRAM-SHA-256 passwords use PostgreSQL-compatible SASLprep, including PostgreSQL’s raw-password fallback for input that cannot be prepared.

The connection object is opaque. Use pgsql-user, pgsql-host, pgsql-port, pgsql-database, pgsql-live-p, pgsql-busy-p, pgsql-transaction-status, and pgsql-parameter instead of depending on transport state. pgsql-connect-timeout bounds the complete TCP, TLS, and authentication sequence; pgsql-read-timeout is an idle timeout and restarts whenever a fragmented response makes progress. pgsql-set-connect-timeout changes the bound used by later auxiliary cancellation connections, while pgsql-set-read-timeout changes the response idle timeout.

Use pgsql-exec-params for an extended-protocol query. Each parameter is a cons of its value and optional PostgreSQL type name. pgsql-null represents SQL NULL, while Lisp nil represents PostgreSQL boolean false:

(pgsql-exec-params
 connection
 "SELECT $1::int4, $2::text, $3::boolean, $4::int4[]"
 (list (cons 42 "int4")
       (cons pgsql-null "text")
       (cons nil "bool")
       (cons (vector 1 pgsql-null 3) "int4[]")))

Recognized built-in type names are sent as Parse OIDs. Unknown, extension, and domain type names remain valid local encoding hints while PostgreSQL infers their type from the SQL context.

Both query functions consume the complete response through ReadyForQuery before returning or signaling. pgsql-transaction-status reports idle, in-transaction, or failed-transaction from that server message. A structured pgsql-server-error retains fields such as :sqlstate, :detail, and :constraint through pgsql-error-fields, and the same connection remains reusable after a synchronized server error.

NotificationResponse messages encountered while reading a response are delivered through pgsql-notification-functions with the connection and a plist containing :pid, :channel, and :payload. The synchronous client does not poll or dispatch messages while a connection is idle.

Results expose pgsql-result-columns, pgsql-result-rows, pgsql-result-command-tag, and pgsql-result-affected-rows. Column metadata is read through pgsql-column-name, pgsql-column-type-oid, and pgsql-column-type-name. Core scalar and array values are decoded from PostgreSQL text format; binary result columns are rejected explicitly rather than guessed. Unknown types remain UTF-8 strings with their OID preserved in the column metadata. Arbitrary-precision numeric and wall-clock date=/=timestamp values remain exact text, while timestamptz becomes an Emacs time value. bytea parameters and results use unibyte strings and accept either PostgreSQL hex or escape output.

pgsql-cancel sends PostgreSQL’s CancelRequest over a separate short-lived connection. The original query path still consumes the cancellation error and its final ReadyForQuery before it becomes reusable. A keyboard quit during pgsql-exec or pgsql-exec-params performs this cancellation and drain internally before re-signaling quit; if synchronization fails, the connection is closed instead of being returned in an unknown state.

Public API

Connections and results are opaque values. Callers use the public symbols below and must not inspect their representation or call pgsql-- symbols.

• Connection lifecycle: pgsql-connect, pgsql-disconnect, and pgsql-connection-p. pgsql-connect accepts :database, :user, :password, :host, :port, :sslmode, :connect-timeout, :read-timeout, and :application-name.
• Connection defaults and bounds: pgsql-connect-timeout, pgsql-read-timeout, pgsql-sslmode, pgsql-application-name, and pgsql-max-message-bytes. pgsql-set-connect-timeout and pgsql-set-read-timeout change the corresponding bounds on an existing connection.
• Connection state and metadata: pgsql-live-p, pgsql-busy-p, pgsql-transaction-status, pgsql-parameter, pgsql-user, pgsql-host, pgsql-port, and pgsql-database.
• Execution and cancellation: pgsql-exec, pgsql-exec-params, and pgsql-cancel.
• Results: pgsql-result-p, pgsql-result-columns, pgsql-result-rows, pgsql-result-command-tag, and pgsql-result-affected-rows.
• Column and type metadata: pgsql-column-name, pgsql-column-type-oid, pgsql-column-type-name, and pgsql-type-name.
• Values and SQL quoting: pgsql-null and pgsql-null-p distinguish SQL NULL; pgsql-array-literal encodes typed arrays; pgsql-escape-identifier and pgsql-escape-literal quote SQL identifiers and string literals.
• Diagnostics and asynchronous server messages: pgsql-error is the root condition for pgsql-connection-error, pgsql-timeout, pgsql-protocol-error, pgsql-authentication-error, and pgsql-server-error. pgsql-error-fields returns structured server fields, and functions in pgsql-notice-functions and pgsql-notification-functions receive a connection plus the corresponding structured plist.

Development

Run the complete non-live gate with:

./test/run-ci.sh all

Run the PostgreSQL 16 live suite with a test server and credentials:

PGSQL_TEST_HOST=127.0.0.1 \
PGSQL_TEST_PORT=5432 \
PGSQL_TEST_USER=pgsql \
PGSQL_TEST_PASSWORD=pgsql \
PGSQL_TEST_DATABASE=pgsql_test \
./test/run-ci.sh live

The live suite uses the named test database and creates then drops one uniquely named login role to verify non-ASCII SCRAM authentication, so its configured user needs CREATEROLE. It never drops or alters a pre-existing role. Unit tests do not require a running PostgreSQL server.

Run the self-contained TLS suite when changing SSL negotiation or certificate verification. It starts one temporary PostgreSQL 16 container with an ephemeral CA and removes all generated state on exit:

./test/run-tls-live-tests.sh

License

pgsql.el is released under the GNU General Public License, version 3 or later.