Download the PHP package hiblaphp/postgres without Composer

Hibla PostgreSQL Client

A modern, async-first, high-performance PostgreSQL client for PHP with robust connection pooling, prepared statements, streaming, transactions, and asynchronous Pub/Sub leveraging native pgsql extension

Features

Contents

Getting started

• Installation
• Quick start
• How it works
• hiblaphp/sql contracts

Configuration

• PgSqlConfig
  • Construction
  • Properties
  • Methods
  • Sharing a config across multiple clients

Core API

• The PostgresClient
• Making queries
  • Simple queries
  • Queries with parameters
  • Named parameters
  • Convenience methods
• SQL parser
• Prepared statements
• Streaming results
• Transactions
  • High-level API: transaction()
  • Automatic retry
  • TransactionOptions reference
  • Low-level API: beginTransaction()
  • Tainted state
  • Cancellation behaviour
  • Savepoints
  • Transaction lifecycle rules

PostgreSQL-specific features

• Pub/Sub: LISTEN and NOTIFY
  • Sending notifications
  • Receiving notifications: PostgresListener
  • Auto-reconnect behaviour

Advanced features

• Connection pooling
  • Check-on-borrow health strategy
  • Shutdown strategies
  • resetConnection and statement cache interaction
• Health checks and pool stats
• SSL/TLS
• Query cancellation
• onConnect hook
• Statement caching
• Type casting

Working with responses

• Result inspection
• Multiple result sets

Development

• Development

Reference

• API Reference
• Exceptions

Meta

• License

Installation

This package is currently in beta. Before installing, ensure your composer.json allows beta releases:

Requirements:

• PHP 8.4+
• The pgsql PHP extension (ext-pgsql)

Quick start

How it works

PostgresClient manages a lazy connection pool of asynchronous PostgreSQL connections. By default, minConnections is 0, meaning no TCP connections are opened until the first query actually arrives. Resources are created on demand and returned to the pool for reuse. This makes the client cheap to instantiate and well-suited to environments where database activity is bursty or infrequent.

All operations return PromiseInterface objects. You can use await() for linear code or .then() chaining.

Parameterized queries use prepared statements, which are SQL-injection safe and benefit from server-side plan caching. Note that ext-pgsql hardcodes the text protocol for both parameter encoding and result delivery so the resultFormat argument to PQsendQueryParams is always 0. Binary protocol is not exposed by the extension, so column values are returned as strings and cast client-side by the OID-based decoder rather than decoded from binary wire format.

Streaming results support two modes. When pg_set_chunked_rows_size is available (PHP 8.4+ and libpq 18+), rows are delivered in chunks directly from the server buffer. When it is not available, the library falls back to server-side cursors (DECLARE ... CURSOR FOR) automatically. The fallback is transparent and requires no changes in your application code.

hiblaphp/sql contracts

PostgresClient fully implements the hiblaphp/sql contract package, which defines the common interfaces shared across all Hibla database drivers:

This means you can type-hint against SqlClientInterface or TransactionInterface in your application code and swap the underlying driver without changing any business logic:

PgSqlConfig

PgSqlConfig is the canonical, immutable connection-level configuration object. All three config formats accepted by PostgresClient (DSN string, associative array, and PgSqlConfig directly) are normalised to this type internally. You can construct it explicitly when you want to share a single config object across multiple clients, derive variants from a base config, or keep all settings in one strongly-typed place.

Because PgSqlConfig is readonly, every property is immutable after construction. Use the factory methods or with*() methods to derive variants.

Construction

Three construction paths are available depending on where your config comes from.

Direct constructor

All properties are named and have defaults except host. Pass only what you need:

PgSqlConfig::fromArray(array $config)

Accepts an associative array of options. Unknown keys are silently ignored. All keys are optional except host:

PgSqlConfig::fromUri(string $uri)

Parses a PostgreSQL DSN string. The scheme must be postgresql or postgres. Most options are passed as query parameters:

If no scheme is present, postgresql:// is prepended automatically. Characters in the username or password that are not URL-safe should be percent-encoded.

Properties

Methods

withQueryCancellation(bool $enabled): self

Returns a new PgSqlConfig with enableServerSideCancellation changed to $enabled. All other properties are copied unchanged:

