FlowsGuide

SQL steps in flows

A SQL step runs one query on one of the project’s PostgreSQL or MySQL connections, and its rows become the step’s output — ready for a loop, a Transform, an e-mail or a script below. With a SQL step, a loop and a Send email step, a whole campaign runs with no script at all.

9 min readBefore this: What a flow is, IntegrationsOpen in Waiso
What you will learn
  • Add a SQL step and give it a connection
  • Pass flow values into the query as :name parameters
  • Read the output, the row limit and the limited flag
  • Loop over the rows, including results too large to pass inline

A query as a step

In the Add Node menu, PostgreSQL and MySQL sit beside the script languages under Scripts. Picking one adds a step whose code is SQL; Existing Script links a SQL script of the project instead. Either way the step runs on the database through the project’s connection — the credentials never reach your code — and answers with its rows as objects keyed by column name.

A PostgreSQL step totals the day, a MySQL step saves the totals, an e-mail sends them — no script step anywhere.
Expected outputSuccessA SQL step’s output
{
  "columns": [
    "region",
    "total"
  ],
  "rows": [
    {
      "region": "South",
      "total": 1840.5
    },
    {
      "region": "North",
      "total": 1312
    }
  ],
  "rowCount": 2,
  "count": 2,
  "durationMs": 4,
  "warnings": [],
  "limited": false,
  "limit": 10000
}

Numeric, date, time, timestamp and UUID columns are decoded into JSON values — a numeric total arrives as a number — so the next step can bind rows.0.total straight away.

Give it a connection

  1. Add the step

    Click a + and pick PostgreSQL or MySQL under Scripts.

  2. Open its code

    Click the step, then Open code editor. Write the query; parameters you declare appear on the Parameters tab.

  3. Pick the connection

    On the Connection tab, choose a connection of the same engine under For this step. A linked SQL script shows its own default (Script default: crm-db); a connection picked on the step wins over it, and Use the script default goes back.

  4. Set the row limit

    Row limit, on the same tab, is how many rows the query may return — 10,000 unless you change it, up to 100,000.

Parameters in a query

Declare each input in a -- @param header and use it as :name in the statement. The value is sent to the database as a real bound parameter, never pasted into the text, so a value can never change the query. On the step, bind each parameter like any other input — From node, Expression, Variable or a constant.

active_customers.pg.sql
PostgreSQL
-- @param min_total number = 100
-- @param country string = BR
SELECT id, email, first_name AS "First Name", total
FROM customers
WHERE active AND total >= :min_total AND country = :country
ORDER BY total DESC;

The row limit and “limited”

A read — a statement starting with SELECT or WITH — is wrapped so the limit holds whatever the query looks like: SELECT * FROM (<your query>) AS _waiso LIMIT n+1. Your own LIMIT still wins when it is smaller. The extra row tells “exactly n” from “more than n”: when there were more, the output says limited: true with the limit, and the step’s header in the run reads Limited to 10,000 rows. Writes (INSERT, UPDATE, DELETE…) are never wrapped; rows they return are still capped.

WhereRows returned
A SQL step in a flow (and a SQL script run)10,000 by default, up to 100,000
The SQL Workbench500 — it is for looking, not for moving data

Looping over the rows

Set a loop’s Items to From Node → the SQL step and it loops over rows by itself; the panel shows the columns (Each row: id · email · First Name). Inside the body, bind item.email, item["First Name"] and index. A parallel loop with a Send email step inside sends the whole list in waves of up to 20.

Large results

Rows are passed between steps inline up to 5 MB. A result larger than that is stored in the project’s S3 bucket — or the platform’s, if the project has none — and the output changes shape: rows is empty, preview holds the first 100 rows, offloaded is true, and downloadPath points at the whole result. The step’s panel says Large result — it travels between steps through S3.

Expected outputSuccessAn offloaded result (preview cut to one row here)
{
  "columns": [
    "id",
    "email"
  ],
  "rows": [],
  "preview": [
    {
      "id": 1,
      "email": "ana@example.com"
    }
  ],
  "offloaded": true,
  "sizeBytes": 7340032,
  "rowCount": 61234,
  "count": 61234,
  "limited": false,
  "limit": 100000,
  "downloadPath": "/api/projects/<project>/runs/<run>/result"
}
  • A loop bound to it still loops over every row: it reads them from storage a page of 1,000 at a time.
  • A script step bound to rows receives the stored result’s reference, with its downloadPath, instead of the rows — and reads the rows from there.
  • downloadPath answers with a short-lived download link; when the stored object is gone it answers 410 result_unavailable.

Limits

  • One statement per step for the limit to apply: a script with several statements is run as written and only its returned rows are capped.
  • Row limit: 1 to 100,000 (10,000 by default). Inline results up to 5 MB; larger ones go to storage.
  • The connection must be of the step’s engine — a PostgreSQL step cannot use a MySQL connection.

Related topics