How to handle Postgres errors inside try catch (with Drizzle)?

[juanvilladev]

I'm trying to understand how to properly type my errors when Drizzle throws due to a Postgres error.

Example, when I attempt to insert a number into a string column, I was thinking within my catch block I could use:

   try {
      const savedPurchaseOrder = await tx
        .insert(purchaseOrderReadModel)
        .values(purchaseOrderCreatedPayload)
        .returning({
          id: purchaseOrderReadModel.id,
        });

      if (!savedPurchaseOrder[0]) {
        tx.rollback();
      }
      purchaseOrderId = savedPurchaseOrder[0].id;
    } catch (e) {
      if (e instanceof TransactionRollbackError) {
        //Here is the logic for my manual rollback which is executed whenever we hit tx.rollback()
      }
      if (e instanceof DrizzleError) {
        //Here is where I expected I'd get the error, however it looks like e is not an instanceof DrizzleError
      }

      tx.rollback();
    }

Should I just use PostgresError?

Thanks!

[d3vr10]

Does anyone know ? I'm stumbling upon the same situation right now.

[FlaTrix1]

I noticed that the error message comes from the database library I use in drizzle. In my case, it is "pg".
import { DatabaseError } from 'pg';, and then err instanceof DatabaseError works.

[March-mitsuki]

In my case (drizzle-orm === v0.44.2), drizzle throw a DrizzleQueryError which wraps a DatabaseError from "pg" or another database driver in DrizzleQueryError.cause.
So I handle the errors as follows:

import { DatabaseError } from "pg";
import { DrizzleQueryError } from "drizzle-orm/errors";

try {
  // ...
} catch (error) {
  if (error instanceof DrizzleQueryError) {
    if (error.cause instanceof DatabaseError) {
      if (error.cause.code === "23505") {
        // ...
      }
    }
    // ...
  }
}

[shawnmclean]

Is this concept possible in the first place?

I'm running a try catch inside a transaction to watch for a db conflict error, but the entire transaction is throwing this error while the closure in the transaction keeps on running.

