node-red-contrib-postgres-variable

Node-RED PostgreSQL Variable Context Module

A Node-RED module for PostgreSQL database operations with support for dynamic configuration from different contexts (flow, global, environment variables).

Edited by Andrii Lototskyi

Features

• Dynamic Configuration: Get database connection parameters from different contexts:
  • String values (direct input)
  • Flow context variables
  • Global context variables
  • Environment variables
• SSL Support: Full SSL configuration with certificate validation options
• Named Parameters: Support for named query parameters using node-postgres-named
• Connection Pooling: Efficient connection management with PostgreSQL connection pools
• Error Handling: Comprehensive error handling and reporting

Installation

npm install node-red-contrib-postgres-variable

Configuration

Database Configuration Node

The PostgreSQL database configuration node supports the following parameters, each can be sourced from different contexts:

Connection Parameters

Parameter	Description	Context Types	Default
Host	PostgreSQL server hostname	str, flow, global, env	localhost
Port	PostgreSQL server port	str, flow, global, env	5432
Database	Database name	str, flow, global, env	-
Username	Database username	str, flow, global, env	-
Password	Database password	str, flow, global, env	-

🔐 Password Security Note: When password type is set to "str" (string), the password is securely stored in Node-RED's credentials system and displayed as a password field (hidden). For other types (flow, global, env), the password reference is stored in the flow configuration for portability.

SSL Parameters

Parameter	Description	Context Types	Default
SSL	Enable SSL connection	bool, flow, global, env	false
Ignore SSL Certificate	Skip certificate validation	bool, flow, global, env	false
SSL Certificate Path	Path to SSL certificate file	str, flow, global, env	-

Context Types

• str: Direct string value
• flow: Value from flow context (flow.get(key))
• global: Value from global context (global.get(key))
• env: Value from environment variable (process.env[key])
• bool: Boolean value (for SSL options)

Usage Examples

Basic Usage

• Configure Database Node:

  • Host: localhost (string)
  • Port: 5432 (string)
  • Database: mydb (string)
  • Username: postgres (string)
  • Password: secret (string)

• Use in Flow:

  msg.payload = "SELECT * FROM users WHERE id = $userId";
  msg.queryParameters = { userId: 123 };
  return msg;
  

Dynamic Configuration from Context

Using Flow Context

// Set in a function node before the postgres node
flow.set("db_host", "production-server");
flow.set("db_name", "prod_database");

Configure database node:

• Host: db_host (flow context)
• Database: db_name (flow context)

Using Global Context

// Set in a function node
global.set("postgres_port", "5433");
global.set("postgres_user", "admin");

Configure database node:

• Port: postgres_port (global context)
• Username: postgres_user (global context)

Using Environment Variables

Set environment variables:

export DB_HOST=production-server
export DB_PORT=5432
export DB_NAME=production_db
export DB_USER=prod_user
export DB_PASSWORD=secure_password

Configure database node:

• Host: DB_HOST (environment)
• Port: DB_PORT (environment)
• Database: DB_NAME (environment)
• Username: DB_USER (environment)
• Password: DB_PASSWORD (environment)

SSL Configuration Examples

SSL with Certificate Validation

// Environment variables
export SSL_ENABLED=true
export SSL_CERT_PATH=/path/to/certificate.pem

Configure database node:

• SSL: SSL_ENABLED (environment) → true
• Ignore SSL Certificate: false (boolean)
• SSL Certificate Path: SSL_CERT_PATH (environment) → /path/to/certificate.pem

SSL without Certificate Validation

Configure database node:

• SSL: true (boolean)
• Ignore SSL Certificate: true (boolean)

Dynamic SSL from Flow Context

// In a function node
flow.set("use_ssl", true);
flow.set("ignore_cert", false);
flow.set("cert_path", "/secure/path/cert.pem");

Configure database node:

