@yo1dog/psql-error v2.0.2

psql-error

Better PostgreSQL errors.

npm install @yo1dog/psql-error

This library creates PostgreSQL errors with messages that emulate the psql client. In fact, the code for generating the error messages is translated directly from the psql C source code with minimal modifications.

PSQLError can be used to wrap errors returned by pg and has the same interface/keys as pg errors for compatibility. It provides access to the PostgreSQL error message fields for identifiable errors via both human readable keys (same as pg errors) and the single-byte identification token. This includes error code, table name, column name, constraint name, detail message, hint message, etc.

Additionally, PSQLError maps PSQL error codes to their PL/pgSQL condition names for human-friendly identification.

You can also include the query that caused the error. This allows PSQLError to capture and display information related to the query. For example, the offending snippet of the query can be included in the error message along with the line and character location.

Before:

syntax error at or near "1234"

After:

PSQLError: ERROR:  42601:  syntax error at or near "1234"
LINE 3: CHAR 11:
LEFT JOIN 1234 ON fu = 'bar'
          ^
LOCATION:  scanner_yyerror, scan.l:1134
QUERY:
 1: SELECT a, b
 2: FROM fubar
 3: LEFT JOIN 1234 ON fu = 'bar'
 ═════════════╛
 4: WHERE a = 'a';

Quick Start

import PSQLError from '@yo1dog/psql-error';

const query = {
  text: `
    SELECT * FROM sometable
    WHERE c = $1
  `,
  values: ['D']
};
pgClient.query(query).catch(err => {
  const psqlErr = new PSQLError(err, query);
  // psqlErr.C    === '42P01';
  // psqlErr.code === '42P01';
  // psqlErr.codeCondition === 'undefined_table';
  // psqlErr.codeCondition === PSQLError.PGCodeCondition.undefined_table;
  throw psqlErr;
});

PSQLError: ERROR:  42P01:  relation "sometable" does not exist
LINE 2: CHAR 19:
    SELECT * FROM sometable
                  ^
LOCATION:  parserOpenTable, parse_relation.c:1180
QUERY:
 1:
 2:     SELECT * FROM sometable
 ═════════════════════╛
 3:     WHERE c = $1
 4:

VALUES:  [ 'D' ]
    at Object.<anonymous> (README.js:13:3)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)

Docs

Usage

new PSQLError(errFieldsObj, [query, [options]])

NOTE: pg does not currently support the V (Nonlocalized Severity) field. Further, this field is only returned by PostgreSQL server 9.6 and up. This field is used for checking if context should be shown. Therefore, if you are wraping pg errors or using PostgreSQL server 9.5 or lower, PQSHOW_CONTEXT_ERRORS will differ from the original functionality and will show context for all errors instead of only fatal ones. In these situations, PQSHOW_CONTEXT_ERRORS and PQSHOW_CONTEXT_ALWAYS are equivalent.

new PSQLError(pgError);
new PSQLError(pgError, query);
new PSQLError(pgError, query, {hideQueryValues: true});
new PSQLError(pgError, {text: query, values});
new PSQLError({message: '...', severity: '...', code: '...'});
new PSQLError({M: '...', S: '...', C: '...'});

PSQLError.createMessage(errFieldsObj, [query, [options]])

Parameters are the same as the constructor.

Creates and returns an error message. Useful if you want to create the error message without creating a PSQLError instance. Used internally by the PSQLError constructor.

You can recreate a PSQLError instance's message with different options like so:

const psqlError = new PSQLError(pgError, query); // message will contain query values
PSQLError.createMessage(psqlError, psqlError.query, {hideQueryValues: true}) // message will not contain query values

Properties

All of the PostgreSQL error message fields can be accessed via either human readable keys or the single-byte identification token. This applies to both input parameters and PSQLError properties. These are listed below along with relevant documentation from the PostgreSQL docs. See: https://www.postgresql.org/docs/13/protocol-error-fields.html

Further, PSQLError maps the PSQL SQLSTATE code (C/code) to its PL/pgSQL condition name and exposes it as the codeCondition property (defined above). This makes errors easier to identify with more human-friendly names. The available condition names can be found at PSQLError.PGCodeCondition.* For example, you can identify a foreign key error with psqlErr.code === '23503', psqlErr.codeCondition === 'foreign_key_violation', or psqlErr.codeCondition === PSQLError.PGCodeCondition.foreign_key_violation. However, note that there are a few codes with the same condition name. This means you can map a code to a condition name but you can not map a condition name to a single code.

