SQL query
Run one SQL read (rows out) or write (records in) against a DSN.
Run one SQL read (rows out) or write (records in) against a DSN.
When to use it
Read rows with a SQL query or write input records using the configured database operation. Use an appropriately scoped connection and check the selected mode. Preview and retry behaviour must be assessed for the operation; a write is not merely a read because it is expressed as SQL.
Add and configure
- Add SQL query from the workflow step picker.
- Give the step a recognisable name and connect its required inputs.
- Set the options below. Nested names identify fields within an object or a list item.
- Validate the recipe, inspect the test result, then run it on the intended data.
The step identifier used in the editor’s code view is sql_query.
Settings
| Setting | Accepted value | Required | Default | What it controls |
|---|---|---|---|---|
Cursor Column (cursor_column) | string or null | No | null | Sort column for read mode pagination. Required when mode='read'. |
Dsn (dsn) | string | Yes | Not specified | SQLAlchemy async DSN. |
Limit (limit) | integer; minimum 1; maximum 10000 | No | 500 | Max rows returned in read mode (one page). |
Mode (mode) | "read" / "write" | No | "read" | Choose read to return query results or write to insert incoming records. |
Query (query) | string or null | No | null | SELECT statement (read mode). Required when mode='read'. |
Table (table) | string or null | No | null | Target table (write mode). Required when mode='write'. |
Upsert Columns (upsert_columns) | list of string or null | No | null | Postgres-only ON CONFLICT columns for write idempotency. |
Inputs and outputs
| Direction | Port | Content | Required | Multiple records |
|---|---|---|---|---|
| Inputs | in | records | Yes | Yes |
| Outputs | out | records | Yes | Yes |
Effects, testing, and recovery
Can write or act on an external destination.
The declared preview behaviour is to skip this step. Configuration can affect preview behaviour; HTTP GET is a notable exception. Read dry runs before using a preview with live connections.
Duplicate prevention uses the operation’s natural identity. Do not assume that manually repeating the step is free of repeated effects.
Example: Read unpaid invoices
Step settings:
{
"cursor_column": "id",
"dsn": "postgresql+asyncpg://reporter@db.internal:5432/warehouse",
"limit": 200,
"mode": "read",
"query": "SELECT id, total FROM invoices WHERE paid = false"
}
Example: Upsert the records into a table
Step settings:
{
"dsn": "postgresql+asyncpg://reporter@db.internal:5432/warehouse",
"mode": "write",
"table": "invoice_flags",
"upsert_columns": [
"id"
]
}
Check the result
Inspect the selected run’s status, counts, errors, and output records. An empty collection can be a valid result, and a completed step can contain per-record failures where the step defines that behaviour. Check the next step’s expected record shape before publishing or delivering the result.