Overview

As this package contains a reimplementation of PostgreSQL’s query parser, it is a bit different from the usual breed of “write-only” query builders:

  • Query is represented by an Abstract Syntax Tree consisting of Nodes. This is quite similar to what Postgres does internally.

  • When building a query, it is possible to start with manually written SQL and work from there.

  • Query parts (e.g. new columns for a SELECT or parts of a WHERE clause) can usually be added to the AST either as Nodes or as strings. Strings are processed by Parser, so query being built is automatically checked for correct syntax.

  • Nodes can be removed and replaced in AST (e.g. calling join() method of a node in FROM clause replaces it with a JoinExpression node having the original node as its left argument).

  • AST can be analyzed and transformed, the package takes advantage of this to allow named parameters like :foo instead of standard PostgreSQL’s positional parameters $1 and to infer parameters’ types from SQL typecasts.

Requirements

pg_builder requires at least PHP 8.2 with ctype extension (it is usually installed and enabled by default).

Minimum supported PostgreSQL version is 12.

The ultimate goal of any query builder is to execute the built queries, this can be done using either native pgsql extension with pg_wrapper package or PDO.

Substantial effort was made to optimise parsing, but not parsing is faster anyway, so it is highly recommended to use PSR-6 compatible cache in production for caching the parts of AST and / or the complete queries.

Installation

Require the package with composer:

composer require "sad_spirit/pg_builder:^3"