{"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,"search_vec":"'0.15.4':32 '1':61 '2':62 'abstract':110 'addit':95 'advanc':123 'away':111 'backpressur':77,136 'bug':43 'cadenc':39 'case':121 'client':116,128 'configur':127 'contrib':10,140 'contribut':17 'control':79 'current':29 'databas':26,90 'dataset':72 'depend':45 'direct':102,112 'dynam':82,126 'editor':109 'effici':68 'emul':64 'enhanc':49 'featur':51 'fix':44 'flexibl':81 'flow':78,108 'focus':41 'handl':69 'id':66 'includ':52 'input':85 'integr':101 'interact':23 'javascript':135 'key':50 'larg':71 'manag':117 'metadata':96 'minor':48 'msg.payload':93 'msg.pgconfig':131 'msg.pgsql':98 'msg.query':87 'name':65 'node':2,5,8,15,21,100,106,114,138,142 'node-postgr':113 'node-r':4,14,105,141 'node-red-contrib':137 'node-red-contrib-postgresql':7 'noder':144 'numer':60 'paramet':67 'parameter':56 'postgr':115,145 'postgresql':1,11,25,146 'primarili':40 'provid':19,89 'queri':58,84 'red':6,9,16,107,139,143 'relat':36 'releas':38 'respons':91 'resultset':74 'robust':53 'scenario':134 'set':129 'specif':133 'split':75 'sql':57,83 'steadi':37 'support':54 'though':122 'timescal':147 'updat':46 'use':120 'user':124 'version':31 'via':86,130","created_at":"2026-04-20T18:43:24.756229+00:00","updated_at":"2026-04-20T18:43:24.756229+00:00","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","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":null,"cli_name":"","cli_version":null,"type":"library","homepage":null,"github":"https://github.com/alexandrainst/node-red-contrib-postgresql","docs":null,"changelog":null,"pypi":null,"npm":"https://www.npmjs.com/package/node-red-contrib-postgresql","openapi_spec":null,"status_page":null,"smithery":null,"categories":["database","web-framework"],"base_url":null,"auth_type":null,"provenance":{"verified_status":null,"verified_at":null,"last_verified":"2026-06-17","next_check":"2026-07-18","install_tag":null}}