Skip to main content

sql_select

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.

Introduced in version 3.59.0.

# Common config fields, showing default values
label: ""
sql_select:
  driver: ""
  dsn: ""
  table: ""
  columns: []
  where: ""
  args_mapping: ""

If the query fails to execute then the message will remain unchanged and the error can be caught using error handling methods outlined here.

Examples​

Here we query a database for columns of footable that share a user_id with the message user.id. A branch processor is used in order to insert the resulting array into the original message at the path foo_rows:

pipeline:
  processors:
    - branch:
        processors:
          - sql_select:
              driver: postgres
              dsn: postgres://foouser:foopass@localhost:5432/testdb?sslmode=disable
              table: footable
              columns: [ '*' ]
              where: user_id = ?
              args_mapping: '[ this.user.id ]'
        result_map: 'root.foo_rows = this'

Fields​

driver​

A database driver 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:

DriverData Source Name Format
clickhousetcp://[netloc][:port][?param1=value1&...&paramN=valueN]
mysql[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
postgrespostgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
mssqlsqlserver://[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 

# 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 query.

Type: string 

# Examples


table: foo

columns​

A list of columns to query.

Type: array 

# Examples


columns:
  - '*'


columns:
  - foo
  - bar
  - baz

where​

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.

Type: string 

# Examples


where: meow = ? and woof = ?


where: user_id = ?

args_mapping​

An optional Bloblang mapping which should evaluate to an array of values matching in size to the number of placeholder arguments in the field where.

Type: string 

# 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 query (before SELECT).

Type: string

suffix​

An optional suffix to append to the select query.

Type: string