postgres

June 1, 2026 · View on GitHub

An enterprise-grade PostgreSQL client library for the Zuri programming language, implementing the PostgreSQL Frontend/Backend wire protocol (version 3.0) in pure Zuri with full binary type encoding and decoding.

Features

  • Single connections via Connection for straightforward use-cases.
  • Connection pooling via Pool for high-throughput applications.
  • Full binary wire protocol for all built-in PostgreSQL data types.
  • Named prepared statements for optimised, repeatable query execution.
  • Doubly-linked cache cursors for memory-efficient result streaming.
  • LISTEN / NOTIFY for real-time async notifications.
  • Extensible type system — register custom OID ↔ Zuri type mappings.
  • Array support — multidimensional arrays with fast binary encode/decode.
  • SASL / MD5 / clear-text authentication.
  • SSL/TLS upgrade via the ssl module.
  • Auto resource cleanup with close() on all disposable objects.

Quick start

import postgres { Connection }
var conn = Connection('postgres://localhost/mydb')
conn.connect()
var result = conn.query(
  'select id, name from users where active = \$1',
  { params: [true] }
)
for row in result.rows {
  echo row
}
conn.close()

pool

Pool — a managed connection pool for high-throughput PostgreSQL access. The pool maintains a set of pre-opened Connection objects and lends them to callers on demand. When a query completes (or a cursor is closed), the connection is automatically returned to the pool rather than being closed.

Design

  • Min connections — pool.min connections are created eagerly at startup and kept alive indefinitely.
  • Max connections — the pool will never open more than pool.max connections simultaneously.
  • Idle eviction — connections idle for longer than pool.idleTimeoutMillis are closed and removed from the pool.
  • Acquire timeout — if no connection is available within pool.acquireTimeoutMillis, a PoolExhaustedError is raised.

Example

import postgres { Pool }
var db = Pool({
  host:     'localhost',
  database: 'mydb',
  user:     'alice',
  password: 'secret',
  pool: {
    min:                  2,
    max:                  10,
    idleTimeoutMillis:    30000,
    acquireTimeoutMillis: 5000,
  }
})
var result = db.query('select count(*) from events')
echo result.rows[0]
db.close()

View API

errors

Exception hierarchy for the postgres client library. All postgres exceptions extend PostgresError which itself extends the built-in Exception, so callers can catch the entire family with a single catch block while still being able to discriminate finer-grained types.

catch {
  conn.query('bad sql')
} as e
if isinstance(e, PostgresError) {
  echo 'postgres error: ' + e.message
}

View API

connection

Connection — a single, direct PostgreSQL database connection. Connection manages a TCP (optionally TLS) socket to a PostgreSQL server, drives the authentication handshake, and exposes a high-level API for query execution, prepared statements, cursors, and notifications. For connection pooling, use Pool instead.

Example

import postgres { Connection, DataTypeOIDs }
var conn = Connection('postgres://alice:secret@localhost:5432/mydb')
conn.connect()
# Simple query
var result = conn.query('select version()')
echo result.rows[0]
# Parameterised query
var res = conn.query(
  'select * from orders where status = \$1 and total > \$2',
  { params: ['open', 100.0] }
)
conn.close()

View API

protocol

Low-level PostgreSQL frontend/backend wire protocol (version 3.0) implementation. This module is an internal implementation detail. Application code should use Connection or Pool rather than this module directly.

Protocol overview (RFC 9522 / PostgreSQL documentation)

The PostgreSQL wire protocol uses a simple message framing scheme: Backend → Frontend messages (server responses):

  • 1 byte message type tag
  • 4 bytes big-endian int32 length (includes these 4 bytes, not the tag)
  • (length - 4) bytes payload Frontend → Backend messages (client requests):
  • Same layout, except the StartupMessage has no tag byte. All multi-byte integers are big-endian (network byte order).

References

View API

statement

PreparedStatement — a named prepared statement for optimised, repeatable query execution. Prepared statements are parsed by the server once and cached for the lifetime of the connection. Repeated executions skip the parse phase and go straight to the bind/execute cycle, significantly reducing latency for hot-path queries.

Example

import postgres { Connection, DataTypeOIDs }
var conn = Connection('postgres://localhost/mydb')
conn.connect()
var stmt = conn.prepare(
  'insert into events (user_id, action) values (\$1, \$2)',
  { paramTypes: [DataTypeOIDs.Int4, DataTypeOIDs.Text] }
)
iter var i = 0; i < 1000; i++ {
  stmt.execute({ params: [i, 'click'] })
}
stmt.close()
conn.close()

View API

notify

High-level LISTEN / NOTIFY support for real-time PostgreSQL notifications. PostgreSQL's NOTIFY mechanism allows one database session to signal other listening sessions asynchronously, enabling lightweight pub/sub patterns without polling.

Example

import postgres { Connection }
var conn = Connection('postgres://localhost/mydb')
conn.connect()
var listener = conn.listen('chat_messages')
# Register a callback for incoming notifications.
listener.on(@(notification) {
  echo 'Channel: ' + notification.channel
  echo 'Payload: ' + notification.payload
  echo 'PID:     ' + notification.pid
})
# Poll for notifications (non-blocking check).
listener.poll()
# Stop listening and free resources.
listener.close()
conn.close()

View API

result

QueryResult and Cursor — result containers for executed queries. QueryResult holds all rows of a completed (non-cursor) query. Cursor is a doubly-linked, cacheable forward/backward iterator over a server-side portal, fetching rows in batches to minimise memory pressure.

View API

types

PostgreSQL data type OIDs and the extensible type codec registry.

Built-in OID constants (DataTypeOIDs)

Every PostgreSQL built-in type has a stable numeric OID. The DataTypeOIDs dict provides named constants so user code never needs to hard-code raw numbers:

import postgres { DataTypeOIDs }
echo DataTypeOIDs.Int4   # 23
echo DataTypeOIDs.Uuid   # 2950

Type codec registry (TypeRegistry)

The TypeRegistry maps OIDs to encoder / decoder function pairs. The singleton DefaultRegistry is pre-loaded with codecs for every type listed in the type-mapping table. Custom types can be registered with TypeRegistry.register().

import postgres { TypeRegistry }
# Register a custom "money" OID → Zuri number codec
TypeRegistry.register(790, @(buf) {   # decode binary → number
  return buf_to_int64(buf) / 100.0
}, @(value) {                          # encode number → binary
  return int64_to_buf(value * 100)
})

View API

View API