All are of type string and can be null.

NOTE: From PostgreSQL docs: The fields for schema name, table name, column name, data type name, and constraint name are supplied only for a limited number of error types; see Appendix A. Frontends should not assume that the presence of any of these fields guarantees the presence of another field. Core error sources observe the interrelationships noted above, but user-defined functions may use these fields in other ways. In the same vein, clients should not assume that these fields denote contemporary objects in the current database.

NOTE: The human readable keys match those used by pg. This allows you to pass pg errors directly into PSQLError and allows PSQLError to be compatibility with other libraries expecting pg errors.

Examples

Here are some common errors and what the PSQLError looks like. (Only a subset of keys are shown for readability. See above for full list of available keys.)

42601: syntax_error

PSQLError: ERROR:  42601:  syntax error at or near "1234"
LINE 3: CHAR 11:
LEFT JOIN 1234 ON fu = 'bar'
          ^
LOCATION:  scanner_yyerror, scan.l:1134
QUERY:
 1: SELECT a, b
 2: FROM fubar
 3: LEFT JOIN 1234 ON fu = 'bar'
 ═════════════╛
 4: WHERE a = 'a';

{
  severity: 'ERROR',
  code: '42601',
  codeCondition: 'syntax_error',
  M: 'syntax error at or near "1234"',
  position: '34',
  file: 'scan.l',
  line: '1134',
  routine: 'scanner_yyerror'
}

42P01: undefined_table

PSQLError: ERROR:  42P01:  relation "fubar" does not exist
LINE 2: CHAR 6:
FROM fubar
     ^
LOCATION:  parserOpenTable, parse_relation.c:1180
QUERY:
 1: SELECT a, b
 2: FROM fubar
 ════════╛
 3: WHERE a = 'a';

{
  severity: 'ERROR',
  code: '42P01',
  codeCondition: 'undefined_table',
  M: 'relation "fubar" does not exist',
  position: '18',
  file: 'parse_relation.c',
  line: '1180',
  routine: 'parserOpenTable'
}

23502: not_null_violation

PSQLError: ERROR:  23502:  null value in column "mycol" violates not-null constraint
DETAIL:  Failing row contains (1, null).
SCHEMA NAME:  pg_temp_3
TABLE NAME:  mytable
COLUMN NAME:  mycol
LOCATION:  ExecConstraints, execMain.c:2042
QUERY:
 1: CREATE TEMP TABLE mytable (id INT, mycol INT NOT NULL);
 2: INSERT INTO mytable VALUES (1, NULL);

{
  severity: 'ERROR',
  code: '23502',
  codeCondition: 'not_null_violation',
  M: 'null value in column "mycol" violates not-null constraint',
  detail: 'Failing row contains (1, null).',
  schema: 'pg_temp_3',
  table: 'mytable',
  column: 'mycol',
  file: 'execMain.c',
  line: '2042',
  routine: 'ExecConstraints'
}

23503: foreign_key_violation

PSQLError: ERROR:  23503:  insert or update on table "mytable" violates foreign key constraint "mytable_parent_id_fkey"
DETAIL:  Key (parent_id)=(-10) is not present in table "parenttable".
SCHEMA NAME:  pg_temp_3
TABLE NAME:  mytable
CONSTRAINT NAME:  mytable_parent_id_fkey
LOCATION:  ri_ReportViolation, ri_triggers.c:2783
QUERY:
 1: CREATE TEMP TABLE parenttable (id INT PRIMARY KEY);
 2: CREATE TEMP TABLE mytable (id INT, parent_id INT REFERENCES parenttable(id));
 3: INSERT INTO mytable VALUES (1, -10);

{
  severity: 'ERROR',
  code: '23503',
  codeCondition: 'foreign_key_violation',
  M: 'insert or update on table "mytable" violates foreign key constraint "mytable_parent_id_fkey"',
  detail: 'Key (parent_id)=(-10) is not present in table "parenttable".',
  schema: 'pg_temp_3',
  table: 'mytable',
  constraint: 'mytable_parent_id_fkey',
  file: 'ri_triggers.c',
  line: '2783',
  routine: 'ri_ReportViolation'
}

