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

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(), or count().
  • Relations have no then, so an async function 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 from db.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 throws
  • find throws RecordNotFound if the id is missing. Given several ids, it returns them in order, from one query, and throws if any are missing.
  • findBy resolves to null when nothing matches, and findByOrThrow throws RecordNotFound. findOrCreateBy creates the record. See Finding or creating a record.
  • first and last sort by the primary key unless the Relation has an order, and last reverses that order. An sql fragment can’t be reversed, so last throws IrreversibleOrder there. take reads a record with no order at all.
  • firstOrThrow, lastOrThrow, and takeOrThrow throw RecordNotFound instead of resolving to null.
  • sole resolves to the only matching record. It throws RecordNotFound when nothing matches, and SoleRecordExceeded when more than one does. findSoleBy(conditions) is short for where(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 keys
  • pluck reads one attribute from every record, or a tuple of several.
  • pick reads from the first record, and resolves to null when nothing matches.
  • ids plucks 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 what toArray() returns, even with limit, offset, or distinct.
  • size(), isEmpty(), and hasAny() count records, or check for any. On a loaded association, they answer without a query.
  • hasMoreThanOne() and hasExactlyOne() 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[]): void
The **`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[]): void
The **`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 query

Merging 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(): QuerySql
The 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[]): void
The **`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[]): void
The **`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 shows 1 = 0, the condition that matches nothing.
  • toSql() returns the real values, filtered attributes included. To see what a query sent, with those filtered, use onQuery.
  • Bulk writes and batches have no toSql(). Use onQuery to see what they sent.

Last updated on