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)