For example:

  await db.transaction(async (tx) => {
    let retryCount = 0;
    while (retryCount < 10) {
      try {
       await tx.insert()

      } catch (error) {
        const queryError = error as QueryError;
        if (
          typeof queryError.code === "string" &&
          queryError.code === "23505"
        ) {
          // Unique violation (Postgres specific error code)
          // Regenerate slug by appending a unique identifier (e.g., retryCount)
          slug = slugify(knowledge.title) + "-" + (retryCount + 1);
          retryCount++;
        } else {
          throw error; // Re-throw other errors
        }
      }
    }
}

But once the first error is thrown, the entire transaction seems to return, while the code just continues to execute.

[RickVM]

You can use the errors thrown by postgres, though that does have some of its own problems.
Here's how I built something to identify uniqueConstraintErrors.

import pg from 'postgres';

const isUniqueConstraintError = (error: unknown): boolean => {
  /** https://github.com/porsager/postgres/pull/901 */
  // eslint-disable-next-line import/no-named-as-default-member
  return error instanceof pg.PostgresError && error.code === '23505';
};

export default isUniqueConstraintError;

import isUniqueConstraintError from '@/lib/database/errors/isUniqueConstraintError';

// Insert item into db
try {
  // Insert item into the database with the given data and max position
  await db.insert(cars).values({ ...data });
} catch (error) {
  // Check if the error is a unique constraint error
  if (isUniqueConstraintError(error)) {
    // handle however you want....
  } else {
    // Handle other errors
    ....
  }
}

Ofcourse, you can use the PostgresError to get more details.

[lucaazalim]

error instanceof pg.PostgresError always returns false. Any idea why?

[itizarsa]

@lucaazalim It works for me. Share more details like cjs/esm, import, versions

[mrevanzak]

its work on local but not on prod. weird

[hexcowboy]

This is the weird way that I've worked around this. Drizzle will throw a DrizzleQueryError and attach the postgres error to the error.cause (which is typed incorrectly) and finally you can cast the error.cause as a postgres.PostgresError. This is for postgres.js, not the pg module.

import { DrizzleQueryError } from "drizzle-orm/errors";
import postgres from "postgres";

export const isUniqueConstraintError = (error: unknown): boolean => {
  /** https://github.com/porsager/postgres/pull/901 */
  // eslint-disable-next-line import/no-named-as-default-member
  if (!(error instanceof DrizzleQueryError)) {
    return false;
  }

  const cause = error.cause;
  if (!cause || typeof cause !== 'object') {
    return false;
  }

  return 'code' in cause && (cause as postgres.PostgresError).code === "23505";
};

[abdulrahim2002]

Background:

Drizzle depends on database drivers to perform actual operations on the database. For postgrsql database ,drizzle supports postgresjs and node-postgres (also called pg) as documented on their website.

Now the problem is, since drizzle does not perform the database operations itself it gets success/failure information about the operation through errors/messages returned by the underlying driver.

The drizzle's session layer is driver agnostic. So the database interation code looks like this:

try {
    return await query();
}
catch (e) {
    throw new DrizzleQueryError(queryString, params, e as Error);
}

Basically, it just tries to run the query using the database driver provided, and in case, any error is thrown at a lower level, it just:

• throws a new DrizzleQueryError
• the original error is attached to the cause field

There are several problems with this approach:

• the type of error thrown cannot be ascertained
• drizzle is providing no abstraction, and the developer needs to figure out what went wrong by understanding the error thrown by the underlying driver.
• what exactly is the original Error?
• the typecast can be incorrect

Though I think this is a difficult problem, because:

• if drizzle depends on individual database drivers and throws errors based on them, then it is no longer driver agnostic.
• it is difficult to track down each possible error the database driver and throw. And since, it must support multiple drivers, it needs to define a common interface, that can cover all possible errors.

I think the best approach out here, is to define a common interface for all Errors (can expand DrizzleQueryError to include more fields). And then, in the catch block above, try to find out what went wrong and populate as must information we have before we throwing the DrizzleQueryError.

Temporary solution

I'm using node-postgres. And I found out that it actually uses pg-protocol under the hood.

Upon failure, it throws a DatabaseError with the error code and message in accordance with postgres error codes as described here.

So I build the following solution, to detect errors:

import { DrizzleQueryError } from "drizzle-orm";
import { DatabaseError }     from "pg-protocol";

// Relevant codes from: https://www.postgresql.org/docs/current/errcodes-appendix.html

export enum OPSTATUS {
    SUCCESS,
    // integrity violations
    FOREIGN_KEY_VIOLATION=23503,
    UNIQUE_VIOLATION=23505,
    CHECK_VIOLATION=23514,
    NOT_NULL_VIOLATION=23502,

    // transaction failure
    INVALID_TRANSACTION_STATE=25000,

    // connection failure
    CONNECTION_DOES_NOT_EXIST=8006,
    CONNECTION_FAILURE=8006,

    // other
    UNKNOWN_FAILURE=-1,
}

// ...

    try {
        const insertedUser = await db.insert(usersSchema).values(user).returning();

        if ( insertedUser[0] === undefined ) {
            throw new Error("Unknown Failure"); // handled below
        }

        return insertedUser[0];
    }

    catch (err: any) {

        // if error has not originated from pg driver, no useful information can be extracted
        if ( !(err instanceof DrizzleQueryError) || !(err.cause instanceof DatabaseError) ) {
            return {
                success: false,
                status: OPSTATUS.UNKNOWN_FAILURE,
                message: "Unknown Failure"
            }
        }

        // ...

        // try to find out what went wrong?
        switch (errCode) {

            case OPSTATUS.UNIQUE_VIOLATION: {
                recommendedHttpResponseCode = StatusCodes.CONFLICT;
                message = "User already exists";
                break;
            }
            case OPSTATUS.CONNECTION_FAILURE: {
                recommendedHttpResponseCode = StatusCodes.SERVICE_UNAVAILABLE;
                message = "Broken connection to database server";
                break;
            }
            // and so on..
        }

        // return approapriate response
    }

[abdulrahim2002]

Why this is not a good practice:

• I am having to import "pg-protocol" even though it is a transitive dependency (i.e. not present in my package.json)

[david-jerry]

One of the most effective approach that worked for me was to create a utility function that extracts the original database error from Drizzle's DrizzleQueryError. Then use a lookup table to map the PostgreSQL error codes to specific, readable messages. This keeps the logic clean and easy to maintain.

1. The Error Handler Utility

Create a new file, for example, dbErrorUtils.ts, to hold the error-handling logic.

import { DrizzleQueryError, DrizzleError } from 'drizzle-orm';
import { DatabaseError } from 'pg';

// Defines the shape of the error handler functions
type ErrorHandler = (error: DatabaseError) => { message: string; constraint: string | null };

// Maps PostgreSQL error codes to specific handler functions
const PostgresErrorHandlers: Record<string, ErrorHandler> = {
  '23505': (error) => ({
    message: 'A duplicate entry was found for a unique field.',
    constraint: error.constraint || null,
  }),
  '23503': (error) => ({
    message: 'A foreign key violation occurred. The record you are trying to link does not exist.',
    constraint: error.constraint || null,
  }),
  '22P02': () => ({
    message: 'The data provided is in an invalid format (e.g., not a valid UUID).',
    constraint: null,
  }),
  '23514': (error) => ({
    message: 'A check constraint was violated.',
    constraint: error.constraint || null,
  }),
  '23502': (error) => ({
    message: `A required field is missing. The column '${error.column}' cannot be null.`,
    constraint: error.column || null,
  }),
  '42703': (error) => ({
    message: 'An undefined column was referenced in the query.',
    constraint: error.column || null,
  }),
  '42601': () => ({
    message: "There's a syntax error in the database query.",
    constraint: null,
  }),
  '25000': () => ({
    message: 'Transaction failed: a data integrity issue occurred within a database transaction.',
    constraint: null,
  }),
  '08006': () => ({
    message: 'Database connection failed. The database may be unavailable.',
    constraint: null,
  }),
  '42P01': () => ({
    message: 'A referenced table does not exist in the database.',
    constraint: null,
  }),
  '40001': () => ({
    message: 'Transaction serialization failure. Please retry the transaction as it could not be completed due to concurrent modifications.',
    constraint: null,
  }),
  'default': (error) => ({
	  message: `A database error occurred: ${error.message}`,
	  constraint: null,
  }),
};

/**
 * Extracts a user-friendly message and constraint from a Drizzle ORM error.
 * @param error The error object from Drizzle.
 * @returns An object with the main error message and constraint name (if applicable).
 */
export function getDbErrorMessage(error: unknown): { message: string; constraint: string | null } {
  if (error instanceof DrizzleQueryError && error.cause instanceof DatabaseError) {
    const originalError = error.cause;
    const handler = PostgresErrorHandlers[originalError.code ?? "default"];

    if (handler) {
      return handler(originalError);
    }

    // Default case for any other unhandled DatabaseError
    return {
      message: `A database error occurred: ${originalError.message}`,
      constraint: null,
    };
  }

  // Fallback for generic Drizzle errors or other Error instances
  if (error instanceof DrizzleError || error instanceof Error) {
    return {
      message: error.message || 'An unexpected error occurred.',
      constraint: null,
    };
  }

  // Final fallback for unknown error types
  return { message: 'An unknown error occurred.', constraint: null };
}

---

2. How to Use It in Your Code

In your controller or service layer, wrap your database calls in a try...catch block and use the new utility function to handle the error gracefully.

// Example usage in a service or controller
import { db } from './db/db'; // Your Drizzle instance
import { users } from './db/schema';
import { getDbErrorMessage } from './dbErrorUtils'; // The new utility

export async function createUser(data: typeof users.$inferInsert) {
  try {
    const result = await db.insert(users).values(data).returning();
    return { success: true, user: result[0] };
  } catch (error) {
    const { message, constraint } = getDbErrorMessage(error);
    
    // Log the error for debugging
    console.error('Database operation failed:', { message, constraint, originalError: error });

    // Throw a specific error for your API handler to catch and format
    // You could also return a specific error object here
    throw new Error(message); 
  }
}

This approach provides a clean separation of concerns and a reliable way to get clear, actionable insights from your database errors, making debugging faster and your application more robust.