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

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

<!--
     THIS FILE IS AUTOGENERATED!

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

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

Inserts a row into an SQL database for each message.

Introduced in version 3.59.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_insert:
    driver: ""
    dsn: ""
    table: ""
    columns: []
    args_mapping: ""
    max_in_flight: 64
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""
```

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

```yaml
# All config fields, showing default values
output:
  label: ""
  sql_insert:
    driver: ""
    dsn: ""
    table: ""
    columns: []
    args_mapping: ""
    prefix: ""
    suffix: ""
    max_in_flight: 64
    batching:
      count: 0
      byte_size: 0
      period: ""
      check: ""
      processors: []
```

</TabItem>
</Tabs>

## Examples

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

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


Here we insert rows into a database by populating the columns id, name and topic with values extracted from messages and metadata:

```yaml
output:
  sql_insert:
    driver: mysql
    dsn: foouser:foopassword@tcp(localhost:3306)/foodb
    table: footable
    columns: [ id, name, topic ]
    args_mapping: |
      root = [
        this.user.id,
        this.user.name,
        meta("kafka_topic"),
      ]
```

</TabItem>
</Tabs>

## Fields

### `driver`

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


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

### `dsn`

A Data Source Name to identify the target database.

#### Drivers

The following is a list of supported drivers and their respective DSN formats:

| Driver | Data Source Name Format |
|---|---|
| `clickhouse` | [`tcp://[netloc][:port][?param1=value1&...&paramN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn)
| `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]` |
| `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` |
| `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&...]` |

Please note that the `postgres` driver enforces SSL by default, you
can override this with the parameter `sslmode=disable` if required.


Type: `string`  

```yaml
# Examples

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

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

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

### `table`

The table to insert to.


Type: `string`  

```yaml
# Examples

table: foo
```

### `columns`

A list of columns to insert.


Type: `array`  

```yaml
# Examples

columns:
  - foo
  - bar
  - baz
```

### `args_mapping`

A [Bloblang mapping](/docs/guides/bloblang/about) which should evaluate to an array of values matching in size to the number of columns specified.


Type: `string`  

```yaml
# Examples

args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ]

args_mapping: root = [ meta("user.id") ]
```

### `prefix`

An optional prefix to prepend to the insert query (before INSERT).


Type: `string`  

### `suffix`

An optional suffix to append to the insert query.


Type: `string`  

```yaml
# Examples

suffix: ON CONFLICT (name) DO NOTHING
```

### `max_in_flight`

The maximum number of inserts to run in parallel.


Type: `int`  
Default: `64`  

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

```yaml
# Examples

processors:
  - archive:
      format: lines

processors:
  - archive:
      format: json_array

processors:
  - merge_json: {}
```