async_postgres

Async PostgreSQL client in Nim.

Features

Protocol & Connection

• PostgreSQL wire protocol v3
• Simple Query and Extended Query Protocol
• Pipeline mode — batch multiple operations in a single network round trip
• Connection pooling with health checks and maintenance (broken connections discarded on acquire/release)
• Pool cluster with read replica routing
• SSL/TLS support (disable, allow, prefer, require, verify-ca, verify-full) with optional client certificate authentication (mTLS)
• sslnegotiation mode (postgres, direct) for Direct SSL connections (PostgreSQL 17+)
• MD5, SCRAM-SHA-256 and SCRAM-SHA-256-PLUS authentication
• channel_binding policy (disable, prefer, require) to harden SCRAM against downgrade
• DSN connection string parsing
• Unix socket connection
• Multi-host failover
• Target session attributes (any, read-write, read-only, primary, standby, prefer-standby)
• load_balance_hosts=random shuffles the configured host list per connection to spread a pool across replicas (reorders the multi-host list only, not multiple addresses behind a single DNS name)

Queries & Statements

• sql macro — compile-time {expr} placeholder extraction with automatic parameterization
• ?-style placeholders (sqlParams) for db_connector compatibility
• Prepared statements with server-side statement cache (LRU eviction)
• Server-side cursors (streaming row chunks)
• Transactions (withTransaction)
• COPY IN / COPY OUT (buffered and streaming)
• Large Object API (streaming binary data)
• LISTEN/NOTIFY with auto-reconnect
• Advisory locks (session/transaction, exclusive/shared)
• Logical and physical replication (pgoutput decoder for logical streams; raw WAL streaming, slot-less or slot-based, with optional TIMELINE for physical)

Types

• Typed parameters (pgParams / toPgParam) and row accessors (getStr, getInt, ...)
• UUID type (PgUuid)
• Numeric type (PgNumeric — arbitrary-precision decimal)
• JSON/JSONB (JsonNode)
• Array types with binary format support
• Range and multirange types (int4range, tsrange, daterange, ...)
• Composite types (user-defined row types via pgComposite macro)
• Enum types (user-defined enums via pgEnum macro)
• Domain types (user-defined domains via pgDomain macro)
• Network types (inet, cidr, macaddr, macaddr8)
• Geometric types (point, line, lseg, box, path, polygon, circle)
• Text search types (tsvector, tsquery)
• Bit string types (bit, varbit)
• Interval type (PgInterval)
• hstore type (PgHstore — extension; requires lookupTypeOids for binary encoding)
• XML type (PgXml)

Parameter values of types built with the pgEnum, pgDomain and pgComposite macros, and Options of them, work with sql"…{v}…" and pgParams(v): both resolve toPgParam at the call site, as a direct toPgParam(v) call there would, so they find the overloads those macros generate. Only pgEnum also generates the seq[T] and seq[Option[T]] forms. Cast the placeholder ($1::mytype) when the client does not know the type's OID. queryDirect / execDirect take their parameters as separate arguments and cannot encode these types.

Performance

• Automatic binary format selection for known-safe types
• Compile-time binary format lookup tables
• Zero-copy binary array decoding
• Optimized protocol encoding with minimized allocations

Observability

• Tracing hooks for connection, query, prepared statement, pipeline, COPY, and pool operations

Platform

• Async backend: asyncdispatch (default) or chronos

Requirements

• Nim >= 2.2.4

• chronos >= 4.4.0 (Only chronos backend)

• nim-bearssl >= 0.2.11 (Only chronos backend; TLS 1.2 only due to BearSSL limitation)

Installation

nimble install async_postgres

Basic Usage

import pkg/async_postgres

proc main() {.async.} =
  let conn = await connect("postgresql://myuser:mypass@127.0.0.1:5432/mydb")
  defer: await conn.close()

  # Insert with typed parameters
  let name = "Alice"
  let age = 30'i32
  let cr = await conn.exec(sql"INSERT INTO users (name, age) VALUES ({name}, {age})")
  echo "Inserted: ", cr.affectedRows

  # Query multiple rows
  let minAge = 25'i32
  let row = await conn.query(sql"SELECT id, name, age FROM users WHERE age > {minAge}")
  for r in row:
    echo r.getStr("name"), " age=", r.getInt("age")

  # Query a single value
  let count = await conn.queryValueOrDefault("SELECT count(*) FROM users", default = "0")
  echo "Total users: ", count

waitFor main()

Reconnection Policy

• Connection pool (PgPool / PgPoolCluster) — broken connections are detected and discarded automatically. On acquire, entries whose state is not csReady (or that fail the optional ping health check) are retired and replaced. On release, connections left in a non-ready or in-transaction state are also closed rather than returned to the idle queue. Configure healthCheckTimeout / pingTimeout to tune idle-connection probing.

• Direct PgConnection — no automatic reconnection for regular queries. Per-query retry would be unsafe for non-idempotent statements and in-flight transactions, so a closed connection must be replaced by calling connect(...) again (or by using the pool). Inspect conn.isConnected() or conn.state to decide whether a handle is still usable.

• Configuration faults — a PgConfigError (a cert, key or CA that will not load, sslcert without sslkey, sslmode=verify-ca without sslrootcert, …) is deliberately not a PgConnectionError. connect(...) raises it in place of the per-host aggregate, so a reconnect-on-PgConnectionError loop never retries a fault no retry can fix. A pool reports it as a PgPoolError of kind pekConfigFault and stops opening connections, since its config cannot change.

