Skip to content
Active Record
Esc
↑↓navigate↵open⌘Jpreview
On this page

Conditions

Filter a query’s records by their attributes.

At a glance

To match Write SQL
equal values where({ role: "admin" }) role = ?
NULL where({ role: null }) role IS NULL
any value in a list where({ role: ["admin", "editor"] }) role IN (…)
anything but a value whereNot({ role: "admin" }) NOT (role = ?)
either of two queries where(a).or(User.where(b)) (…) OR (…)
a comparison where({ createdAt: gte(date) }) created_at >= ?
a range where({ createdAt: between(a, b) }) both ends included
anything else where(sql`…`) your SQL, with values bound
a replacement for a condition rewhere({ role: "admin" }) drops the old role = ?
fewer parts of a query unscope("order"), only("where") drops the parts named
all but some records excluding(record) leaves those records out

The Model

The examples use this Model, whose attributes come from its table through the schema active-record schema generates:

import { ApplicationRecord } from "./application-record";

export class User extends ApplicationRecord {}
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 7ace010c47bc

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** users — last changed by 0001_create_users.sql */
export const users = {
  id: { type: "string", null: false, generateId: true },
  email: { type: "string", null: false },
  role: "string",
  isActive: "boolean",
  createdAt: { type: "datetime", null: false },
  updatedAt: { type: "datetime", null: false },
} as const;

/** The database's adapter and binding, its tables (with their primary keys), and the Models built on them. */
export const schema = defineSchema({
  adapter: d1({ binding: "DB" }),
  tables: {
    users: { attributes: users, primaryKey: "id" },
  },
  models: { User: "users" },
});

declare module "../models/user" {
  interface User extends TableAttributes<typeof users, (typeof User)["attributes"]> {}
}
CREATE TABLE users (
  id TEXT PRIMARY KEY NOT NULL,
  email TEXT NOT NULL,
  role TEXT,
  is_active BOOLEAN,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  role TEXT,
  is_active BOOLEAN,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);

Matching values

Pass where an object of attributes that must all match:

import { class UserUser } from "./models/user";

const const admins: User[]admins = await class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "admin", isActive?: boolean | readonly (boolean | null)[] | null | undefinedisActive: true }).Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();

class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: null }); // role IS NULL
class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: ["admin", "editor"] }); // role IN (…), any number of values
  • null matches NULL. An array matches any of its values, and is bound as one value, so it can be any length.
  • Values are cast to the attribute’s type, so text from a query string works as it is: where({ isActive: "false" }) finds inactive users. A value that can’t be cast matches nothing: findBy resolves to null, and find throws RecordNotFound.
  • undefined throws TypeError. Leave the key out, or use null.
  • A value of an attribute with a normalization is normalized first, so findBy({ email: " Jane@Example.com" }) finds jane@example.com.

Attribute names are checked when you compile, and a misspelled one that gets past the compiler throws UnknownAttribute:

import { class UserUser } from "./models/user";

class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ rol: "admin" });
Object literal may only specify known properties, and 'rol' does not exist in type 'SafeSql | Conditions<User>'.

Negating conditions

Negate a condition with whereNot, or combine two queries with or:

import { class UserUser } from "./models/user";

const const others: User[]others = await class UserUser.whereNot<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>
Negates the whole condition: `NOT (…)`. Rows where it is NULL are excluded too.
whereNot
({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "admin" }).Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();
const const either: User[]either = await class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "admin" }) .Relation<User, ScopesOf<typeof User>>.or(other: Relation<User, ScopesOf<typeof User>>): Relation<User, ScopesOf<typeof User>>
Rows matching this Relation's conditions or `other`'s. Both must be of the same Model and identical in everything but their conditions (select, order, limit, offset, distinct).
or
(class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ isActive?: boolean | readonly (boolean | null)[] | null | undefinedisActive: false }))
.Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();
  • whereNot negates the whole condition. As in SQL, whereNot({ role: "admin" }) also leaves out users whose role is NULL.
  • or combines two Relations of the same Model that differ only in their conditions.

Comparing values

Pass a comparison helper as a value:

