npm package: @inteli.city/node-red-contrib-postgres-async-pg
Description: Node-RED node for async PostgreSQL query execution with concurrency control and Nunjucks templating
Latest version: 1.0.1

# async.pg — Node-RED PostgreSQL Async Node

A Node-RED node for executing PostgreSQL queries with controlled concurrency and Nunjucks templating.

---

## Index

1. [Overview](#1-overview)
2. [Features](#2-features)
3. [Installation](#3-installation)
4. [Configuration](#4-configuration)
5. [Usage](#5-usage)
6. [Templating (Nunjucks)](#6-templating-nunjucks)
7. [Concurrency Model](#7-concurrency-model)
8. [Message Flow](#8-message-flow)
9. [Status Indicator](#9-status-indicator)
10. [Error Handling](#10-error-handling)
11. [Design Principles](#11-design-principles)

---

## 1. Overview

`async.pg` executes a SQL query for each incoming message.

It is designed to:
- control database concurrency
- avoid hidden queues
- provide predictable execution

---

## 2. Features

- Asynchronous query execution
- Configurable concurrency limit
- Internal FIFO queue
- Nunjucks-based SQL templating
- PostgreSQL connection pooling (aligned with concurrency)
- Real-time execution status

---

## 3. Installation

```bash
npm install @inteli.city/node-red-contrib-postgres-async-pg
```

Or via the Node-RED palette manager, search for `async-pg`.

---

## 4. Configuration

### Database config node (`postgresDB`)

| Field    | Description                       | Default     |
|----------|-----------------------------------|-------------|
| Host     | PostgreSQL server address         | `127.0.0.1` |
| Port     | PostgreSQL server port            | `5432`      |
| Database | Database name                     | `postgres`  |
| SSL      | Enable SSL connection             | `false`     |
| User     | Database user (credential)        | —           |
| Password | Database password (credential)    | —           |

### Node properties (`async.pg`)

| Field           | Description                                       | Default |
|-----------------|---------------------------------------------------|---------|
| Name            | Optional display name for the node                | —       |
| Server          | Reference to a `postgresDB` config node           | —       |
| Max Concurrency | Maximum number of queries running simultaneously  | `1`     |
| Query           | SQL query template (Nunjucks syntax)              | —       |

---

## 5. Usage

1. Add a `postgresDB` config node with your connection details.
2. Drop an `async.pg` node onto your flow.
3. Connect it to a `postgresDB` config node.
4. Write your SQL query in the editor (Nunjucks templating supported).
5. Connect an input and an output node.

On each incoming message, the node renders the SQL template against `msg`, executes the query, and sets `msg.payload` to the result rows before forwarding the message.

---

## 6. Templating (Nunjucks)

The SQL query is rendered using [Nunjucks](https://mozilla.github.io/nunjucks/) with the entire `msg` object as the template context.

```sql
SELECT * FROM orders WHERE id = {{ payload.id }};

INSERT INTO events (name, ts)
VALUES ('{{ topic }}', NOW());
```

Any property on `msg` is accessible directly by name.

---

## 7. Concurrency Model

`Max Concurrency` is the **single source of truth** for concurrency in this node.

- It defines how many queries run in parallel.
- The internal PostgreSQL connection pool is sized to the same value (`pool.max = maxConcurrency`).
- Excess messages are held in an internal FIFO queue and dispatched as slots become free.

This guarantees:

| `maxConcurrency` | Behaviour                                         |
|------------------|---------------------------------------------------|
| `1`              | Strictly sequential — one query at a time         |
| `N`              | Exactly N queries in parallel, no hidden buffering|

There is no independent pool configuration. The pool is an implementation detail.

---

## 8. Message Flow

```
msg arrives
    │
    ▼
enqueued (FIFO)
    │
    ▼ (slot available)
SQL rendered via Nunjucks
    │
    ▼
query executed
    │
    ▼
msg.payload = result.rows
    │
    ▼
msg forwarded
```

---

## 9. Status Indicator

The node status always follows the format:

```
<queued> (<running>/<maxConcurrency>)
```

| Example   | Meaning                                |
|-----------|----------------------------------------|
| `0 (0/5)` | Idle — no messages in queue or running |
| `0 (3/5)` | 3 queries running, none waiting        |
| `2 (5/5)` | All 5 slots busy, 2 messages queued    |

---

## 10. Error Handling

- On query error, `node.error(err, msg)` is called with the original message.
- The node remains active — the queue continues processing subsequent messages.
- Pool-level errors are also reported via `node.error`.

---

## 11. Design Principles

- **Predictability over flexibility** — one concurrency knob, no hidden limits.
- **No silent buffering** — the queue is visible in the status indicator at all times.
- **Fail loudly** — errors surface immediately via `node.error`, never swallowed.
- **Minimal footprint** — no retries, no fallbacks, no extra abstractions.