Dbal Tools Laravel Package

Getting Started

phpro/dbal-tools is a thin layer of Doctrine DBAL expression objects (plus a couple of table-introspection helpers) aimed at PostgreSQL. You reach for it when Laravel's query builder can't express what you need — jsonb_agg, jsonb_object_agg, ordered aggregates, FILTER clauses — and you don't want to hand-write SQL strings scattered across your codebase.

composer require phpro/dbal-tools

It requires doctrine/dbal ^4.4|^4.5 (4.5 support landed in 2.15.0). Laravel already ships DBAL transitively in most apps; if you've pinned an older DBAL, resolve that first.

Where to look first: vendor/phpro/dbal-tools/src/Expression/. Every expression is a small stringable class, so reading two or three of them tells you the whole API shape. For the new addition, look at JsonbAggStrict and JsonbObjectAgg side by side — they share the optional OrderBy parameter and behave identically apart from the SQL function emitted.

First use case — build a keyed JSON object per group instead of an array of rows:

use Phpro\DbalTools\Expression\JsonbObjectAgg;
use Phpro\DbalTools\Expression\OrderBy;
use Phpro\DbalTools\Expression\SqlExpression;

$expression = new JsonbObjectAgg(
    new SqlExpression('locale'),
    new SqlExpression('row_data'),
    new OrderBy(new SqlExpression('locale')),
);

// jsonb_object_agg(locale, row_data ORDER BY locale)

Implementation Patterns

Compose, don't concatenate. Everything is an expression object, so you nest rather than interpolate. The OrderBy inside the aggregate is the same OrderBy you'd use at statement level — it renders inside the parentheses, which is exactly the syntax PostgreSQL wants:

->select('tenant_id')
->selectRaw((string) new JsonbObjectAgg(
    new SqlExpression('locale'),
    new SqlExpression('row_data'),
    new OrderBy(new SqlExpression('locale'))
))
->groupBy('tenant_id')

Use the string cast at the boundary. (string) $expression is the seam between the typed expression world and Laravel's selectRaw() / whereRaw(). Keep the expression objects in your own service or query-object classes and only stringify where Laravel requires raw SQL.

Prefer SqlExpression for identifiers, bindings for values. new SqlExpression('locale') is for column names and known-safe identifiers. Literal user input should still go through bindings — build the aggregate around a placeholder and bind alongside it, or wrap untrusted values in a validated cast first.

Escape hatch for unsupported functions. When the package doesn't have an expression for a function you need, subclass the base expression and implement __toString(). The codebase is deliberately small; extending it is cheaper than fighting DB::raw().

Introspection in migrations and generators. Table::columnType() and Table::columnTypes() remain the supported way to resolve a column's DBAL type without touching the deprecated Column::getType(). If you have custom generators, schema-diff tooling, or code that builds migration stubs from an existing database, route through these helpers so you stay compatible with DBAL 4.5.

Testing. Expressions are pure value objects that stringify deterministically — assert on the SQL string in a unit test rather than hitting the database. Save integration tests for the aggregate semantics (ordering, duplicate keys) that only PostgreSQL can confirm.

Gotchas and Tips

A null key is a hard error. jsonb_object_agg raises at query time if the key expression evaluates to NULL for any row. Guard the source column with NOT NULL, a COALESCE(..., 'fallback'), or a WHERE key IS NOT NULL filter. This is the single most common runtime failure with the new expression, and it's invisible until the offending row exists.

Duplicate keys silently collapse. PostgreSQL keeps the last value in aggregation order — but "aggregation order" is only well-defined if you supply an OrderBy. Without one, which value survives is an implementation detail of the scan, so the same query can return different results after an index change or a plan flip. Always pass an OrderBy when the key column isn't unique, and make the tiebreaker column explicit in code review.

Ordering inside the aggregate can cost you parallelism. An ORDER BY inside an aggregate forces PostgreSQL to aggregate serially for that step. On large fact tables that's a real plan change — check EXPLAIN before adding the order clause to a hot query, and consider pre-aggregating or sorting in a CTE instead.

DBAL 4.5 deprecations are now dodged, not just avoided. 2.15.0 stops calling Column::getType() internally. If you call that method in your own code alongside this package, you'll still see the deprecation — the fix is in your code, not the library's.

Raw SQL means no automatic quoting. SqlExpression emits identifiers verbatim. Reserved words, mixed case, and schema-qualified names need to be quoted yourself, or you'll get syntax errors that look like the expression is broken.

Check the PostgreSQL version. The jsonb_* aggregates exist since 9.5, but ordered-aggregate syntax and jsonb normalisation behaviour (duplicate key handling, whitespace, key ordering on output) have shifted across versions. Pin your CI database image to the production version rather than testing against whatever postgres:latest resolves to.

Debug by stringifying, not by reading the query log. Laravel's log shows the final SQL with bindings substituted, which obscures whether an expression rendered correctly. var_dump((string) $expression) gives you the exact fragment the package produced.