sad_spirit/pg_builder
pg_builder is a Laravel/PHP query builder for PostgreSQL that generates SQL with support for CTEs, window functions, JSON operators, arrays, and advanced Postgres features, offering a fluent, type-safe API as an alternative to raw SQL.
Install the package (it pulls in sad_spirit/pg_wrapper, the connection/type-conversion layer it is designed to work with):
composer require sad_spirit/pg_builder
Look at these in order:
sad_spirit\pg_builder\Parser — the entry point. It exposes parseSelect(), parseInsert(), parseUpdate(), parseDelete(), parseStatement(), parseExpression() and parseFrom().sad_spirit\pg_builder\nodes\Select / Insert / Update / Delete — the mutable AST you inspect and edit. Every construct is a node (ColumnReference, FunctionCall, subqueries, CTEs, window clauses…).sad_spirit\pg_wrapper\Connection — executes a statement and converts result values to native PHP types (int, DateTimeImmutable, arrays, decoded json, …).tests/ directory — with 0 dependents and thin docs, tests are the de-facto API reference for node shapes.The fastest first use case is "parse an existing query, adjust one clause, run it":
use sad_spirit\pg_builder\Parser;
use sad_spirit\pg_wrapper\Connection;
$connection = new Connection('host=localhost dbname=app');
$parser = new Parser($connection);
$select = $parser->parseSelect(
'SELECT id, email, created_at FROM users WHERE deleted_at IS NULL'
);
// add a per-request filter as a parsed fragment
$select->where->condition = $parser->parseExpression(
'deleted_at IS NULL AND created_at >= :since'
);
$result = $connection->execute($select, ['since' => '2024-01-01']);
foreach ($result as $row) {
// $row['id'] is an int, $row['created_at'] a DateTimeImmutable
}
If you only have a PDO or Laravel connection, the statement is stringable: use $statement->toSql() and execute it yourself. You lose the automatic result typing, but the SQL generation still works.
Template + clone per request. The AST is mutable, so the canonical pattern is: parse a base statement once, clone it (nodes implement deep cloning), mutate the copy, execute it. Never mutate a cached statement in place across requests.
$base = $parser->parseSelect('SELECT id, email FROM users');
$query = clone $base;
$query->where->condition = $parser->parseExpression('country = :country');
$connection->execute($query, ['country' => 'DE']);
Repository methods returning statements. Have your data layer return Select/Insert objects rather than strings. Callers can execute them, log them, or assert on toSql() in unit tests without a live DB. Type-hint against the abstract statement class.
Composable fragments. Parse predicates and subqueries independently and stitch them into a tree. This is what makes the package worth using over string concatenation — fragments stay valid SQL and parameters survive the composition.
$sub = $parser->parseSelect('SELECT user_id FROM orders WHERE total > :min');
// use $sub inside IN (...), a FROM item, a CTE, or a UNION branch
$select->where->condition = $parser->parseExpression('id = ANY(...)');
Dynamic filter/search forms. Loop over a whitelist of filters, parse each predicate, and combine with AND via OperatorExpression. Anything the user supplies goes through the parser (and therefore the grammar), not through your string.
Parameters, never literals. Write :name placeholders in the SQL fragments you parse. The parameter nodes live in the tree, so you can collect names/values in an array and pass them to Connection::execute(). pg_wrapper also uses the parser's type information to convert PHP arrays to Postgres arrays and encode json/jsonb values correctly.
INSERT … RETURNING / UPDATE … RETURNING. Build or parse the statement with RETURNING id, bind values as parameters, and read typed IDs straight off the result — no lastInsertId() dance.
Framework integration. Register Connection and Parser as singletons in a service provider; construct them once per request (or per worker), not per query. If you are on Laravel's DB layer, keep pg_builder purely as a SQL factory (DB::select($stmt->toSql(), $params)) and accept the loss of typed results.
Logging. toSql() keeps placeholders intact, so logging the generated SQL does not leak bound values — a good default for query logs and error reports.
clone a template before modifying, and be careful about attaching the same child node to two parents — clone the child too.SELECT * needs server metadata. The parser uses the connection to expand stars and resolve types. Parsing SELECT * against a database where the table doesn't exist fails even though the SQL is syntactically fine. In tests, either use the real schema or list columns explicitly.toSql() emits named placeholders by default; the serializer can be configured for native $1-style output. Pick one, and don't hand a named-placeholder string to a driver that wants positional parameters.JSON_* functions) can fail to parse client-side even though the server accepts it. This is easy to catch in tests because parsing is pure client work.try/catch and turn them into 400s, and log the offending SQL plus offset.int8 and numeric come back as strings by default (overflow safety). Configure pg_wrapper type converters on the Connection if you need ints/floats.= NULL is still wrong. The builder builds what you wrote; it will not rewrite a null comparison into IS NULL. Use the IS NULL expression nodes.(string) $statement or $statement->toSql() is almost always more useful than var_dump-ing the node tree. Diff the SQL string before and after a mutation to confirm the edit landed where you expected.Parser in your own factory/repository rather than subclassing AST nodes — node internals are not a stable extension point. Because the package has no downstream dependents, treat the changelog as mandatory reading on upgrades (3.x → 4.x changed Parser construction and node namespaces).How can I help you explore Laravel packages today?