---
title: Querying
description: "Build a query as a Relation, and run it when you ask for results."
seo:
  description: "Build Active Record queries as lazy Relations, and run them with find, findBy, first, count, pluck, and exists, with links to conditions, joins, and paging."
---

## 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](/conditions)                           |
| sort and page                               | [Ordering and pagination](/ordering-and-pagination) |
| count, add up, and group                    | [Calculations](/calculations)                       |
| name reusable queries                       | [Scopes](/scopes)                                   |
| filter by associated records                | [Joins](/joins)                                     |
| walk a large table                          | [Batch iteration](/batch-iteration)                 |
| send your own SQL                           | [Raw statements](/raw-statements)                   |
| load associations with each record          | [Preloading associations](/preloading)              |

## The Model

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

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

export class User extends ApplicationRecord {}
```

```ts schema.ts
// 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"]> {}
}
```

```sql D1
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
);
```

```sql Postgres
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:

```ts
import { User } from "./models/user";

const admins = User.where({ role: "admin" }).order({ createdAt: "desc" });
const firstTen = admins.limit(10); // a new Relation; admins is unchanged
const users = await firstTen.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](/connecting).

## Finding records

Find one record with a finder:

```ts
import { User } from "./models/user";

const user = await User.findByOrThrow({ email: "jane@example.com" }); // or RecordNotFound
const nobody = await User.findBy({ email: "nobody@example.com" }); // null
const again = await User.find(user.id); // by primary key, or RecordNotFound
const oldest = await User.first(); // by primary key, unless ordered
const newest = await User.order({ createdAt: "asc" }).last(); // the order reversed
const owner = await User.findSoleBy({ role: "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](/persistence#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`:

```ts
import { User } from "./models/user";

const emails = await User.order({ email: "asc" }).pluck("email"); // string[]
const pairs = await User.order({ email: "asc" }).pluck("id", "email"); // [string, string][]
const latest = await User.order({ createdAt: "desc" }).pick("email"); // string | null
const adminIds = await User.where({ role: "admin" }).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:

```ts
import { User } from "./models/user";

const admins = await User.where({ role: "admin" }).count();
const hasAdmins = await User.exists({ role: "admin" });
const severalAdmins = await User.where({ role: "admin" }).hasMoreThanOne();
const oneOwner = await User.where({ role: "owner" }).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.

:::tip
`exists()`, `isEmpty()`, and `hasAny()` ignore the Relation’s order, so D1 stops at the first matching row. `count()` ignores it too, unless a `limit` or `offset` needs it. D1 bills each row it reads, so checks and counts stay cheap on an ordered Relation, such as an ordered association.
:::

## Selecting attributes

Load only some attributes with `select`:

```ts
import { User } from "./models/user";

const [first] = await User.order({ email: "asc" }).select("email").toArray();
console.log(first?.email); // loaded
console.log(first?.id); // the primary key is always loaded too
// first?.role would throw MissingAttribute: it wasn't selected
const roles = await User.select("role").distinct().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:

```ts
import { User } from "./models/user";

const nothing = await User.none().where({ role: "admin" }).toArray(); // [], with no query
```

## Merging Relations

Combine two Relations of the same Model with `merge`:

```ts
import { User } from "./models/user";

const newest = User.order({ createdAt: "desc" }).limit(1);
const newestAdmin = await User.where({ role: "admin" }).merge(newest).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](/joins#filtering-by-a-joined-model).

## Seeing a query’s SQL

See the SQL a Relation’s `toArray()` would send, without sending it, with `toSql()`:

```ts
import { User } from "./models/user";

const { sql, params } = User.where({ role: "admin" }).limit(10).toSql();
console.log(sql); // SELECT * FROM "users" WHERE ("role" = ?) LIMIT ?, with $1 and $2 on Postgres
console.log(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`](/logging).
- Bulk writes and [batches](/batches#batching-writes) have no `toSql()`. Use [`onQuery`](/logging) to see what they sent.

> **Compared with Rails**
>
> `size()`, `isEmpty()`, and `hasAny()` are Rails’ `size`, `empty?`, and `any?`, and `hasMoreThanOne()` and `hasExactlyOne()` are `many?` and `one?`. `firstOrThrow()` is `first!`, and `findSoleBy` is `find_sole_by`. There’s no `second` or `third`. Use `offset(1).first()` instead. Rails’ `ids` gives `nil` for a built record with no key, where `ids()` leaves it out.
>
> `merge` replaces the receiver’s order, where Rails adds the argument’s to it. `toSql()` is Rails’ `to_sql`, except that Rails writes the values into the SQL and `toSql()` keeps them in `params`.
