PostgreSQL Node Errors

Written by Data Pilot
November 18, 2019
  • NodeJS
  • PostgreSQL

Introduction to PostgreSQL Node errors

Constructing a PostgreSQL NodeJs app has so many advantages including indexing distinctiveness and flexibility in search. But building apps is a process. That process becomes extended when errors are returned. Well, you can prevent raised exceptions from stopping your coding in its tracks. Learn how to handle some common PostgreSQL Node errors, and then you’ll be ready to launch your new app in record time.

If you already know how to process Postgres errors this way, skip to Just the Code.

Prerequisites to accessing PostgreSQL in NodeJs

Install the pg module for the NodeJs PostgreSQL client

  • Install the pg client with install command:
1
npm install pg

Make a new file package.json file for your new application.

  • To do this, initialize your Node directory with the command npm init.

Properties of a Node Postgres error response

Get every attribute for an error object when you make a pg Pool error response to the JavaScript method Object.keys().

  • Create a script to save in the NodeJs console that shows a detailed log of the properties for PostgreSQL Node errors:
1
console.log("ERROR Object.keys():", Object.keys(err));
  • Your result should resemble something like this:
1
2
3
4
5
6
7
8
9
10
11
ERROR Object.keys(): [
'name', 'length',
'severity', 'code',
'detail', 'hint',
'position', 'internalPosition',
'internalQuery', 'where',
'schema', 'table',
'column', 'dataType',
'constraint', 'file',
'line', 'routine'
]

As shown above, the error object’s property string names are listed.

NOTE: The message attribute is missing on the list even though it does contain more information about the error Postgres API. You won’t find that kind of information in the attribute detail, which happens to be on the list. In some cases, an error code identifier of a Postgres pg error object may have an undefined attribute setting.

Class 42 PostgreSQL error codes

Here’s a syntax and access rule (Class 42) list for PostgreSQL Node errors. It contains some of the most commonly raised exceptions along with a succinct explanation of why they could appear.

  • Error code: 42601 Name: syntax_error Description: The keyword needs to have the correct spacing or a semicolon to be identified.

  • Error code: 42501 Name: insufficient_privilege Description: The command GRANT must be executed to grant SQL user privileges such as INSERT, SELECT, or UPDATE.

  • Error code: 42602 Name: invalid_name Description: A table or database name has incorrect capitalization, characters or a mixture of both errors.

  • Error code: 42622 Name: name_too_long Description: The identifier is longer than the 63-byte limit. This applies to names for databases, tables, schema, and other Postgres database object identifier names.

  • Error code: 42939 Name: reserved_name Description: The database or table already has a SQL-reserved value.

  • Error code: 42703 Name: undefined_column Description: The column name doesn’t exist.

Postgres error code 42601 for syntax errors in Node

Since the error 42601, the snytax_error is more probable than most other PostgreSQL Node errors, it’s helpful to learn a fast way to resolve it.

  • Parse and analyze error 42601 by reading the error log from the pg library:
1
2
3
4
5
6
7
8
9
if (err.code !== undefined) {
  console.log("pg error code:", err.code);

  // 42601 = 'syntax_error'
  if (err.code === "42601") {
    // return the position of the SQL syntax error
    console.log("SQL syntax error position:", err.position);
  }
}

NOTE: SQL statement body errors including SQL syntax errors have undefined position attributes.

Node and pg error handling for PostgreSQL errors

After an &mdash method call receives a SQL string that was passed, the method query() of the pg library returns:

  • result object
  • error object

An undefined attribute of the error object means success. However, an undefined attribute of the result object indicates Postgres Node errors were present.

  • Try this Postgres pg module SQL statement:
1
2
3
4
5
6
7
8
9
pool.query("SELECT * FROM some_table", (err, res) => {
  if (err) {
    console.log("pg returned an error");
    throw error;
  }
  if (res) {
    console.log("pg returned a result from the SQL query");
  }
});

The error was handled, and then the NodeJs app stops because the keyword throw was used.

Declare a function to handle the Postgres error object

The parseError() function in JavaScript parses the SQL string and tracks the error log details to display in the terminal window.

  • Make a pg error object function declaration with the function in JavaScript like this:
1
2
function parseError(err, sqlString) {
console.log("nparseError:", sqlString)

*Now within a key-value object, add some typical PostgreSQL Node error codes with short explanations like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
let errorCodes = {
  "08003": "connection_does_not_exist",
  "08006": "connection_failure",
  "2F002": "modifying_sql_data_not_permitted",
  "57P03": "cannot_connect_now",
  "42601": "syntax_error",
  "42501": "insufficient_privilege",
  "42602": "invalid_name",
  "42622": "name_too_long",
  "42939": "reserved_name",
  "42703": "undefined_column",
  "42000": "syntax_error_or_access_rule_violation",
  "42P01": "undefined_table",
  "42P02": "undefined_parameter"
};

Parse the PostgreSQL error object returned from ‘pg’

  • Analyze the various attributes of the error object:
1
2
3
4
5
6
7
8
9
if (err === undefined) {
console.log("No errors returned from Postgres")
}

else {

if (err.message !== undefined) {
console.log('ERROR message:', err.message)
}

The attribute message from the error object is logged to the console because the explanation for the error is contained in the message attribute.

  • When the client library pg does not return the error object after a function is passed, it indicates that the attribute code was undefined as shown here:
1
2
if (err.code !== undefined) {
console.log("Postgres error code:", err.code)
  • Print the PostgreSQL error’s particulars with the function console.log().
1
2
3
4
5
6
if (errorCodes[err.code] !== undefined) {
console.log('Error code details:', errorCodes[err.code])
}
}

console.log('severity:', err.severity)

The SQL statement parsing tells the location of the error when it appeared.

  • Use the attribute position turns the SQL statement into a substring by breaking it up into smaller sections and characters for close examination:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
if (err.position !== undefined) {

console.log("PostgreSQL error position:", err.position)

// get the end of the error pos
let end = err.position+7
if (err.position+7 >= sqlString.length) {
end = sqlString.length
}

// get the start position for SQL error
let start = err.position-2
if (err.position-2 <= 1) {
start = 0
}

// log the partial SQL statement around error position
console.log("---> " + sqlString.substring(start, end) + " <---")
}

if (err.code === undefined && err.position === undefined) {
console.log("nUnknown Postgres error:", err)
}
}
}

