pg_builder 2.0.0 and pg_wrapper 2.0.0 packages for PHP released

Posted on 2022-01-02 by Alexey Borzov
Related Open Source

I'm pleased to announce the new releases of pg_builder and pg_wrapper packages. The main topic of these releases is support for Postgres 14 and PHP 8.1

pg_builder version 2.0.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 14 for SELECT (and VALUES), INSERT, UPDATE, and DELETE 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.

Release highlights

  • Support for new syntax of Postgres 14: most of the keywords can be used as column aliases without AS; DISTINCT clause for GROUP BY; SEARCH and CYCLE clauses for Common Table Expressions; alias for USING clause of JOIN expressions.
  • SQL functions with custom argument format (arguments separated by keywords, keywords as arguments, etc) are now parsed to specialized Nodes and will appear in generated SQL the same way they did in source: trim(trailing 'o' from 'foo') rather than pg_catalog.rtrim('foo', 'o'). This follows the changes done in Postgres 14 itself.
  • No E_DEPRECATED errors when running under PHP 8.1

Full release notes

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

$ composer require sad_spirit/pg_builder

pg_builder can be used on its own, using it together with pg_wrapper allows to run the built queries with transparent conversion of query parameters to Postgres types.

pg_wrapper version 2.0.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.

Release highlights

  • Full support for multirange types added in Postgres 14, with types\Multirange and its descendants to represent the values on PHP side and converters\containers\MultiRangeConverter to transform the values to and from DB string representation.
  • Support changes to pgsql extension done in PHP 8.1: objects are used instead of resources for connection and query results.

Full release notes

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

$ composer require sad_spirit/pg_wrapper