Skip to main content
Version: Next

SQL Query

Runs a statement against one of your SQL connections. It is the node most pipelines end at, and its writes are the ones store-and-forward protects.

Type: database-sql · Category: Datasource · Ports: one input · one output

The SQL Query node as it appears on the pipeline canvas.The SQL Query node as it appears on the pipeline canvas.

When to use it​

  • Writing production, downtime or quality records to a reporting database
  • Reading context a pipeline needs — the running work order, a product spec, a threshold
  • Updating rows a later step has enriched
Reach for something else when

For high-rate time series use QuestDB, which is built for the volume. For tag history, enable history on the tag and let the historian do it — do not write your own.

Settings​

SettingKeyPurpose
ConnectionconnectionIdWhich SQL connection.
QueryqueryThe statement, with {{…}} filled from the incoming message.

Getting values into the statement​

{{ }} is not string pasting

Each {{…}} is replaced by a bound placeholder and its value is handed to the driver separately, so the statement is parameterised and a product code containing an apostrophe cannot corrupt it. This is the safe way to get a value into a query — do not build SQL by concatenating strings in a Python node instead.

This is not the :name syntax used by named queries. Named queries are a Designer feature for screen bindings; this node has no named-query control and runs the statement you type.

What you can reference

PathResolves to
{{payload}}The whole payload
{{payload.units}}A field, at any depth — {{payload.order.line}} works
{{topic}}The message topic
{{timestamp}} · {{id}}The message timestamp and id
{{vars.shift}}Message metadata, also available as {{metadata.shift}}
{{origin.id}} · {{origin.type}}The node that produced the message

With no upstream message the query runs exactly as typed, with nothing substituted — which is what makes a plain SELECT work behind an Inject.

A path that does not exist is left as literal text

{{payload.data}} against a message with no data field is not blanked and not bound — the token stays in the statement exactly as you typed it and is handed to the database as raw SQL:

-- you wrote
WHERE units = {{payload.units}}
WHERE data  = {{payload.data}}

-- the database receives
WHERE units = ?                 -- bound, value 42
WHERE data  = {{payload.data}}  -- raw text, no argument

Which is a syntax error, and the error the database returns names the {{, not the missing field. The rule is deliberate — a silent NULL would make a WHERE match the wrong rows and a DELETE match too many — but it means a typo in a path fails at the database, not in the editor. Check the path against a real message in the Debug node before you rely on it.

Note this is the opposite of what the notification and AI nodes do with the same syntax, where an unresolved token becomes empty.

Example A — reading​

Fetch the work order currently running on a line, to enrich everything downstream.

SELECT wo.id            AS "workOrder",
       wo.product_code  AS "productCode",
       wo.target_rate   AS "targetRate",
       wo.started_at    AS "startedAt"
FROM work_order wo
WHERE wo.line = {{payload.line}}
  AND wo.status = 'RUNNING'
ORDER BY wo.started_at DESC
LIMIT 1

In

{ "payload": { "line": "LINE1" } }

Out — rows come back as an array, even when there is one:

{
  "payload": [
    { "workOrder": "WO-10442", "productCode": "SKU-889", "targetRate": 3000, "startedAt": "2026-08-12T06:00:00Z" }
  ]
}

A query that matches nothing returns [], not null. Guard for it:

def transform(payload, message):
    if not payload:
        return None          # no running order — end this branch quietly
    return payload[0]

Example B — writing​

INSERT INTO production_log (ts, line, work_order, units, oee)
VALUES ({{payload.ts}}, {{payload.line}}, {{payload.workOrder}}, {{payload.units}}, {{payload.oee}})

In

{
  "payload": {
    "ts": "2026-08-12T07:00:00Z",
    "line": "LINE1",
    "workOrder": "WO-10442",
    "units": 2841,
    "oee": 0.847
  }
}

Out — a write reports what it affected rather than returning rows:

{ "payload": { "rowsAffected": 1 } }

Example C — an upsert, so a replay cannot duplicate​

Writes here are covered by store-and-forward: if the database is unreachable the write is buffered and replayed in order rather than lost. That is also why an INSERT should be idempotent — a replay after a timeout may repeat a statement whose first attempt actually committed.

INSERT INTO production_log (ts, line, work_order, units, oee)
VALUES ({{payload.ts}}, {{payload.line}}, {{payload.workOrder}}, {{payload.units}}, {{payload.oee}})
ON CONFLICT (ts, line) DO UPDATE
   SET units = EXCLUDED.units,
       oee   = EXCLUDED.oee

Gotchas​

  • A placeholder that names a key the message does not have resolves to nothing. Rename a field in an upstream Python node and the insert silently changes shape.
  • This node opens no transaction. For atomicity across several statements, use system.db.transaction() in a scripting node.

See also​