import { 
function between<T>(start: T, end: T, options?: {
    excludeEnd?: boolean;
}): Comparison<T>
`attribute BETWEEN start AND end`, both ends included. With `excludeEnd`, `start <= attribute < end` (e.g. a window of days).
between
, function gte<T>(value: T): Comparison<T>
`attribute >= value`: everything from `value` on.
gte
, function lt<T>(value: T): Comparison<T>
`attribute < value`: everything before `value`.
lt
} from "@forestfuture/active-record";
import { class UserUser } from "./models/user"; const const since: Datesince = new
var Date: DateConstructor
new (value: number | string | Date) => Date (+4 overloads)
Date
("2026-01-01");
const const until: Dateuntil = new
var Date: DateConstructor
new (value: number | string | Date) => Date (+4 overloads)
Date
("2026-02-01");
class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ createdAt?: Date | Comparison<Date> | readonly Date[] | undefinedcreatedAt: gte<Date>(value: Date): Comparison<Date>
`attribute >= value`: everything from `value` on.
gte
(const since: Datesince) }); // created_at >= ?
class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ createdAt?: Date | Comparison<Date> | readonly Date[] | undefinedcreatedAt: lt<Date>(value: Date): Comparison<Date>
`attribute < value`: everything before `value`.
lt
(const since: Datesince) }); // created_at < ?
class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ createdAt?: Date | Comparison<Date> | readonly Date[] | undefinedcreatedAt:
between<Date>(start: Date, end: Date, options?: {
    excludeEnd?: boolean;
}): Comparison<Date>
`attribute BETWEEN start AND end`, both ends included. With `excludeEnd`, `start <= attribute < end` (e.g. a window of days).
between
(const since: Datesince, const until: Dateuntil) }); // both ends included
class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ createdAt?: Date | Comparison<Date> | readonly Date[] | undefinedcreatedAt:
between<Date>(start: Date, end: Date, options?: {
    excludeEnd?: boolean;
}): Comparison<Date>
`attribute BETWEEN start AND end`, both ends included. With `excludeEnd`, `start <= attribute < end` (e.g. a window of days).
between
(const since: Datesince, const until: Dateuntil, { excludeEnd?: boolean | undefinedexcludeEnd: true }) });

gt, gte, lt, lte, and between work on number, string, and datetime attributes, and dates compare by time. between includes both ends, unless you pass { excludeEnd: true }.

Writing SQL fragments

Write anything else as an sql tagged template, with column() for an attribute’s column:

import { function column(attribute: string): ColumnReference
Names the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).
column
, function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class UserUser } from "./models/user"; const const email: "Jane@Example.com"email = "Jane@Example.com"; const const user: User | nulluser = await class UserUser.findBy<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Promise<User | null>
The first record matching `conditions` (in no particular order), or null.
findBy
(function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`lower(${function column(attribute: string): ColumnReference
Names the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).
column
("email")}) = lower(${const email: "Jane@Example.com"email})`);

Values are always bound, as they are, so a Date is ISO text. To write a value the way its attribute stores it, use column(name).value(v):

import { function column(attribute: string): ColumnReference
Names the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).
column
, function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class UserUser } from "./models/user"; const const createdAt: ColumnReferencecreatedAt = function column(attribute: string): ColumnReference
Names the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).
column
("createdAt");
const const newcomers: User[]newcomers = await class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where( function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`${const createdAt: ColumnReferencecreatedAt} >= ${const createdAt: ColumnReferencecreatedAt.ColumnReference.value(value: unknown): ColumnValue
A value of this attribute for a fragment, converted as the attribute stores it: a `Date` as the integer of an attribute kept as a Unix time, a boolean as 0 or 1, and so on. It's still a bound parameter. Without it, a value has no attribute to say how it's stored (#142).
value
(new
var Date: DateConstructor
new (value: number | string | Date) => Date (+4 overloads)
Date
("2026-01-01"))}`,
).Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();

value works for any attribute, and writes a boolean as 0 or 1 and a JSON value as text. It casts as assigning would, so a value the attribute can’t hold throws TypeError naming the attribute, when where is given the fragment. A filtered attribute’s value is hidden from onQuery, as it is anywhere else.

