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

---
title: sql
type: output
status: stable
categories: ["Services"]
---

<!--
     THIS FILE IS AUTOGENERATED!

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

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


Runs an SQL prepared query against a target database for each message.

Introduced in version 3.33.0.


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

<TabItem value="common">

```yaml
# Common config fields, showing default values
output:
  label: ""
  sql:
    driver: mysql
    data_source_name: ""
    query: ""
    args_mapping: ""
    max_in_flight: 1
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""
```

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

```yaml
# All config fields, showing default values
output:
  label: ""
  sql:
    driver: mysql
    data_source_name: ""
    query: ""
    args_mapping: ""
    max_in_flight: 1
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""
      processors: []
```

</TabItem>
</Tabs>

## Alternatives

For basic inserts use the [`sql_insert`](/docs/components/outputs/sql_insert) output instead. For more complex queries use the [`sql_raw`](/docs/components/outputs/sql_raw) output.

## Performance

This output benefits from sending multiple messages in flight in parallel for
improved performance. You can tune the max number of in flight messages with the
field `max_in_flight`.

This output benefits from sending messages as a batch for improved performance.
Batches can be formed at both the input and output level. You can find out more
[in this doc](/docs/configuration/batching).

## Examples

<Tabs defaultValue="Table Insert (MySQL)" values={[
{ label: 'Table Insert (MySQL)', value: 'Table Insert (MySQL)', },
{ label: 'Table Insert (PostgreSQL)', value: 'Table Insert (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
output:
  sql:
    driver: mysql
    data_source_name: foouser:foopassword@tcp(localhost:3306)/foodb
    query: "INSERT INTO footable (foo, bar, baz) VALUES (?, ?, ?);"
    args_mapping: '[ this.document.foo, this.document.bar, meta("kafka_topic") ]'
    batching:
      count: 500
```

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


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

```yaml
output:
  sql:
    driver: postgres
    data_source_name: postgres://foouser:foopassword@localhost:5432/foodb?sslmode=disable
    query: "INSERT INTO footable (foo, bar, baz) VALUES ($1, $2, $3);"
    args_mapping: '[ this.document.foo, this.document.bar, meta("kafka_topic") ]'
    batching:
      count: 500
```

</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 (?, ?, ?);
```

### `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)
```

### `max_in_flight`

The maximum number of messages to have in flight at a given time. Increase this to improve throughput.


Type: `int`  
Default: `1`  

### `batching`

Allows you to configure a [batching policy](/docs/configuration/batching).


Type: `object`  

```yaml
# Examples

batching:
  byte_size: 5000
  count: 0
  period: 1s

batching:
  count: 10
  period: 1s

batching:
  check: this.contains("END BATCH")
  count: 0
  period: 1m
```

### `batching.count`

A number of messages at which the batch should be flushed. If `0` disables count based batching.


Type: `int`  
Default: `0`  

### `batching.byte_size`

An amount of bytes at which the batch should be flushed. If `0` disables size based batching.


Type: `int`  
Default: `0`  

### `batching.period`

A period in which an incomplete batch should be flushed regardless of its size.


Type: `string`  
Default: `""`  

```yaml
# Examples

period: 1s

period: 1m

period: 500ms
```

### `batching.check`

A [Bloblang query](/docs/guides/bloblang/about/) that should return a boolean value indicating whether a message should end a batch.


Type: `string`  
Default: `""`  

```yaml
# Examples

check: this.type == "end_of_transaction"
```

### `batching.processors`

A list of [processors](/docs/components/processors/about) to apply to a batch as it is flushed. This allows you to aggregate and archive the batch however you see fit. Please note that all resulting messages are flushed as a single batch, therefore splitting the batch into smaller batches using these processors is a no-op.


Type: `array`  
Default: `[]`  

```yaml
# Examples

processors:
  - archive:
      format: lines

processors:
  - archive:
      format: json_array

processors:
  - merge_json: {}
```