Download the PHP package sad_spirit/pg_wrapper without Composer

sad_spirit\pg_wrapper

Note: master branch contains code for the upcoming 3.0 version that requires PHP 8.2+

Branch 2.x contains the stable version compatible with PHP 7.2+

This package has two parts and purposes

• Converter of PostgreSQL data types to their PHP equivalents and back and
• An OO wrapper around PHP's native pgsql extension.

While the converter part can be used separately e.g. with PDO, features like transparent conversion of query results work only with the wrapper.

Installation

Require the package with composer:

pg_wrapper requires at least PHP 8.2. Native pgsql extension should be enabled to use classes that access the DB (the extension is not a hard requirement).

Minimum supported PostgreSQL version is 12

It is highly recommended to use PSR-6 compatible metadata cache in production to prevent possible metadata lookups from database on each page request.

Why type conversion?

PostgreSQL supports a large (and extensible) set of complex database types: arrays, ranges, geometric and date/time types, composite (row) types, JSON...

Unfortunately neither of PHP extensions for talking to PostgreSQL (pgsql and PDO_pgsql) can map these complex types to their PHP equivalents. They return string representations instead: both

and

yield

And that is where this library kicks in:

yields

Note that no configuration is needed here: proper types are deduced from metadata returned with the result.

Why another OO wrapper when we have PDO, Doctrine DBAL, etc?

The goal of an abstraction layer is to target the Lowest Common Denominator, and thus it intentionally hides some low-level APIs that we can use with the native extension and / or adds another level of complexity.

• PDO does not expose pg_query_params(), so you have to prepare() / execute() each query even if you execute() it only once. Doctrine DBAL has Connection::executeQuery() but it uses prepare() / execute() under the hood.
• Postgres only supports $1 positional parameters natively, while PDO has positional ? and named :foo parameters. PDO rewrites the query to convert the latter to the former, shortcomings in that rewrite logic prevented using Postgres operators containing ? with PDO until PHP 7.4 and led to problems when using dollar quoting for strings until PHP 8.4.
• PDO does not expose pg_field_type_oid() and its PDOStatement::getColumnMeta() returns type name without a schema name and may run a metadata query each time to get that.

Variable number of parameters: native vs. abstraction

A very common problem for database abstraction is providing a list of parameters to a query with an IN clause

where ? actually represents a variable number of parameters.

On the one hand, if you don't need the abstraction, then Postgres has native array types, and this can be easily achieved with the following query

passing an array literal as its parameter value

Obviously, yhe above query can be prepared as usual and executed with another array literal.

On the other hand, Doctrine DBAL has its own solution for parameter lists which once again depends on rewriting SQL and does not work with prepare() / execute(). It also has "support" for array types, but that just (un)serializes PHP arrays rather than converts them from/to native DB representation. Serialized PHP arrays will obviously not work with the above query.

Documentation

Is in the wiki

Type conversion:

• TypeConverter interface and its implementations
• TypeConverterFactory interface and its default implementation

Working with PostgreSQL:

• Connecting to a DB
• Executing a query
• Working with a query result
• Transactions handling