Copy page

DAL Library

Query DSL

The Query DSL is a composable, type-safe way to express WHERE, ORDER BY, JOIN, and SELECT clauses. Every expression is a plain object the Repository resolves into parameterized SQL at query time — no string interpolation, no SQL injection.

The five building blocks are:

Building block	Purpose	SQL clause
field(Entity, "prop")	Type-safe column reference	table.column
Filter.fieldValue / fieldField / raw / group	WHERE predicates	WHERE ...
Sort.by	Sort direction	ORDER BY ...
Join.on	Join another entity	JOIN ... ON ...
Project.field / expr	Select specific columns or expressions	SELECT ...

field() — type-safe column reference

field(Entity, "property_name") returns a FieldRef — a typed reference to a column on an entity. The property name is checked at compile time against the entity's keys, so a typo is a TypeScript error, not a runtime SQL error.

Code
import { field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";

const nameRef = field(CustomerEntity, "name");
//    ^? FieldRef<CustomerEntity, "name">

// TypeScript error: Argument of type '"nme"' is not assignable to parameter of type 'keyof CustomerEntity & string'.
// const bad = field(CustomerEntity, "nme");

Every Filter, Sort, Join, and Project.field accepts a FieldRef. The Repository resolves it to the qualified <schema>.<table>.<column> name using entity metadata at query time.

Filter — WHERE predicates

Filter is a namespace with four factories. Each returns a FilterExpr — a plain object the query builder turns into a parameterized WHERE clause.

Filter.fieldValue(left, op, right, operand?)

The most common form: compare a column to a literal value. right is bound as a $n parameter — never interpolated into the SQL string.

Code
import { Filter, field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";

const filters = [
  Filter.fieldValue(field(CustomerEntity, "is_active"), "=", true),
  Filter.fieldValue(field(CustomerEntity, "name"), "ILIKE", "Alice%"),
];
// → WHERE customers.is_active = $1 AND customers.name ILIKE $2

Filter.fieldField(left, op, right, operand?)

Compare two columns (useful in joins and self-comparisons).

Code
import { Filter, field } from "@primebrick/dal-pg";
import { OrderEntity } from "./entities/order.entity.js";

const filters = [
  Filter.fieldField(field(OrderEntity, "shipped_at"), ">", field(OrderEntity, "created_at")),
];
// → WHERE orders.shipped_at > orders.created_at

Filter.raw(left, op, right, operand?)

Escape hatch for raw SQL fragments. Use sparingly — left and right are emitted as-is into the SQL string, so you are responsible for safety. Prefer fieldValue whenever the operand is a literal.

Code
import { Filter } from "@primebrick/dal-pg";

const filters = [
  Filter.raw("EXTRACT(YEAR FROM customers.created_at)", "=", "2025"),
];
// → WHERE EXTRACT(YEAR FROM customers.created_at) = 2025

Filter.group(filters, operand?)

Group filters with parentheses. The operand controls whether the group is AND-ed or OR-ed with its siblings.

Code
import { Filter, field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";

const filters = [
  Filter.fieldValue(field(CustomerEntity, "is_active"), "=", true),
  Filter.group([
    Filter.fieldValue(field(CustomerEntity, "name"), "ILIKE", "Alice%"),
    Filter.fieldValue(field(CustomerEntity, "name"), "ILIKE", "Bob%"),
  ], "OR"),
];
// → WHERE customers.is_active = $1 AND (customers.name ILIKE $2 OR customers.name ILIKE $3)

SqlOperator

The full set of operators accepted by Filter.fieldValue / fieldField / raw:

Operator	SQL	Notes
=	=	equality
!= / <>	!= / <>	inequality (both accepted)
<, <=, >, >=	<, <=, >, >=	comparison
ILIKE	ILIKE	case-insensitive LIKE (PostgreSQL)
LIKE	LIKE	case-sensitive LIKE
IN	IN	right is an array → = ANY($n)
NOT IN	NOT IN	right is an array → <> ALL($n)
BETWEEN	BETWEEN	right is [low, high]
IS / IS NOT	IS / IS NOT	for NULL / TRUE / FALSE

SqlExpressionOperand

Every filter accepts an optional operand: "AND" | "OR" (default "AND") that controls how it combines with the previous filter in the array. This is how you build WHERE a AND (b OR c) without nested groups — though Filter.group is clearer for that case.

Sort — ORDER BY

Sort.by(field, dir?) returns a SortingExpr. dir defaults to "ASC".

Code
import { Sort, field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";

const sorting = [
  Sort.by(field(CustomerEntity, "created_at"), "DESC"),
  Sort.by(field(CustomerEntity, "name"), "ASC"),
];
// → ORDER BY customers.created_at DESC, customers.name ASC

SqlSortDirection is "ASC" | "DESC".

Join — JOIN another entity

Join.on(left, right, type?, options?) returns a JoinExpr. The left is the joined table's field; right is the base table's field. The argument order matches standard SQL JOIN left ON left.x = right.y reading order.

Breaking change in 0.1.9: the argument order was swapped from Join.on(right, left, ...) to Join.on(left, right, ...). See Changelog.

Code
import { Join, field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";
import { OrderEntity } from "./entities/order.entity.js";

const joins = [
  Join.on(
    field(OrderEntity, "customer_id"),   // left  — joined table
    field(CustomerEntity, "id"),         // right — base table
    "LEFT",                              // type  — INNER | LEFT | RIGHT (default INNER)
  ),
];
// → LEFT JOIN orders ON orders.customer_id = customers.id

castRightTo / castLeftTo — type casts in the ON clause

When the two sides of an ON have different PG types (e.g. text vs uuid), use castRightTo / castLeftTo to add an explicit cast. This is how buildAuditTrailJoins() safely joins changed_by (text) to user_profile.uuid (uuid): the cast triggers a regex guardrail that excludes non-UUID values like "system" rather than throwing a cast error.

Code
Join.on(
  field(UserProfileEntity, "uuid"),
  field(AuditLogEntity, "changed_by"),
  "LEFT",
  { castRightTo: "uuid", castLeftTo: "uuid" },
),
// → LEFT JOIN user_profile ON user_profile.uuid::uuid = audit_log.changed_by::uuid

SqlJoinType is "INNER" | "LEFT" | "RIGHT".

Project — SELECT specific columns or expressions

By default, finders select every column of the entity. Pass a projections array to select specific columns or SQL expressions. Each projection becomes a column in the result row.

Project.field(fieldRef, alias?)

Select a column from the entity (or a joined entity).

Code
import { Project, field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";

const projections = [
  Project.field(field(CustomerEntity, "uuid")),
  Project.field(field(CustomerEntity, "name"), "display_name"),
];
// → SELECT customers.uuid, customers.name AS display_name

Project.expr(expr, alias)

Select a raw SQL expression with an alias. Use for aggregates, function calls, or computed columns.

Code
import { Project } from "@primebrick/dal-pg";

const projections = [
  Project.expr("COUNT(*)", "row_count"),
  Project.expr("MAX(customers.created_at)", "latest_created"),
];
// → SELECT COUNT(*) AS row_count, MAX(customers.created_at) AS latest_created

Putting it all together

A complete query with filters, sorting, a join, and projections:

Code
import { getDal, Filter, Sort, Join, Project, field } from "@primebrick/dal-pg";
import { CustomerEntity } from "./entities/customer.entity.js";
import { OrderEntity } from "./entities/order.entity.js";
import { UserProfileEntity } from "./entities/user-profile.entity.js";

const dal = getDal({ connectionString: process.env.DATABASE_URL!, schema: "myapp" });

const rows = await dal.findAll(
  CustomerEntity,
  [
    Project.field(field(CustomerEntity, "uuid")),
    Project.field(field(CustomerEntity, "name")),
    Project.field(field(UserProfileEntity, "display_name"), "created_by_name"),
    Project.expr("COUNT(orders.id)", "order_count"),
  ],
  {
    filters: [
      Filter.fieldValue(field(CustomerEntity, "is_active"), "=", true),
      Filter.fieldValue(field(CustomerEntity, "created_at"), ">=", new Date("2025-01-01")),
    ],
    joins: [
      Join.on(field(OrderEntity, "customer_id"), field(CustomerEntity, "id"), "LEFT"),
      Join.on(
        field(UserProfileEntity, "uuid"),
        field(CustomerEntity, "created_by"),
        "LEFT",
        { castRightTo: "uuid", castLeftTo: "uuid" },
      ),
    ],
    sorting: [
      Sort.by(field(CustomerEntity, "created_at"), "DESC"),
    ],
  },
);
// Each row has: { uuid, name, created_by_name, order_count }

The generated SQL (simplified):

Code
SELECT customers.uuid, customers.name,
       user_profile.display_name AS created_by_name,
       COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
LEFT JOIN user_profile ON user_profile.uuid::uuid = customers.created_by::uuid
WHERE customers.is_active = $1 AND customers.created_at >= $2
ORDER BY customers.created_at DESC

Next steps

• Repository — pass these expressions to findAll, findByPage, find, count.
• Entities & decorators — the entities that field() references.
• Audit trail — buildAuditTrailJoins() uses Join.on with castRightTo.
• Architecture — how the query builder turns these expressions into SQL.