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:

composer require "sad_spirit/pg_wrapper:^3"

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...

create table test (
    strings  text[],
    coords   point,
    occupied daterange,
    age      interval,
    document json
);

insert into test values (
    array['Mary had', 'a little lamb'], point(55.75, 37.61),
    daterange('2014-01-13', '2014-09-19'), age('2014-09-19', '2014-01-13'),
    '{"title":"pg_wrapper","text":"pg_wrapper is cool"}'
);

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

var_dump(pg_fetch_assoc(pg_query($conn, 'select * from test')));

and

var_dump($pdo->query('select * from test')->fetch(\PDO::FETCH_ASSOC));

yield

array(5) {
  ["strings"]=>
  string(28) "{"Mary had","a little lamb"}"
  ["coords"]=>
  string(13) "(55.75,37.61)"
  ["occupied"]=>
  string(23) "[2014-01-13,2014-09-19)"
  ["age"]=>
  string(13) "8 mons 6 days"
  ["document"]=>
  string(50) "{"title":"pg_wrapper","text":"pg_wrapper is cool"}"
}

And that is where this library kicks in:

$result = $connection->execute('select * from test');
var_dump($result[0]);

yields

array(5) {
  ["strings"]=>
  array(2) {
    [0]=>
    string(8) "Mary had"
    [1]=>
    string(13) "a little lamb"
  }
  ["coords"]=>
  object(sad_spirit\pg_wrapper\types\Point)#28 (2) {
    ["x"]=>
    float(55.75)
    ["y"]=>
    float(37.61)
  }
  ["occupied"]=>
  object(sad_spirit\pg_wrapper\types\DateTimeRange)#29 (5) {
    ...
  }
  ["age"]=>
  object(sad_spirit\pg_wrapper\types\DateInterval)#32 (10) {
    ...
  }
  ["document"]=>
  array(2) {
    ["title"]=>
    string(10) "pg_wrapper"
    ["text"]=>
    string(18) "pg_wrapper is cool"
  }
}

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

SELECT * FROM stuff WHERE id IN (?)

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

-- in case of using PDO just replace $1 with a PDO-compatible placeholder
SELECT * FROM stuff WHERE id = ANY($1::INTEGER[])

passing an array literal as its parameter value

use sad_spirit\pg_wrapper\converters\DefaultTypeConverterFactory;

$arrayLiteral = (new DefaultTypeConverterFactory())
    ->getConverterForTypeSpecification('INTEGER[]')
    ->output([1, 2, 3]);

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