Async PostgreSQL client for Nim.

Implements the PostgreSQL wire protocol v3 with full support for the extended query protocol, connection pooling, SSL/TLS, and binary format optimization.

Async Backend

Select at compile time with -d:asyncBackend=asyncdispatch (default) or -d:asyncBackend=chronos.

TLS backend differences

asyncdispatch uses OpenSSL (-d:ssl) and chronos uses BearSSL (via nim-bearssl).

Behavior	asyncdispatch (OpenSSL)	chronos (BearSSL)
Negotiated TLS	1.2 minimum, 1.3 when offered	Pinned to 1.2
Expired/not-yet-valid cert	Accepted (not verified) under allow/prefer/require	Handshake fails with PgConnectionError
IP host, verify-full	Verifies IP SANs (PgSecurityError if OpenSSL lacks SSL_get0_param or X509_VERIFY_PARAM_set1_ip_asc)	Refused with PgSecurityError (dNSName SAN only)
SNI	Every sslmode (except IP/empty host, a host name of 256 bytes or more, or sslsni=0)	verify-full only; sslsni has no effect
Host name >= 256 bytes	Under verify-full: verified; the ClientHello carries no SNI, with no notice	Under verify-full: refused with PgSecurityError
TRUSTED CERTIFICATE	In sslrootcert (openssl x509 -trustout): aux trust settings honoured	Skipped, so the CA is not trusted (PgConfigError if no other CA is left); export CAs as plain CERTIFICATE
Root CA without CA:TRUE	Accepted as a CA with keyUsage keyCertSign; a bare root and CA:FALSE are refused as a CA	Refused as a CA with PgSecurityError
Client cert, key, root CA	Staged as temp files (owner-only on POSIX, /dev/shm preferred on Linux), deleted once loaded	Decoded in memory
ALPN postgresql	Sent unless libssl lacks SSL_CTX_set_alpn_protos (then traditional mode sends none and direct mode fails); if that call errors, the connect fails in either mode	Always sent

A certificate refused as a CA (CA:FALSE, or a root OpenSSL rates not CA-capable) is still usable as a direct pin: with the server's own certificate in sslrootcert, both backends verify it — BearSSL matches the DN and public key, whatever the CA flag says.

Both backends trust only the pinned sslrootcert (no system store), enforce ALPN for sslnegotiation=direct only, and drop from SCRAM-SHA-256-PLUS to SCRAM-SHA-256 under channel_binding=prefer when the server certificate is unavailable (require fails the connect on both).

sslrootcert reaches the trust store only under verify-ca / verify-full; under require (and allow / prefer) the server certificate is accepted unverified even when a CA is configured (libpq loads the file whenever it exists and verifies against it even under require). A DSN's sslrootcert= is read while the DSN is parsed, whatever the mode, so a missing or unreadable file is a PgConfigError.

Downgrade and fallback notices

These go to stderr only, one pg_connection: line per event:

• the sslmode=prefer plaintext fallback after the server refuses SSL
• a client certificate that will not be sent (no TLS support, a Unix-socket connection, or that plaintext fallback, which then writes two lines)
• channel_binding=prefer passing over an offered SCRAM-SHA-256-PLUS for lack of a server certificate (written once startup completes)
• temp-file cleanup failures

They are not reported to PgTracer and cannot be hooked, filtered, or suppressed; a notification hook is future work.

Quick Start

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()

Choosing a query API

Four parameterised entry points are provided. They differ in how parameters are supplied and in the amount of per-call allocation:

1. sql"..." macro — compile-time {expr} interpolation

Most readable. {expr} placeholders are rewritten to $1, $2, … at compile time and the expressions are collected into a seq[PgParam]. Works with query, exec, pool.query, cluster.withReadConnection, etc.

let name = "Alice"
await conn.query(sql"SELECT id FROM users WHERE name = {name}")

• Pros: concise; compile-time placeholder rewriting; SQL injection-safe.
• Cons: still allocates a seq[PgParam] per call; {expr} must be a compile-time-visible expression (not an openArray spread).
• Use when: SQL is a literal and ergonomics matter more than zero-alloc.

2. query/exec with an explicit seq[PgParam]

The baseline runtime API. Use when the SQL or the parameter list is constructed dynamically (e.g. conditional WHERE clauses).

var params: seq[PgParam]
params.add name.toPgParam
params.add age.toPgParam
await conn.query("SELECT id FROM users WHERE name = $1 AND age > $2", params)

The pgParams(a, b, c) macro builds a seq[PgParam] in one call. A second overload takes seq[PgParamInline] — constructed manually as @[toPgParamInline(a), toPgParamInline(b)] — which avoids per-parameter heap allocations for scalar types.

3. queryDirect/execDirect — low-allocation macros

Encodes parameters directly into the connection's send buffer at compile time; no intermediate seq[PgParam] or seq[byte] is built.

let qr = await conn.queryDirect("SELECT name FROM users WHERE id = $1", myId)

• Pros: no per-call allocations for the parameter path (building the QueryResult still allocates); same statement cache semantics as query.
• Cons: SQL must be a string literal/compile-time constant; arguments are positional ($1, $2, …), no {expr} sugar.
• Use when: the call site is on a hot path and params are scalars.

4. simpleQuery/simpleExec — simple query protocol

Parameter-less, text-only, single round trip. Allows multiple ;-separated statements and session-only commands that the extended protocol rejects (SET, LISTEN, VACUUM, …).

discard await conn.simpleExec("SET search_path TO myschema, public")

Quick decision table

Situation	Prefer
Literal SQL, readability	sql"..." macro
Dynamic SQL or params	query(sql, params) / exec
Hot path, scalar params	queryDirect / execDirect
SET / multi-statement	simpleQuery / simpleExec

sql"...", query/exec, and queryDirect/execDirect share the per-connection prepared-statement cache; simpleQuery/simpleExec use the simple protocol and are not cached. A timeout parameter is accepted by query/exec, queryDirect/execDirect, and simpleQuery/simpleExec; on timeout the connection is retired (csClosed) because the wire protocol desynchronises, unless the wire had settled (asyncdispatch always retires: the timed-out operation stays on the socket).

Client encoding

client_encoding is pinned to UTF8. A non-UTF8 value in the config (including -c client_encoding= in options) raises PgConfigError, and a session reporting a switch away (SET client_encoding) is closed with PgProtocolError. The switch is detected only after the fact, so do not change it within a query or pipeline.

Modules

• pg_connection — Connection management, DSN parsing, SSL, LISTEN/NOTIFY
• pg_client — Query execution, prepared statements, cursors, pipelines, transactions, COPY, low-alloc macros (queryDirect/execDirect)
• pg_pool — Connection pooling with health checks and maintenance
• pg_pool_cluster — Read replica pool cluster with automatic query routing
• pg_types — Type conversions (toPgParam, row accessors, arrays, ranges, composites, enums)
• pg_protocol — Wire protocol encoding/decoding
• pg_largeobject — Large Object API for streaming binary data
• pg_advisory_lock — Advisory lock API (session/transaction, exclusive/shared)
• pg_replication — Logical replication streaming with pgoutput decoder
• async_backend — Async framework abstraction (asyncdispatch / chronos)

Supported imports are pkg/async_postgres and the hub modules listed above. Importing a submodule directly (e.g. async_postgres/pg_connection/types) is unsupported and carries no compatibility guarantee; only the re-exported surface is covered by semantic versioning. Promised vs sibling * exports are frozen in tests/api_surface.{public,internal}.golden.