github.com/Jeffail/benthos/v3@v3.65.0/website/docs/components/processors/sql_insert.md (about)

     1  ---
     2  title: sql_insert
     3  type: processor
     4  status: stable
     5  categories: ["Integration"]
     6  ---
     7  
     8  <!--
     9       THIS FILE IS AUTOGENERATED!
    10  
    11       To make changes please edit the contents of:
    12       lib/processor/sql_insert.go
    13  -->
    14  
    15  import Tabs from '@theme/Tabs';
    16  import TabItem from '@theme/TabItem';
    17  
    18  Inserts rows into an SQL database for each message, and leaves the message unchanged.
    19  
    20  Introduced in version 3.59.0.
    21  
    22  
    23  <Tabs defaultValue="common" values={[
    24    { label: 'Common', value: 'common', },
    25    { label: 'Advanced', value: 'advanced', },
    26  ]}>
    27  
    28  <TabItem value="common">
    29  
    30  ```yaml
    31  # Common config fields, showing default values
    32  label: ""
    33  sql_insert:
    34    driver: ""
    35    dsn: ""
    36    table: ""
    37    columns: []
    38    args_mapping: ""
    39  ```
    40  
    41  </TabItem>
    42  <TabItem value="advanced">
    43  
    44  ```yaml
    45  # All config fields, showing default values
    46  label: ""
    47  sql_insert:
    48    driver: ""
    49    dsn: ""
    50    table: ""
    51    columns: []
    52    args_mapping: ""
    53    prefix: ""
    54    suffix: ""
    55  ```
    56  
    57  </TabItem>
    58  </Tabs>
    59  
    60  If the insert fails to execute then the message will still remain unchanged and the error can be caught using error handling methods outlined [here](/docs/configuration/error_handling).
    61  
    62  ## Examples
    63  
    64  <Tabs defaultValue="Table Insert (MySQL)" values={[
    65  { label: 'Table Insert (MySQL)', value: 'Table Insert (MySQL)', },
    66  ]}>
    67  
    68  <TabItem value="Table Insert (MySQL)">
    69  
    70  
    71  Here we insert rows into a database by populating the columns id, name and topic with values extracted from messages and metadata:
    72  
    73  ```yaml
    74  pipeline:
    75    processors:
    76      - sql_insert:
    77          driver: mysql
    78          dsn: foouser:foopassword@tcp(localhost:3306)/foodb
    79          table: footable
    80          columns: [ id, name, topic ]
    81          args_mapping: |
    82            root = [
    83              this.user.id,
    84              this.user.name,
    85              meta("kafka_topic"),
    86            ]
    87  ```
    88  
    89  </TabItem>
    90  </Tabs>
    91  
    92  ## Fields
    93  
    94  ### `driver`
    95  
    96  A database [driver](#drivers) to use.
    97  
    98  
    99  Type: `string`  
   100  Options: `mysql`, `postgres`, `clickhouse`, `mssql`.
   101  
   102  ### `dsn`
   103  
   104  A Data Source Name to identify the target database.
   105  
   106  #### Drivers
   107  
   108  The following is a list of supported drivers and their respective DSN formats:
   109  
   110  | Driver | Data Source Name Format |
   111  |---|---|
   112  | `clickhouse` | [`tcp://[netloc][:port][?param1=value1&...&paramN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn)
   113  | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]` |
   114  | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` |
   115  | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname&param1=value1&...]` |
   116  
   117  Please note that the `postgres` driver enforces SSL by default, you
   118  can override this with the parameter `sslmode=disable` if required.
   119  
   120  
   121  Type: `string`  
   122  
   123  ```yaml
   124  # Examples
   125  
   126  dsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000
   127  
   128  dsn: foouser:foopassword@tcp(localhost:3306)/foodb
   129  
   130  dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable
   131  ```
   132  
   133  ### `table`
   134  
   135  The table to insert to.
   136  
   137  
   138  Type: `string`  
   139  
   140  ```yaml
   141  # Examples
   142  
   143  table: foo
   144  ```
   145  
   146  ### `columns`
   147  
   148  A list of columns to insert.
   149  
   150  
   151  Type: `array`  
   152  
   153  ```yaml
   154  # Examples
   155  
   156  columns:
   157    - foo
   158    - bar
   159    - baz
   160  ```
   161  
   162  ### `args_mapping`
   163  
   164  A [Bloblang mapping](/docs/guides/bloblang/about) which should evaluate to an array of values matching in size to the number of columns specified.
   165  
   166  
   167  Type: `string`  
   168  
   169  ```yaml
   170  # Examples
   171  
   172  args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ]
   173  
   174  args_mapping: root = [ meta("user.id") ]
   175  ```
   176  
   177  ### `prefix`
   178  
   179  An optional prefix to prepend to the insert query (before INSERT).
   180  
   181  
   182  Type: `string`  
   183  
   184  ### `suffix`
   185  
   186  An optional suffix to append to the insert query.
   187  
   188  
   189  Type: `string`  
   190  
   191  ```yaml
   192  # Examples
   193  
   194  suffix: ON CONFLICT (name) DO NOTHING
   195  ```
   196  
   197