Querying
Build a query as a Relation, and run it when you ask for results.
At a glance
| To | Write |
|---|---|
| build a query | User.where({ role: "admin" }).order({ … }).limit(10) |
| run it | .toArray(), .first(), .count() |
| find one record | find(id), findBy({ … }), findByOrThrow({ … }) |
| read attributes without records | pluck("email"), pick("email"), ids() |
| check for records | exists({ … }), hasAny(), hasMoreThanOne() |
| load only some attributes | select("email") |
| match nothing, without a query | none() |
| combine two queries | where(…).merge(other) |
| see the SQL | toSql() |
Each part of a query has a page of its own:
| To | See |
|---|---|
filter with where, comparisons, and sql |
Conditions |
| sort and page | Ordering and pagination |
| count, add up, and group | Calculations |
| name reusable queries | Scopes |
| filter by associated records | Joins |
| walk a large table | Batch iteration |
| send your own SQL | Raw statements |
| load associations with each record | Preloading associations |
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
);Building a Relation
A query is a Relation, and each method that narrows, orders, or pages it returns a new one:
import { class UserUser } from "./models/user";
const const admins: Relation<User, ScopesOf<typeof User>>admins = 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" });
const const firstTen: Relation<User, ScopesOf<typeof User>>firstTen = const admins: Relation<User, ScopesOf<typeof User>>admins.Relation<User, ScopesOf<typeof User>>.limit(count: number): Relation<User, ScopesOf<typeof User>>limit(10); // a new Relation; admins is unchanged
const const users: User[]users = await const firstTen: Relation<User, ScopesOf<typeof User>>firstTen.Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray(); // the query runs here- Nothing runs until you ask for results, with a method such as
toArray(),first(), orcount(). - Relations have no
then, so anasyncfunction that returns one never runs it by accident. - Every Relation method is also a static of every Model:
User.where(…),User.find(id),User.count().User.all()is the Relation itself. - A query runs on the current database: the request’s, if it was opened with
withDatabase, or the Model’s binding. To use another, start fromdb.model(User). See Connections.
Finding records
Find one record with a finder:
import { class UserUser } from "./models/user";
const const user: Useruser = 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" }); // or RecordNotFound
const const nobody: User | nullnobody = 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({ email?: string | readonly string[] | Comparison<string> | undefinedemail: "nobody@example.com" }); // null
const const again: Useragain = await class UserUser.OverloadedStatics.find: <typeof User>(this: typeof User, id: string | number) => Promise<User> (+2 overloads)find(const user: Useruser.id: stringid); // by primary key, or RecordNotFound
const const oldest: User | nulloldest = await class UserUser.first<typeof User>(this: typeof User): Promise<User | null>The first record by this Relation's order, or by primary key if it has none.first(); // by primary key, unless ordered
const const newest: User | nullnewest = await class UserUser.order<typeof User>(this: typeof User, 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: "asc" }).Relation<User, ScopesOf<typeof User>>.last(): Promise<User | null>The last record: the first in the reverse of every order direction. With `limit`/`offset`,
the last record of that page (loaded, then taken from the end).last(); // the order reversed
const const owner: Userowner = await class UserUser.findSoleBy<typeof User>(this: typeof User, conditions: SafeSql | Conditions<User>): Promise<User>The only record matching `conditions`, as `where(conditions).sole()` finds it.findSoleBy({ role?: string | Comparison<string> | readonly (string | null)[] | null | undefinedrole: "owner" }); // the only one, or throwsfindthrowsRecordNotFoundif the id is missing. Given several ids, it returns them in order, from one query, and throws if any are missing.findByresolves tonullwhen nothing matches, andfindByOrThrowthrowsRecordNotFound.findOrCreateBycreates the record. See Finding or creating a record.firstandlastsort by the primary key unless the Relation has an order, andlastreverses that order. Ansqlfragment can’t be reversed, solastthrowsIrreversibleOrderthere.takereads a record with no order at all.firstOrThrow,lastOrThrow, andtakeOrThrowthrowRecordNotFoundinstead of resolving tonull.soleresolves to the only matching record. It throwsRecordNotFoundwhen nothing matches, andSoleRecordExceededwhen more than one does.findSoleBy(conditions)is short forwhere(conditions).sole().
Reading attributes
Read attributes without loading records with pluck, pick, and ids:
import { class UserUser } from "./models/user";
const const emails: string[]emails = await class UserUser.order<typeof User>(this: typeof User, 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({ email?: OrderDirection | {
direction: OrderDirection;
nulls?: NullsPlacement;
} | undefined
email: "asc" }).Relation<User, ScopesOf<typeof User>>.pluck<"email">(attribute: "email"): Promise<string[]> (+1 overload)Converted values of one attribute, or tuples of several, in this Relation's order.pluck("email"); // string[]
const const pairs: [string, string][]pairs = await class UserUser.order<typeof User>(this: typeof User, 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({ email?: OrderDirection | {
direction: OrderDirection;
nulls?: NullsPlacement;
} | undefined
email: "asc" }).Relation<User, ScopesOf<typeof User>>.pluck<readonly ["id", "email"]>(attributes_0: "id", attributes_1: "email"): Promise<[string, string][]> (+1 overload)Converted values of one attribute, or tuples of several, in this Relation's order.pluck("id", "email"); // [string, string][]
const const latest: string | nulllatest = await class UserUser.order<typeof User>(this: typeof User, 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>>.pick<"email">(attribute: "email"): Promise<string | null> (+1 overload)The first row's value of one attribute, or tuple of several, in this Relation's order; or null.pick("email"); // string | null
const const adminIds: (string | number)[]adminIds = 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>>.ids(): Promise<(string | number)[]>Each record's primary key, in this Relation's order: `pluck` of the primary key, leaving out a
loaded association's built records that have no key yet.ids(); // primary keyspluckreads one attribute from every record, or a tuple of several.pickreads from the first record, and resolves tonullwhen nothing matches.idsplucks every primary key. On a loaded association, it leaves out records you’ve built that don’t have a key yet.
Counting and checking
Count records, or check whether any match:
import { class UserUser } from "./models/user";
const const admins: numberadmins = 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>>.count(attribute?: "id" | "email" | "role" | "isActive" | "createdAt" | "updatedAt" | undefined): Promise<number>The number of matching records, or of non-null values of `attribute`.count();
const const hasAdmins: booleanhasAdmins = await class UserUser.exists<typeof User>(this: typeof User, conditions?: SafeSql | Conditions<User> | undefined): Promise<boolean>Whether any record matches, optionally with extra conditions.exists({ role?: string | readonly (string | null)[] | Comparison<string> | null | undefinedrole: "admin" });
const const severalAdmins: booleanseveralAdmins = 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>>.hasMoreThanOne(): Promise<boolean>Whether more than one record matches: one `COUNT` of at most two rows.hasMoreThanOne();
const const oneOwner: booleanoneOwner = 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: "owner" }).Relation<User, ScopesOf<typeof User>>.hasExactlyOne(): Promise<boolean>Whether exactly one record matches: one `COUNT` of at most two rows.hasExactlyOne();count()always agrees with whattoArray()returns, even withlimit,offset, ordistinct.size(),isEmpty(), andhasAny()count records, or check for any. On a loaded association, they answer without a query.hasMoreThanOne()andhasExactlyOne()count no further than two records. On a loaded association, they answer without a query too.
Selecting attributes
Load only some attributes with select:
import { class UserUser } from "./models/user";
const [const first: Userfirst] = await class UserUser.order<typeof User>(this: typeof User, 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({ email?: OrderDirection | {
direction: OrderDirection;
nulls?: NullsPlacement;
} | undefined
email: "asc" }).Relation<User, ScopesOf<typeof User>>.select(...attributes: ("id" | "email" | "role" | "isActive" | "createdAt" | "updatedAt")[]): Relation<User, ScopesOf<typeof User>>Loads only these attributes (a partial record); the primary key is always loaded.select("email").Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();
var console: Consoleconsole.Console.log(...data: any[]): voidThe **`console.log()`** static method outputs a message to the console.
[MDN Reference](https://developer.mozilla.org/docs/Web/API/console/log_static)log(const first: Userfirst?.email: stringemail); // loaded
var console: Consoleconsole.Console.log(...data: any[]): voidThe **`console.log()`** static method outputs a message to the console.
[MDN Reference](https://developer.mozilla.org/docs/Web/API/console/log_static)log(const first: Userfirst?.id: stringid); // the primary key is always loaded too
// first?.role would throw MissingAttribute: it wasn't selected
const const roles: (string | null)[]roles = await class UserUser.select<typeof User>(this: typeof User, ...args: ("id" | "email" | "role" | "isActive" | "createdAt" | "updatedAt")[]): Relation<User, ScopesOf<typeof User>>Loads only these attributes (a partial record); the primary key is always loaded.select("role").Relation<User, ScopesOf<typeof User>>.distinct(): Relation<User, ScopesOf<typeof User>>Removes duplicate rows (of the selected attributes).distinct().Relation<User, ScopesOf<typeof User>>.pluck<"role">(attribute: "role"): Promise<(string | null)[]> (+1 overload)Converted values of one attribute, or tuples of several, in this Relation's order.pluck("role");- The primary key is always loaded too, so the records can still be saved.
- Reading an attribute that wasn’t loaded throws
MissingAttribute, including from a validator or callback.record.hasAttribute("email")says whether it was loaded. - Saving a partial record writes only what changed.
reload()loads the whole row. - After
distinct(), the primary key isn’t added, since it would make every row distinct, so those records can’t be saved.
Matching nothing
none() matches nothing without asking the database, and chaining more onto it keeps it empty:
import { class UserUser } from "./models/user";
const const nothing: User[]nothing = await class UserUser.none<typeof User>(this: typeof User): Relation<User, ScopesOf<typeof User>>A Relation that matches nothing and never queries D1; it stays empty when chained.none().Relation<User, ScopesOf<typeof User>>.where(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>>.toArray(): Promise<User[]>toArray(); // [], with no queryMerging Relations
Combine two Relations of the same Model with merge:
import { class UserUser } from "./models/user";
const const newest: Relation<User, ScopesOf<typeof User>>newest = class UserUser.order<typeof User>(this: typeof User, 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(1);
const const newestAdmin: User[]newestAdmin = 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>>.merge<User, ScopesOf<typeof User>>(other: Relation<User, ScopesOf<typeof User>>): Relation<User, ScopesOf<typeof User>>ANDs `other`'s conditions onto this Relation's. For select, order, limit, offset and
distinct, a clause `other` sets explicitly replaces this one's; one it doesn't set is kept.
Orders are replaced, not concatenated, so a merged order reads as written. `other` may be a
Relation of a Model this one joins once (#24): its conditions apply to the joined table.merge(const newest: Relation<User, ScopesOf<typeof User>>newest).Relation<User, ScopesOf<typeof User>>.toArray(): Promise<User[]>toArray();Their conditions are joined with AND, and anything the argument sets, such as an order, limit, or select, replaces the receiver’s. To merge a Relation of another Model, join that Model first. See Joins.
Seeing a query’s SQL
See the SQL a Relation’s toArray() would send, without sending it, with toSql():
import { class UserUser } from "./models/user";
const { const sql: stringsql, const params: unknown[]params } = 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>>.limit(count: number): Relation<User, ScopesOf<typeof User>>limit(10).Relation<User, ScopesOf<typeof User>>.toSql(): QuerySqlThe SQL `toArray()` sends, and its bound values, without sending it. Values
are never inlined into the SQL (ADR-0006), and none are redacted, filtered attributes included.toSql();
var console: Consoleconsole.Console.log(...data: any[]): voidThe **`console.log()`** static method outputs a message to the console.
[MDN Reference](https://developer.mozilla.org/docs/Web/API/console/log_static)log(const sql: stringsql); // SELECT * FROM "users" WHERE ("role" = ?) LIMIT ?, with $1 and $2 on Postgres
var console: Consoleconsole.Console.log(...data: any[]): voidThe **`console.log()`** static method outputs a message to the console.
[MDN Reference](https://developer.mozilla.org/docs/Web/API/console/log_static)log(const params: unknown[]params); // ["admin", 10]- The values come back in
params, apart from the SQL. Postgres numbers its placeholders:$1,$2. - A
none()Relation shows1 = 0, the condition that matches nothing. toSql()returns the real values, filtered attributes included. To see what a query sent, with those filtered, useonQuery.- Bulk writes and batches have no
toSql(). UseonQueryto see what they sent.