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

Batch iteration

Work through a large table a batch of records at a time, so it never sits in memory all at once.

At a glance

To Write
visit each record for await (const post of Post.findEach())
visit each batch as an array for await (const posts of Post.findInBatches())
write a batch at a time for await (const batch of drafts.inBatches()) await batch.updateAll(…)
bound the keys findEach({ start: 1, finish: 100 })
walk from the largest key down findInBatches({ order: "desc" })
PropType
batchSize?number

Records in each batch: a whole number from 1.

Typenumber
Default1000
start?primary key

The first key to visit, included. The largest, with order: desc.

Typeprimary key
finish?primary key

The last key to visit, included. The smallest, with order: desc.

Typeprimary key
order?"asc" | "desc"

Which way to walk the primary key.

Type"asc" | "desc"
Default"asc"

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 Post extends ApplicationRecord {}
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: ad18f958558e

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** posts — last changed by 0001_create_posts.sql */
export const posts = {
  id: { type: "integer", null: false },
  title: { type: "string", null: false },
  publishedAt: "datetime",
} 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: {
    posts: { attributes: posts, primaryKey: "id" },
  },
  models: { Post: "posts" },
});

declare module "../models/post" {
  interface Post extends TableAttributes<typeof posts, (typeof Post)["attributes"]> {}
}
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  published_at DATETIME
);
CREATE TABLE posts (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  published_at TIMESTAMPTZ
);

Visiting each record

findEach loads a Relation’s records a batch at a time, and yields them one by one:

import { class PostPost } from "./models/post";

for await (const const post: Postpost of class PostPost.where<typeof Post>(this: typeof Post, conditions: SafeSql | Conditions<Post>): Relation<Post, ScopesOf<typeof Post>>where({ publishedAt?: Date | readonly (Date | null)[] | Comparison<Date> | null | undefinedpublishedAt: null }).Relation<Post, ScopesOf<typeof Post>>.findEach(options?: IterationOptions): AsyncIterable<Post>
Each record this Relation matches, in primary-key order, loaded a batch at a time (#347): `for await (const user of User.findEach())`.
findEach
()) {
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 post: Postpost.title: stringtitle); // one post at a time, loaded 1,000 at a time
}

Batches go in primary-key order, 1,000 records each by default. Each batch is one query, and starts after the last batch’s key, so a deep batch costs no more than the first.

Visiting each batch

findInBatches yields each batch as an array:

import { class PostPost } from "./models/post";

for await (const const posts: Post[]posts of class PostPost.findInBatches<typeof Post>(this: typeof Post, options?: IterationOptions | undefined): AsyncIterable<Post[]>
This Relation's records as arrays of up to `batchSize`, in primary-key order (#347).
findInBatches
({ IterationOptions.batchSize?: number | undefined
Records in each batch: each is one query. 1000 by default.
batchSize
: 2 })) {
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 posts: Post[]posts.Array<T>.length: number
Gets or sets the length of the array. This is a number one higher than the highest index in the array.
length
); // up to 2 posts at a time
}

includes() preloads each batch’s associations. Breaking out of the loop sends no more queries.

Writing a batch at a time

inBatches yields a Relation of each batch’s records, for updateAll, deleteAll, and destroyAll:

import { class PostPost } from "./models/post";

