Error Handling

A failed query throws the driver’s own error, and 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 one the error carries and gives it one name:

import { queryErrorKind } from 'uql-orm';
import { User } from './shared/models/index.js';
import { pool } from './uql.config.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 works on any driver error, whether or not a querier threw it, and leaves the error untouched, so instanceof and the driver’s own code still work.

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.
optimisticLock	An update carried a version the row has moved past, or the row is gone.
usage	The call itself is wrong, such as a $lock outside a transaction.
security	A security filter refused: its context is missing, or a write would leave its scope.
undefined	Anything else: a syntax error, a lost connection, an error from outside the database.

optimisticLock, usage and security come from UQL itself, as a UqlOptimisticLockError, UqlUsageError or UqlSecurityError. All three extend UqlError and carry kind and an HTTP status (409, 400 and 403). Check them by kind; reach for instanceof UqlOptimisticLockError only to read its expected and actual version.

Retrying a transaction

A retryable failure has already rolled the transaction back, so you can safely run the whole callback again. Retry the transaction, never one statement inside it:

import { queryErrorKind } from 'uql-orm';
import { Item } from './shared/models/index.js';
import { pool } from './uql.config.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 an error that has a numeric status, such as UQL’s own or one a hook throws, with that status and its message. A uniqueViolation or foreignKeyViolation becomes 409 Conflict and a notNullViolation or checkViolation 400 Bad Request, with only that generic message, because the driver’s message names your tables and PostgreSQL’s echoes the value. Anything else is a 500 with the error’s message.

Per engine

queryErrorKind reads the SQLSTATE on the Postgres family (code, or errno on Bun SQL), errno on MySQL and MariaDB, number on MSSQL, code and errorLabels on MongoDB, and the message on the SQLite family:

Engine	unique	foreign key	not null	check	retryable
PostgreSQL, CockroachDB, Neon, PGlite, Bun SQL	23505	23503	23502	23514	40P01, 40001, 55P03
MySQL, MariaDB	1062	1451, 1452	1048, 1364	3819, 4025	1213, 1205, 3572
MSSQL	2627, 2601	547 naming a FOREIGN KEY	515	547 naming a CHECK	1205, 1222, 3960
MongoDB	11000			121	112, TransientTransactionError
SQLite, LibSQL, Turso, Cloudflare D1	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.
• Optimistic Locking: where optimisticLock comes from.
• HTTP: the error envelope the handlers return.