PostgreSQL Node Errors
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
Check that you have PostgresSQL installed.
At the terminal window command line, input the command
psql -V
to find out whichpsql
version is installed.Verify that you have NodeJs and the Node Package Manager (NPM) installed.
Install the pg module for the NodeJs PostgreSQL client
- Install the
pg
client withinstall
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 attributedetail
, which happens to be on the list. In some cases, an error code identifier of a Postgres pg error object may have anundefined
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 commandGRANT
must be executed to grant SQL user privileges such asINSERT
,SELECT
, orUPDATE
.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 thepg
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 thefunction
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 attributecode
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 objecterr
which will then be passed to the functionparseError()
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 keywordawait
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 examplepg-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 |
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(); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started