const const drafts: Relation<Post, ScopesOf<typeof Post>>drafts = class PostPost.where<typeof Post>(this: typeof Post, conditions: SafeSql | Conditions<Post>): Relation<Post, ScopesOf<typeof Post>>where({ publishedAt?: Date | readonly (Date | null)[] | Comparison<Date> | null | undefinedpublishedAt: null });
for await (const const batch: Relation<Post, ScopesOf<typeof Post>>batch of const drafts: Relation<Post, ScopesOf<typeof Post>>drafts.Relation<Post, ScopesOf<typeof Post>>.inBatches(options?: IterationOptions): AsyncIterable<Relation<Post, ScopesOf<typeof Post>>>
A Relation of each batch's records, in primary-key order (#347), to write a batch at a time: `for await (const posts of drafts.inBatches()) await posts.deleteAll()`.
inBatches
({ IterationOptions.batchSize?: number | undefined
Records in each batch: each is one query. 1000 by default.
batchSize
: 500 })) {
await const batch: Relation<Post, ScopesOf<typeof Post>>batch.
Relation<Post, ScopesOf<typeof Post>>.updateAll(changes: Partial<AttributeProperties<OverriddenSpecs<{
    readonly id: {
        readonly type: "integer";
        readonly null: false;
    };
    readonly title: {
        readonly type: "string";
        readonly null: false;
    };
    readonly publishedAt: "datetime";
}, Readonly<Record<string, false | AttributeOverride>> | undefined>>> | SafeSql): Promise<number>
Updates every row this Relation matches, now, setting attribute values or an `sql` fragment: ``updateAll(sql`${column("views")} = ${column("views")} + 1`)``. Resolves to the number of rows updated. Skips validations and callbacks, and leaves `updatedAt` alone. A `none()` Relation sends nothing and resolves to 0.
updateAll
({ publishedAt?: Date | null | undefinedpublishedAt: new
var Date: DateConstructor
new () => Date (+4 overloads)
Date
() }); // one write per 500 drafts
}
  • One query reads each batch’s keys, and the write you run is another.
  • A batch’s Relation keeps your conditions, so a row that stopped matching since the keys were read isn’t written.
  • Each batch is its own write. If one fails, the batches before it stay written.

Choosing the batches

Every method takes the same options:

import { class PostPost } from "./models/post";

const const firstTwo: AsyncIterable<Post>firstTwo = class PostPost.findEach<typeof Post>(this: typeof Post, options?: IterationOptions | undefined): AsyncIterable<Post>
Each record this Relation matches, in primary-key order, loaded a batch at a time (#347): `for await (const user of User.findEach())`.
findEach
({ IterationOptions.start?: string | number | undefined
The primary key to start from, included: the smallest, or with `order: "desc"`, the largest.
start
: 1, IterationOptions.finish?: string | number | undefined
The primary key to finish at, included: the largest, or with `order: "desc"`, the smallest.
finish
: 2 }); // keys 1 and 2, both included
const const largestKeysFirst: AsyncIterable<Post[]>largestKeysFirst = class PostPost.findInBatches<typeof Post>(this: typeof Post, options?: IterationOptions | undefined): AsyncIterable<Post[]>
This Relation's records as arrays of up to `batchSize`, in primary-key order (#347).
findInBatches
({ IterationOptions.order?: "desc" | "asc" | undefined
`"asc"` (the default) from the smallest primary key up, or `"desc"` from the largest down.
order
: "desc", IterationOptions.batchSize?: number | undefined
Records in each batch: each is one query. 1000 by default.
batchSize
: 100 });
  • batchSize sets the records in each batch: a whole number from 1, or TypeError.
  • start and finish bound the primary key, both included. With order: "desc", start is the largest key and finish the smallest.
  • A limit() caps the records across every batch.
  • Batches follow the primary key, so a Relation with its own order throws IncompatibleRelation, a default scope’s order included. Remove it with reorder() first: Post.reorder().findEach().
  • An offset() throws too, so bound the walk with start instead.

Staying within D1’s query limit

On D1, one Worker invocation can send up to 1,000 queries, or 50 on the Free plan:

  • findEach and findInBatches send one per batch.
  • inBatches sends one for the keys, and one for an updateAll or deleteAll.
  • A destroyAll loads the batch’s records and reads each record’s "destroy" and restricted associations, each with a query of its own. It then sends its deletes as one D1 batch, which counts as one query.

A larger batchSize sends fewer queries, up to D1’s 50 MB limit on a query’s result. For a table larger than that, split the work across invocations, each with its own start and finish, such as one Queue message per range.

Last updated on