Error handling in NestJS with Drizzle

NestJS Drizzle error handling without a custom filter. Each exception extends a Nest HTTP exception, so a missing row is a 404 and a duplicate a 409.

NestJS Drizzle error handling in nestjs-drizzle-crud works out of the box: every exception the service throws extends a NestJS HttpException subclass, so Nest's default exception filter answers with the right status code and no custom filter. A missing row is a 404, a unique or version conflict is a 409, a missing tenant is a 403, and bad input is a 400. Postgres driver errors caused by bad input are translated at the service boundary instead of reaching the client as a 500.

The exceptions carry a message, not fields

The classes take an entity name, a field or an id in their constructor, but only to build the message. They have no entity, field, value or id property. Read getStatus(), message and getResponse() instead.

Which status does each exception return?

Each class extends the Nest exception that carries its status, and the constructor builds the message:

ExceptionExtendsHTTPConstructorMessage
EntityNotFoundExceptionNotFoundException404(entityName, id)users with id 9 not found
DuplicateEntityExceptionConflictException409(entityName, field, value)users with email 'a@b.co' already exists
StaleVersionExceptionConflictException409(entityName, id)projects with id 4 was changed by someone else; read it again and retry
EntityInUseExceptionConflictException409(entityName)teams is still referenced by other records and cannot be deleted
MissingScopeExceptionForbiddenException403(entityName, key)projects is scoped by "tenant_id", but the context has no value for it
ValidationFailedExceptionBadRequestException400(message)Validation failed: <message>
ForeignKeyViolationExceptionBadRequestException400(entityName, field)Invalid reference for users: 'team_id' does not refer to an existing record
BulkOperationExceptionBadRequestException400(message, errors)Mass update errors. 2 error(s) occurred.

The entity name is the table name Drizzle reports. Each class also sets name to its own class name, so exception.name is "EntityNotFoundException" and so on. Every class is exported from the package root; see Exports.

TransactionException (500, InternalServerErrorException) means the driver cannot run the transaction a call needs. Since 3.7.0 it is thrown on Cloudflare D1 and on synchronous SQLite drivers by the bulk writes, nested creates and attach / detach / sync; see SQLite, libSQL and Cloudflare D1. A transaction that fails while it runs still rejects with the error that caused it. DatabaseConnectionException (500) is still exported but deprecated: nothing throws it, and it goes in the next major version.

What does the response body look like?

Nest's standard body for an HttpException built from a message:

404 response
{
  "statusCode": 404,
  "message": "users with id 9 not found",
  "error": "Not Found"
}

BulkOperationException is the one exception with a richer body. It lists every failed item, with index for massCreate and massUpsert (the position in the input array) and id for the rest:

400 response from a bulk call
{
  "statusCode": 400,
  "error": "Bad Request",
  "message": "Mass update errors. 2 error(s) occurred.",
  "errors": [
    { "id": 4, "status": 404, "message": "projects with id 4 not found" },
    { "id": 7, "status": 409, "message": "projects with id 7 was changed by someone else; read it again and retry" }
  ]
}

An item that failed with an HttpException keeps its own status and message. Any other error is reported as status: 500 with the message Internal server error, because a raw driver error can carry SQL and parameters. The original errors stay on the exception's public errors property (Array<{ id?, index?, error }>) for your logs. See Bulk insert and update with Drizzle.

How are database errors translated?

The service catches Postgres errors by SQLSTATE code (on the error or its Drizzle cause) and throws the matching exception:

SQLSTATEMeaningBecomes
23505unique violationDuplicateEntityException (409), field and value parsed from the driver detail
23503 on create or updatethe row references a parent that does not existForeignKeyViolationException (400), column parsed from detail
23503 on deleteother rows still reference this one (ON DELETE RESTRICT or NO ACTION)EntityInUseException (409)
22001value too long for its columnValidationFailedException (400)
23514check constraint failedValidationFailedException (400)
22003numeric value out of rangeValidationFailedException (400)
22P02value does not match the column type ("abc" for an integer, an unknown enum label)ValidationFailedException (400)
22007, 22008date or time with an invalid format, or out of rangeValidationFailedException (400)
21000the same key twice in one massUpsertValidationFailedException (400)
23502a required column got no valueValidationFailedException (400), with the column name

On MySQL, ER_DUP_ENTRY (1062) is a 409 DuplicateEntityException, ER_NO_REFERENCED_ROW_2 (1452) a 400 ForeignKeyViolationException, and ER_ROW_IS_REFERENCED_2 (1451) on a delete a 409 EntityInUseException. Since 3.7.0 the strict mode data errors are a 400 ValidationFailedException too, with the column name from the message:

MySQL errnoMeaning
1048, 1364a required column got no value
1406value too long for its column
1264numeric value out of range
1265, 1366value does not match the column type
1292date or time with an invalid format
3819check constraint failed

