@inteli.city/node-red-contrib-db-collection 1.0.2
Unified Node-RED database collection: async PostgreSQL query, async Oracle query, and table copy nodes
node-red-contrib-db-collection
A Node-RED package with three database nodes: async PostgreSQL queries, async Oracle queries, and table-to-table copy between PostgreSQL and Oracle.
Index
1. Installation
npm install @inteli.city/node-red-contrib-db-collection
Oracle support uses the oracledb package (included as a dependency). Runs in Thin mode — no Oracle Instant Client required. Oracle 12c or later is needed for offset/fetch pagination.
2. Nodes
| Node | Description |
|---|---|
async.pg |
Executes a PostgreSQL query for each incoming message |
async.oracle |
Executes an Oracle SQL statement for each incoming message |
copy.db |
Copies a table from a source database to a destination database |
3. Config nodes
config.pg — PostgreSQL connection
| Field | Description | Default |
|---|---|---|
| Host | Server address | 127.0.0.1 |
| Port | Server port | 5432 |
| Database | Database name | postgres |
| SSL | Enable SSL | false |
| User | Username (credential) | — |
| Password | Password (credential) | — |
config.oracle — Oracle connection
| Field | Description | Required |
|---|---|---|
| Connect String | TNS alias (e.g. mydb_high) or EZConnect string (host:port/service) |
Yes |
| Username | Database user (credential) | Yes |
| Password | Database password (credential) | Yes |
| Wallet Directory | Path to unzipped wallet folder (Oracle Cloud / mTLS only) | No |
| Wallet Password | Only for ewallet.p12 wallets; leave blank for cwallet.sso |
No |
4. async.pg
Executes one PostgreSQL SQL statement per incoming message with an explicit FIFO queue and concurrency limit.
Properties
| Field | Description | Default |
|---|---|---|
| Name | Optional display label | — |
| DB | config.pg connection config node |
— |
| Queue | Max concurrent queries / connection pool size | 1 |
| SQL Query | SQL statement to execute | SELECT 1; |
| Syntax | SQL (plain) or Nunjucks (template) |
SQL |
Inputs
| Property | Type | Description |
|---|---|---|
msg |
object | Full message — available as Nunjucks context in template mode |
msg.params |
object | array | Named ({ id: 1 }) or positional ([1]) bind parameters for $1 $2 … placeholders |
msg.stop |
boolean | true cancels all running queries and drains the queue |
Output
| Property | Type | Description |
|---|---|---|
msg.payload |
array | Array of row objects; empty for non-SELECT |
Syntax modes
SQL — the query is sent as-is. Use $1, $2, … placeholders and supply values via msg.params.
Nunjucks — the query is rendered as a Nunjucks template with the full msg object as context before execution. Use for structural parts of the query (table names, dynamic clauses) that bind parameters cannot cover. Do not use Nunjucks interpolation for user-supplied scalar values — use msg.params for those.
Concurrency
Queue controls both the maximum number of simultaneous queries and the size of the underlying connection pool. Excess messages wait in a FIFO queue. Node status shows queued (executing/max) while busy.
5. async.oracle
Executes one Oracle SQL statement per incoming message with an explicit FIFO queue and concurrency limit. Runs in Thin mode — no Oracle Instant Client required.
Properties
| Field | Description | Default |
|---|---|---|
| Name | Optional display label | — |
| Oracle DB | config.oracle connection config node |
— |
| Queue | Max concurrent queries / connection pool size | 1 |
| SQL Query | SQL statement to execute | SELECT 1 FROM DUAL |
| Syntax | SQL (plain) or Nunjucks (template) |
SQL |
Inputs
| Property | Type | Description |
|---|---|---|
msg |
object | Full message — available as Nunjucks context in template mode |
msg.params |
object | array | Named (:name) or positional (:1 :2) bind variables |
msg.stop |
boolean | true cancels all running queries and drains the queue |
Outputs
| Property | Type | Description |
|---|---|---|
msg.payload |
array | Array of row objects; empty for DML/DDL |
msg.rowsAffected |
number | Rows affected by INSERT/UPDATE/DELETE; undefined for SELECT |
Syntax modes
Same as async.pg: SQL sends the query as-is using Oracle bind variables (:name or :1). Nunjucks renders the query as a template before execution — use only for structural parts, not for scalar user-supplied values.
Concurrency
Same model as async.pg. Node status shows queued (executing/max).
6. copy.db
Copies a table from a source database to a destination database. Supports PostgreSQL and Oracle in any combination.
Properties
| Field | Description | Default |
|---|---|---|
| Name | Optional display label | — |
| Source type | PostgreSQL or Oracle |
PostgreSQL |
| Source server | Connection config node for the source | — |
| Source schema | PG: namespace inside the database. Oracle: table owner. Optional. | — |
| Source table | Table to read from (required) | — |
| Dest type | PostgreSQL or Oracle |
PostgreSQL |
| Dest server | Connection config node for the destination | — |
| Dest schema | PG: namespace inside the database. Oracle: table owner. Optional. | — |
| Dest table | Table to write into (required) | — |
| Mode | Copy mode: append, overwrite, or incremental |
append |
| Extra cols | What to do when source has columns absent from dest | fail |
| Batch size | Rows read and written per cycle | 1000 |
Copy modes
Append — inserts all rows from the source. Existing destination rows are untouched.
Overwrite — truncates the destination table, then copies all rows. Not atomic: if the copy fails mid-way, partial data remains.
Incremental (new rows by primary key) — for each source row, if a row with the same primary key already exists in the destination it is skipped; otherwise it is inserted. Requirements:
- Both tables must have a primary key.
- Primary key column names must match between source and destination.
- All primary key columns must be present in the matched column set.
Incremental mode never updates or deletes existing rows and does not detect updated or deleted source rows.
Schema vs Owner
| Database | Meaning | Blank default |
|---|---|---|
| PostgreSQL | A namespace inside the database | public |
| Oracle | The user who owns the table | connected user's objects |
The editor labels the field Owner / Schema when Oracle is selected.
Column behavior
Columns are matched by name (case-insensitive). Manual column selection is not supported.
Only columns present in both source and destination are copied. The Extra cols setting controls what happens when the source has columns absent from the destination:
| Setting | Behavior |
|---|---|
fail |
Error if any source column is absent from the destination |
ignore |
Silently skip source columns not present in the destination |
Inputs
| Property | Type | Description |
|---|---|---|
msg.stop |
boolean | true cancels all running operations and clears the queue |
msg.sourceTable |
string | Overrides the configured source table at runtime |
msg.destTable |
string | Overrides the configured destination table at runtime |
msg.sourceSchema |
string | Overrides the configured source schema at runtime |
msg.destSchema |
string | Overrides the configured destination schema at runtime |
msg.copyMode |
string | Overrides the configured copy mode at runtime |
Output
msg.payload on successful completion:
| Field | Type | Description |
|---|---|---|
rowsCopied |
number | Rows written to the destination |
rowsSkipped |
number | Rows skipped (incremental: already existed; 0 for other modes) |
batches |
number | Source read batches processed |
copyMode |
string | Mode used for this execution |
source |
string | Qualified source table identifier |
destination |
string | Qualified destination table identifier |
Concurrency and status
The node processes one copy operation at a time. Excess messages wait in a FIFO queue. Node status shows Nq (running/max) R rows while busy, where N is the queue depth and R is the total rows inserted so far.
The stop button (⏹) in the node editor cancels all running operations and clears the queue.
Error handling
Any failure stops the current operation and emits node.error(message, msg). The node stays active for subsequent messages. Partial progress is not rolled back — no cross-database transaction guarantees.
