Db Pgsql Laravel Package

Getting Started

yiisoft/db-pgsql is the PostgreSQL driver for the yiisoft/db abstraction layer — not a Laravel package. There is no service provider, facade, or Eloquent bridge; it is a DBAL you instantiate yourself and talk to through Connection → QueryBuilder / Command / Schema. That makes it a good fit for a Postgres-heavy bounded context (reporting service, ingestion worker, CLI job) living next to a Laravel app, not a replacement for Eloquent.

composer require yiisoft/db-pgsql:^2.0

Requires PHP 8.1+, ext-pdo, ext-pdo_pgsql. First steps:

1. composer show yiisoft/db-pgsql to confirm 2.0.1, then read vendor/yiisoft/db-pgsql/src/Connection.php for the constructor signature (driver, schema, PSR-3 logger, profiler, query cache — all optional, injected by hand).
2. Browse src/QueryBuilder.php and src/DDLQueryBuilder.php first — they are where every Postgres-specific capability is exposed (array literals, JSONB operators, range literals, ILIKE, SIMILAR TO, DDL with check constraints).
3. Then src/Column/ and the parsers (ArrayParser, StructuredParser, RangeParser) — these define how Postgres text output becomes PHP values.

use Yiisoft\Db\Pgsql\Connection;
use Yiisoft\Db\Pgsql\Driver;
use Yiisoft\Db\Pgsql\Dsn;

$db = new Connection(
    new Driver(new Dsn('pgsql', '127.0.0.1', 'app', 5432, user: 'app', password: getenv('DB_PASSWORD'))),
);

First use case: a table with a text[] tags column and a jsonb payload. Create it through DDLQueryBuilder, insert with an array value, read it back through createQuery() (not createCommand()) so the Column classes typecast {a,b} into a PHP array.

Implementation Patterns

• Prefer Query over Command for reads. $db->createQuery()->select('*')->from('events')->all() routes each value through its Column::phpTypecast(), so text[] becomes array, jsonb becomes array, ranges become range value objects. $db->createCommand($sql)->queryAll() returns raw strings — a very common source of "why is my JSON a string?" confusion.
• Arrays on write. Array values passed to insert()/update() are rendered as ARRAY[...] literals by the query builder. Prefer flat, list-shaped arrays; if you pass string keys, keys become an explicit dimension and Postgres will reject or mis-shape the value.
• JSONB stays in SQL. Use the expression builder for containment/existence predicates rather than decoding in PHP and filtering — that is the whole reason to pick this driver over a generic one.
• Schema-first, models hand-written. There is no ActiveRecord here. The idiomatic pattern is thin repository classes that hold a Connection and compose QueryBuilder calls; map rows to DTOs yourself.
• Migrations. No migration runner ships with the package. Either drive DDLQueryBuilder from your own versioned scripts, or pair it with yiisoft/db-migration; do not let two tools own the same schema if Laravel's artisan migrate also touches the database.
• Transactions. Wrap multi-statement work in Connection::transaction(); Postgres isolation levels are exposed as constants, so set REPEATABLE READ explicitly for reporting reads instead of relying on the default.
• Laravel coexistence. Register a singleton in a service provider that builds the Connection from config('database.connections.pgsql'), and never call DB:: and this connection inside the same transaction — they are separate PDO handles and will deadlock under write contention.
• Extension points worth knowing on day one: subclass QueryBuilder/DDLQueryBuilder to add house SQL, and add custom Column classes for domain types (enums, composite types) so phpTypecast() handles them centrally.

Gotchas and Tips

• 2.0.1 is a safe patch. Performance rework inArrayParser::parse() and StructuredParser::parse() (#477, #478), explicit use function/use const imports (#479), slimmer Composer archive (#480), and a fix for quoting array column names (#481). No API changes, no deprecations — composer update is a no-op for your code.
• #481 matters if you have quoted or mixed-case columns. Quoting array column names was broken in 2.0.0; selects or DDL involving "myArray"-style identifiers could emit invalid SQL. If you had a workaround (lowercasing identifiers, aliasing, raw expressions), remove it after upgrading.
• The 2.0.1 parser speedups are only visible at scale — many rows × many array/structured columns. Do not expect a benchmark delta on single-row lookups; do expect one in exports and bulk all() calls.
• Typecasting depends on schema introspection. Queries against CTEs, views over unions, or dynamically named temp tables may return columns the schema cannot type, leaving values as strings. Cast explicitly with the relevant Column object rather than assuming phpTypecast() ran.
• Schema cache is a classic trap. If you inject a PSR-16 cache into the schema, a newly added array/json column is invisible until you clear it — the symptom is a string where an array used to appear.
• {} vs NULL. An empty Postgres array round-trips as [], not null; write null checks accordingly, and be careful with strict_types array shape assumptions in DTOs.
• Multidimensional arrays must be rectangular. ArrayParser will happily produce nested PHP arrays from a valid Postgres literal, but writing ragged arrays back will fail server-side — validate shape before insert.
• Debugging: attach a PSR-3 logger to Connection, dump Command::getRawSql() for the interpolated statement, and run EXPLAIN (ANALYZE, BUFFERS) through createCommand() when a QueryBuilder-generated query underperforms.
• Imports (#479) hint at a strict namespace. The package no longer falls back to global functions/constants; if you override globals or rely on namespace fallback in your own subclasses under Yiisoft\Db\Pgsql\*, import explicitly.
• Do not confuse it with Laravel's connection. DSN, credentials, and pooling are entirely yours — config/database.php, DB::listen, and Laravel's query log do not see this connection's traffic, so instrument it separately.