PostgreSQL Node for Node-RED

Common errors

• TypeError: Cannot read properties of null (reading 'config')

  cause The PostgreSQL node was deployed without being correctly linked to a valid PostgreSQL configuration node, or the configuration node was deleted or became invalid.
  fix Ensure that the PostgreSQL query node is connected to a properly configured and deployed PostgreSQL configuration node in your Node-RED flow.

• syntax error at or near "..."

  cause The SQL query provided in the node's configuration or via `msg.query` contains invalid PostgreSQL syntax, or Mustache templating was used incorrectly (e.g., missing quotes for text values).
  fix Carefully review the SQL query for any syntax errors. If using Mustache templates for text values, ensure they are correctly quoted (e.g., `WHERE name = '{{{msg.name}}}'`). Prefer parameterized queries to avoid such issues and enhance security.

• Error: connect ECONNREFUSED <ip>:<port>

  cause The Node-RED instance cannot establish a connection with the PostgreSQL database server. This often means the server is not running, is not accessible from the Node-RED host, or the connection details (host, port) are incorrect.
  fix Verify that your PostgreSQL database server is running and network accessible. Double-check the host IP address/hostname and port number in your PostgreSQL configuration node.

• Error: password authentication failed for user "..."

  cause The username or password provided in the PostgreSQL connection configuration is incorrect or lacks the necessary permissions to connect to the specified database.
  fix Confirm that the username and password configured in the PostgreSQL connection node are correct and that the user has appropriate database access privileges.

Warnings

• gotcha Named parameters (e.g., `$id` in queries with `msg.queryParameters`) are emulated by this library and not natively supported by PostgreSQL. This emulation can be less robust than standard numeric parameters (`$1`, `$2`).
  Fix: Prefer numeric parameterized queries (`$1`, `$2`, etc.) with `msg.params` for enhanced robustness and direct PostgreSQL compatibility, especially for critical applications.
• gotcha Providing dynamic connection parameters via `msg.pgConfig` for each message bypasses the internal connection pooling mechanism of the Node-RED node. This can lead to decreased performance and resource inefficiency compared to using the persistent configuration node.
  Fix: For most scenarios, configure your PostgreSQL database connection using the dedicated configuration node within the Node-RED editor to benefit from connection pooling and optimized resource management.
• gotcha When the 'Split results' option is enabled and 'Number of rows per message' is set to 1, the `msg.payload` output will contain a single-row object directly, rather than the typical array of objects.
  Fix: Downstream nodes processing the output should be designed to handle both array and single-object payloads or explicitly check `msg.payload`'s type when the 'Split results' option is active.
• breaking Directly embedding user-supplied values into SQL query templates using Mustache (e.g., `{{{msg.id}}}`) can introduce SQL injection vulnerabilities if inputs are not properly sanitized.
  Fix: Always use parameterized queries with `msg.params` or `msg.queryParameters` for any user-controlled input. This separates data from the query, preventing injection attacks.
• breaking Versions prior to `0.15.3` contained a bug that could lead to `pg-cursor` exceptions, particularly when processing large result sets with the 'Split results' and backpressure features enabled.
  Fix: Upgrade to `v0.15.3` or a newer version to benefit from the fix for `pg-cursor` exceptions and ensure better stability with large datasets.

Install

• npm install node-red-contrib-postgresql npm
• yarn add node-red-contrib-postgresql yarn
• pnpm add node-red-contrib-postgresql pnpm

Imports

• PostgreSQL Node (Conceptual)
  wrong:

  import { PostgresqlNode } from 'node-red-contrib-postgresql';

  correct:

  Install 'node-red-contrib-postgresql' via Node-RED palette manager.

  This package is a Node-RED contribution; its primary 'import' is installation via the Node-RED editor's palette manager. It is not designed for direct JavaScript `import` or `require` in application code.
• msg.query for Dynamic SQL
  wrong:

  import { query } from 'node-red-contrib-postgresql';

  correct:

  // In a preceding Function node:
  msg.query = 'SELECT * FROM my_table WHERE id = $1';

  This refers to the `msg.query` property, which is used to pass a dynamic SQL query string to the PostgreSQL node from an upstream Node-RED message.
• msg.params for Parameterized Queries
  wrong:

  import { params } from 'node-red-contrib-postgresql';

  correct:

  // In a preceding Function node:
  msg.params = [msg.payload.id];

  This refers to the `msg.params` property, an array used to provide parameters for numeric parameterized SQL queries (`$1`, `$2`, etc.) to the PostgreSQL node.

Quickstart

Demonstrates preparing `msg.query` and `msg.params` in a Node-RED Function node for an upsert operation into a PostgreSQL database, typically followed by the `node-red-contrib-postgresql` node.

/* This is a Node-RED Function node preparing a message for the PostgreSQL node */

// Example 1: Dynamic SQL query
// msg.query = 'SELECT * FROM users WHERE status = $1';
// msg.params = ['active'];

// Example 2: Insert data with parameterized query
const userId = 'user_' + Date.now();
const username = 'testuser_' + Math.floor(Math.random() * 1000);
const email = `${username}@example.com`;

msg.query = `
  INSERT INTO public.users (id, username, email, created_at)
  VALUES ($1, $2, $3, NOW())
  ON CONFLICT (id) DO UPDATE SET
  username = EXCLUDED.username, email = EXCLUDED.email;
`;
msg.params = [userId, username, email];

// Example 3: Select data by ID using named parameters (less robust, prefer numeric)
// msg.query = 'SELECT * FROM products WHERE id = $productId;';
// msg.queryParameters = { productId: 123 };

return msg;