Skip to content

Latest commit

 

History

History
131 lines (101 loc) · 2.36 KB

params.md

File metadata and controls

131 lines (101 loc) · 2.36 KB

params

Specifies parameter values to fill in for placeholders inside SQL.

This option is designed to be used through API (though nothing really prevents usage from command line).

Option value

  • Array of strings for position placeholders.
  • Object of name-value pairs for named (and indexed) placeholders.

Note: The escaping of values must be handled by user of the API.

Positional placeholders

For positional placeholders use array of values:

format('SELECT * FROM persons WHERE fname = ? AND age = ?', {
  params: ["'John'", '27'],
  language: 'sql',
});

Results in:

SELECT
  *
FROM
  persons
WHERE
  fname = 'John'
  AND age = 27

Named placeholders

For named placeholders use object of name-value pairs:

format('SELECT * FROM persons WHERE fname = @name AND age = @age', {
  params: { name: "'John'", age: '27' },
  language: 'tsql',
});

Results in:

SELECT
  *
FROM
  persons
WHERE
  fname = 'John'
  AND age = 27

Numbered placeholders

Treat numbered placeholders the same as named ones and use an object of number-value pairs:

format('SELECT * FROM persons WHERE fname = $1 AND age = $2', {
  params: { 1: "'John'", 2: '27' },
  language: 'postgresql',
});

Results in:

SELECT
  *
FROM
  persons
WHERE
  fname = 'John'
  AND age = 27

Quoted placeholders

Some dialects (BigQuery, Transact SQL) also support quoted names for placeholders:

format('SELECT * FROM persons WHERE fname = @`first name` AND age = @`age`', {
  params: { 'first name': "'John'", 'age': '27' },
  language: 'bigquery',
});

Results in:

SELECT
  *
FROM
  persons
WHERE
  fname = 'John'
  AND age = 27

Available placeholder types

The placeholder types available by default depend on SQL dialect used:

  • sql - ?
  • bigquery - ?, @name, @`name`
  • db2 - ?, :name
  • db2i - ?, :name
  • hive - no support
  • mariadb - ?
  • mysql - ?
  • n1ql - ?, $1, $name
  • plsql - :1, :name
  • postgresql - $1
  • redshift - $1
  • snowflake - no support
  • sqlite - ?, ?1, :name, @name, $name
  • spark - no support
  • tidb - ?
  • tsql - @name, @"name", @[name]
  • trino - no support

If you need to use a different placeholder syntax than the builtin one, you can configure the supported placeholder types using the paramTypes config option.