30th September 2021: PostgreSQL 14 Released!

pg_builder 1.0.0 and pg_wrapper 1.0.0 packages for PHP released

Posted on 2021-07-06 by Alexey Borzov
Related Open Source

I'm pleased to announce first stable releases of pg_builder and pg_wrapper packages.

Both packages now require PHP 7.2+, are tested and supported on PHP 8 and pass static code analysis with phpstan and psalm tools.

pg_builder version 1.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 13 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

  • Implemented support for Unicode escapes in strings and identifiers.
  • Fixed parsing of function-like constructs in FROM clause.
  • When generating SQL, it is possible to target PDO rather than pg_wrapper / native pgsql extension.
  • Substantial performance improvements, especially when using cache to store parsed queries.

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

  • Types metadata is pre-populated with built-in data types of PostgreSQL. Loading metadata from DB / cache will only be needed when custom types are used.
  • Connection::atomic() method which accepts a callable and executes it atomically. atomic() calls can be nested and can set callbacks to execute after a final COMMIT or ROLLBACK.
  • Specialized exception subclasses that are thrown on database errors based on SQLSTATE error code.
  • Objects representing database types are now immutable.

Full release notes

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

$ composer require sad_spirit/pg_wrapper