Skip to main content

SQL Query

Beta

This Node is in Beta and may change.

The SQL Node executes SQL queries against databases (MariaDB, Microsoft SQL Server, MySQL, PostgreSQL), optionally storing query results in a Variable for further processing in your Flow.

Basic Configuration​

  • Database Type: Choose your database system (Supported: MariaDB, Microsoft SQL Server, MySQL, or PostgreSQL).
  • Database Host: Enter the hostname or IP address of your database server (e.g., localhost, db.example.com).
    • The Database Host must be reachable on the specified Database Port by the Robot CU where RobFlow is running.
  • Database Port: Specify the database port (Default: 3306 for MySQL/MariaDB, 5432 for PostgreSQL).
  • Database User: Enter the username for database authentication.
  • Database Password: Enter the password for database authentication.
  • Database Name: Specify the name of the database you want to connect to.

SQL Query​

Enter your SQL query to execute. The SQL Node supports SELECT, INSERT, UPDATE, and DELETE operations:

  • SELECT: Retrieve data from tables (e.g., SELECT * FROM inventory WHERE id = 25)
  • INSERT: Add new records (e.g., INSERT INTO inventory (name, quantity) VALUES ('Cogs', '10'))
  • UPDATE: Modify existing records (e.g., UPDATE inventory SET quantity = 15 WHERE name = 'Cogs')
  • DELETE: Remove records (e.g., DELETE FROM inventory WHERE id = 5)

Store Query Result​

For SELECT queries, you can optionally store the results in a Variable:

  • Result Variable: Select a String Variable to store the query results in JSON format.
  • Result Processing: Optionally extract specific data from the query results using bracket notation.

Result Processing​

Query results are automatically converted to JSON format. You can narrow down what you store in the Variable by specifying a specific part of the JSON response using bracket notation.

Example: queryResult[0]["name"]​

  • queryResult: The Variable to store the query results in (should match the previously selected Result Variable)
  • [0]: First row in the query result array
  • ["name"]: The name column value from that row

Node Outputs​

The SQL Node has two outputs for Flow control:

  • Success: Triggered when the query executes successfully.
  • Failure: Triggered when the DB connection or the query fails (Connection or auth issues, syntax errors, constraint violations, etc.).

Example Usage​

Fetching data from a database

  • Database Type: PostgreSQL
  • Host: localhost
  • Port: 5432
  • Database: myapp
  • Query: SELECT * FROM inventory WHERE id = 42
  • Result Variable: inventoryList
  • Result Processing: inventoryList[0]["quantity"] (Stores the quantity value of the first row returned from the query to the selected Variable)

Inserting data into a database

  • Database Type: MySQL
  • Host: db.example.com
  • Port: 3306
  • Database: warehouse
  • Query: INSERT INTO products (name, price, stock) VALUES ('BoltA5', 19.99, 100)

Tips​

  • Connect to the database with a user with minimal required permissions (e.g., read-only for SELECT queries).
  • Be mindful when using queries that modify data in your database (INSERT, UPDATE, DELETE) as these changes are permanent.
  • Test your SQL query in a database client before using it in the SQL Node.
  • Use Result Processing to extract only the data you need, reducing Variable size.
  • Query results are automatically converted from database-specific types (DECIMAL, DATETIME) to JSON-compatible formats.
  • The Node displays "Executing query...", "Query completed", or "Query failed" status during execution.
  • Use the Success/Failure outputs to handle errors gracefully (Ex. retry logic or error notifications).
  • Database connections are automatically closed after query execution to prevent resource leaks.