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 valuesnullmatches 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:findByresolves tonull, andfindthrowsRecordNotFound. undefinedthrowsTypeError. Leave the key out, or usenull.- A value of an attribute with a normalization is normalized first, so
findBy({ email: " Jane@Example.com" })findsjane@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" });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();whereNotnegates the whole condition. As in SQL,whereNot({ role: "admin" })also leaves out users whose role is NULL.orcombines 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): ColumnReferenceNames 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): ColumnReferenceNames 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): ColumnReferenceNames 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): ColumnReferenceNames 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): ColumnValueA 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 isrewhere({ userId }). Start fromPostto read past the owner. - Merging a Relation built with
unscopedrops 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.