• SSL: use_ssl (flow context)
• Ignore SSL Certificate: ignore_cert (flow context)
• SSL Certificate Path: cert_path (flow context)

Query Examples

Simple Query

msg.payload = "SELECT * FROM users";
return msg;

Parameterized Query

msg.payload = "SELECT * FROM users WHERE age > $minAge AND city = $userCity";
msg.queryParameters = {
    minAge: 18,
    userCity: "New York"
};
return msg;

Insert with Parameters

msg.payload = "INSERT INTO users (name, email, age) VALUES ($name, $email, $age)";
msg.queryParameters = {
    name: "John Doe",
    email: "[e-mail]",
    age: 30
};
return msg;

Dynamic Query from Message

msg.payload = "SELECT * FROM " + msg.tableName + " WHERE id = $recordId";
msg.queryParameters = {
    recordId: msg.payload.id
};
return msg;

Custom Connection Configuration

You can also define custom connection configurations in Node-RED settings.js:

module.exports = {
    // ... other settings
    pgConnects: {
        "production": {
            user: "prod_user",
            password: "prod_password",
            host: "prod-server",
            port: 5432,
            database: "production_db",
            ssl: true,
            ignore_check_ssl: false,
            ssl_path: "/path/to/prod-cert.pem"
        },
        "development": {
            user: "dev_user",
            password: "dev_password",
            host: "localhost",
            port: 5432,
            database: "dev_db",
            ssl: false
        }
    }
};

Use custom connection:

msg.connectName = "production"; // or "development"
msg.payload = "SELECT * FROM users";
return msg;

Password Security

The module implements intelligent password handling based on the selected type:

String Passwords (Secure Storage)

When password type is set to "str":

• Password is stored in Node-RED's encrypted credentials system
• Input field is automatically masked with dots/asterisks (password type)
• Password is not visible in flow exports
• Field dynamically switches to hidden mode when "str" type is selected
• Ideal for static passwords in production environments

Context Passwords (Portable Configuration)

When password type is set to "flow", "global", or "env":

• Only the reference key is stored in flow configuration
• Input field shows plain text for easy editing of context keys
• Actual password value is resolved at runtime from the specified context
• Flow can be exported/imported without exposing passwords
• Field automatically switches to text mode for visibility of context references
• Ideal for dynamic environments and CI/CD deployments

Example Scenarios

Production with Static Password

// Configuration: Password type = "str", value = "mySecretPassword"
// Result: Password stored securely in credentials, not in flow

Development with Environment Variable

// Configuration: Password type = "env", value = "DB_PASSWORD"
// Environment: DB_PASSWORD=dev_password_123
// Result: Password resolved from environment at runtime

Dynamic Password from Global Context

// In a function node:
global.set("current_db_password", "dynamic_password_" + Date.now());

// Configuration: Password type = "global", value = "current_db_password"
// Result: Password resolved from global context at runtime

Error Handling

The module provides comprehensive error handling:

// Errors are sent to the node's error output
// and also attached to the message
if (msg.payload.error) {
    node.warn("Database error: " + msg.payload.error);
    return null; // Stop processing
}

SSL Configuration Logic

The SSL configuration follows this logic:

• SSL = false: No SSL connection
• SSL = true + Ignore SSL Certificate = true: SSL connection without certificate validation
• SSL = true + Ignore SSL Certificate = false: SSL connection with certificate validation
• SSL = true + Ignore SSL Certificate = false + SSL Certificate Path: SSL with custom certificate

Migration from Previous Versions

If you're upgrading from a previous version that used Node-RED credentials:

• Username and Password: Now configured through typedInput fields instead of credentials
• All Fields: Now support context resolution (flow, global, env)
• SSL Configuration: Enhanced with better boolean handling and certificate path options

Dependencies

• pg: PostgreSQL client for Node.js
• node-postgres-named: Named parameter support for node-postgres

License

Apache License 2.0

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Support

For issues and questions, please use the GitHub issue tracker.