Replacing conditions

Replace the conditions on some attributes with rewhere:

import { class UserUser } from "./models/user";

const const members: Relation<User, ScopesOf<typeof User>>members = class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "member", isActive?: boolean | readonly (boolean | null)[] | null | undefinedisActive: true });
const const activeAdmins: User[]activeAdmins = await const members: Relation<User, ScopesOf<typeof User>>members.Relation<User, ScopesOf<typeof User>>.rewhere(conditions: Conditions<User>): Relation<User, ScopesOf<typeof User>>
Replaces the conditions on these attributes with these: those on other attributes, and fragments, are kept.
rewhere
({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "admin" }).Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray(); // is_active = ? AND role = ?

Conditions on other attributes stay, and so do sql fragments. A default scope’s condition on the attribute is replaced too. A whereNot or or is replaced only if it names no other attributes.

Drop parts of a query with unscope, or keep only some with only:

import { class UserUser } from "./models/user";

const const recent: Relation<User, ScopesOf<typeof User>>recent = class UserUser.where<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Relation<User, ScopesOf<typeof User>>where({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "admin" }).Relation<User, ScopesOf<typeof User>>.order(spec: SafeSql | OrderSpec<User>): Relation<User, ScopesOf<typeof User>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
createdAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
createdAt
: "desc" }).Relation<User, ScopesOf<typeof User>>.limit(count: number): Relation<User, ScopesOf<typeof User>>limit(10);
const recent: Relation<User, ScopesOf<typeof User>>recent.
Relation<User, ScopesOf<typeof User>>.unscope(...parts: (QueryPart | {
    where: AttributeNames<User>;
})[]): Relation<User, ScopesOf<typeof User>>
Drops parts of the query: `unscope("order", "limit")`, or the conditions on some attributes, `unscope({ where: "role" })`. A default scope's are dropped too.
unscope
("order", "limit"); // every admin, unordered
const recent: Relation<User, ScopesOf<typeof User>>recent.
Relation<User, ScopesOf<typeof User>>.unscope(...parts: (QueryPart | {
    where: AttributeNames<User>;
})[]): Relation<User, ScopesOf<typeof User>>
Drops parts of the query: `unscope("order", "limit")`, or the conditions on some attributes, `unscope({ where: "role" })`. A default scope's are dropped too.
unscope
({ where: AttributeNames<User>where: "role" }); // the 10 newest users
const recent: Relation<User, ScopesOf<typeof User>>recent.Relation<User, ScopesOf<typeof User>>.only(...parts: QueryPart[]): Relation<User, ScopesOf<typeof User>>
Keeps only these parts of the query: `only("where", "order")`.
only
("where"); // every admin
  • The parts are "where", "order", "limit", "offset", "select", "distinct", "includes", "joins", "leftJoins", and "createWith". { where: "role" } drops only the conditions on those attributes.
  • A default scope’s parts are dropped too. To start without the default scope, use unscoped().
  • On an association, the link to its owner stays: user.posts().unscope("where") is still the user’s posts, and so is rewhere({ userId }). Start from Post to read past the owner.
  • Merging a Relation built with unscope drops those parts from the Relation it’s merged into.
  • A join can’t be dropped while a merged condition or order uses it. That throws IncompatibleRelation.

Leaving records out

Leave out records you already have with excluding:

import { class UserUser } from "./models/user";

const const me: Userme = await class UserUser.findByOrThrow<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Promise<User>
Like `findBy`, but throws `RecordNotFound` instead of returning null.
findByOrThrow
({ email?: string | readonly string[] | Comparison<string> | undefinedemail: "jane@example.com" });
const const everyoneElse: User[]everyoneElse = await class UserUser.excluding<typeof User>(this: typeof User, ...args: (User | readonly User[])[]): Relation<User, ScopesOf<typeof User>>
Leaves these records out, by primary key: `excluding(post)`, or `excluding(posts)`. A record not yet saved leaves nothing out.
excluding
(const me: Userme).Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();

It takes records of the Relation’s Model, or arrays of them. A record that isn’t saved yet leaves nothing out.

Last updated on