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&...¶mN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn) 113 | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]` | 114 | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` | 115 | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname¶m1=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