node-red-contrib-postgres-variable 0.3.0
PostgreSQL module for Node-RED with dynamic configuration from contexts (flow, global, environment)
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)
- Host:
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: "[email protected]",
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.jsnode-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.