Pg Wrapper Laravel Package

Getting Started

Requirements and install. sad_spirit/pg_wrapper is not a PDO driver — it wraps PHP's native pgsql extension, so ext-pgsql must be enabled (you can have both pdo_pgsql and pgsql installed). Current 2.x releases target PHP 8.1+ and use the \PgSql\Connection object; 1.x is for older PHP.

composer require sad_spirit/pg_wrapper

use sad_spirit\pg_wrapper\Connection;

$conn = Connection::connect('host=127.0.0.1 port=5432 dbname=app user=app password=secret');

$result = $conn->executeParams(
    'SELECT id, name, tags, published_at FROM articles WHERE id = :id',
    [':id' => 42]
);

$row = $result->fetchRow();
if ($row !== null) {
    echo $row['name'], PHP_EOL;
    var_dump($row['tags']);         // real PHP array, not '{php,sql}'
    var_dump($row['published_at']); // DateTimeImmutable, not a string
}

Where to look first. Connection (connect, execute, prepare, transactions), Result / ResultRow (fetching), PreparedStatement, converters\ConnectionConverter (type mapping and the extension point for custom types), types\* (value objects for ranges, geo, inet, bit strings), and exceptions\server\* (SQLSTATE-specific exceptions). The package has thin docs; its test suite is the most precise specification of converter behaviour.

First use case. The natural entry point is a read-heavy or reporting query where raw pg_query + pg_fetch_assoc forces you to hand-parse arrays, JSON, ranges, and timestamps, or a write path where you want named parameters without string interpolation. Reach for it when you specifically need PostgreSQL types as PHP values; keep Eloquent/PDO for the rest.

Implementation Patterns

Connection lifecycle. Create one connection per request/worker and inject it. There is no pool, no facade, and no Laravel service provider — register it yourself:

// AppServiceProvider::register()
$this->app->scoped(Connection::class, fn () => Connection::connect(
    config('database.connections.pgsql.dsn') // or build from host/port/dbname...
));

Use scoped() rather than singleton() under Octane/queue workers so session state does not leak between requests.

One-off vs. prepared statements. executeParams() prepares an unnamed statement per call — fine for ad-hoc queries. For hot paths, prepare once and execute the statement object repeatedly:

$stmt = $conn->prepare('SELECT id, email FROM users WHERE email = :email', 'users_by_email');

foreach ($emails as $email) {
    $row = $stmt->execute([':email' => $email])->fetchRow();
}

Named statements live for the session; use stable, generated names (users_by_email), never user input.

Result consumption.

foreach ($conn->execute('SELECT id, title FROM articles ORDER BY id') as $row) {
    // $row is a ResultRow: ArrayAccess + IteratorAggregate + Countable
}

$count    = $conn->execute('SELECT count(*) FROM articles')->fetchColumn();
$ids      = $conn->execute('SELECT id FROM articles')->fetchAllColumns(0);
$affected = $conn->executeParams('DELETE FROM articles WHERE id = :id', [':id' => 7])
                 ->getAffectedRows();

Prefer streaming iteration over fetchAllRows() for large outputs, but remember libpq buffers the entire result anyway — genuinely large scans need DECLARE CURSOR + FETCH.

Transactions and error mapping. Wrap writes explicitly and map SQLSTATE exceptions to domain/HTTP errors:

$conn->beginTransaction();
try {
    $conn->executeParams('INSERT INTO orders (id, total) VALUES (:id, :total)',
        [':id' => $id, ':total' => $total]);
    $conn->commit();
} catch (sad_spirit\pg_wrapper\exceptions\server\UniqueViolationException $e) {
    $conn->rollback();
    throw new ConflictException($e->getSQLState()); // 23505
}

Retry SerializationFailureException / deadlocks (40001, 40P01) with backoff. After any failed statement inside a transaction the session is aborted — always roll back before running another query.

Type conversion as a design tool. This is where the package earns its keep:

