September 26, 2024: PostgreSQL 17 Released!

pg_builder and pg_wrapper 2.3.0 released with support for Postgres 16 syntax

Posted on 2023-09-21 by Alexey Borzov
Related Open Source

I'm pleased to announce the release of pg_builder and pg_wrapper PHP packages updated for Postgres 16 syntax as well as the initial release of pg_gateway.

pg_builder 2.3.0

pg_builder is a query builder for Postgres backed by a partial PHP reimplementation of PostgreSQL's own SQL parser. It supports almost all syntax available in Postgres 16 for SELECT (and VALUES), INSERT, UPDATE, DELETE, and MERGE queries.

With pg_builder it is possible to start with a manually written query, parse it into an Abstract Syntax Tree, add query parts (either as Node objects or as strings) to this tree or remove them, and finally convert the tree back to an SQL string.

The new release adds support for features added in PostgreSQL 16:

  • SQL/JSON functions and expressions: IS JSON predicate, aggregate functions json_arrayagg() and json_objectagg(), constructor functions json_array() and json_object().
  • Non-decimal integer literals and underscores as separators in numeric literals.
  • Optional aliases for subqueries in FROM, added SYSTEM_USER server variable, [NO] INDENT option for XMLSERIALIZE() expression.

Full release notes

The package can be downloaded from Github or installed with Composer:

$ composer require sad_spirit/pg_builder

pg_wrapper 2.3.0

pg_wrapper provides converters for PostgreSQL data types and an OO wrapper around PHP's native pgsql extension that uses these converters. Conversion of query result fields is done automatically using database metadata, query parameters may require specifying type.

For those types where a corresponding native PHP type or class is available it is used (text -> string, timestamp -> DateTimeImmutable, hstore -> associative array, etc.). For other types (geometric types, ranges) the package provides custom classes.

The new release adds support for features added in PostgreSQL 16:

  • When connected to Postgres 16+, numeric type converters will accept non-decimal integer literals and numeric literals with underscores for digit separators, allowing to use those as query parameter values.

Full release notes

The package can be downloaded from Github or installed with Composer:

$ composer require sad_spirit/pg_wrapper

pg_gateway 0.1.0

This is a Table Data Gateway implementation built upon pg_wrapper and pg_builder packages. It adds the following features

  • Gateways are aware of the table metadata and contain helper methods to create common conditions and the like,
  • There are means to cache the complete query to skip parse/build cycle,
  • It is possible to create a query via one gateway and join it to the query built by another.

As usual, the package can be downloaded from Github or installed with Composer:

$ composer require sad_spirit/pg_gateway