programster / pgsql-lib
A library for interfacing with a PostgreSQL database in PHP.
Requires
- php: ^8.2
- ext-pgsql: *
- programster/collections: ^4.0
Requires (Dev)
- phpstan/phpstan: ^2.1
- programster/autoloader: ^1.0.3
- programster/pgsql-migrations: ^1.1
Suggests
None
Provides
None
Conflicts
None
Replaces
None
This package is auto-updated.
Last update: 2026-11-22 13:28:31 UTC
README
- Requirements
- Installation
- Usage
- Escaping Values
- Queries With Values
- Transactions
- Using a Schema
- UUIDs
- Errors
- Asynchronous Connections
- Testing
- Upgrading
- Licence
- Full documentation
A library for helping interface with PostgreSQL.
Requirements
- PHP 8.2 or later
- The
pgsqlextension
Installation
composer require programster/pgsql-lib
Usage
Create a connection with PgSqlConnection::create(), or wrap an existing PgSql\Connection from pg_connect().
create()'s forceNew parameter is required:
truealways opens a new, independent connection. Use this unless you have a reason not to.falselets PHP return a connection that the script already has open with the same details. BothPgSqlConnectionobjects then share one database session, including its transactions and session settings, and closing one closes the other. To share a connection deliberately, pass the samePgSqlConnectionobject around instead.
use Programster\PgsqlLib\Conjunction; use Programster\PgsqlLib\PgSqlConnection; $db = PgSqlConnection::create('localhost', 'myDbName', 'myUsername', 'myPassword', forceNew: true); # or: $db = new PgSqlConnection(pg_connect($connectionString)); $insertQuery = $db->generateBatchInsertQuery('user', [ ['id' => '952036f3-3486-4630-9889-9f46b50a54e5', 'name' => 'bob', 'email' => 'bob@example.com'], ]); $db->query($insertQuery); $selectQuery = $db->generateSelectWhereQuery('user', ['email' => 'bob@example.com'], Conjunction::AND); $result = $db->query($selectQuery);
query() returns a PgSql\Result, and throws an ExceptionQueryError if the query fails. The exception holds
PostgreSQL's error message and the query that failed. The warning PHP's pg_query() raises on failure is captured
rather than passed to your error handler, so a handler that turns warnings into exceptions won't throw first:
use Programster\PgsqlLib\Exceptions\ExceptionQueryError; try { $db->query($query); } catch (ExceptionQueryError $ex) { error_log("{$ex->getErrorMessage()} in query: {$ex->getQuery()}"); }
With generateBatchInsertQuery(), every row must have the same columns, otherwise it throws an
ExceptionMismatchedRows. If some rows leave out columns so that the database fills in their default values, list
those columns in the columnsWithDefaults parameter. Rows that leave them out then get DEFAULT. An explicit null
still inserts NULL.
$insertQuery = $db->generateBatchInsertQuery( 'user', [ ['id' => '952036f3-3486-4630-9889-9f46b50a54e5', 'name' => 'alice', 'status' => 'admin'], ['id' => '952036f3-3559-4396-9352-81f7dc5a978a', 'name' => 'bob'], # status gets its default ], columnsWithDefaults: ['status'] );
generateDeleteWhereQuery() throws an ExceptionNoData if it's given no where pairs, rather than generating a
query that deletes every row in the table. If you really want to empty a table, write that query explicitly.
generateQueryPairs() also throws an ExceptionNoData if it's given no pairs.
In where pairs, a value can be a single value, or a list of values to generate IN(). A null value generates
IS NULL, including when it's one of the values in a list:
# WHERE ("nickname" IN('ali','caz') OR "nickname" IS NULL) AND "status" = 'active' $query = $db->generateSelectWhereQuery( 'user', ['nickname' => ['ali', 'caz', null], 'status' => 'active'], Conjunction::AND );
PgsqlLib provides the same functionality as static methods that take the PgSql\Connection as their first
parameter, for if you'd rather not use the wrapper.
Escaping Values
The generators escape values for you. To build your own queries, use escapeValue() or escapeValues(), which
return each value in the form to put straight into the query:
| Value | Returned as |
|---|---|
| String | A quoted, escaped literal, e.g. 'o''brien' |
| Int | The int, unchanged |
| Float | A string with full precision, e.g. 0.30000000000000004. PHP's own float to string conversion only keeps 14 significant digits. INF, -INF and NAN become 'Infinity'::float8, '-Infinity'::float8 and 'NaN'::float8. |
| Bool | TRUE or FALSE |
| Null | NULL |
| Backed enum | Its backing value, escaped as above |
Any other type throws an ExceptionUnexpectedValueType.
Some things to know about the special float values:
Infinity,-InfinityandNaNcan be stored inrealanddouble precisioncolumns.numericcolumns only acceptNaNbefore PostgreSQL 14, and all three from PostgreSQL 14.integercolumns reject all of them.- PostgreSQL treats
NaNas equal to itself, unlike PHP, so a where clause such as['value' => NAN]matches rows where the value isNaN. PostgreSQL also sortsNaNabove every other value, includingInfinity. - When reading these values back, PHP's
(float)cast doesn't understand PostgreSQL's names for them, so(float)'Infinity'gives0. Map'Infinity','-Infinity'and'NaN'toINF,-INFandNANyourself.
$query = "SELECT * FROM {$db->escapeIdentifier('user')} WHERE {$db->escapeIdentifier('id')} > {$db->escapeValue($id)}";
Queries With Values
query() runs a query as it's given, such as one built with the escaping methods above. Optionally, give it the
values as a second argument instead, and they're sent separately from the query (PostgreSQL's pg_query_params), as
$1, $2 and so on, so they're never part of the SQL and need no escaping:
$result = $db->query('SELECT * FROM "user" WHERE "email" = $1 AND "status" = $2', [$email, 'active']);
The values must be a list, in the order of their placeholders. Strings are sent as they are, nulls as SQL NULL,
bools as true or false (pg_query_params on its own would send false as an empty string), floats with their
full precision (or as Infinity, -Infinity and NaN), and backed enums as their backing values. Anything else, or
values keyed by name, throws an ExceptionUnexpectedValueType, before anything is sent.
Without values, the query is sent as it is (pg_query), as before, so it can hold several statements separated by
semicolons. A query with values can only hold one, as PostgreSQL doesn't allow more.
Transactions
Run several queries so that either they all take effect, or none do:
$db->runInTransaction( 'UPDATE "account" SET "balance" = "balance" - 100 WHERE "id" = 1', 'UPDATE "account" SET "balance" = "balance" + 100 WHERE "id" = 2' );
To build one up, pass it around and run it later, use a Transaction:
use Programster\PgsqlLib\Transaction; $transaction = new Transaction($db, ['UPDATE "account" SET "balance" = "balance" - 100 WHERE "id" = 1']); $transaction->append('UPDATE "account" SET "balance" = "balance" + 100 WHERE "id" = 2'); $transaction->execute(); # commit(), run() and $transaction() do the same
Anywhere that takes a query takes either a string or a Query. Use a Query to send values separately from the SQL,
so they need no escaping, and a large value isn't copied into the query:
use Programster\PgsqlLib\Query; $db->runInTransaction( new Query('UPDATE "account" SET "name" = $1 WHERE "id" = $2', ['Bob', 1]), 'UPDATE "account" SET "balance" = 0 WHERE "id" = 2' );
It can also run after-commit callbacks, and retry on serialization failures and deadlocks. See Transactions.
Using a Schema
Wherever a method takes a table, you can pass either a table name as a string, or a Table. A string, or a Table
without a schema, is left unqualified, so PostgreSQL finds the table using the connection's search_path. That is
public by default, but it can be changed per role, database or session. To target a table in a specific schema,
give the Table a schema:
use Programster\PgsqlLib\Conjunction; use Programster\PgsqlLib\Table; # SELECT * FROM "app"."user" WHERE "email" = 'bob@example.com' $query = $db->generateSelectWhereQuery(new Table('user', 'app'), ['email' => 'bob@example.com'], Conjunction::AND);
Don't pass a dotted name such as app.user as the table name. It gets escaped as a single identifier, so PostgreSQL
looks for a table literally called app.user. Pass the table and schema separately.
To build your own queries, use escapeTableName(), which escapes the schema and table separately:
$db->escapeTableName('user'); # "user" $db->escapeTableName(new Table('user')); # "user" $db->escapeTableName(new Table('user', 'app')); # "app"."user"
The same methods are available statically on PgsqlLib, taking the PgSql\Connection as the first parameter.
UUIDs
PgsqlLib::generateUuid() generates a UUID (RFC 9562) of the version you
give it, in lowercase. PostgreSQL's own uuidv7() only arrived in PostgreSQL 18, whereas this works with any version,
and lets you know a row's ID before inserting it.
UuidVersion::V7starts with the current time, to a 4096th of a millisecond, and ends with 62 random bits, so UUIDs generated later sort after those generated earlier, both as strings and in auuidcolumn. That makes them good primary keys, as each new row goes at the end of the index, rather than somewhere random in it. Within a process, each is always greater than the one before it, even if two are generated within the same 4096th of a millisecond, or the clock goes backwards.UuidVersion::V4is 122 random bits, so use it where a UUID mustn't give away when it was made, or the order things were made in.UuidVersion::V1is for systems that expect one. It holds the time too, to a 10th of a microsecond, but doesn't sort by it. It ends with a random node, with its multicast bit set (as RFC 9562 allows), rather than the machine's MAC address, so it doesn't give away which machine made it. The node is chosen afresh in each process, including after a fork.
PgsqlLib::isValidUuid() checks whether a string is a UUID of any version, in its usual form: 32 hex digits, in either
case, in groups of 8, 4, 4, 4 and 12, separated by hyphens. Unlike PostgreSQL's uuid type, it doesn't accept braces,
missing hyphens, spaces or a trailing newline, so check an ID with it before using it in a query, rather than having
PostgreSQL reject the query, or tidy up an ID that doesn't then match the one you were given.
use Programster\PgsqlLib\PgsqlLib; use Programster\PgsqlLib\UuidVersion; $id = PgsqlLib::generateUuid(UuidVersion::V7); # e.g. "019a8f2e-5c1b-7d3a-9e4f-0b6c2d8a1f37" $db->query('INSERT INTO "user" ("id", "name") VALUES ($1, $2)', [$id, 'bob']); PgsqlLib::isValidUuid($id); # true PgsqlLib::isValidUuid("{019a8f2e-5c1b-7d3a-9e4f-0b6c2d8a1f37}"); # false
Errors
Every exception the library throws extends Programster\PgsqlLib\Exceptions\AbstractException, so you can catch
them all in one place:
| Exception | Thrown when |
|---|---|
ExceptionConnectionError |
Connecting fails. The message includes PostgreSQL's reason. |
ExceptionQueryError |
A query fails. |
ExceptionNoData |
A generator is given nothing to work with, such as no rows or no where pairs. |
ExceptionMismatchedRows |
Batch insert rows have different columns, and the missing ones aren't in columnsWithDefaults. |
ExceptionUnexpectedValueType |
A value can't be escaped. |
ExceptionInvalidTableName |
A Table is given an empty table or schema name. |
ExceptionInvalidQuery |
A Query is given SQL it can't use. |
ExceptionInvalidTransaction |
A transaction is used in a way that can't work, such as running it twice. |
ExceptionAfterCommitFailed |
An after-commit callback failed. The transaction was committed. |
Asynchronous Connections
With useAsync: true, create() returns straight away, before the connection is established. Poll it until it's
ready before running any queries:
$db = PgSqlConnection::create('localhost', 'myDbName', 'myUsername', 'myPassword', forceNew: true, useAsync: true); # ... do other work ... do { $status = pg_connect_poll($db->getResource()); } while ($status !== PGSQL_POLLING_OK && $status !== PGSQL_POLLING_FAILED);
Testing
The tests need Docker, or a throwaway PostgreSQL database. To run them against every supported version:
composer install testing/run-all-versions.sh
See Testing.
Upgrading
See Upgrading for what changed in each release.
Licence
MIT. See LICENSE.