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
• Pool cluster with read replica routing
• SSL/TLS support (disable, allow, prefer, require, verify-ca, verify-full)
• MD5, SCRAM-SHA-256 and SCRAM-SHA-256-PLUS authentication
• DSN connection string parsing
• Unix socket connection
• Multi-host failover
• Target session attributes (any, read-write, read-only, primary, standby, prefer-standby)

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 replication with pgoutput decoder

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)
• 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.0

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

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)

Examples

The examples directory contains runnable samples:

• basic_query — Connect, insert, and query rows
• 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