> ## Documentation Index
> Fetch the complete documentation index at: https://docs.alumio.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Execute SQL query

> Executes an SQL query as a prepared statement and stores the result at a destination path.

Runs a SQL statement against a database and places the result in your data at a location you choose. Use it to look up records in the middle of a route, for example fetching stock levels or customer details to enrich the data flowing through, or to run an `UPDATE` or `INSERT` and record how many rows it changed. You write the statement once with named parameters such as `:sku` and supply the values separately, so values from your data are never pasted into the SQL text. This keeps the query safe when a value contains quotes or other special characters. This is the successor of the previous Execute SQL query transformer, which built the SQL text from placeholders.

This documentation describes: [Schema](/schemas/transformer/pdo-transformer-v2)

## Fields

**Database connection**

Required

The pre-configured connection to the database you want to query. It decides the database type, driver and credentials used for the statement.

See the [database-connection](/references/database-connection) reference.

**Query**

Required

The SQL statement to run, written in a SQL editor. Placeholders such as `&{sku}` cannot be used in the statement itself. Instead, use a named parameter wherever a value belongs, for example `SELECT * FROM products WHERE sku = :sku`, and supply the value under Parameters. Use each parameter name only once in the statement. Any kind of statement works: a `SELECT` returns rows, while `UPDATE`, `INSERT` and `DELETE` return the number of affected rows. A stored procedure that returns several sets of rows has all of them combined into one list.

**Parameters**

Optional

The values to bind to the named parameters in the statement. Enter them as key/value rows. The key is the parameter name without the colon, so `:sku` in the statement becomes a key named `sku`. Values support [placeholders](/references/placeholders) such as `&{sku}` to take the value from your data. A value that consists of exactly one placeholder keeps the type of the data it points to, so a number stays a number and `null` stays `null`. Any other value is sent as text. Lists and objects cannot be bound and cause the statement to fail. On IBM DB2 connections named parameters are not available: use `?` in the statement and name the parameters `1`, `2` and so on.

**Destination**

Required

The location in your data where the result is stored. Whatever was at that location is replaced. For a statement that returns rows, this becomes a list of rows, one object per row, or an empty list when nothing matched. For a statement without rows, such as `UPDATE`, it becomes `{"affectedRows": n}` with the number of rows the statement changed. Placeholders are supported, so the destination can be built from your data.

**Logging**

Optional

Turn this on to record every statement in the logs with its SQL, the bound parameter values, how long it took and the number of rows returned or affected. Useful while troubleshooting; switch it off again afterwards to keep the logs quiet.

See the [database-query-logger](/references/database-query-logger) reference.

## Sample data

The rows you get back depend on the data in your database. In the example below the `products` table holds a product with SKU `SHIRT-RED-M`, and the transformer looks it up by the incoming `sku`.

| Field | Value |
| - | - |
| Database connection | The configured database connection |
| Query | `SELECT sku, name, price, stock FROM products WHERE sku = :sku` |
| Parameters | `sku` = `&{sku}` |
| Destination | `matches` |

Input:

```json theme={null}
{
    "sku": "SHIRT-RED-M"
}
```

Output:

```json theme={null}
{
    "sku": "SHIRT-RED-M",
    "matches": [
        {
            "sku": "SHIRT-RED-M",
            "name": "Red T-Shirt M",
            "price": 19.95,
            "stock": 12
        }
    ]
}
```

```json Configuration theme={null}
{
  "prototype": "pdo-transformer-v2",
  "parameters": {
    "client": "default",
    "query": "SELECT sku, name, price, stock FROM products WHERE sku = :sku",
    "parameters": {
      "sku": "&{sku}"
    },
    "destination": "matches"
  }
}
```

A statement that changes data stores the number of affected rows instead of a list. This example lowers the stock of the incoming product by the incoming quantity.

| Field | Value |
| - | - |
| Query | `UPDATE products SET stock = stock - :qty WHERE sku = :sku AND stock >= :qty` |
| Parameters | `sku` = `&{sku}`, `qty` = `&{qty}` |
| Destination | `result` |

For an input of `{ "sku": "SHIRT-RED-M", "qty": 2 }` the output is:

```json theme={null}
{
    "sku": "SHIRT-RED-M",
    "qty": 2,
    "result": {
        "affectedRows": 1
    }
}
```

```json Configuration theme={null}
{
  "prototype": "pdo-transformer-v2",
  "parameters": {
    "client": "default",
    "query": "UPDATE products SET stock = stock - :qty WHERE sku = :sku AND stock >= :qty",
    "parameters": {
      "sku": "&{sku}",
      "qty": "&{qty}"
    },
    "destination": "result"
  }
}
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.