toConnectionString(): string

Returns the libpq connection string format required by pg_connect(). Useful for debugging, but note that the password is included in plaintext. Do not log this value directly.

Sharing a config across multiple clients

Because PgSqlConfig is immutable, one instance can be safely shared and derived from across as many clients as you need:

PgSqlConfig covers only what is negotiated at the TCP and PostgreSQL handshake level: credentials, SSL, and per-connection protocol behaviour. Pool-level settings such as maxConnections, idleTimeout, and statementCacheSize are constructor parameters on PostgresClient.

The PostgresClient

Constructor parameters

Making queries

Simple queries (text protocol)

Queries with parameters (prepared statements)

When $params are provided, the library uses a prepared statement, which is SQL-injection safe and allows the server to cache the query plan. Parameters and results are transmitted over the text protocol but this is a limitation of ext-pgsql, which hardcodes resultFormat = 0 in its PQsendQueryParams call regardless of column types.

Native PostgreSQL $n placeholders:

Positional ? placeholders (converted to $n automatically):

Named :name placeholders:

You cannot mix placeholder formats within the same query. Attempting to do so throws a QueryException.

SQL parser

The parameter placeholder parser assumes standard_conforming_strings = on, which has been the PostgreSQL default since version 9.1 and is enabled on every currently maintained PostgreSQL version (15, 16, 17, 18).

Under this setting, single-quoted strings use '' to escape a literal quote, and backslashes are treated as ordinary characters. The parser correctly handles:

• Single-quoted strings: 'it''s fine'
• Dollar-quoted strings: $$...$$ and $tag$...$tag$
• The :: cast operator (never mistaken for a named placeholder)
• The := PL/pgSQL assignment operator

If your server has standard_conforming_strings = off, the escape string syntax (E'...' or backslash escapes in plain strings) is not currently recognised by the parser. A placeholder that appears inside an escape string literal may be incorrectly substituted. This setting is off by default only on PostgreSQL 8.x and earlier, and on any server where a DBA has explicitly disabled it for legacy application compatibility.

Planned: A future release will add a standardConformingStrings config flag to PgSqlConfig so the parser can be switched to escape-string-aware mode for servers that have this option turned off.

Named parameters

Named placeholders are resolved entirely on the client side before the query reaches PostgreSQL, so there is no server-side dependency and they work identically across all PostgreSQL versions.

Rules for named parameters:

• Named and positional ? placeholders cannot be mixed in the same query.
• Parameter names must start with a letter (a-z, A-Z) or underscore (_) and may contain letters, digits, and underscores.
• The PostgreSQL :: cast operator (e.g. value::int) is correctly recognised and is never mistaken for a named placeholder.
• The := assignment operator used in PL/pgSQL is also passed through untouched.
• Dollar-quoted strings ($$...$$ and $tag$...$tag$) are fully parsed, so placeholders inside them are never substituted.

Convenience methods

Prepared statements

Use explicit prepared statements when you need to execute the same query many times and want direct control over the statement lifecycle. Both positional and named placeholders are supported.

close() sends DEALLOCATE to the server and releases the statement handle. It is called automatically on object destruction if omitted, but explicit calls are strongly recommended to free server-side memory promptly.

PostgresClient::query() with parameters handles statement preparation and caching for you transparently. Explicit prepare() is intended for cases where you hold the statement open yourself across many executions.

Streaming results

Rows are yielded as they arrive from the server with backpressure support, so the socket is automatically paused when the internal buffer fills and resumed when it drains below half capacity.

The library chooses the most efficient streaming strategy automatically:

• Chunked mode (PHP 8.4+ with pg_set_chunked_rows_size): rows are delivered in chunks directly from the server's send buffer with no additional round-trips.
• Server-side cursor (fallback): the library issues DECLARE ... NO SCROLL CURSOR FOR and loops with FETCH n FROM cursor, managing BEGIN/COMMIT automatically when not already inside a transaction.

Note: it requires libpq >= 18 compiled with your PHP version for pg_set_chunked_rows_size to work.

Both modes are completely transparent from the application's perspective.

You can also stream prepared statement results:

To cancel a stream before it is fully consumed, call $stream->cancel():