• Retry classification — retryAdvice(err) returns raRetry, raStop or raUnclear for any failure; isTransientError(err) is the raRetry case. raRetry does not make replaying safe — a connection lost during COMMIT may have committed — and retries need a cap regardless. See pg_errors.

• Refusals — a server refusing the config itself (28P01, 3D000, 42501 at startup) is raStop. A pool reports it as pekRefused and keeps it in PgPool.connectRefusal, but unlike a config fault keeps dialing, so a server-side fix is picked up. See pg_pool.

• LISTEN/NOTIFY — this is the one exception. The listen pump reconnects with exponential backoff (up to 10 attempts, 30 s cap) and re-subscribes to all channels. A NOTIFY sent while the connection is down — from the drop until the channels are listened to again — is lost, and nothing else reports it, so register a callback with onReconnect to re-read whatever the missed notifications would have announced. The new session is built from the connection's config alone: SET / set_config changes, temporary tables, prepared statements and session advisory locks do not carry over, and a SET ROLE is lost too, so later queries on the connection run with the login role's privileges. Put a setting that must survive in ConnConfig.extraParams (e.g. ("search_path", "app") or ("role", "app_reader"); unknown DSN keys land there too). If reconnection is ultimately exhausted the pump gives up permanently; a refusal or a config fault ends it at once. The failure surfaces as one of two types, chosen by what the recovery is:

  • PgListenError (a PgConnectionError) — the connection itself is gone; recover by re-dialling with connect(...).
  • PgListenStoppedError (a PgStateError, deliberately not a PgConnectionError) — the pump died but the transport is still usable; recover by calling listen again. A reconnect-on-PgConnectionError loop must not re-dial here, which is why this type sits outside that hierarchy.

  Pull-API callers see either type raised from waitNotification, so catch both (or PgError) rather than PgListenError alone. Push-API callers (onNotify) should register onListenError, which always reports a PgListenError; its transportAlive field is true for the death the pull API reports as PgListenStoppedError.

Async Backend

By default, asyncdispatch is used. To use chronos:

# asyncdispatch (default)
nim c your_app.nim
# asyncdispatch with ssl
nim c -d:ssl your_app.nim

# chronos
nim c -d:asyncBackend=chronos your_app.nim

chronos is recommended. chronos supports proper future cancellation, which enables reliable timeout handling and clean connection teardown. asyncdispatch lacks real cancellation — timed-out futures continue running in the background, and cancelAndWait is a no-op.

SSL backend differs by async backend (asyncdispatch: OpenSSL, chronos: BearSSL via nim-bearssl). Without -d:ssl, sslmode=prefer/allow on asyncdispatch connect in plaintext (with a stderr notice only if a client certificate is configured), and settings that need TLS raise PgConfigError. Differences to weigh when choosing a backend:

• chronos negotiates TLS 1.2 only; asyncdispatch also uses 1.3 when offered.
• chronos fails the handshake on an expired or not-yet-valid server certificate even under allow/prefer/require, where asyncdispatch does not check it.
• chronos refuses verify-full to an IP-literal host.
• chronos sends SNI only under verify-full (sslsni has no effect).

The API docs list every TLS difference and the downgrade notices, which go to stderr only (not to PgTracer) and cannot be suppressed.

sslrootcert reaches the trust store only under sslmode=verify-ca / verify-full. Under require (and allow / prefer) the server certificate is accepted without verification even when a CA is configured; libpq instead loads the file whenever it exists and verifies against it even under require (host-name matching still needs verify-full). A DSN's sslrootcert= is read while the DSN is parsed, whatever the mode, so a missing or unreadable file fails there as a PgConfigError — ConnConfig.sslRootCert holds the PEM.

Client certificate authentication (mTLS) is enabled by setting sslCert and sslKey on ConnConfig (or sslcert= / sslkey= in a DSN, which load the files from disk). Both must be provided together, and sslMode must be sslPrefer or stronger (otherwise it is rejected at config time). The key must be an unencrypted PKCS#8, PKCS#1 or SEC1 PEM; no passphrase callback is wired up on either backend. When loaded via DSN on POSIX, the sslkey file must have no group or world permission bits (stricter than libpq's 0o640 for root-owned keys); use chmod 0600.

Direct SSL negotiation (sslnegotiation=direct) requires sslmode=require or stronger.

Examples

The examples directory contains runnable samples:

• basic_query — Connect, insert, and query rows
• query_variants — queryExists / queryValueOrDefault / queryValueOpt / queryRowOpt / queryColumn / queryEach / simpleExec / simpleQuery
• query_direct — Low-allocation queryDirect / execDirect macros for hot paths (parameter encoding is allocation-free)
• prepared_statement — Server-side prepared statements
• transaction — Transaction control with rollback and isolation levels
• cursor — Server-side cursors for streaming large result sets
• pipeline — Batch multiple operations in a single round trip
• copy — Bulk import/export with COPY protocol
• large_object — Large Object API for binary data
• listen_notify — LISTEN/NOTIFY
• pool — Connection pooling
• pool_cluster — Read/write splitting with pool cluster
• advisory_lock — Application-level distributed locking
• replication — Logical replication with pgoutput

Documentation

https://fox0430.github.io/async-postgres/async_postgres.html

License

MIT