• Arrays (int[], text[], uuid[]) round-trip as PHP arrays; pass array_values() for lists.
• json/jsonb encode from PHP arrays/objects and decode on the way back; keep raw JSON by casting to text in SQL.
• hstore ↔ associative array; date/timestamp/timestamptz → DateTimeImmutable; bytea → raw string; numeric usually stays a string to preserve precision.
• int4range/tstzmultirange → Range / MultiRange value objects; inet/cidr/macaddr, point/box/polygon, bit/varbit have their own classes.
• Composite and domain types resolve through the catalog; enums come back as strings unless you register a converter.

Use parameters this way instead of building literals:

$conn->executeParams(
    'INSERT INTO article_tags (article_id, tag_id)
     SELECT :id, unnest(:tags::int[])',
    [':id' => $articleId, ':tags' => $tagIds]
);

Custom converters. For PHP enums, money objects, or bespoke domains, subclass TypeConverter (input() / output()) and register it on the connection converter (setConverterForType('app.status', ...)). Registrations are per-connection-instance, so do it in the factory that builds the connection.

LISTEN/NOTIFY and async. For cache invalidation or queue wakeups, Connection exposes the libpq notification path; long-running queries can be issued asynchronously (executeAsync/executeParamsAsync in 2.x) so a worker can do other work while polling. Verify the exact polling API in the docs for your pinned version.

Gotchas and Tips

• It is a second connection, not Eloquent. PDO cannot hand you a \PgSql\Connection, so pg_wrapper always opens its own session. Transactions, temp tables, SET LOCAL, advisory locks, and LISTEN state are not shared with DB::/Eloquent. Never assume a model save and a pg_wrapper query are in the same transaction.
• Not a replacement for PDO/Eloquent. No query builder, no migrations, no hydrator, no connection pool. Treat it as a specialized data-access layer for rich PostgreSQL types and analytics queries.
• Named-parameter syntax. Keys include the leading colon ([':id' => 1]); this trips people up constantly. The parser must distinguish placeholders from :: casts, string literals, and dollar-quoted function bodies. If a query contains : inside quotes or a DO $$ ... $$ block, prefer positional $1 parameters or verify the rewrite before shipping.
• Type inference needs help. Parameter types come from the server's prepared-statement metadata. In expressions like SELECT unnest(:arr) or WHERE x = :val, inference can fall back to text; add an explicit cast (:arr::int[]). Result columns of unknown/extension types come back as raw strings.
• Arrays are rectangular. Ragged PHP arrays or non-sequential keys will produce invalid literals or surprising output. Normalize with array_values(); remember empty array ('{}') and SQL NULL are different values.
• Numeric and JSON fidelity. numeric/int8 may arrive as strings; casting to float can silently lose precision. JSON round-trips lose formatting and duplicate keys, and jsonb reorders keys — never compare JSON as strings.
• Prepared-statement lifecycle. Duplicate statement names raise 42P05; prepared statements accumulate for the life of the session (relevant for long-lived workers and pg_pconnect). Issue DEALLOCATE ALL/DISCARD ALL when recycling a connection. Under PgBouncer in transaction-pooling mode, named prepared statements break unless the pooler/server supports them — prefer unnamed executeParams() there.
• Aborted transactions. Catch the exception, rollback(), and check getTransactionStatus() before reuse; a "current transaction is aborted" error usually means someone swallowed an earlier failure.
• Identifiers are not parameters. Table/column names, ORDER BY expressions, and LIMIT direction must go through escapeIdentifier() or a strict allowlist. Converters only handle values.
• NULL vs. missing. isset($row['col']) is false for SQL NULL; use offsetExists()/column metadata to distinguish "column not selected" from "value is null".
• Debugging conversions. When a value looks wrong, compare pg_fetch_assoc() output against the converted value for the same query; that isolates converter bugs from SQL bugs. Log getSQLState() and the server-side error detail/context, and enable log_min_duration_statement while profiling.
• Small ecosystem, pin versions. 22 stars and no known dependents means no Stack Overflow safety net; read CHANGELOG between majors (converter signatures and type classes changed across 1.x → 2.x), pin the major, and cover your type usage with integration tests against a real PostgreSQL (multiranges, for example, need PG 14+).
• Extension points worth knowing. A custom ConnectionConverter or per-type converter is the sanctioned way to change app-wide behaviour (e.g., numeric → Money, app.status → BackedEnum); subclassing the SQLSTATE exceptions is a clean way to add domain context.