23505: unique_violation

Primary Key

PSQLError: ERROR:  23505:  duplicate key value violates unique constraint "mytable_pkey"
DETAIL:  Key (id)=(20) already exists.
SCHEMA NAME:  pg_temp_3
TABLE NAME:  mytable
CONSTRAINT NAME:  mytable_pkey
LOCATION:  _bt_check_unique, nbtinsert.c:534
QUERY:
 1: CREATE TEMP TABLE mytable (id INT PRIMARY KEY);
 2: INSERT INTO mytable VALUES (20), (20);

{
  severity: 'ERROR',
  code: '23505',
  codeCondition: 'unique_violation',
  M: 'duplicate key value violates unique constraint "mytable_pkey"',
  detail: 'Key (id)=(20) already exists.',
  schema: 'pg_temp_3',
  table: 'mytable',
  constraint: 'mytable_pkey',
  file: 'nbtinsert.c',
  line: '534',
  routine: '_bt_check_unique'
}

Unique Constraint

PSQLError: ERROR:  23505:  duplicate key value violates unique constraint "mytable_mycol_key"
DETAIL:  Key (mycol)=(10) already exists.
SCHEMA NAME:  pg_temp_3
TABLE NAME:  mytable
CONSTRAINT NAME:  mytable_mycol_key
LOCATION:  _bt_check_unique, nbtinsert.c:534
QUERY:
 1: CREATE TEMP TABLE mytable (id INT, mycol INT UNIQUE);
 2: INSERT INTO mytable VALUES (1, 10), (2, 10);

{
  severity: 'ERROR',
  code: '23505',
  codeCondition: 'unique_violation',
  M: 'duplicate key value violates unique constraint "mytable_mycol_key"',
  detail: 'Key (mycol)=(10) already exists.',
  schema: 'pg_temp_3',
  table: 'mytable',
  constraint: 'mytable_mycol_key',
  file: 'nbtinsert.c',
  line: '534',
  routine: '_bt_check_unique'
}

23514: check_violation

PSQLError: ERROR:  23514:  new row for relation "mytable" violates check constraint "mytable_mycol_check"
DETAIL:  Failing row contains (1, -30).
SCHEMA NAME:  pg_temp_3
TABLE NAME:  mytable
CONSTRAINT NAME:  mytable_mycol_check
LOCATION:  ExecConstraints, execMain.c:2089
QUERY:
 1: CREATE TEMP TABLE mytable (id INT, mycol INT CHECK (mycol > 0));
 2: INSERT INTO mytable VALUES (1, -30);

{
  severity: 'ERROR',
  code: '23514',
  codeCondition: 'check_violation',
  M: 'new row for relation "mytable" violates check constraint "mytable_mycol_check"',
  detail: 'Failing row contains (1, -30).',
  schema: 'pg_temp_3',
  table: 'mytable',
  constraint: 'mytable_mycol_check',
  file: 'execMain.c',
  line: '2089',
  routine: 'ExecConstraints'
}

Caveats

There are some potential display issues regarding unaccountable inconsistencies regarding character display and encoding:

• The line position may not exactly match your editor and/or terminal if the query contains abnormal line breaks. psql considers \r, \n, and \r\n line breaks.
• The character position may not exactly match your editor and/or terminal if the query contains certain "wide" characters or grapheme clusters. This is due to lack of standardization around handling the display width of these characters in fixed width environments and how grapheme clusters are counted. For example, your console/environment may display Family: Man, Woman, Boy, Boy emoji 👨‍👩‍👦‍👦 the same width as 1 character, 2 characters, 2.5, 11, etc. The character after may be considered to be at column/char 2, 3, 8, 12 etc. PSQLError follows psql and uses an implementation of wcwidth for calculating display width. This does not support grapheme clustering.

Project Structure

All library files are in /lib/. Code translated from C is in /lib/translated/.

The original C code that was translated is kept in /original/. If/when the psql client code updates and we want to update this library along with it, we can diff the stored originals with the new versions. This will hopefully make updating the translated code easier.