@topcs/node-red-contrib-postgres 2.0.0
Yet another Node-RED node to query PostgreSQL with query parameters and listening
🐘 @topcs/node-red-contrib-postgres
Three nodes for PostgreSQL in Node-RED. Config node manages the connection pool. Query node executes SQL with Mustache templates and parameterized queries. Listener node pushes real-time NOTIFY events into your flow with automatic reconnection.
✨ Features
This fork brings a full TypeScript rewrite with 137 tests and zero regressions:
| Feature | Description |
|---|---|
| 🔒 Parameterized queries | msg.params → $1, $2, ... |
| 🎨 Named parameters | {name: 'value'} → auto-bound in insertion order |
| 🧩 Mustache templates | SELECT * FROM {{msg.table}} |
| 🔄 Multi-step transactions | Array of {query, params, output} → BEGIN / COMMIT / ROLLBACK |
| 📡 Real-time LISTEN/NOTIFY | Push events from Postgres, auto-reconnects on connection drop |
| 🔮 Cursor streaming | DECLARE / FETCH large result sets in configurable batches |
| 📦 COPY import/export | High-speed CSV via PostgreSQL COPY protocol |
| ♻️ Self-healing retry | Retries on deadlock (40P01), serialization failures, connection drops with jittered backoff |
| 💚 Pool health badge | Active / idle / waiting / total visible on the node |
| 🔐 Full SSL | sslmode, CA cert, client cert/key — RDS/Azure/Supabase ready |
| 🧹 Structured errors | msg.error.code, .detail, .constraint, .table |
| ⏱️ Query timeout | Per-node SET statement_timeout with guaranteed reset |
| 🎯 Type mapping | NUMERIC→number, TIMESTAMPTZ→ISO, JSONB→object |
| ⚡ Prepared statements | Auto-named via MD5 hash, no configuration needed |
| 🛡️ Channel sanitization | LISTEN/UNLISTEN uses pg-format %I |
| 🧪 137 tests | Zero regressions |
📦 Install
npm install @topcs/node-red-contrib-postgres
Or use Node-RED's Manage Palette → search @topcs/node-red-contrib-postgres.
🚀 Quick Start
1. Drop a PostgresDBNode — set host, port, database, user, password. Configure SSL if needed. The badge shows pool health.
2. Wire a PostgresNode — write SQL with optional {{msg.field}} templates.
Parameterized:
{"params": [42, "hello"]}
Named parameters:
{"params": {"id": 42, "name": "Alice"}}
Enable "Named Parameters" toggle on the node.
Transaction (array of queries on one connection):
{"payload": [
{"query": "INSERT INTO users VALUES($1, $2)", "params": {"id": 1, "name": "Ada"}, "output": true},
{"query": "INSERT INTO logs VALUES($1)", "params": {"action": "created"}}
]}
Enable "Transaction Mode". First output: true entry determines msg.payload.rows.
Cursor streaming:
Enable "Cursor Mode". SELECT queries stream batches via DECLARE / FETCH:
// Sequential messages per batch:
{"payload": [...rows], "batch": {"index": 0, "rows": 100, "total": 100}}
// Final signal:
{"payload": [], "complete": true, "total": 10500}
3. Add a PostgresListenerNode — set channel name. When Postgres sends NOTIFY channel, 'payload', it arrives as {channel, payload, _original}. JSON payloads auto-parse. Connection drops auto-recover.
🔧 Editor Toggles
| Toggle | Description |
|---|---|
| Throw Exception | Halt the flow on SQL error (throwErrors) |
| Named Parameters | Bind msg.params object as $1, $2, ... |
| Transaction Mode | Execute msg.payload array atomically |
| Cursor Mode | Stream SELECT results in batches |
| COPY Mode | Use COPY protocol for CSV import/export |
| Retry on Transient Errors | Auto-retry deadlocks, serialization failures, connection drops |
| Type Mapping | NUMERIC→number, TIMESTAMPTZ→ISO, JSONB→object |
🎯 Requirements
- Node.js >= 18.0.0
- Node-RED >= 3.0.0
- PostgreSQL (any recent version)
🧪 Development
npm install
npm run build # tsc + build HTML templates
npm test # 137 tests (Jest)
npm run lint # ESLint 9.x
📜 License
GNU AGPL-3.0. Originally by doing-things-with-node-red, forked and maintained by Andrea Batazzi. Revived with TypeScript.
