Composing find options with `pipe()` and `Q`

pipe() builds a FindOptions object step by step, left to right. JavaScript
has no |> operator yet, so this is the function form of it: each step takes
the options so far and returns the next ones. It is optional sugar over the
options object every read method already takes; the two forms produce the same
query.

import { pipe, Q, Op } from 'ts-prorm-orm';

const since: Date | undefined = new Date('2026-01-01T00:00:00Z');

const rows = await Order.findAll(
  pipe(
    Q.where({ status: 'paid' }),
    Q.when(since, Q.where({ createdAt: { [Op.gte]: since } })),
    Q.orderBy('createdAt', 'DESC'),
    Q.page(3, 50),
    Q.select('id', 'total'),
  ),
);

pipe() itself only builds an object. With since set, the when applies
and the result is:

({
  where: { [Op.and]: [{ status: 'paid' }, { createdAt: { [Op.gte]: since } }] },
  order: [['createdAt', 'DESC']],
  limit: 50,
  offset: 100,
  attributes: ['id', 'total'],
});

Handed straight to the SQLite dialect's buildSelectQuery, that compiles to
placeholders plus values:

and Order.findAll() on a define() model sends it with the values escaped in:

pipe() returns a plain object, so it works with every method that takes
FindOptions — findAll, findOne, count, findAndCountAll, the
aggregates — and steps are plain values that can be named and reused:

import { pipe, Q } from 'ts-prorm-orm';

const paidOnly = Q.where({ status: 'paid' });
const newestFirst = Q.orderBy('createdAt', 'DESC');

const total = await Order.count(pipe(paidOnly));
const latest = await Order.findOne(pipe(paidOnly, newestFirst));

A search endpoint

The case pipe() is built for: a handler whose filters are all optional.
Each absent filter contributes nothing, so there is no if ladder and no
half-built where object to mutate.

import { pipe, Q, Op } from 'ts-prorm-orm';

interface OrderSearch {
  status?: string;
  minTotal?: number;
  page?: number;
}

export function orderSearch(userId: number, qs: OrderSearch) {
  return pipe(
    Q.where({ userId }),
    Q.when(qs.status, Q.where({ status: qs.status })),
    Q.when(qs.minTotal !== undefined, Q.where({ total: { [Op.gte]: qs.minTotal } })),
    Q.orderBy('createdAt', 'DESC'),
    Q.orderBy('id', 'DESC'),          // tie-breaker, so pages are stable
    Q.page(qs.page ?? 1, 25),
  );
}

const firstPage = await Order.findAll(orderSearch(42, {}));
const filtered  = await Order.findAll(orderSearch(42, { status: 'shipped', minTotal: 100, page: 2 }));

With no filters, only the owner condition is left:

With both filters, each Q.where wraps what came before in another AND:

Because the owner condition is the first step and every later where is
AND-ed onto it, no combination of query-string filters can widen the result
past that user's orders.

How steps merge

Merging follows what reads naturally when the steps are read top to bottom:

Key	Rule
`where`, `having`	AND-ed together: a later step narrows, never replaces.
`order`, `include`	Appended, so `Q.orderBy('a')` then `Q.orderBy('b')` sorts by `a` then `b`.
everything else (`limit`, `offset`, `attributes`, `group`, `lock`, `distinct`, …)	The last value given wins.

An empty where ({}) merges as nothing, so a conditional step that
contributes no condition leaves the query alone. The AND is spelled with
Op.and, the key every where builder renders, and it goes through the same
fail-closed guard as a hand-written where.

import { pipe, Q } from 'ts-prorm-orm';

pipe(Q.where({ status: 'paid' }), Q.orderBy('createdAt', 'DESC'), Q.orderBy('id'));
// { where: { status: 'paid' }, order: [['createdAt', 'DESC'], ['id', 'ASC']] }

Two order or include values can only be combined when both are arrays;
otherwise pipe() throws a TypeError telling you to use Q.orderBy() /
Q.include() or the array form:

import { pipe, Q } from 'ts-prorm-orm';

pipe({ order: 'id' }, Q.orderBy('createdAt'));
// TypeError: pipe(): cannot combine two 'order' values unless both are arrays.
//            Use Q.orderBy() or the array form.

[!WARNING] Last value wins, including across helpers
Q.page() is just limit plus offset, so a later Q.limit() overrides
half of it: pipe(Q.page(2, 20), Q.limit(5)) is { limit: 5, offset: 20 }.
Likewise a second Q.select() replaces the first rather than adding columns.
Put the paging step last.

The steps

Step	Sets	Notes
`Q.where(where)`	`where`	AND-ed onto the where so far.
`Q.having(having)`	`having`	AND-ed onto the having so far.
`Q.orderBy(column, direction?)`	`order`	Appends one sort key. `direction` is `'ASC'` (default) or `'DESC'`, or the `SortDirection` enum; anything else throws `RangeError`.
`Q.select(...columns)`	`attributes`	Replaces any earlier choice.
`Q.include(...includes)`	`include`	Appends.
`Q.groupBy(...columns)`	`group`	Replaces.
`Q.limit(n)`, `Q.offset(n)`	`limit`, `offset`	Non-negative integers, else `RangeError`.
`Q.page(page, size)`	`limit`, `offset`	One-based: `Q.page(3, 50)` is `limit: 50, offset: 100`. `Q.page(0, 10)` throws `Q.page() page numbers start at 1, got 0.`
`Q.distinct()`	`distinct: true`
`Q.lock(lock)`	`lock`	Any `LockOptions` value.
`Q.when(condition, ...steps)`	—	Applies `steps` only when `condition` is truthy.
`Q.compose(...steps)`	—	Groups several steps into one reusable step.

Falsy entries are skipped, so cond && Q.where(...) works as well as
Q.when(cond, ...), and a plain options fragment is accepted in place of a
step and merged by the same rules:

import { pipe, Q } from 'ts-prorm-orm';

const includeDeleted = false;

const options = pipe(
  { where: { status: 'paid' } },              // a fragment, merged like a step
  includeDeleted && { paranoid: false },       // skipped when false
  Q.compose(Q.orderBy('createdAt', 'DESC'), Q.limit(20)),
);

Writing your own step

A step is any (options: FindOptions) => FindOptions that does not mutate its
input. mergeFindOptions(base, next) is exported so a custom step merges by
the same rules as the built-in ones:

import { mergeFindOptions, Op } from 'ts-prorm-orm';
import type { PipeStep } from 'ts-prorm-orm';

const createdBetween =
  (from: Date, to: Date): PipeStep =>
  (options) =>
    mergeFindOptions(options, { where: { createdAt: { [Op.between]: [from, to] } } });

The types are PipeStep (a step), PipeInput (a step, a fragment, or
null / undefined / false) and SortDirectionLike (SortDirection or its
string value), all exported from the package root.

Querying — every option a step can set, with the SQL

Query operators — what goes inside Q.where()

Scopes — the model-level way to name a reusable query fragment

Query safety — the guard a piped where goes through

Composing find options with `pipe()` and `Q`

A search endpoint#

How steps merge#

The steps#

Writing your own step#

Related reading#

A search endpoint

How steps merge

The steps

Writing your own step

Related reading