Error Handling

A failed query throws the driver’s own error, so each engine names the same failure differently: a duplicate key is 23505 on PostgreSQL, 1062 on MySQL, 2627 on MSSQL, 11000 on MongoDB, and only a message on SQLite. queryErrorKind(err) reads whichever of those the error carries and names it once:

import { queryErrorKind } from 'uql-orm';
import { pool } from './uql.config.js';
import { User } from './shared/models/index.js';

export async function signUp(email: string, password: string) {
  try {
    return await pool.insertOne(User, { email, password });
  } catch (err) {
    if (queryErrorKind(err) === 'uniqueViolation') {
      throw Object.assign(new Error('That email is taken', { cause: err }), {
        status: 409,
      });
    }
    throw err;
  }
}

It only reads the error, never changes it: instanceof your driver’s error class and its own code keep working, and it answers for any driver error, not only ones a querier threw.

Kinds

Kind	What happened
uniqueViolation	A primary key or unique index already holds the value.
foreignKeyViolation	The referenced row is missing, or a row still referenced was deleted.
notNullViolation	A required column got no value.
checkViolation	A check constraint, or MongoDB schema validation, rejected the row.
retryable	A deadlock, serialization failure, lock timeout or busy database: run it again.
undefined	Anything else: a syntax error, a lost connection, an error that is not the database’s.

Retrying a transaction

A retryable failure rolled the transaction back, so running the whole callback again is safe. Retry the transaction, never a single statement inside it:

import { queryErrorKind } from 'uql-orm';
import { pool } from './uql.config.js';
import { Item } from './shared/models/index.js';

async function withRetry<T>(work: () => Promise<T>, attempts = 3): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await work();
    } catch (err) {
      if (attempt === attempts || queryErrorKind(err) !== 'retryable') {
        throw err;
      }
    }
  }
}

export function takeOne(id: number) {
  return withRetry(() =>
    pool.transaction(
      async (querier) => {
        const item = await querier.findOneById(Item, id, {
          $select: { stock: true },
        });
        await querier.updateOneById(Item, id, { stock: item!.stock! - 1 });
      },
      { isolationLevel: 'serializable' },
    ),
  );
}

Over HTTP

The HTTP handlers answer a uniqueViolation or foreignKeyViolation with 409 Conflict and a notNullViolation or checkViolation with 400 Bad Request, where any other failure is a 500. The message stays that generic, since the driver’s names your tables and constraints, and PostgreSQL’s echoes the value. A numeric status a hook throws still wins, with its own message.

Per engine

What each kind is read from:

Engine	Read from	unique	foreign key	not null	check	retryable
PostgreSQL, CockroachDB, Neon, PGlite, Bun SQL	SQLSTATE (code, or errno)	23505	23503	23502	23514	40P01, 40001, 55P03
MySQL, MariaDB	errno	1062	1451, 1452	1048, 1364	3819, 4025	1213, 1205, 3572
MSSQL	number	2627, 2601	547 naming a FOREIGN KEY	515	547 naming a CHECK	1205, 1222, 3960
MongoDB	code, errorLabels	11000			121	112, TransientTransactionError
SQLite, LibSQL, Turso, Cloudflare D1	the message	UNIQUE constraint failed	FOREIGN KEY constraint failed	NOT NULL constraint failed	CHECK constraint failed	database is locked

Next Steps

• Transactions: Isolation levels, and who owns the commit you retry.
• Row Locking: NOWAIT, whose failure is retryable.
• HTTP: The error envelope the handlers answer with.