---
url: https://docs.forestfuture.dev/d1-record/guide/querying.md
description: >-
  Reference for querying: a Model's statics, Relations, where and comparisons,
  sql fragments, ordering and paging, paginate, find/findBy/first/count, partial
  records, none(), merge, scopes, and preloading.
---

# Querying

A query is a **Relation**. Each method that narrows, orders, or pages it returns a new Relation, and nothing runs until you ask for results:

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

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
```

::: warning TIP
Relations have no `then`, so an `async` function that returns one never runs it by accident. Ask for results with a method such as `toArray()`, `first()`, or `count()`.
:::

## Starting a query

Every Relation method is also a static of every Model: `User.where(…)`, `User.find(id)`, `User.count()`, `User.create(…)`. `User.all()` is the Relation itself, and scopes are statics too: `User.active()`.

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).

## Adding conditions

Pass `where` an object of attributes that must all match:

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

User.where({ role: "admin", isActive: true }); // role = ? AND is_active = ?
User.where({ role: null }); // role IS NULL
User.where({ role: ["admin", "editor"] }); // role IN (…), any number of values
User.whereNot({ role: "admin" }); // NOT (role = ?)
User.where({ role: "admin" }).or(User.where({ isActive: false }));
```

* `null` matches NULL, and an array matches any of its values. The list is bound as one value, so it can be any length.
* `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.
* `undefined` throws `TypeError`. Leave the key out, or use `null`.

Values are [cast to the attribute’s type](./models#casting-assigned-values), so `where({ quantity: "42" })` finds 42. A value that can’t be cast matches nothing: `findBy` resolves to `null`, and `find` throws `RecordNotFound`.

::: warning TIP
In a list, values that can’t be cast are left out. So `whereNot({ id: "abc" })` on an integer key excludes nothing.
:::

### Comparing values

Pass a comparison helper as a value:

```ts
import { between, gte, lt } from "@forestfuture/d1-record";
import { Post, User } from "./models";

Post.where({ publishedAt: gte(since) }); // published_at >= since
User.where({ createdAt: lt(since) }); // created_at < since
User.where({ createdAt: between(since, until) }); // both ends included
User.where({ createdAt: between(since, until, { excludeEnd: true }) });
```

`gt`, `gte`, `lt`, `lte`, and `between` work on number, string, and datetime attributes, and dates compare by time. `between` includes both ends. Pass `{ excludeEnd: true }` to leave out the end.

### Writing SQL fragments

For anything else, write an `sql` tagged template. Values are always bound, and `column()` looks up an attribute’s column:

```ts
import { column, sql } from "@forestfuture/d1-record";
import { User } from "./models";

const email = "Ada@Example.com";
User.where(sql`lower(${column("email")}) = lower(${email})`);
```

## Ordering and paging

Sort by each key in turn with `order`, and page with `limit` and `offset`:

```ts
import { column, sql } from "@forestfuture/d1-record";
import { User } from "./models";

User.order({ role: "asc", createdAt: "desc" });
User.order({ role: { direction: "asc", nulls: "last" } });
User.order(sql`lower(${column("email")})`);
User.order({ role: "asc" }).reorder({ email: "asc" }); // replaces the order
User.order({ createdAt: "desc" }).limit(20).offset(40); // page 3
```

Calling `order` again adds more keys. `reorder` replaces the order so far, and `reorder()` removes it. To get a page with its total, use [`paginate`](#paginating).

::: warning TIP
NULLs sort first in ascending order, as in SQLite. Place them with `nulls: "first"` or `nulls: "last"`.
:::

## Paginating

`paginate` resolves to one page of records and the total they’re a page of, in a single round trip to D1:

```ts
import { Post } from "./models";

export async function listPosts(request: Request): Promise<Response> {
  const page = new URL(request.url).searchParams.get("page");
  const posts = await Post.order({ publishedAt: "desc" }).paginate({ page, perPage: 20 });
  return Response.json(posts); // { records, page, perPage, total, totalPages, hasNext, hasPrevious }
}
```

* `page` and `perPage` take numbers, or text from a query string. Missing, `null`, or empty, they mean the first page and the default size. Anything that isn’t a whole number from 1 throws `TypeError`.
* Pages sort by the Relation’s order, then by the primary key, so records that tie keep their pages between requests.
* A page past the last has no records, but still has the real `total`.
* `includes()` preloads the page’s associations after it, with one more query per association, as for `toArray()`.
* A Relation with its own `limit` or `offset` throws `IncompatibleRelation`.

### Skipping the count

Counting reads every matching row, and D1 bills each row it reads. If you only need to know whether there’s a next page, pass `count: false`:

```ts
import { Post } from "./models";

const posts = Post.order({ publishedAt: "desc" });
const { records, hasNext } = await posts.paginate({ page: 2, count: false });
```

The page then has no `total` or `totalPages`.

### Setting the page size

By default, a page holds 25 records, and none holds more than 100. To change both, set `perPage` and `maxPerPage` on your app’s base class:

```ts
import { Model } from "@forestfuture/d1-record";

