Bulk insert and update with Drizzle

Bulk insert, update and delete with Drizzle in NestJS. Each mass method is one statement in a transaction, and a failure lists every failed item.

For a Drizzle bulk insert or update in NestJS, call massCreate, massUpdate, massSoftDelete, massRestore, massDelete, massForceDelete or massUpsert on your service. Each one writes the whole set with one statement inside a single transaction. If any item fails, nothing is written and the call throws a BulkOperationException, a 400 whose body lists every failed item with its own status and message.

All or nothing

A bulk call that returns has written every row. A bulk call that throws has written none of them. You never have to clean up a half-finished batch.

Which bulk methods are there?

Seven, one per single-row write:

users.bulk.ts
await users.massCreate([dto1, dto2, dto3]);
await users.massUpdate([1, 2, 3], { status: 'archived' });
await users.massSoftDelete([1, 2, 3]);
await users.massRestore([1, 2, 3]);
await users.massDelete([1, 2, 3]);
await users.massForceDelete([1, 2, 3]); // soft-deleted rows too
await users.massUpsert(rows, { conflictTarget: 'email' });
MethodReturnsNotes
massCreate(data[], options?)Promise<T[]>One multi-row insert.
massUpdate(ids[], data, options?)Promise<T[]>The same changes applied to every id.
massSoftDelete(ids[], options?)Promise<boolean>Needs soft delete on the entity.
massRestore(ids[], options?)Promise<T[]>Needs soft delete on the entity.
massDelete(ids[], options?)Promise<boolean>Hard delete. With soft delete on, an id already in the trash is a 404 entry.
massForceDelete(ids[], options?)Promise<boolean>Hard delete, including rows that are in the trash.
massUpsert(data[], upsert?, options?)Promise<T[]>See Upsert.

Duplicate ids are written once. The rows come back in the order of the ids you passed. massSoftDelete and massRestore are a 400 BadRequestException when soft delete is not enabled for the entity.

How many queries does a bulk call cost?

A fixed number, whatever the number of ids:

MethodStatements
massCreateone multi-row INSERT ... RETURNING
massUpdateone SELECT to check every id exists, one UPDATE ... WHERE id IN (...) per distinct SET (one, unless beforeUpdate returns different data per id; one shared sql expression object counts as one value)
massSoftDelete, massRestoreone SELECT, one UPDATE ... WHERE id IN (...)
massDelete, massForceDeleteone SELECT, one DELETE ... WHERE id IN (...)
massUpsertone INSERT ... ON CONFLICT DO UPDATE

So massUpdate over 100 ids is two queries, not two hundred.

Two exceptions on MySQL, which has no RETURNING: one more SELECT reads the written rows back, and massCreate of rows with auto-increment keys inserts row by row, because MySQL only reports the first id of a multi-row insert, then reads them all back with one SELECT (since 3.8.0; before, one per row). If every row carries its own primary key, massCreate stays one INSERT plus one SELECT.

On SQLite the statements are the same as on PostgreSQL, but only with a driver that has async transactions, such as libSQL. On Cloudflare D1 and on synchronous drivers (better-sqlite3, bun:sqlite, sql.js) every bulk method throws a TransactionException that says why; SQLite, libSQL and Cloudflare D1 has the details.

What does a bulk call options object take?

Every bulk method takes the same SqlOperationOptions as its single-row counterpart. These are the ones that matter most for bulk writes:

transactionOptional

Drizzle transaction

Run inside your own transaction instead of opening a new one. See Transactions.
bulkModeOptional

"set" | "perItem"

One statement for the whole set, or the single-row method once per item in its own savepoint. massUpsert always uses one statement.

Defaults to "set"

relationsOptional

string[]

Relations to load onto the returned rows, one query per relation for the whole batch.
countsOptional

string[]

Relation counts to put on each returned row under _count.
expectedVersionsOptional

Record<string, number>

With a version column: the expected version per id, keyed by the id as a string. An id at another version is a 409 entry.
hooks.skipBeforeOptional

boolean

Skip the before hooks for every item.

Defaults to false

hooks.skipAfterOptional

boolean

Skip the after hooks for every item.

Defaults to false

skipEventsOptional

boolean

Do not raise module-level events for this call.

Defaults to false

What happens when one item fails?

The whole call is rolled back and throws BulkOperationException. Every bulk method runs in one transaction, so no partial write is ever visible to a later read.

When the set statement fails, the package still needs to know which items caused it. It replays the batch one item at a time, each in its own savepoint, and reports the items that fail on their own. That replay only runs on the failure path, and it leaves nothing behind, even inside your own transaction:

  • In the transaction the bulk method opened itself (no transaction option), the happy path sends no savepoint at all since 3.8.0: massUpdate is BEGIN, one SELECT, one UPDATE, COMMIT. When the set statement fails, that transaction rolls back and the replay runs in a fresh transaction that is rolled back too. Once a before event has handed the transaction to a subscriber, which may have written in it, the replay stays inside it, as below.
  • In your transaction, the set statement runs in a savepoint and the replay inside an outer savepoint that is rolled back at the end, because on PostgreSQL a failed statement would otherwise abort your whole transaction. A failed bulk call leaves your transaction usable.

Two cases are worth knowing:

  • Two rows of the same batch that clash only with each other (the same unique email, say) show up as the second of the pair, because the replay has already written the first.
  • When no single item fails on its own, the statement's own error is thrown instead of a BulkOperationException.

What does a failed bulk call return over HTTP?

A 400 whose body names every failed item. massCreate and massUpsert identify an item by its index in the input array; the other methods use the id you passed:

