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:

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

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.

Adding conditions

Pass where an object of attributes that must all match:

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, so where({ quantity: "42" }) finds 42. A value that can’t be cast matches nothing: findBy resolves to null, and find throws RecordNotFound.

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:

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:

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:

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.

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:

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:

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:

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
}

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.

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:

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.

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:

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:

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():

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.

TIP

toSql() returns the real values, filtered attributes included. To see what a query sent, with those filtered, use onQuery.

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

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:

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:

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

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:

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

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.