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:
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' });| Method | Returns | Notes |
|---|---|---|
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:
| Method | Statements |
|---|---|
massCreate | one multi-row INSERT ... RETURNING |
massUpdate | one 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, massRestore | one SELECT, one UPDATE ... WHERE id IN (...) |
massDelete, massForceDelete | one SELECT, one DELETE ... WHERE id IN (...) |
massUpsert | one 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:
transactionOptionalDrizzle transaction
bulkModeOptional"set" | "perItem"
Defaults to "set"
relationsOptionalstring[]
countsOptionalstring[]
expectedVersionsOptionalRecord<string, number>
hooks.skipBeforeOptionalboolean
Defaults to false
hooks.skipAfterOptionalboolean
Defaults to false
skipEventsOptionalboolean
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
transactionoption), the happy path sends no savepoint at all since 3.8.0:massUpdateisBEGIN, oneSELECT, oneUPDATE,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:
{
"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:
indexOptionalnumber
idOptionalunknown
statusRequirednumber
messageRequiredstring
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:
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:
| Method | Per item, before the statement | Per item, after the statement |
|---|---|---|
massCreate | validateCreate, beforeCreate | afterCreate |
massUpdate | validateUpdate, beforeUpdate (after the existence check) | afterUpdate |
massSoftDelete | beforeSoftDelete | afterSoftDelete |
massRestore | beforeRestore | afterRestore |
massDelete, massForceDelete | beforeDelete | afterDelete |
massUpsert | validateCreate, beforeCreate | none |
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.
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:
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.
relationsandcountsload onto the returned rows with one query per relation for the whole batch. See Relations on writes. - When the relations load.
massCreateloads 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,massRestoreandmassUpsertgive 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 asmassUpdate(ids, { views: sql`views + 1` }): run twice, it adds twice. Since 3.8.2 such amassUpdateormassUpsertloads inside its transaction likemassCreate, 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:
| Database | Bound parameters per statement |
|---|---|
| PostgreSQL | 65,535 |
| MySQL | 65,535 |
| SQLite, libSQL | 32,766 (SQLITE_MAX_VARIABLE_NUMBER) |
| Cloudflare D1 | 100 |
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
Yes. Every bulk method runs in one transaction, so a failure rolls back the whole batch and nothing is written.
Catch BulkOperationException. Its response body lists each failed item by index (for massCreate and massUpsert) or by id, with the status and message that item alone would have produced.
No. It runs one SELECT to check the ids and one UPDATE ... WHERE id IN (...), whatever the number of ids. When beforeUpdate returns different data for different ids, it sends one UPDATE per distinct result (since 3.8.0), so a hook that sets two values over 40 ids is two UPDATEs, not 40. Pass bulkMode: 'perItem' if you want the single-row method per item.
A 400. Each entry in its errors array carries the item's own status, such as 409 for a duplicate or 404 for a missing id.
Next steps
Transactions
Run several service calls in one transaction with executeSqlTransaction.
Errors
How BulkOperationException and the other exceptions map to HTTP statuses.
Drizzle hooks and validation in NestJS
The before and after hooks that run once per item in a bulk call.
Upsert with Drizzle in NestJS
Upsert rows with Drizzle in NestJS in one statement. upsert and massUpsert insert a row or update the one that holds its unique key.
Nested create with Drizzle relations
Drizzle nested create in NestJS. Write a row and its related rows in one transaction, return writes with relations, and attach or sync links.