Database Pgsql Laravel Package

Getting Started

hyperf/database-pgsql is not a general-purpose library — it is the PostgreSQL driver split out of the Hyperf monorepo. If you know illuminate/database, you already know 90% of this package: Hyperf's database layer is a port of Laravel's, and this package supplies the Postgres-specific pieces (connection, query grammar/builder, schema grammar/builder, processor) that hyperf/database delegates to.

Minimal steps:

composer require hyperf/database-pgsql hyperf/database
# pdo_pgsql must be compiled/installed in the PHP CLI image
php -m | grep pdo_pgsql

Configure a connection in config/autoload/databases.php:

return [
    'default' => [
        'driver'     => 'pgsql',          // in 3.1+ this resolves via the package's ConfigProvider
        'host'       => env('DB_HOST', '127.0.0.1'),
        'port'       => env('DB_PORT', 5432),
        'database'   => env('DB_DATABASE', 'app'),
        'username'   => env('DB_USERNAME', 'app'),
        'password'   => env('DB_PASSWORD', ''),
        'charset'    => 'utf8',
        'schema'     => 'public',
        'sslmode'    => 'prefer',
        'timezone'   => '+00:00',
        'pool' => ['min_connections' => 1, 'max_connections' => 10, 'wait_timeout' => 3.0],
        'options'    => [PDO::ATTR_CASE => PDO::CASE_NATURAL],
    ],
];

Where to look first, in this order:

1. vendor/hyperf/database-pgsql/src/ — the driver class, PostgresConnection/QueryGrammar/SchemaGrammar. This is the only place Postgres divergence from Laravel lives, so read the grammar, not the docs.
2. config/autoload/databases.php in the skeleton — the config keys are the real API surface.
3. hyperf/hyperf on GitHub (src/database-pgsql) — this repo is marked READ ONLY, so the monorepo is the source of truth for issues, PRs and changelog.

First use case: boot a Hyperf app, write one model, and verify the round trip end to end — Db::select('select version()'), then php bin/hyperf.php gen:model users, then php bin/hyperf.php migrate. Doing this first surfaces the two things most likely wrong on day one: the missing pdo_pgsql extension and an unresolvable driver value.

Implementation Patterns

Queries and models. Use the static facade for ad-hoc work and Hyperf\Database\Model\Model for the rest; relationships, casts, soft deletes, accessors and observers are all Eloquent-compatible.

use Hyperf\Db\Db;

Db::table('orders')->where('status', 'paid')->orderByDesc('id')->limit(50)->get();
Db::connection('reporting')->select('select * from mv_daily_sales where day = ?', [$day]);

class Order extends Model
{
    protected ?string $table = 'orders';
    protected array $casts = [
        'id'     => 'integer',
        'meta'   => 'array',      // jsonb
        'amount' => 'decimal:2',  // numeric comes back as string
        'paid'   => 'boolean',
    ];
}

Postgres-native querying. The grammar passes operators through, so ilike works without raw SQL: ->where('name', 'ilike', "%{$q}%"). For JSONB containment and array operators, drop to raw with bindings rather than fighting the builder:

$rows = Db::table('events')
    ->whereRaw("payload @> ?::jsonb", [json_encode(['type' => 'signup'])])
    ->whereRaw("tags && ?::text[]", ['{billing,refund}'])
    ->get();

If you need -> / ->> JSON paths, prefer a jsonb column plus whereRaw("payload->>'plan' = ?", ['pro']) — path selectors are the part of the grammar most likely to differ from Laravel across Hyperf versions.

Writes. insertGetId() appends a RETURNING id clause; upsert() emits ON CONFLICT ... DO UPDATE (requires the unique index to exist or the statement fails at runtime, not at build time). insertOrIgnore() maps to ON CONFLICT DO NOTHING. Batch insert() returns a bool only, so if you need IDs back, loop with insertGetId() or use a raw insert ... returning id.

Transactions. Keep them inside a single closure and never yield to sibling coroutines mid-transaction:

