{"id":14747,"library":"node-red-contrib-postgresql","title":"PostgreSQL Node for Node-RED","description":"node-red-contrib-postgresql is a Node-RED contribution that provides a node for interacting with PostgreSQL databases. It is currently at version 0.15.4 and has a relatively steady release cadence, primarily focused on bug fixes, dependency updates, and minor enhancements. Key features include robust support for parameterized SQL queries (both numeric $1/$2 and emulated named $id parameters), efficient handling of large datasets through resultset splitting and backpressure (flow control), and flexible dynamic SQL query input via `msg.query`. It provides database responses in `msg.payload`, with additional metadata in `msg.pgsql`. The node integrates directly into the Node-RED flow editor, abstracting away direct `node-postgres` client management for most use cases, though advanced users can dynamically configure client settings via `msg.pgConfig` for specific scenarios.","status":"active","version":"0.15.4","language":"javascript","source_language":"en","source_url":"https://github.com/alexandrainst/node-red-contrib-postgresql","tags":["javascript","backpressure","node-red-contrib","node-red","nodered","postgres","postgresql","timescale"],"install":[{"cmd":"npm install node-red-contrib-postgresql","lang":"bash","label":"npm"},{"cmd":"yarn add node-red-contrib-postgresql","lang":"bash","label":"yarn"},{"cmd":"pnpm add node-red-contrib-postgresql","lang":"bash","label":"pnpm"}],"dependencies":[],"imports":[{"note":"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.","wrong":"import { PostgresqlNode } from 'node-red-contrib-postgresql';","symbol":"PostgreSQL Node (Conceptual)","correct":"Install 'node-red-contrib-postgresql' via Node-RED palette manager."},{"note":"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.","wrong":"import { query } from 'node-red-contrib-postgresql';","symbol":"msg.query for Dynamic SQL","correct":"// In a preceding Function node:\nmsg.query = 'SELECT * FROM my_table WHERE id = $1';"},{"note":"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.","wrong":"import { params } from 'node-red-contrib-postgresql';","symbol":"msg.params for Parameterized Queries","correct":"// In a preceding Function node:\nmsg.params = [msg.payload.id];"}],"quickstart":{"code":"/* This is a Node-RED Function node preparing a message for the PostgreSQL node */\n\n// Example 1: Dynamic SQL query\n// msg.query = 'SELECT * FROM users WHERE status = $1';\n// msg.params = ['active'];\n\n// Example 2: Insert data with parameterized query\nconst userId = 'user_' + Date.now();\nconst username = 'testuser_' + Math.floor(Math.random() * 1000);\nconst email = `${username}@example.com`;\n\nmsg.query = `\n  INSERT INTO public.users (id, username, email, created_at)\n  VALUES ($1, $2, $3, NOW())\n  ON CONFLICT (id) DO UPDATE SET\n  username = EXCLUDED.username, email = EXCLUDED.email;\n`;\nmsg.params = [userId, username, email];\n\n// Example 3: Select data by ID using named parameters (less robust, prefer numeric)\n// msg.query = 'SELECT * FROM products WHERE id = $productId;';\n// msg.queryParameters = { productId: 123 };\n\nreturn msg;","lang":"javascript","description":"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."},"warnings":[{"fix":"Prefer numeric parameterized queries (`$1`, `$2`, etc.) with `msg.params` for enhanced robustness and direct PostgreSQL compatibility, especially for critical applications.","message":"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`).","severity":"gotcha","affected_versions":">=0.1.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=0.1.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=0.1.0"},{"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.","message":"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.","severity":"breaking","affected_versions":">=0.1.0"},{"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.","message":"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.","severity":"breaking","affected_versions":"<0.15.3"}],"env_vars":null,"last_verified":"2026-04-19T00:00:00.000Z","next_check":"2026-07-18T00:00:00.000Z","problems":[{"fix":"Ensure that the PostgreSQL query node is connected to a properly configured and deployed PostgreSQL configuration node in your Node-RED flow.","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.","error":"TypeError: Cannot read properties of null (reading 'config')"},{"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.","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).","error":"syntax error at or near \"...\""},{"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.","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.","error":"Error: connect ECONNREFUSED <ip>:<port>"},{"fix":"Confirm that the username and password configured in the PostgreSQL connection node are correct and that the user has appropriate database access privileges.","cause":"The username or password provided in the PostgreSQL connection configuration is incorrect or lacks the necessary permissions to connect to the specified database.","error":"Error: password authentication failed for user \"...\""}],"ecosystem":"npm"}