Integrate Node-RED with Tiger Cloud

Node-RED is a flow-based programming tool for wiring together hardware devices, APIs, and services using a browser-based editor.

This page shows you how to use Node-RED to write events into your Tiger Data service or database so you can persist, query, and analyze them in a hypertable.

In this integration guide, you:

• Create a hypertable to receive Node-RED events.
• Configure Node-RED with a PostgreSQL / TimescaleDB node and connection details.
• Send a test event from Node-RED and verify it arrived in your hypertable.

Prerequisites for this integration guide

To follow these steps, you'll need:

• A Tiger Cloud service.

  These steps use Tiger Cloud, but the same approach applies to a self-hosted TimescaleDB instance.

• Your connection details.

• A running Node-RED instance that can reach your Tiger Cloud service on port 5432 (install instructions: https://nodered.org/docs/getting-started/).
• The node-red-contrib-postgresql (or equivalent PostgreSQL) node installed in Node-RED.
• A database user with INSERT privileges on the target database in your Tiger Cloud service.

Prepare your database

Create a hypertable that matches the event columns you plan to send from Node-RED. This example uses ts as the time partition, tag_id to identify the source, and value for the measurement:

1. Connect to your Tiger Cloud service

   Use the Tiger Console, psql, or any SQL editor to connect to your service.

2. Create a hypertable for Node-RED events

   Run the following SQL to create the table as a hypertable with explicit chunk interval:

   CREATE TABLE node_red_events (
     ts      TIMESTAMPTZ NOT NULL,
     tag_id  TEXT        NOT NULL,
     value   DOUBLE PRECISION
   ) WITH (
     timescaledb.hypertable,
     timescaledb.chunk_interval = '7 days'
   );

   Modify this schema to match your data. If you send additional fields from Node-RED (such as device ID, location, or unit), add them as columns — for example, device_id TEXT, unit TEXT, or location TEXT. You can also change value to NUMERIC, INT, or TEXT if your measurements are non-numeric.

   The chunk_interval setting controls how much data each chunk holds. Aim for chunks that fit the most recent working set in memory — a good starting point is an interval that produces chunks roughly 25% of your service's RAM. For a light event stream publishing a few events per second, 7 days is a reasonable default; for higher throughput, reduce the interval to hours or even minutes. You can change the interval later with set_chunk_time_interval() — it only affects new chunks.

Configure Node-RED to send data to your database

This section shows a minimal Node-RED flow: an inject node produces a sample payload, a function node formats the values, and a PostgreSQL node performs an INSERT using the service connection details.

1. Install the PostgreSQL node in Node-RED

   Open the Node-RED palette manager and install node-red-contrib-postgresql (or your preferred PostgreSQL client node). Restart Node-RED if required.

2. Configure the PostgreSQL node connection

   In the PostgreSQL node configuration, create a new server config and enter the host, port, database, user, and password from your connection details. Enable SSL/TLS if your service requires it.

   Example connection settings (fill from your connection details):

   • Host: YOUR_SERVICE_HOST
   • Port: 5432
   • Database: tsdb
   • User: tsdbadmin
   • Password: (from your connection details)
   • SSL/TLS: enabled (for Tiger Cloud)

   The PostgreSQL node typically accepts an SQL string and an array of parameters (for example, msg.params).

3. Build a flow that inserts events

   Create a simple flow:

   1. inject node — sends a test JSON object.
   2. function node — maps the incoming JSON to msg.topic (SQL) and msg.params (parameters array).
   3. postgresql node — configured to run parameterized SQL using msg.topic and msg.params.

   Single-row INSERT (simplest approach):

   // msg.payload is the event object
   const ev = msg.payload || {
     tag_id: 'sensor-001',
     value: 22.5
   };
   
   msg.topic = 'INSERT INTO node_red_events (ts, tag_id, value) VALUES (NOW(), $1, $2)';
   msg.params = [ev.tag_id, ev.value];
   return msg;

   Batched INSERT (for higher throughput):

   For production workloads, buffer multiple events and send them in a single multi-row INSERT to reduce commit overhead. This example flushes every 2 seconds to balance throughput with latency:

   // Accumulate events into context.batch with timestamps
   if (!context.batch) {
     context.batch = [];
     context.batchStart = null;
   }
   
   const ev = msg.payload;
   if (ev) {
     context.batch.push(ev);
   
     // Record start time on first event
     if (!context.batchStart) {
       context.batchStart = Date.now();
     }
   }
   
   // Flush if 2 seconds have elapsed OR if explicitly triggered
   const elapsed = Date.now() - context.batchStart;
   const shouldFlush = context.batch.length > 0 && (elapsed >= 2000 || msg.flush);
   
   if (shouldFlush) {
     const batch = context.batch;
     context.batch = [];
     context.batchStart = null;
   
     // Build multi-row VALUES clause
     const values = batch.map((_, i) => {
       const offset = i * 2;
       return `(NOW(), $${offset + 1}, $${offset + 2})`;
     }).join(', ');
   
     const params = batch.flatMap(e => [e.tag_id, e.value]);
   
     msg.topic = `INSERT INTO node_red_events (ts, tag_id, value) VALUES ${values}`;
     msg.params = params;
     return msg;
   }
   
   // Don't send if not flushing yet
   return null;

   Set up a delay node (2 seconds) that feeds back into the function node and inject a message with msg.flush = true. This ensures that even during quiet periods, partial batches are flushed every 2 seconds so data never waits too long.

   Deploy the flow and trigger the inject node to send the test event.

Send a test event and verify data arrived

1. Send a test event from Node-RED

   Trigger the inject node you created. The PostgreSQL node should return success and the flow sidebar will show the node status.

2. Query Tiger Cloud service to confirm events arrived

   Connect to your service and run:

   SELECT ts, tag_id, value
   FROM node_red_events
   ORDER BY ts DESC
   LIMIT 10;

   You should see one or more rows matching the test event you injected, with payload containing the JSON you sent.

You have successfully integrated Node-RED with Tiger Data.

Troubleshooting

• No rows appear after inject: Verify the PostgreSQL node connection details (host/port/user/password) and check the Node-RED logs for errors. Confirm TLS is enabled if using Tiger Cloud.
• Parameter/placeholder errors: Some PostgreSQL nodes expect $1,$2 parameter placeholders; others use ?. Use the syntax required by your Node-RED PostgreSQL node.

Next steps

Find connection details

Where to find the host, port, user, and password for your Tiger Cloud service.