400 response
{
  "statusCode": 400,
  "error": "Bad Request",
  "message": "Mass create errors. 1 error(s) occurred.",
  "errors": [
    { "index": 1, "status": 409, "message": "users with email 'ann@x.io' already exists" }
  ]
}

Each entry is a BulkOperationItemError:

indexOptional

number

The position of the item in the input array. Set by massCreate and massUpsert.
idOptional

unknown

The id you passed. Set by the methods that take ids.
statusRequired

number

The HTTP status that item alone would have produced: 409 for a duplicate, 404 for a missing id, 400 for bad input.
messageRequired

string

The message that item alone would have produced.

An error that is not an HTTP exception (a driver fault, say) appears as status: 500 with the message Internal server error, so SQL and parameters never reach the client. BulkOperationException extends Nest's BadRequestException, so if you let it propagate, Nest's default filter sends this body as is.

How do I handle BulkOperationException in code?

Catch it and read errors, which keeps the original Error per failed item:

users.import.ts
import { BulkOperationException } from '@avytrix/nestjs-drizzle-crud';

try {
  await users.massCreate([
    { name: 'a', email: 'a@x.io' },
    { name: 'b', email: 'taken@x.io' },
  ]);
} catch (err) {
  if (err instanceof BulkOperationException) {
    // The original errors, one per failed item
    for (const { index, id, error } of err.errors) {
      console.error(`Item ${index ?? id} failed:`, error);
    }
    // The response body shown above
    console.log(err.getResponse());
  }
  throw err;
}

The errors property is for logging. The response body carries the safe summary.

Do hooks run once per row in a bulk call?

Yes. The per-service hooks run once per item, in array order; only the SQL is shared:

MethodPer item, before the statementPer item, after the statement
massCreatevalidateCreate, beforeCreateafterCreate
massUpdatevalidateUpdate, beforeUpdate (after the existence check)afterUpdate
massSoftDeletebeforeSoftDeleteafterSoftDelete
massRestorebeforeRestoreafterRestore
massDelete, massForceDeletebeforeDeleteafterDelete
massUpsertvalidateCreate, beforeCreatenone

So massCreate([a, b, c]) calls validateCreate and beforeCreate three times, runs one insert, then calls afterCreate three times.

If a before hook throws for one item, that item is recorded as failed, the rest still run so every failure is reported, and nothing is written. If beforeUpdate returns different data for different ids, one SET cannot express it, so massUpdate writes those rows with one UPDATE each.

Module-level events fire once for the whole call instead, with bulk: true and every id, before and after the statement and inside the transaction.

Can I still run one query per item?

Yes, with bulkMode: 'perItem'. Each item then goes through the single-row method (create, update, delete and so on) in its own savepoint, as in 3.3. Use it when a per-item hook must see the rows written before it.

users.bulk.ts
await users.massUpdate(ids, { status: 'archived' }, { bulkMode: 'perItem' });

How do I run a bulk call inside my own transaction?

Pass { transaction: tx }. The bulk method then joins your transaction instead of opening its own, and its savepoints nest inside it:

users.service.ts
async setRoles(adminIds: number[], memberIds: number[]) {
  return this.executeSqlTransaction(async (tx) => {
    await this.massUpdate(adminIds, { role: 'admin' }, { transaction: tx });
    await this.massUpdate(memberIds, { role: 'member' }, { transaction: tx });
  });
}

executeSqlTransaction is protected, so call it from a method on your service. Transactions covers the cross-service pattern.

Do bulk writes work with relations, scope and versions?

Yes:

  • Relations. relations and counts load onto the returned rows with one query per relation for the whole batch. See Relations on writes.
  • When the relations load. massCreate loads them inside its transaction, so a load that fails (a pool timeout, a dropped connection) rolls the rows back and a retry does not store them twice. massUpdate, massRestore and massUpsert give the same rows when run twice, so in a transaction they opened themselves they load after the commit and hold their row locks for less time. A failed load there answers with an error although the write is stored; retrying it is safe. The exception is a write whose values hold an SQL expression, such as massUpdate(ids, { views: sql`views + 1` }): run twice, it adds twice. Since 3.8.2 such a massUpdate or massUpsert loads inside its transaction like massCreate, so a failed load rolls the increment back and the retry adds once.
  • Tenant scope. On a scoped entity, an id of another tenant is a 404 entry and nothing is written.
  • Versions. With optimistic locking, every written row's version goes up by one, and expectedVersions: { '1': 4 } turns an id at another version into a 409 entry.

How large can a bulk call be?

As large as one SQL statement allows. Since 3.7.2 the service counts the bound parameters of every statement it builds and compares them with the database's own limit:

DatabaseBound parameters per statement
PostgreSQL65,535
MySQL65,535
SQLite, libSQL32,766 (SQLITE_MAX_VARIABLE_NUMBER)
Cloudflare D1100

A statement past the limit is a 400 that names its count and the limit, before it reaches the database: Too many values in one statement: 75003 bound parameters, and PostgreSQL takes at most 65535. Split the batch. The count is the real one, so everything adds up: rows times the columns actually written (timestamps and scope included) for massCreate and massUpsert, about three values per id for a versioned massUpdate with expectedVersions, the children of a nested create, the targets of attach and sync, and every list in an IN filter, OR'd lists together. The refusal is not replayed item by item, and the transaction rolls back.

On a server built with a different limit, override the protected bindLimit(db) method in your service.

One transaction holding thousands of row locks can also block other connections. For thousands of rows, split the array into chunks of a few hundred and call the bulk method once per chunk, accepting that each chunk commits on its own.

Common questions

Next steps

Last updated View change history

On this page