Concurrent use: If you are consuming a stream alongside other concurrent async work, wrap the foreach in async() to avoid blocking the event loop while waiting for the next buffer fill:

Transactions

Transactions use BEGIN, which means isolation levels are scoped strictly to the individual transaction. They do not leak into the session or affect any other concurrent query on the same connection. Each transaction starts clean, and the connection is returned to the pool in its original session state when the transaction completes.

High-level API: transaction()

The transaction() method is the recommended way to run a transaction. It handles BEGIN, commit, rollback, and automatic retry automatically so you only write the business logic.

The callback is implicitly wrapped in a Fiber via async(). This means await() is safe to call freely inside it without blocking the event loop. Concurrent async work, nested queries, and streaming all behave correctly inside the callback with no extra setup required.

Partial failure is never silently committed. If any await() inside the callback throws, the client automatically rolls back the entire transaction and re-throws the exception.

Automatic retry

transaction() automatically retries the entire callback on deadlocks (DeadlockException, SQLSTATE 40P01) and lock wait timeouts (LockWaitTimeoutException, SQLSTATE 55P03). These two exception types implement the RetryableException marker interface, which the retry engine recognises without any configuration.

The default TransactionOptions has attempts: 1 (no retry). Pass withAttempts() to enable retry:

On each retry the callback runs again from scratch on a fresh BEGIN. The rollback from the failed attempt is issued automatically before the next attempt begins.

TransactionOptions reference

TransactionOptions is an immutable value object. All with*() methods return a new instance.

Retry decision hierarchy. When a transaction attempt fails, transaction() calls $options->shouldRetry($e) to decide whether to try again. The check follows a strict three-tier order:

1. RetryableException marker interface. Any exception implementing this interface retries automatically. DeadlockException (SQLSTATE 40P01) and LockWaitTimeoutException (SQLSTATE 55P03) implement it out of the box. Your own exceptions can opt in the same way:

2. Known permanent SQL failures. Exceptions the SQL layer has classified as non-retryable are never retried, regardless of what any user predicate returns. This protects against accidentally retrying errors that will never resolve, such as a UNIQUE constraint violation. These include ConstraintViolationException, QueryException, PreparedException, ConnectionException, and TransactionException.

3. User predicate. For third-party exceptions that fail tiers 1 and 2, the predicate from withRetryableExceptions() is consulted:

Low-level API: beginTransaction()

Use beginTransaction() when you need explicit control over the transaction lifecycle.

Unlike transaction(), the low-level API does not retry automatically and does not wrap the work in a fiber. Prefer transaction() in all cases where it is sufficient.

Tainted state

If any query inside a transaction throws, the transaction is immediately marked tainted. All subsequent calls to query(), execute(), stream(), prepare(), and savepoint() on that transaction will throw a TransactionException until the taint is cleared.

The only two operations that accept a tainted transaction are rollback() (rolls back and releases the connection) and rollbackTo(string $identifier) (rolls back to a savepoint and clears the tainted state).

Attempting to commit() a tainted transaction throws TransactionException immediately without contacting the server.

Cancellation behaviour

Promise cancellation inside a transaction follows the same enableServerSideCancellation setting as standalone queries. When enabled and a query promise is cancelled mid-execution, the connection dispatches pg_cancel_backend via a side-channel TCP connection. Unlike MySQL's KILL QUERY, PostgreSQL sends an ErrorResponse on the main wire, which QueryResultHandler consumes automatically. No scrub query is needed.

Cancelling the outer transaction() promise causes the client to interrupt any currently running query on the connection and then issue ROLLBACK before the cancellation propagates:

commit() and rollback() are never cancellable, regardless of this setting. Both operations always run to completion so the server-side transaction state is always deterministic.

Savepoints

Savepoints let you mark a point within a transaction and roll back to it selectively without abandoning the entire transaction.

Commit and rollback hooks

The TransactionInterface exposes onCommit() and onRollback() methods. These allow you to register callbacks that fire after the transaction has successfully committed or rolled back on the server.

These hooks are useful for triggering side-effects such as dispatching domain events, clearing caches, or enqueuing background jobs, only when you are guaranteed the database state has been durably persisted or completely aborted.

Hook rules:

• Post-execution: They execute after the COMMIT or ROLLBACK has been acknowledged by the PostgreSQL server.
• Mutually exclusive: A successful commit clears all rollback hooks, and a rollback clears all commit hooks.
• FIFO order: Multiple callbacks registered to the same hook are executed in the exact order they were added.
• Active registration: Hooks must be registered while the transaction is active. Attempting to call onCommit() or onRollback() after the transaction has closed will throw a TransactionException.

Transaction lifecycle rules

Isolation level scoping. Isolation levels are applied via SET TRANSACTION ISOLATION LEVEL immediately before BEGIN, scoping them strictly to that transaction. The session isolation level is never mutated.

commit() and rollback() are uninterruptible. They are internally wrapped with Promise::uninterruptible() so a concurrent cancel() on the outer promise does not interrupt either operation mid-flight.

rollback() is idempotent. Calling it on an already-committed, already-rolled-back, or released transaction silently returns a resolved promise. It is safe to place in finally blocks.

Automatic rollback on garbage collection. If a Transaction object is garbage collected without an explicit commit() or rollback(), a fire-and-forget ROLLBACK is issued automatically. Always manage the lifecycle explicitly rather than relying on this safety net.

commit() is rejected while tainted. Attempting to commit a tainted transaction throws TransactionException immediately without contacting the server.

Pub/Sub: LISTEN and NOTIFY

PostgreSQL's LISTEN/NOTIFY mechanism provides lightweight asynchronous messaging between database clients. PostgresClient exposes two complementary APIs: notify() for sending, and createListener() for receiving.

Sending notifications

notify() is a thin wrapper around SELECT pg_notify(channel, payload) and shares a connection from the pool like any other query.

Receiving notifications: PostgresListener

createListener() creates a dedicated, unpooled TCP connection to PostgreSQL that is completely isolated from the connection pool. This separation is necessary because a connection in LISTEN mode must maintain an idle read watcher at all times, which is incompatible with the pool's checkout/release lifecycle.

To stop listening on a specific channel:

To close the listener connection entirely and drop all subscriptions:

Auto-reconnect behaviour

The PostgresListener includes transparent auto-reconnection with exponential backoff. If the underlying TCP connection drops (due to a network partition, server restart, or proxy timeout), the listener detects the disconnection and begins reconnecting automatically. All channel subscriptions are restored on reconnect without any intervention from your application code.

The backoff interval doubles on each failed attempt, from minReconnectInterval up to maxReconnectInterval. Once reconnected, all channels are re-subscribed via fresh LISTEN commands before the listener is considered ready again.

Note on payload size. PostgreSQL limits NOTIFY payloads to 8000 bytes. Larger payloads will cause the server to reject the pg_notify call with an error. Store large data in a table and pass only an identifier in the payload if you need to signal larger datasets.

Connection pooling

The pool manages the full connection lifecycle automatically. By default it is fully lazy (minConnections: 0).

Check-on-borrow health strategy

Before a connection is checked out of the pool, the client verifies it is still alive by checking connection state, idle timeout, and max lifetime. Connections that fail any of these checks are discarded and replaced transparently. A more thorough check is also available via healthCheck(), which sends a round-trip ping to every idle connection.

Shutdown strategies

The two modes are safe to combine. Calling close() while closeAsync() is pending will force-resolve the shutdown promise before tearing everything down, so the caller awaiting closeAsync() is never left hanging.

The destructor issues a force-close automatically when the object is garbage collected.

resetConnection and statement cache interaction

When resetConnection is enabled, DISCARD ALL wipes all server-side prepared statement handles and session state. The client automatically clears the per-connection statement cache on checkout to prevent executing stale statement names. The onConnect hook is also re-invoked after every reset to restore session state.

Health checks and pool stats

Health check

Pool stats

SSL/TLS

PostgreSQL's sslmode option controls both whether SSL is used and how strictly the server's certificate is verified.

sslmode values:

SSL negotiation is handled by libpq during the connection handshake. The sslCa, sslCert, and sslKey properties map to libpq's sslrootcert, sslcert, and sslkey parameters.

Query cancellation

Server-side query cancellation is disabled by default. When disabled, $promise->cancel() transitions the promise to the cancelled state on the client side only. The PostgreSQL server continues executing the query to completion.

