WHERE Modifiers

pg-schemata uses plain JavaScript objects to build WHERE clauses. Pass conditions as an array of objects to findWhere, countWhere, deleteWhere, updateWhere, and other query methods.

Basic equality

Pass column names as keys with their expected values:

await db().users.findWhere([{ role: 'admin' }]);
// WHERE "role" = 'admin'

await db().users.findWhere([{ role: 'admin', is_active: true }]);
// WHERE "role" = 'admin' AND "is_active" = true

Null values produce IS NULL:

await db().users.findWhere([{ last_name: null }]);
// WHERE "last_name" IS NULL

Operators

Use operator objects for more expressive conditions:

$eq — explicit equality

{
  role: {
    $eq: 'admin';
  }
}
// WHERE "role" = 'admin'

$ne — not equal

{
  role: {
    $ne: 'guest';
  }
}
// WHERE "role" != 'guest'

{
  deleted_at: {
    $ne: null;
  }
}
// WHERE "deleted_at" IS NOT NULL

$like / $ilike — pattern matching

{
  email: {
    $like: '%@example.com';
  }
}
// WHERE "email" LIKE '%@example.com'

{
  first_name: {
    $ilike: 'ali%';
  }
}
// WHERE "first_name" ILIKE 'ali%'

$from / $to — range queries

{ created_at: { $from: '2025-01-01', $to: '2025-12-31' } }
// WHERE "created_at" >= '2025-01-01' AND "created_at" <= '2025-12-31'

Can be used separately:

{
  age: {
    $from: 18;
  }
}
// WHERE "age" >= 18

$in — array membership

{
  role: {
    $in: ['admin', 'moderator'];
  }
}
// WHERE "role" IN ('admin', 'moderator')

The array must be non-empty.

$is / $not — null checks

{
  deleted_at: {
    $is: null;
  }
}
// WHERE "deleted_at" IS NULL

{
  deleted_at: {
    $not: null;
  }
}
// WHERE "deleted_at" IS NOT NULL

Currently $is and $not only support null. { $not: null } and { $ne: null } emit identical SQL — $not is an alias for the null case; prefer $ne for anything else.

$max / $min / $sum — subquery operators

{
  score: {
    $max: true;
  }
}
// WHERE "score" = (SELECT MAX("score") FROM "public"."users")

{
  score: {
    $min: true;
  }
}
// WHERE "score" = (SELECT MIN("score") FROM "public"."users")

{
  amount: {
    $sum: true;
  }
}
// WHERE "amount" = (SELECT SUM("amount") FROM "public"."users")

When soft delete is enabled, the subquery applies the same deactivated_at IS NULL filter as the outer query unless includeDeactivated: true is passed.

Combining conditions

Multiple conditions with AND (default)

await db().users.findWhere([{ role: 'admin' }, { is_active: true }], 'AND');
// WHERE "role" = 'admin' AND "is_active" = true

OR conditions

await db().users.findWhere([{ role: 'admin' }, { role: 'moderator' }], 'OR');
// WHERE "role" = 'admin' OR "role" = 'moderator'

Nested boolean logic with $and / $or

await db().users.findWhere([
  {
    $or: [{ role: 'admin' }, { $and: [{ role: 'user' }, { is_active: true }] }],
  },
]);
// WHERE ("role" = 'admin' OR ("role" = 'user' AND "is_active" = true))

Boolean groups alongside plain columns

An object may carry a boolean group and ordinary column keys together. The group becomes its own parenthesized fragment and the plain predicates sit beside it, joined by the outer join type:

await db().users.findWhere([
  { $or: [{ role: 'admin' }, { role: 'owner' }], tenant_id: TENANT },
]);
// WHERE ("role" = $1 OR "role" = $2) AND "tenant_id" = $3

Both $and and $or can appear on the same object, each contributing its own group.

Changed in 3.0.0

Before 3.0.0 an object carrying $and or $or contributed only that group — every ordinary column key beside it was silently discarded, so the query above was not scoped to the tenant. The emitted SQL was valid and simply matched more rows than asked for, which made it invisible except in results.

If you worked around this by splitting the group into a sibling object, that form still works and still means the same thing.

Combining operators on a single column

Multiple operators can be applied to one column:

{ age: { $from: 18, $to: 65 } }
// WHERE "age" >= 18 AND "age" <= 65

{ email: { $ne: null, $ilike: '%@example.com' } }
// WHERE "email" IS NOT NULL AND "email" ILIKE '%@example.com'

Additional query options

Most query methods accept an options object:

Option	Type	Description
columnWhitelist	string[]	Columns to return (SELECT list)
orderBy	string | string[]	Sort columns
limit	number	Maximum rows to return
offset	number	Rows to skip
includeDeactivated	boolean	Include soft-deleted rows (default false)
filters	object	Additional filter object applied with AND

await db().users.findWhere([{ is_active: true }], 'AND', {
  columnWhitelist: ['id', 'email', 'first_name'],
  orderBy: 'email',
  limit: 25,
  offset: 0,
});