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)

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.
• 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. Register a reconnectCallback if you need to resynchronise application state after a reconnect. If reconnection is ultimately exhausted the pump gives up permanently: pull-API callers see the failure raised from waitNotification, and push-API callers (onNotify) should register onListenError to be told the pump is gone.

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 (requires -d:ssl)
• chronos: BearSSL (via nim-bearssl; TLS 1.2 only due to BearSSL limitation)

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 (sslcert/sslkey paired with sslDisable or sslAllow is rejected at config time because TLS would not be negotiated). The private key must be unencrypted on both backends — neither chronos (BearSSL) nor asyncdispatch (OpenSSL) is wired to a passphrase callback. On chronos the key specifically must be PKCS#8 PEM (RSA or EC); PKCS#1 is not supported. When loaded via DSN on POSIX, the sslkey file must not have any group or world permission bits set. This is intentionally stricter than libpq (which permits owner-group-read 0o640 for root-owned keys); tighten offending keys with chmod 0600 (or 0400).

Direct SSL negotiation (sslnegotiation=direct) requires sslmode=require or stronger. On the chronos backend it needs chronos >= 4.4.0 for ALPN support.

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 — Zero-allocation queryDirect / execDirect macros for hot paths
• 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