Node Postgres error examples

It’s time to practice making PostgreSQL Node errors and handling them.

Declare a function that will execute PostgreSQL commands in Node

  • Use this code to make a SQL statement string. The pg API call will return the object err which will then be passed to the function parseError() that you declared previously.
1
2
3
4
5
6
7
8
9
10
function makeQuery(sql) {
  // pass the SQL string to the pg Pool's query() method
  pool.query(sql, (err, res) => {
    // pass the Pool's error object to parseError() func
    parseError(err, sql);

    // Pool result will be undefined
    console.log("SQL result:", res);
  });
}

Execute the Postgres error examples in a synchronous order

The asynchronously (async) function handles SQL commands in sequence, one after another.

  • Start a SQL command string. Use the async function along with the keyword await to run them in progression.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
async function postgresErrors() {
  // Array of bad SQL queries
  let queryStrings = [
    "SELECT * FROM does_not_exist",
    "SELECT * FROM BAD SQL SYNTAX",
    "INSERT INTO some_table (bad, col, names) VALUES ('uno', 'dos', 'tres')"
  ];

  // Iterate over the array of SQL strings
  for (i = 0; i < queryStrings.length; i++) {
    // Call the function in synchronous order using 'await'
    await makeQuery(queryStrings[i]);
  }
}

// Call the async function
postgresErrors();

NOTE: Remember to save the code as a JavaScript file with the .js extension (for example pg-errors.js).

Conclusion to PostgreSQL Node errors

  • Use the command node to execute the script that handles errors in PostgreSQL Node.
1
node pg-errors.js
  • You should see a result similar to this one:
1
2
3
4
5
6
7
8
parseError: SELECT * FROM does_not_exist
ERROR message: relation "does_not_exist" does not exist
Postgres error code: 42P01
Error code details: undefined_table
severity: ERROR
PostgreSQL error position: 15
---> does_not_exist <---
SQL result: undefined

Screenshot of PostgreSQL Node errors returned by pg in a terminal window

In application development, errors happen because that’s normal. You refine the application as you develop it. Having said that, handling Postgres Node errors is a cinch if you know what they mean when they occur. To that end, the NodeJs pg client library is a most helpful assistant. This tutorial explained how to resolve some of the most common Postgres Node errors you’ll run across. In addition, it gave some real-world examples. Save it as a reference to use in all of your Postgres Node development projects moving forward.

Just the Code

Here’s the entire sample code for working with the NodeJs pg client library to handle Postgres Node errors.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
const { Pool } = require("pg");

// Declare a new client instance from Pool()
const pool = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

function parseError(err, sqlString) {
  console.log("nparseError:", sqlString);

  let errorCodes = {
    "08003": "connection_does_not_exist",
    "08006": "connection_failure",
    "2F002": "modifying_sql_data_not_permitted",
    "57P03": "cannot_connect_now",
    "42601": "syntax_error",
    "42501": "insufficient_privilege",
    "42602": "invalid_name",
    "42622": "name_too_long",
    "42939": "reserved_name",
    "42703": "undefined_column",
    "42000": "syntax_error_or_access_rule_violation",
    "42P01": "undefined_table",
    "42P02": "undefined_parameter"
  };

  if (err === undefined) {
    console.log("No errors returned from Postgres");
  } else {
    // console.log("ERROR Object.keys():", Object.keys(err))
    if (err.message !== undefined) {
      console.log("ERROR message:", err.message);
    }

    if (err.code !== undefined) {
      console.log("Postgres error code:", err.code);

      if (errorCodes[err.code] !== undefined) {
        console.log("Error code details:", errorCodes[err.code]);
      }
    }

    console.log("severity:", err.severity);

    if (err.position !== undefined) {
      console.log("PostgreSQL error position:", err.position);

      // get the end of the error pos
      let end = err.position + 7;
      if (err.position + 7 >= sqlString.length) {
        end = sqlString.length;
      }

      // get the start position for SQL error
      let start = err.position - 2;
      if (err.osition - 2 <= 1) {
        start = 0;
      }

      // log the partial SQL statement around error position
      console.log("---> " + sqlString.substring(start, end) + " <---");
    }

    if (err.code === undefined && err.position === undefined) {
      console.log("nUnknown Postgres error:", err);
    }
  }
}

function makeQuery(sql) {
  // pass the SQL string to the pg Pool's query() method
  pool.query(sql, (err, res) => {
    // pass the Pool's error object to parseError() func
    parseError(err, sql);

    // Pool result will be undefined
    console.log("SQL result:", res);
  });
}

async function postgresErrors() {
  // Array of bad SQL queries
  let queryStrings = [
    "SELECT * FROM does_not_exist",
    "SELECT * FROM BAD SQL SYNTAX",
    "INSERT INTO some_table (bad, col, names) VALUES ('uno', 'dos', 'tres')"
  ];

  // Iterate over the array of SQL strings
  for (i = 0; i < queryStrings.length; i++) {
    // Call the function in synchronous order using 'await'
    await makeQuery(queryStrings[i]);
  }
}

// Call the async function
postgresErrors();
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.