---
title: Bulk writes and batches
description: "Write many rows in one statement, and send several writes that all succeed or all fail."
seo:
  description: "Write many rows in one SQL statement with insertAll, upsert, updateAll, and deleteAll, and send writes in an atomic batch on D1 or one Postgres transaction."
---

## At a glance

| To                                          | Write                                                    |
| ------------------------------------------- | -------------------------------------------------------- |
| update or delete every row a Relation finds | `updateAll({ … })`, `deleteAll()`, `deleteBy({ … })`     |
| insert rows without loading records         | `insert({ … })`, `insertAll([…])`                        |
| insert, or update a row that clashes        | `upsert({ … }, { uniqueBy: "email" })`, `upsertAll([…])` |
| get attributes back from an insert          | `{ returning: ["id", "body"] }`                          |
| write several statements atomically         | `batch((b) => [b.insert(…), b.updateAll(…)])`            |
| save or destroy records in that batch       | `b.save(record)`, `b.destroy(record)`                    |

## The Models

The examples use these Models, whose attributes come from their tables through the schema `active-record schema` generates:

```ts author.ts
import { hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";

export class Author extends ApplicationRecord {
  articles = hasMany(Article, { dependent: "destroy" });
}
```

```ts article.ts
import { belongsTo, hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Author } from "./author";
import { Comment } from "./comment";

export class Article extends ApplicationRecord {
  author = belongsTo(Author);
  comments = hasMany(Comment, { dependent: "deleteAll" });
}
```

```ts comment.ts
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";

export class Comment extends ApplicationRecord {
  article = belongsTo(Article);
}
```

```ts schema.ts
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 01a9c70ab9ef

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

/** articles — last changed by 0001_create_tables.sql */
export const articles = {
  id: { type: "string", null: false, generateId: true },
  authorId: { type: "string", null: false },
  title: { type: "string", null: false },
} as const;

/** authors — last changed by 0001_create_tables.sql */
export const authors = {
  id: { type: "string", null: false, generateId: true },
  name: { type: "string", null: false },
  email: { type: "string", null: false },
} as const;

/** comments — last changed by 0001_create_tables.sql */
export const comments = {
  id: { type: "string", null: false, generateId: true },
  articleId: { type: "string", null: false },
  body: { type: "string", null: false },
} 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: {
    articles: { attributes: articles, primaryKey: "id" },
    authors: { attributes: authors, primaryKey: "id" },
    comments: { attributes: comments, primaryKey: "id" },
  },
  models: { Article: "articles", Author: "authors", Comment: "comments" },
});

declare module "../models/article" {
  interface Article extends TableAttributes<typeof articles, (typeof Article)["attributes"]> {}
}

declare module "../models/author" {
  interface Author extends TableAttributes<typeof authors, (typeof Author)["attributes"]> {}
}

declare module "../models/comment" {
  interface Comment extends TableAttributes<typeof comments, (typeof Comment)["attributes"]> {}
}
```

```sql D1
CREATE TABLE authors (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);
CREATE UNIQUE INDEX index_authors_on_email ON authors (email);
CREATE TABLE articles (
  id TEXT PRIMARY KEY NOT NULL,
  author_id TEXT NOT NULL REFERENCES authors (id),
  title TEXT NOT NULL
);
CREATE TABLE comments (
  id TEXT PRIMARY KEY NOT NULL,
  article_id TEXT NOT NULL REFERENCES articles (id),
  body TEXT NOT NULL
);
```

```sql Postgres
CREATE TABLE authors (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);
CREATE UNIQUE INDEX index_authors_on_email ON authors (email);
CREATE TABLE articles (
  id UUID PRIMARY KEY,
  author_id UUID NOT NULL REFERENCES authors (id),
  title TEXT NOT NULL
);
CREATE TABLE comments (
  id UUID PRIMARY KEY,
  article_id UUID NOT NULL REFERENCES articles (id),
  body TEXT NOT NULL
);
```

## Writing many rows

Call a bulk write on a Relation. It runs straight away, without validations or callbacks:

```ts
import { column, sql } from "@forestfuture/active-record";
import { Article } from "./models/article";
import { Comment } from "./models/comment";

const article = await Article.findByOrThrow({ title: "Getting started" });
const deleted = await Comment.where({ articleId: article.id }).deleteAll(); // how many
await Article.where({ id: article.id }).updateAll({ title: "Edited" });
await Article.where({ id: article.id }).updateAll(
  sql`${column("title")} = upper(${column("title")})`,
);
const { rowsAffected, rows } = await Comment.insert({ articleId: article.id, body: "First!" });
console.log(deleted, rowsAffected, rows[0]?.id); // 1 1, and the new comment's key
```

- **`updateAll(changes)`** takes attributes or an `sql` fragment, and resolves to the number of rows updated. It leaves `updatedAt` alone. With a `limit` or `offset`, the Relation’s order picks which rows are written.
- **`deleteAll()`** resolves to the number of rows deleted. On `none()`, both send nothing and resolve to `0`.
- **`deleteBy(conditions)`** is `where(conditions).deleteAll()`. **`Comment.delete(id)`** deletes by primary key, and takes an array of keys too. Both resolve to the number of rows deleted.
- **`insert(attrs)`** writes one row. It resolves to `{ rowsAffected, rows }`, and `rows` holds the new row’s primary key. Declared defaults and timestamps fill what isn’t given. A Relation’s equality conditions are written over what is given. So `Article.where({ authorId: jane.id }).insert({ title: "Hi", authorId: sam.id })` writes Jane’s key.

