github.com/Jeffail/benthos/v3@v3.65.0/website/docs/components/processors/sql.md

---
title: sql
type: processor
status: stable
categories: ["Integration"]
---

<!--
     THIS FILE IS AUTOGENERATED!

     To make changes please edit the contents of:
     lib/processor/sql.go
-->

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';


Runs an SQL prepared query against a target database for each message and, for
queries that return rows, replaces it with the result according to a
[codec](#result-codecs).


<Tabs defaultValue="common" values={[
  { label: 'Common', value: 'common', },
  { label: 'Advanced', value: 'advanced', },
]}>

<TabItem value="common">

```yaml
# Common config fields, showing default values
label: ""
sql:
  driver: mysql
  data_source_name: ""
  query: ""
  args_mapping: ""
  result_codec: none
```

</TabItem>
<TabItem value="advanced">

```yaml
# All config fields, showing default values
label: ""
sql:
  driver: mysql
  data_source_name: ""
  query: ""
  unsafe_dynamic_query: false
  args_mapping: ""
  result_codec: none
```

</TabItem>
</Tabs>

## Alternatives

For basic inserts or select queries use use either the [`sql_insert`](/docs/components/processors/sql_insert) or the [`sql_select`](/docs/components/processors/sql_select) processor.

For more complex queries use the [`sql_raw`](/docs/components/processors/sql_raw) processor.

## Examples

<Tabs defaultValue="Table Insert (MySQL)" values={[
{ label: 'Table Insert (MySQL)', value: 'Table Insert (MySQL)', },
{ label: 'Table Query (PostgreSQL)', value: 'Table Query (PostgreSQL)', },
]}>

<TabItem value="Table Insert (MySQL)">


The following example inserts rows into the table footable with the columns foo,
bar and baz populated with values extracted from messages:

```yaml
pipeline:
  processors:
    - sql:
        driver: mysql
        data_source_name: foouser:foopassword@tcp(localhost:3306)/foodb
        query: "INSERT INTO footable (foo, bar, baz) VALUES (?, ?, ?);"
        args_mapping: '[ document.foo, document.bar, meta("kafka_topic") ]'
```

</TabItem>
<TabItem value="Table Query (PostgreSQL)">


Here we query a database for columns of footable that share a `user_id`
with the message `user.id`. The `result_codec` is set to
`json_array` and a [`branch` processor](/docs/components/processors/branch)
is used in order to insert the resulting array into the original message at the
path `foo_rows`:

```yaml
pipeline:
  processors:
    - branch:
        processors:
          - sql:
              driver: postgres
              result_codec: json_array
              data_source_name: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable
              query: "SELECT * FROM footable WHERE user_id = $1;"
              args_mapping: '[ this.user.id ]'
        result_map: 'root.foo_rows = this'
```

</TabItem>
</Tabs>

## Fields

### `driver`

A database [driver](#drivers) to use.


Type: `string`  
Default: `"mysql"`  
Options: `mysql`, `postgres`, `clickhouse`, `mssql`.

### `data_source_name`

A Data Source Name to identify the target database.


Type: `string`  
Default: `""`  

```yaml
# Examples

data_source_name: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000

data_source_name: foouser:foopassword@tcp(localhost:3306)/foodb

data_source_name: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable
```

### `query`

The query to run against the database.


Type: `string`  
Default: `""`  

```yaml
# Examples

query: INSERT INTO footable (foo, bar, baz) VALUES (?, ?, ?);
```

### `unsafe_dynamic_query`

Whether to enable dynamic queries that support interpolation functions. WARNING: This feature opens up the possibility of SQL injection attacks and is considered unsafe.


Type: `bool`  
Default: `false`  

### `args_mapping`

A [Bloblang mapping](/docs/guides/bloblang/about) that produces the arguments for the query. The mapping must return an array containing the number of arguments in the query.


Type: `string`  
Default: `""`  
Requires version 3.47.0 or newer  

```yaml
# Examples

args_mapping: '[ this.foo, this.bar.not_empty().catch(null), meta("baz") ]'

args_mapping: root = [ uuid_v4() ].merge(this.document.args)
```

### `result_codec`

A [codec](#result-codecs) to determine how resulting rows are converted into messages.


Type: `string`  
Default: `"none"`  
Options: `none`, `json_array`.

## Result Codecs

When a query returns rows they are serialised according to a chosen codec, and
the message contents are replaced with the serialised result.

### `none`

The result of the query is ignored and the message remains unchanged. If your
query does not return rows then this is the appropriate codec.

### `json_array`

The resulting rows are serialised into an array of JSON objects, where each
object represents a row, where the key is the column name and the value is that
columns value in the row.