node-red-contrib-teamogy-postgres 0.1.2

Connect to PostgreSQL database.

npm install node-red-contrib-teamogy-postgres

PostgreSQL node for Node-RED

Connect to PostgreSQL database.

You must configure Connection before using this node.

Node Settings

Parameter Type Default Description
Name string — Optional display name
Connection config — Select a postgres-config node
Debug mode checkbox off If checked, emits a warning before each query showing the assembled SQL and dynamic parameters

Connection Configuration

Parameter Type Required Description
Name string Optional Enter a name or it will be generated on first save
Server string Required Enter the hostname or IP address
Port number Required Port to connect to (default 5432)
TLS checkbox Optional If checked, the connection will use TLS (sslmode=require)
Database string Required Database name
User string Required Username
Password string Required User's password
Max. number of connections number Required Value between 1 and 100, default 10
Idle connection timeout (s) number Required Value between 0 and 60, default 0
Connection timeout (s) number Required Value between 0 and 600, default 30

Input Message

Plain SQL (backward compatible)

Send any SQL string in msg.payload:

msg.payload = "SELECT * FROM users WHERE id = 15"

Multiple commands separated by a semicolon:

msg.payload = "SELECT * FROM users WHERE id = 15; SELECT * FROM groups WHERE group_name = 'accounting'"

Dynamic SQL

Use placeholders in msg.payload together with the msg.sql object to build parameterized queries dynamically. Column names are automatically quoted to prevent SQL injection.

The property names inside msg.sql match exactly the placeholder names in msg.payload:

Property Type Description
msg.payload string SQL with optional placeholders: {columns}, {data}, {columnsUpdate}
msg.sql.data object | object[] Data for INSERT or UPDATE. An array triggers a bulk operation.
msg.sql.columns string[] Column filter — selects which keys from data are used, or which columns to return in SELECT
msg.sql.columnsUpdate string[] Columns for UPSERT conflict resolution; expands {columnsUpdate} to "col" = EXCLUDED."col" pairs

Placeholders

Placeholder Expands to Used for
{columns} ("col1", "col2") in INSERT; stripped in UPDATE Column list
{data} ($1, $2), ($3, $4) in INSERT; "col"=$1, "col"=$2 in UPDATE Values / SET pairs
{columnsUpdate} "col1" = EXCLUDED."col1", "col2" = EXCLUDED."col2" UPSERT ON CONFLICT SET clause

Examples

INSERT with explicit column list

msg.payload = "INSERT INTO users {columns} VALUES {data}"
msg.sql = {
    data: { name: "Alice", age: 30, role: "admin" },
    columns: ["name", "age"]   // only these columns are inserted
}

→ executes: INSERT INTO users ("name", "age") VALUES ($1, $2)

Bulk INSERT

msg.payload = "INSERT INTO users {columns} VALUES {data}"
msg.sql = {
    data: [{ name: "Alice", age: 30 }, { name: "Bob", age: 25 }],
    columns: ["name", "age"]
}

→ executes: INSERT INTO users ("name", "age") VALUES ($1, $2), ($3, $4)

UPDATE with column filter

{columns} is stripped from the SQL and acts only as a column filter directive for {data}.

msg.payload = "UPDATE users {columns} SET {data} WHERE id = 5"
msg.sql = {
    data: { name: "Alice", age: 30, role: "admin" },
    columns: ["age"]   // only this column is updated
}

→ executes: UPDATE users SET "age"=$1 WHERE id = 5

UPSERT (INSERT … ON CONFLICT DO UPDATE)

msg.payload = "INSERT INTO users {columns} VALUES {data} ON CONFLICT (name) DO UPDATE SET {columnsUpdate}"
msg.sql = {
    data: { name: "Alice", age: 30, role: "admin" },
    columns: ["name", "age"],   // columns to insert
    columnsUpdate: ["age"]      // columns to update on conflict
}

→ executes: INSERT INTO users ("name", "age") VALUES ($1, $2) ON CONFLICT (name) DO UPDATE SET "age" = EXCLUDED."age"

Dynamic SELECT columns

msg.payload = "SELECT {columns} FROM users WHERE active = true"
msg.sql = { columns: ["name", "age"] }

→ returns only the specified columns; names are safely quoted

Output Message

Property Type Description
msg.payload array Records returned by the server (empty array if no rows)
msg.count number Number of records (or arrays for multi-command queries)
msg.error object Error detail from the postgres client or server
  • Single command → msg.payload is an array of records, msg.count is the row count
  • Multiple commands → msg.payload is an array of arrays, msg.count is the command count
  • Command with no result (INSERT/UPDATE/DELETE) → msg.payload is [], msg.count is 0
  • On error → msg.error contains message, code, detail, hint

References

Node Info

Version: 0.1.2
Updated 3 days ago
License: MIT
Rating: 5.0 3
View on npm
View on GitHub
View Scorecard 12

Categories

Actions

Rate:

Downloads

950 in the last week

Nodes

  • postgres-config
  • postgres-client

Keywords

  • node-red
  • teamogy
  • postgres

Maintainers