# SQL query

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

1. Add **SQL query** from the workflow step picker.
2. Give the step a recognisable name and connect its required inputs.
3. Set the options below. Nested names identify fields within an object or a list item.
4. 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](/docs/workflows/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:

```json
{
  "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:

```json
{
  "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.