---
title: Batch iteration
description: "Work through a large table a batch of records at a time, so it never sits in memory all at once."
seo:
  description: "Work through large tables with Active Record’s findEach, findInBatches, and inBatches, a batch of records at a time, within D1’s per-invocation query limit."
---

:::note
Nothing is atomic across batches: each batch is its own query, and its own write. To write several statements all at once or not at all, use a [batch](/batches).
:::

## 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" })`                                       |

| Prop | Type | Default | Description |
| - | - | - | - |
| `batchSize?` | `number` | `1000` | Records in each batch: a whole number from 1. |
| `start?` | `primary key` | - | The first key to visit, included. The largest, with order: desc. |
| `finish?` | `primary key` | - | The last key to visit, included. The smallest, with order: desc. |
| `order?` | `"asc" \| "desc"` | `"asc"` | Which way to walk the primary key. |

## The Model

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

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

export class Post extends ApplicationRecord {}
```

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

```sql D1
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  published_at DATETIME
);
```

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

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

for await (const post of Post.where({ publishedAt: null }).findEach()) {
  console.log(post.title); // 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:

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

for await (const posts of Post.findInBatches({ batchSize: 2 })) {
  console.log(posts.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`:

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

const drafts = Post.where({ publishedAt: null });
for await (const batch of drafts.inBatches({ batchSize: 500 })) {
  await batch.updateAll({ publishedAt: new 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:

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

const firstTwo = Post.findEach({ start: 1, finish: 2 }); // keys 1 and 2, both included
const largestKeysFirst = Post.findInBatches({ order: "desc", 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.

:::note
A row added while you iterate is visited only if its key sorts after the batch you’re on. With random keys, such as UUIDs, whether a new row is visited comes down to chance.
:::

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

> **Compared with Rails**
>
> `findEach`, `findInBatches`, and `inBatches` are `find_each`, `find_in_batches`, and `in_batches`, as async iterators rather than blocks. `inBatches` takes `batchSize`, not `of:`. Rails ignores a Relation’s order with a warning, and applies an offset to every batch. Here both throw `IncompatibleRelation`.