Enable it explicitly for long-running queries where stopping server execution and releasing locks immediately has meaningful value:

When enabled, cancelling a query promise dispatches pg_cancel_backend(<pid>) via a dedicated side-channel TCP connection to avoid blocking the main wire. The PostgreSQL server then sends an ErrorResponse on the main connection, which QueryResultHandler consumes automatically, resetting the connection state to READY with no scrub query needed.

Key implementation details:

The cancel dispatch is idempotent: if a cancel is already in-flight for the same backend PID, a second dispatch is suppressed to prevent orphaned promises. The promise is also registered synchronously before the next event loop tick, so close() always sees the pending cancel correctly even under race conditions.

When the pool receives a connection back after a cancellation, it uses a ping() as a synchronization barrier to confirm the ErrorResponse has been fully drained before returning the connection to the idle pool.

commit() and rollback() are never cancellable regardless of this setting.

onConnect hook

The hook receives a ConnectionSetup instance, which exposes a minimal query surface (query() and execute()) without leaking the internal Connection object. Both synchronous and async (promise-returning) hooks are supported. If the hook rejects or throws, the connection is dropped entirely rather than returned to the pool in an unknown session state.

If resetConnection is enabled, DISCARD ALL wipes all session variables. The onConnect hook is therefore re-invoked after every reset to restore session state.

Statement caching

Prepared statements are cached per connection with LRU eviction (default: 256 slots).

When a cached statement is evicted from the LRU cache, DEALLOCATE is sent to the server automatically to free the server-side handle. When resetConnection is enabled, DISCARD ALL drops all server-side statement handles, so the per-connection cache is cleared on checkout to prevent executing stale statement names.

Type casting

Type casting applies only when castPreparedTypes is true (the default) and the query is executed via a prepared statement, meaning any call to query() or execute() with $params, or explicit prepare().

When castPreparedTypes is false, every column value is returned as a PHP string regardless of the PostgreSQL column type, matching the behaviour of the text protocol.

The casting is OID-based: the library reads the field type OID from the result metadata and applies the appropriate conversion. This is more precise than name-based heuristics because it works correctly with custom domains and type aliases.

Note: Because ext-pgsql uses the text protocol even on prepared statements, all column values arrive as PHP strings. The OID-based decoder then casts them to native PHP types client-side. This is different from true binary protocol decoding but produces the same result for the supported scalar and array types.

Scalar types

Array types

Array column values are parsed from PostgreSQL's wire literal format (e.g. {1,2,3}) into native PHP arrays by PgArrayParser.

PgArrayParser handles nested arrays, quoted elements with escaped characters, and NULL values inside arrays.

Types returned as strings

All other types (including NUMERIC/DECIMAL, TEXT, VARCHAR, UUID, JSON, JSONB, DATE, TIMESTAMP, TIMESTAMPTZ, BYTEA, and custom types) are returned as string. This is intentional for precision-sensitive types like NUMERIC, where casting to float would silently lose precision.

For arithmetic on NUMERIC columns, use bcmath:

Result inspection

Multiple result sets

Queries that return multiple result sets can be traversed via nextResult():

API Reference Summary

PostgresClient

Implements Hibla\Postgres\Interfaces\PostgresClientInterface.

PostgresListener

The callback signature is function(string $channel, string $payload, int $pid): void.

PreparedStatementInterface (ManagedPreparedStatement)

TransactionInterface

Exceptions

All database exceptions extend Hibla\Sql\Exceptions\SqlException.

Development

Requirements

• Docker and Docker Compose
• PHP 8.4+
• Composer
• The pgsql PHP extension

Setup

Running tests

The test suite requires a running PostgreSQL instance. Each supported version has a dedicated Docker Compose service pair: one plain TCP and one SSL.

Start the database services you want to test against:

Wait for the containers to report healthy before running tests:

Run the tests for a specific version:

Tear down services when done:

Static analysis

Code formatting

Port reference

All ports are defined in docker-compose.yml. The Composer test scripts set POSTGRES_PORT and POSTGRES_SSL_PORT automatically and you do not need to export them manually unless you want to point the suite at an external server.

License

MIT License. See LICENSE for more information.