:::note
To run each record’s callbacks, destroy the records with [`destroyAll`](/persistence#destroying-many-records) instead.
:::

### Inserting many rows

Insert many rows at once with `insertAll`:

```ts
import { Article } from "./models/article";
import { Author } from "./models/author";
import { Comment } from "./models/comment";

const article = await Article.findByOrThrow({ title: "Getting started" });
await Comment.insertAll([
  { articleId: article.id, body: "One" },
  { articleId: article.id, body: "Two" },
]);
await Author.upsert({ name: "Jane Doe", email: "jane@example.com" }, { uniqueBy: "email" }); // renames Jane
await Author.insert({ name: "Jane", email: "jane@example.com" }, { onDuplicate: "skip" }); // writes nothing
```

- **`insertAll(rows)`** inserts every row, however many. Every row must have the same attributes. A duplicate throws and writes nothing.
- **`{ onDuplicate: "skip" }`** skips rows that clash with a unique index.
- **`upsert(attrs)`** and **`upsertAll(rows)`** update a row that clashes with the primary key, or with the unique index named by `uniqueBy`. The attributes you give are updated. So are the values a Relation adds to each row, from its equality conditions, its default scope, and `createWith`. Defaults filled in for you aren’t updated. `updatedAt` is set.
- **Through a Relation**, `insertAll`, `upsert`, and `upsertAll` write its equality conditions over each row’s values, as `insert` does.
- **Through a `hasMany`**, an upsert only updates the owner’s records. A row that clashes with another owner’s record isn’t written, counted, or returned. See [Writing many rows](/associations#writing-many-rows).

### Returning attributes

Inserts and upserts return the primary key of each row they write. Name other attributes with `returning`:

```ts
import { Article } from "./models/article";
import { Comment } from "./models/comment";

const article = await Article.findByOrThrow({ title: "Getting started" });
const { rows } = await Comment.insertAll(
  [
    { articleId: article.id, body: "One" },
    { articleId: article.id, body: "Two" },
  ],
  { returning: ["id", "body"] },
);
for (const { id, body } of rows) console.log(id, body);
await Comment.insert({ articleId: article.id, body: "Three" }, { returning: false });
```

- **`rows`** has one entry for each row written, typed from the attributes you name. A row skipped as a duplicate isn’t in it.
- **`returning: false`** returns no rows.
- **Order:** the rows come back in no set order.

> **Compared with Rails**
>
> These are Rails’ `insert_all`, `upsert_all`, `update_all`, and `delete_all`, with two differences. Declared defaults fill what an insert isn’t given, since on D1 defaults are where keys are generated. And skipping duplicates is something you opt in to, so an insert’s duplicate is never silently lost. The `returning` option works as Rails’ does.

## Batching writes

Prepare several writes in `batch`’s callback to write them atomically:

```ts
import { batch } from "@forestfuture/active-record";
import { Article } from "./models/article";
import { Author } from "./models/author";

const authorId = crypto.randomUUID(); // generated in code, so the rows can point at it
await batch((b) => [
  b.insert(Author, { id: authorId, name: "Alex", email: "alex@example.com" }),
  b.insert(Article, { authorId, title: "Hello" }),
  b.updateAll(Article.where({ title: "Getting started" }), { authorId }), // Alex takes it over
]);
```

The callback gets `b`, with a method for each bulk write: `insert`, `insertAll`, `upsert`, `upsertAll`, `updateAll`, `updateCounters`, and `deleteAll`. Each takes what it writes to, then the same arguments as the write itself. Pass a Model to write to its table, or a Relation to write to its rows, such as `Article.where(…)` or `author.articles()`.

`batch` resolves to each write’s result, in order. A bulk write gives the same `{ rowsAffected, rows }` it resolves to when it runs alone, and `rows` is empty for `updateAll` and `deleteAll`.

- **Keys:** rows in one batch can only point at each other through keys that exist before it’s sent, so generate keys in code, with [`generateId`](/models#generating-ids).
- **Errors:** a write that can’t be prepared, such as one with an unknown attribute, rejects the batch before anything is sent.
- **Databases:** `batch` writes a Model’s rows on the current database, and `db.batch` on `db`. A Relation or a record writes on the database it came from. Writes on two databases throw `IncompatibleRelation`.
- **Relations in `db.batch`:** `Article.where(…)` uses the current database. Build it with `db.model(Article).where(…)` to write on `db`.

:::note
The callback returns its writes without awaiting anything, since the batch is sent as one: an atomic batch on D1, and one transaction on Postgres. Load what the writes need before you call `batch`. D1 can’t keep a transaction open across `await`s, so a batch is how you make writes atomic.
:::

> **Compared with Rails**
>
> A batch reads like a `transaction do … end` block, but its callback only prepares writes. They’re sent together when it returns, so it can’t read in between.

## Batching records

Add a record’s save or destroy to a batch with `b.save(record)` and `b.destroy(record)`. Its validations and callbacks still run:

```ts
import { batch } from "@forestfuture/active-record";
import { Article } from "./models/article";
import { Author } from "./models/author";
import { Comment } from "./models/comment";

const article = await Article.findByOrThrow({ title: "Getting started" });
article.title = "Getting started, again";
const author = Author.build({ name: "Kim", email: "kim@example.com" });
const leaving = await Author.findByOrThrow({ email: "sam@example.com" });

const [savedArticle, savedAuthor] = await batch((b) => [
  b.save(article),
  b.save(author),
  b.destroy(leaving), // with Sam's articles and their comments
  b.deleteAll(Comment.where({ articleId: article.id })),
]);
```

When the batch runs:

1. Each record, in order, is validated and runs its before-callbacks. The first that’s invalid or aborts throws `RecordInvalid`, `RecordNotSaved`, or `RecordNotDestroyed`, and nothing is sent.
2. Every write is sent as one atomic batch. If the database refuses it with a constraint error, nothing is written, every record keeps its unsaved state, and each record’s `afterRollback` runs.
3. Each record takes its saved state, then its after-callbacks run.
4. Every record’s [commit callbacks](/callbacks#running-code-after-the-commit) run, in the same order.

A record’s autosaved associations and `dependent` strategies join the same batch. A record listed twice, or already saved by its owner’s autosave, is written once.

To validate a record in a [context of your own](/validations#naming-your-own-contexts), pass the context to `b.save`, such as `b.save(member, { context: "signup" })`.

> **Compared with Rails**
>
> In a Rails transaction, one record’s `after_save` runs before the next record’s `before_save`. In a batch, every before-callback runs first.
