github.com/Jeffail/benthos/v3@v3.65.0/website/docs/components/processors/sql_select.md (about) 1 --- 2 title: sql_select 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_select.go 13 --> 14 15 import Tabs from '@theme/Tabs'; 16 import TabItem from '@theme/TabItem'; 17 18 Runs an SQL select query against a database and returns the result as an array of objects, one for each row returned, containing a key for each column queried and its value. 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_select: 34 driver: "" 35 dsn: "" 36 table: "" 37 columns: [] 38 where: "" 39 args_mapping: "" 40 ``` 41 42 </TabItem> 43 <TabItem value="advanced"> 44 45 ```yaml 46 # All config fields, showing default values 47 label: "" 48 sql_select: 49 driver: "" 50 dsn: "" 51 table: "" 52 columns: [] 53 where: "" 54 args_mapping: "" 55 prefix: "" 56 suffix: "" 57 ``` 58 59 </TabItem> 60 </Tabs> 61 62 If the query fails to execute then the message will remain unchanged and the error can be caught using error handling methods outlined [here](/docs/configuration/error_handling). 63 64 ## Examples 65 66 <Tabs defaultValue="Table Query (PostgreSQL)" values={[ 67 { label: 'Table Query (PostgreSQL)', value: 'Table Query (PostgreSQL)', }, 68 ]}> 69 70 <TabItem value="Table Query (PostgreSQL)"> 71 72 73 Here we query a database for columns of footable that share a `user_id` 74 with the message `user.id`. A [`branch` processor](/docs/components/processors/branch) 75 is used in order to insert the resulting array into the original message at the 76 path `foo_rows`: 77 78 ```yaml 79 pipeline: 80 processors: 81 - branch: 82 processors: 83 - sql_select: 84 driver: postgres 85 dsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable 86 table: footable 87 columns: [ '*' ] 88 where: user_id = ? 89 args_mapping: '[ this.user.id ]' 90 result_map: 'root.foo_rows = this' 91 ``` 92 93 </TabItem> 94 </Tabs> 95 96 ## Fields 97 98 ### `driver` 99 100 A database [driver](#drivers) to use. 101 102 103 Type: `string` 104 Options: `mysql`, `postgres`, `clickhouse`, `mssql`. 105 106 ### `dsn` 107 108 A Data Source Name to identify the target database. 109 110 #### Drivers 111 112 The following is a list of supported drivers and their respective DSN formats: 113 114 | Driver | Data Source Name Format | 115 |---|---| 116 | `clickhouse` | [`tcp://[netloc][:port][?param1=value1&...¶mN=valueN]`](https://github.com/ClickHouse/clickhouse-go#dsn) 117 | `mysql` | `[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]` | 118 | `postgres` | `postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]` | 119 | `mssql` | `sqlserver://[user[:password]@][netloc][:port][?database=dbname¶m1=value1&...]` | 120 121 Please note that the `postgres` driver enforces SSL by default, you 122 can override this with the parameter `sslmode=disable` if required. 123 124 125 Type: `string` 126 127 ```yaml 128 # Examples 129 130 dsn: tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000 131 132 dsn: foouser:foopassword@tcp(localhost:3306)/foodb 133 134 dsn: postgres://foouser:foopass@localhost:5432/foodb?sslmode=disable 135 ``` 136 137 ### `table` 138 139 The table to query. 140 141 142 Type: `string` 143 144 ```yaml 145 # Examples 146 147 table: foo 148 ``` 149 150 ### `columns` 151 152 A list of columns to query. 153 154 155 Type: `array` 156 157 ```yaml 158 # Examples 159 160 columns: 161 - '*' 162 163 columns: 164 - foo 165 - bar 166 - baz 167 ``` 168 169 ### `where` 170 171 An optional where clause to add. Placeholder arguments are populated with the `args_mapping` field. Placeholders should always be question marks, and will automatically be converted to dollar syntax when the postgres driver is used. 172 173 174 Type: `string` 175 176 ```yaml 177 # Examples 178 179 where: meow = ? and woof = ? 180 181 where: user_id = ? 182 ``` 183 184 ### `args_mapping` 185 186 An optional [Bloblang mapping](/docs/guides/bloblang/about) which should evaluate to an array of values matching in size to the number of placeholder arguments in the field `where`. 187 188 189 Type: `string` 190 191 ```yaml 192 # Examples 193 194 args_mapping: root = [ this.cat.meow, this.doc.woofs[0] ] 195 196 args_mapping: root = [ meta("user.id") ] 197 ``` 198 199 ### `prefix` 200 201 An optional prefix to prepend to the query (before SELECT). 202 203 204 Type: `string` 205 206 ### `suffix` 207 208 An optional suffix to append to the select query. 209 210 211 Type: `string` 212 213