Query

The Query node allows you to write and run queries against database tables managed by FlowFuse Tables. The node is pre-configured to connect automatically when used within a FlowFuse Node-RED instance.

With FlowFuse AI Assistant integration, queries can be generated from natural language prompts, making database operations accessible without SQL expertise.

Outputs

The response (rows) is provided in msg.payload as an array. When Split results is enabled with Number of rows = 1, msg.payload contains a single row object instead.

Additional Metadata

  • msg.pgsql.rowCount - Number of rows affected
  • msg.pgsql.command - The executed command

For multiple queries, msg.pgsql is returned as an array.

Inputs

SQL queries can be configured directly in the node or passed dynamically via msg.query.

Parameterized Queries (Recommended)

Pass parameters as an array via msg.params:

Input Data
msg.params = [ msg.id ];
Query defined in the node
SELECT * FROM table WHERE id = $1

Tip: For production environments, it is recommended to use parameterized queries instead. Parameterized queries automatically handle quoting and escaping, making them safer and more reliable.

Named Parameters

Pass parameters as an object via msg.queryParameters:

Input Data
msg.queryParameters.id = msg.id;
Query defined in the node
SELECT * FROM table WHERE id = $id;

Mustache Templates

Reference message properties using Mustache syntax:

Query defined in the node
SELECT * FROM table WHERE id = {{{ msg.id }}}
SELECT * FROM table WHERE name = '{{{ msg.name }}}'

Note: Care must be taken to ensure incoming string data is properly escaped (e.g., single quotes must be doubled: ' to '') to prevent syntax errors and SQL injection.

Note: Inserting dynamic values into SQL statements using Mustache templates exposes your data to SQL Injection risks if the input is untrusted. We strongly recommend using Parameterized Queries or Named Parameters instead; these features are designed to safely separate data from the SQL command.

Important Details

Case Sensitivity

By default, PostgreSQL converts unquoted table and column names to lowercase, making them case-insensitive (e.g., SELECT DataVal FROM MyTable is the same as SELECT dataval FROM mytable). To avoid errors and ensure portability, it is common to use only lowercase, unquoted identifiers. However, where required, you can wrap names in double quotes (e.g., SELECT "DataVal" FROM "MyTable") to explicitly force them to be case-sensitive if the names were defined that way.

Security Best Practices

Parameterized queries are strongly recommended for production use over Mustache templates for security and maintainability.

Named Parameters Limitation

Named parameters are emulated (not native PostgreSQL), making them less robust than numeric parameters.

Backpressure Management

When Split results is enabled, the node waits for msg.tick before releasing the next batch, preventing memory issues. It exposes node.tickConsumer and node.tickProvider for automatic flow control.

Split Results Sequences

Streaming messages follow sequence conventions with:

  • msg.parts.id
  • msg.parts.index
  • msg.parts.count
  • msg.complete flag

Requirements

FlowFuse Tables requires Enterprise tier and must be enabled for your team.

Example Flow

Generate Queries with AI Assistant

In the Query node, click "Assistant", enter plain English like "Show me all readings from today", and the AI automatically generates the SQL query.

Query Node AI Assistant

For more detailed information on natural language queries with the Query node, read this article: AI Assistant for FlowFuse Tables.