export class Base extends Model.Base {
  static override perPage = 20; // when paginate() isn't given a perPage
  static override maxPerPage = 50; // a larger perPage is capped at this
}
```

::: warning TIP
D1 limits neither the rows a query returns nor the size of its response. `maxPerPage` keeps one request from reading, and paying for, too many rows, within a Worker’s 128 MB of memory and a query’s 30 seconds.
:::

::: rails Beyond Rails
Rails leaves pagination to gems such as Kaminari and Pagy. d1-record builds it in, and sends the count and the page to D1 together.
:::

## Getting results

Run a query with one of these methods:

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

const all = await User.toArray();
const user = await User.find("3b241101-e2bb-4255-8caf-4136c566a962"); // or RecordNotFound
const two = await User.find(["id-1", "id-2"]); // in the ids' order
const byEmail = await User.findBy({ email: "ada@example.com" }); // or null
const oldest = await User.first(); // by primary key, unless ordered
const newest = await User.order({ createdAt: "asc" }).last(); // order reversed
const any = await User.take(); // no ordering at all
const emails = await User.pluck("email"); // string[]
const pairs = await User.pluck("id", "email"); // [string, string][]
const admins2 = await User.where({ role: "admin" }).count();
const hasAdmins = await User.exists({ role: "admin" });
```

* `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`.
* `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.
* `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.

::: rails Compared with Rails
`size()`, `isEmpty()`, and `hasAny()` are Rails’ `size`, `empty?`, and `any?`.
:::

## Selecting attributes

Load only some attributes with `select`. The primary key is always loaded too, so the records can still be saved:

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

const partial = await User.select("email").toArray();
console.log(partial[0]?.email); // loaded
console.log(partial[0]?.id); // the primary key is always loaded too
// partial[0]?.role would throw MissingAttribute: it wasn't selected
const roles = await User.select("role").distinct().pluck("role");
```

* Reading an attribute that wasn’t loaded throws `MissingAttribute`, including from a validator or callback.
* 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 D1, so it needs no database to answer. Chaining more onto it keeps it empty:

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

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

## Seeing a query’s SQL

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

```ts
import { sql } from "@forestfuture/d1-record";
import { User } from "./models";

const { sql: text, params } = User.where({ role: "admin" }).limit(10).toSql();
console.log(text); // SELECT * FROM "users" WHERE ("role" = ?) LIMIT ?
console.log(params); // ["admin", 10]
```

The values come back in `params`, apart from the SQL.

A Statement from `toInsert`, `toUpdateAll`, `toDeleteAll`, and the other `to…` methods has `toSql()` too. A `none()` Relation shows `1 = 0`, the condition that matches nothing.

::: warning TIP
`toSql()` returns the real values, filtered attributes included. To see what a query sent, with those filtered, use [`onQuery`](./logging).
:::

A record’s `toSave()` and `toDestroy()` have no `toSql()`. Their SQL depends on validations and callbacks that only run inside `db.batch`.

::: rails Compared with Rails
`toSql()` is Rails’ `to_sql`, except that Rails writes the values into the SQL and `toSql()` keeps them in `params`.
:::

## Defining scopes

A scope is a named, reusable part of a query. Declare one as a static that receives the Relation, plus any arguments, and returns a narrower one:

```ts
import { column, Model, sql } from "@forestfuture/d1-record";

export class User extends Model({
  id: { type: "string", null: false, generateId: true },
  email: { type: "string", null: false },
  role: "string",
  isActive: "boolean", // column: is_active
  htmlURL: "string", // column: html_url (a run of capitals is one word)
  legacyId: { type: "integer", column: "person_identifier" },
  settings: { type: "json", default: () => ({ theme: "light" }) },
  createdAt: "datetime",
  updatedAt: "datetime",
}) {
  static primaryKey = "id"; // the default

  static active = this.scope((q) => q.where({ isActive: true }));
  static createdAfter = this.scope((q, since: Date) =>
    q.where(sql`${column("createdAt")} > ${since}`),
  );
}
```

Call it as a static, or on any Relation of the Model:

```ts
const admins = await User.active().where({ role: "admin" }).toArray();
const recent = await User.createdAfter(new Date(Date.now() - 86_400_000)).count();
```

* A scope’s arguments, and anything it computes such as `new Date()`, are evaluated when you call it.
* Scopes are inherited, including those on your app’s base class ([Shared behavior](./shared-behavior)). A subclass can replace one with `static override active = this.scope(…)`.
* A scope can’t reuse a Relation method’s name (`where`, `count`) or a Model static’s (`validates`, `scope`, `all`).
* Inside a scope, `q` has every Relation method but not the Model’s other scopes. Share logic between scopes with a plain function.

::: warning TIP
A scope whose body names its own class needs its type written out: `static roots: ScopeStatic<[]> = this.scope(…)`.
:::

## Merging Relations

Combine two Relations of the same Model with `merge`. Their conditions are joined with AND, and anything the argument sets, such as an order, limit, or select, replaces the receiver’s:

```ts
const recent = User.order({ createdAt: "desc" }).limit(10);
const admins = await User.where({ role: "admin" }).merge(recent).toArray();
```

::: rails Compared with Rails
Rails adds the argument’s order to the receiver’s. d1-record replaces it.
:::

## Preloading associations

`includes` loads associations for every record, with one query per association. See [Associations](./associations#preloading-associations).