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" }) |
batchSize?number
Records in each batch: a whole number from 1.
number1000start?primary key
The first key to visit, included. The largest, with order: desc.
primary keyfinish?primary key
The last key to visit, included. The smallest, with order: desc.
primary keyorder?"asc" | "desc"
Which way to walk the primary key.
"asc" | "desc""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[]): voidThe **`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 | undefinedRecords in each batch: each is one query. 1000 by default.batchSize: 2 })) {
var console: Consoleconsole.Console.log(...data: any[]): voidThe **`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: numberGets 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 | undefinedRecords 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 | undefinedThe primary key to start from, included: the smallest, or with `order: "desc"`, the largest.start: 1, IterationOptions.finish?: string | number | undefinedThe 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 | undefinedRecords in each batch: each is one query. 1000 by default.batchSize: 100 });batchSizesets the records in each batch: a whole number from 1, orTypeError.startandfinishbound the primary key, both included. Withorder: "desc",startis the largest key andfinishthe 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 withreorder()first:Post.reorder().findEach(). - An
offset()throws too, so bound the walk withstartinstead.
Staying within D1’s query limit
On D1, one Worker invocation can send up to 1,000 queries, or 50 on the Free plan:
findEachandfindInBatchessend one per batch.inBatchessends one for the keys, and one for anupdateAllordeleteAll.- A
destroyAllloads 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.