Db::transaction(function () use ($order) {
    Db::table('orders')->where('id', $order->id)->update(['status' => 'paid']);
    Db::table('ledger')->insert([...]); // same pooled connection
}, 3); // attempts -> retries on deadlock/serialization failure

Migrations and schema. php bin/hyperf.php gen:migration create_orders_table, then use the Postgres-specific column types the grammar supports ($table->jsonb(), $table->uuid(), $table->inet(), $table->tsvector(), $table->geometry() where available). Migrations run transactionally because Postgres DDL is transactional — useful, but it means CREATE INDEX CONCURRENTLY must be issued as a raw statement outside the migration transaction.

Observability. Register a Db::listen(fn ($sql, $bindings, $time) => ...) hook (or listen for Hyperf\Database\Events\QueryExecuted) at boot to log slow statements. In production, pair it with EXPLAIN (ANALYZE, BUFFERS) run through Db::select() against the same connection config.

Multi-datasource. Every key in databases.php becomes a named pool: default, reporting, tenant, etc. Use Model::$connection to pin a model to one. For read replicas, the Laravel-style read/write split exists in the base manager, but verify it against your Hyperf minor version before relying on it.

Gotchas and Tips

• Read-only repo. No issues or PRs are accepted here. File bugs against hyperf/hyperf and patch there; this package only republishes.
• Pin versions in lockstep. hyperf/database and hyperf/database-pgsql share internal signatures (grammar constructors, builder methods). A patch bump of one without the other produces Call to undefined method or type errors that look like framework bugs. Always composer update hyperf/database* together.
• Driver resolution. In Hyperf 3.1+ the driver is resolved through a driver class exposed by this package's ConfigProvider; in older versions driver => 'pgsql' maps to a class inside hyperf/database itself. If you get "driver [pgsql] not supported", open src/ConfigProvider.php in the installed version — that tells you instantly whether to use the short name or FQCN.
• Pooled session state leaks between coroutines. SET search_path, SET TIME ZONE, temp tables, advisory locks and prepared statements all survive on the physical PDO connection after the coroutine releases it. Either set these once in the pool's connection options/after hook or reset them explicitly. This is the single most common "it works locally, breaks under load" cause with Hyperf + Postgres.
• lastInsertId() is not your friend. Use insertGetId(); calling PDO's lastInsertId() directly returns garbage for non-serial keys or after a RETURNING insert. For UUID primary keys, set $incrementing = false and $keyType = 'string' on the model.
• Type fidelity. numeric/decimal and int8 arrive as strings from pdo_pgsql; booleans depend on PHP version. Declare $casts on every model with money, bigcounts or booleans rather than hoping.
• Case folding. Unquoted Postgres identifiers fold to lowercase. A model with protected $table = 'UserEvents' becomes userevents in queries and will not match a "UserEvents" table. Stick to snake_case.
• Timestamps and timezones. The grammar emits timestamp(0) without time zone for timestamps(). Store UTC, set timezone => '+00:00' on the connection so now() matches the app clock, and convert at the edge.
• Stale connections. Idle-timeout killers (pgbouncer, cloud proxies) drop pooled connections silently. Enable the pool heartbeat and set wait_timeout so you get a fast exception instead of a hung coroutine; catch "server closed the connection unexpectedly" and retry once.
• Coroutine boundaries break transactions. Db::connection() called from a nested Parallel/wait() callback hands you a different physical connection — writes inside it won't see or roll back with the outer transaction. Pass the model/connection down, or keep the transaction's work strictly sequential.
• upsert() needs a real unique constraint. There is no fallback; without it Postgres errors with "there is no unique or exclusion constraint matching the ON CONFLICT specification".
• Debugging aids. Set PDO to exception error mode, Db::listen() for SQL + bindings + ms, and Hyperf\Database\...\Connection::enableQueryLog() for short-lived inspection. For schema drift, diff Db::select("select * from information_schema.columns where table_name = ?") against your migration files.
• Extension points. Subclass PostgresConnection and swap the query/schema grammar or processor via the setters on the connection for dialect tweaks; register extra connections through Db::extend() where available in your version. Keep extensions thin — anything deep will conflict on the next lockstep upgrade.