On SQLite the constraint kind decides: UNIQUE or PRIMARY KEY is a 409 DuplicateEntityException (SQLite names the column but not the value), FOREIGN KEY is a 400 on create or update and a 409 on delete, and NOT NULL or CHECK is a 400 ValidationFailedException. libSQL and better-sqlite3 report the kind as an extended code such as SQLITE_CONSTRAINT_UNIQUE; D1 only in the message, which is read instead. Since 3.7.2 two type errors are a 400 ValidationFailedException too: a STRICT table refusing a value of the wrong type (extended code 3091, which libSQL reports as UNKNOWN_SQLITE_ERROR_3091, with the column name), and SQLITE_MISMATCH, such as "abc" for an INTEGER PRIMARY KEY.

Any other error passes through unchanged.

Which other 400s does the service throw?

Plain Nest BadRequestExceptions for input the service cannot use:

  • an unknown filter column or operator, an operator on the wrong column type, or an invalid date,
  • an invalid cursor, or a findOne with no condition,
  • an unknown fullTextSearch column,
  • a statement with more bound parameters than the database takes (65,535 on PostgreSQL and MySQL, 32,766 on SQLite, 100 on D1), see how large a bulk call can be,
  • anything outside an allowlist,
  • anything parseQuery cannot read.

See clamping and fail-fast input for which pagination values are clamped and which throw. ValidationFailedException covers an update with no column left to set, a column outside writableColumns, a non-integer version, and whatever your validateCreate or validateUpdate hooks throw.

When does each method throw?

Reads return null instead of throwing

find, findOne, findAll, exists, count and fullTextSearch return null, false or an empty page for missing rows, and never throw EntityNotFoundException. You decide how to respond. The controller factory does that for you: its GET /:id route answers a missing row with EntityNotFoundException, a 404.

Bad input on a read is a 400, the same as on a write: find("abc") on an integer key, findAll({ status: "archived" }) when the enum has no such label, or { createdAt: { gte: "garbage" } }. A soft-deleted row counts as missing when soft delete is on.

Writes

Method404409400
create(dto)unique violationhook rejection, missing FK target
upsert(dto, upsert)conflict with another tenant's rowunique violation on another keyhook rejection, missing FK target, unknown conflict target
update(id, dto)row missing, soft-deleted or outside the scopeunique violation, stale versionhook rejection, missing FK target, nothing to update
delete(id)row missing or soft-deletedstill referenced, stale version
forceDelete(id)row missingstill referenced
softDelete(id)row missing or already soft-deletedstale version
restore(id)row missingstale version
attach / detach / syncrow missingunknown target ids (404 entries), not a many-to-many

On a scoped entity, a row of another tenant counts as missing everywhere, and a call without the tenant is a 403 MissingScopeException. update and restore return the written row.

softDelete on an entity without soft delete is a 400

softDelete and restore throw a BadRequestException (Soft delete is not enabled for this entity) when soft delete is off. Before 3.5.2 this was a plain Error and reached the client as a 500.

Bulk methods

massCreate, massUpdate, massUpsert, massSoftDelete, massRestore, massDelete and massForceDelete each run in one transaction. Any failure throws BulkOperationException (400) and rolls back the whole call, so there are no partial writes.

Transactions

executeSqlTransaction wraps a callback in a transaction. On any throw inside the callback, the transaction rolls back and the same error propagates unchanged. Pass { transaction: tx } in the options of any CRUD method to enlist it. See Transactions.

How do I write a custom exception filter?

You only need one to change the body shape. Catch the package's classes and read what an HttpException really has: getStatus(), message, name and getResponse():

crud-exception.filter.ts
import { type ArgumentsHost, Catch, type ExceptionFilter } from "@nestjs/common";
import type { Response } from "express";
import {
  BulkOperationException,
  DuplicateEntityException,
  EntityNotFoundException,
  StaleVersionException,
} from "@avytrix/nestjs-drizzle-crud";

@Catch(
  EntityNotFoundException,
  DuplicateEntityException,
  StaleVersionException,
  BulkOperationException,
)
export class CrudExceptionFilter implements ExceptionFilter {
  catch(
    exception:
      | EntityNotFoundException
      | DuplicateEntityException
      | StaleVersionException
      | BulkOperationException,
    host: ArgumentsHost,
  ) {
    const res = host.switchToHttp().getResponse<Response>();
    const status = exception.getStatus();

    if (exception instanceof BulkOperationException) {
      // getResponse() holds the per-item list: [{ index | id, status, message }].
      const body = exception.getResponse() as { errors: unknown[] };
      res.status(status).json({ code: exception.name, items: body.errors });
      return;
    }

    res.status(status).json({ code: exception.name, message: exception.message });
  }
}

Register it with app.useGlobalFilters(new CrudExceptionFilter()) or @UseFilters on a controller. Without a filter, Nest's default one already returns the status codes on this page.

Common questions

Next steps

Last updated View change history

On this page