Skip to content

Latest commit

 

History

History
764 lines (563 loc) · 10.4 KB

QUERY_BUILDING.md

File metadata and controls

764 lines (563 loc) · 10.4 KB
Raw

Query building

Queries are built using methods of the sql tagged template literal.

sql.valueList

Note: Before using sql.valueList evaluate if sql.array is not a better option.

(
  values: $ReadOnlyArray<PrimitiveValueExpressionType>
) => ValueListSqlTokenType;

Creates a list of values, e.g.

await connection.query(sql`
  SELECT (${sql.valueList([1, 2, 3])})
`);

Produces:

{
  sql: 'SELECT ($1, $2, $3)',
  values: [
    1,
    2,
    3
  ]
}

Value list can describe other SQL tokens, e.g.

await connection.query(sql`
  SELECT (${sql.valueList([1, sql.raw('to_timestamp($1)', [2]), 3])})
`);

Produces:

{
  sql: 'SELECT ($1, to_timestamp($2), $3)',
  values: [
    1,
    2,
    3
  ]
}

sql.array

(
  values: $ReadOnlyArray<PrimitiveValueExpressionType>
) => ValueListSqlTokenType;

Creates an array value binding, e.g.

await connection.query(sql`
  SELECT (${sql.array([1, 2, 3], 'int4')})
`);

Produces:

{
  sql: 'SELECT $1::int4[]',
  values: [
    [
      1,
      2,
      3
    ]
  ]
}

Unlike sql.valueList, sql.array generates a stable query of a predictable length, i.e. regardless of the number of the values in the array, the generated query remains the same:

  • Having a stable query enables pg_stat_statements to aggregate all query execution statistics.
  • Keeping the query length short reduces query parsing time.

Furthermore, unlike sql.valueList, sql.array can be used with an empty array of values.

Example:

sql`SELECT id FROM foo WHERE id IN (${sql.valueList([1, 2, 3])})`;
sql`SELECT id FROM foo WHERE id NOT IN (${sql.valueList([1, 2, 3])})`;

Is equivalent to:

sql`SELECT id FROM foo WHERE id = ANY(${sql.array([1, 2, 3], 'int4')})`;
sql`SELECT id FROM foo WHERE id != ALL(${sql.array([1, 2, 3], 'int4')})`;

In short, when the value list length is dynamic then sql.array should be preferred over sql.valueList.

sql.tuple

(
  values: $ReadOnlyArray<PrimitiveValueExpressionType>
) => TupleSqlTokenType;

Creates a tuple (typed row construct), e.g.

await connection.query(sql`
  INSERT INTO (foo, bar, baz)
  VALUES ${sql.tuple([1, 2, 3])}
`);

Produces:

{
  sql: 'INSERT INTO (foo, bar, baz) VALUES ($1, $2, $3)',
  values: [
    1,
    2,
    3
  ]
}

Tuple can describe other SQL tokens, e.g.

await connection.query(sql`
  INSERT INTO (foo, bar, baz)
  VALUES ${sql.tuple([1, sql.raw('to_timestamp($1)', [2]), 3])}
`);

Produces:

{
  sql: 'INSERT INTO (foo, bar, baz) VALUES ($1, to_timestamp($2), $3)',
  values: [
    1,
    2,
    3
  ]
}

sql.tupleList

(
  tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>
) => TupleListSqlTokenType;

Creates a list of tuples (typed row constructs), e.g.

await connection.query(sql`
  INSERT INTO (foo, bar, baz)
  VALUES ${sql.tupleList([
    [1, 2, 3],
    [4, 5, 6]
  ])}
`);

Produces:

{
  sql: 'INSERT INTO (foo, bar, baz) VALUES ($1, $2, $3), ($4, $5, $6)',
  values: [
    1,
    2,
    3,
    4,
    5,
    6
  ]
}

Tuple list can describe other SQL tokens, e.g.

await connection.query(sql`
  INSERT INTO (foo, bar, baz)
  VALUES ${sql.tupleList([
    [1, sql.raw('to_timestamp($1)', [2]), 3],
    [4, sql.raw('to_timestamp($1)', [5]), 6]
  ])}
`);

Produces:

{
  sql: 'INSERT INTO (foo, bar, baz) VALUES ($1, to_timestamp($2), $3), ($4, to_timestamp($5), $6)',
  values: [
    1,
    2,
    3,
    4,
    5,
    6
  ]
}

sql.unnest

(
  tuples: $ReadOnlyArray<$ReadOnlyArray<PrimitiveValueExpressionType>>,
  columnTypes: $ReadOnlyArray<string>
): UnnestSqlTokenType;

Creates an unnest expressions, e.g.

await connection.query(sql`
  SELECT bar, baz
  FROM ${sql.unnest(
    [
      [1, 'foo'],
      [2, 'bar']
    ],
    [
      'int4',
      'text'
    ]
  )} AS foo(bar, baz)
`);

Produces:

{
  sql: 'SELECT bar, baz FROM unnest($1::int4[], $2::text[]) AS foo(bar, baz)',
  values: [
    [
      1,
      2
    ],
    [
      'foo',
      'bar'
    ]
  ]
}

sql.identifier

(
  names: $ReadOnlyArray<string>
) => IdentifierSqlTokenType;

Delimited identifiers are created by enclosing an arbitrary sequence of characters in double-quotes ("). To create create a delimited identifier, create an sql tag function placeholder value using sql.identifier, e.g.

sql`
  SELECT 1
  FROM ${sql.identifier(['bar', 'baz'])}
`;

Produces:

{
  sql: 'SELECT 1 FROM "bar"."bar"',
  values: []
}

sql.identifierList

(
  identifiers: $ReadOnlyArray<$ReadOnlyArray<string>>
) => IdentifierListSqlTokenType;

Creates a list of identifiers, e.g.

sql`
  SELECT 1
  FROM ${sql.identifierList([
    ['bar', 'baz'],
    ['qux', 'quux']
  ])}
`;

Produces:

{
  sql: 'SELECT 1 FROM "bar"."baz", "qux"."quux"',
  values: []
}

Identifier aliases

A member of the identifier list can be aliased:

sql`
  SELECT 1
  FROM ${sql.identifierList([
    {
      alias: 'qux',
      identifier: ['bar', 'baz']
    },
    {
      alias: 'corge',
      identifier: ['quux', 'quuz']
    }
  ])}
`;

Produces:

{
  sql: 'SELECT 1 FROM "bar"."baz" "qux", "quux"."quuz" "corge"',
  values: []
}

sql.raw

(
  rawSql: string,
  values?: $ReadOnlyArray<PrimitiveValueExpressionType>
) => RawSqlTokenType;

Raw/ dynamic SQL can be inlined using sql.raw, e.g.

sql`
  SELECT 1
  FROM ${sql.raw('"bar"')}
`;

Produces:

{
  sql: 'SELECT 1 FROM "bar"',
  values: []
}

The second parameter of the sql.raw can be used to bind positional parameter values, e.g.

sql`
  SELECT ${sql.raw('$1', [1])}
`;

Produces:

{
  sql: 'SELECT $1',
  values: [
    1
  ]
}

Building dynamic queries

If you require to build a query based on a dynamic condition, then consider using an SQL builder for that specific query, e.g. Sqorn.

const query = sq
  .return({
    authorId: 'a.id',
    name: 'a.last_name'
  })
  .distinct
  .from({
    b: 'book'
    })
  .leftJoin({
    a: 'author'
  })
  .on`b.author_id = a.id`
  .where({
    title: 'Oathbringer',
    genre: 'fantasy'
  })
  .query;

sql`${sql.raw(query.text, query.args)}`

Named parameters

sql.raw supports named parameters, e.g.

sql`
  SELECT ${sql.raw(':foo, :bar', {bar: 'BAR', foo: 'FOO'})}
`;

Produces:

{
  sql: 'SELECT $1, $2',
  values: [
    'FOO',
    'BAR'
  ]
}

Named parameters are matched using /[\s,(]:([a-z_]+)/g regex.

sql.booleanExpression

(
  members: $ReadOnlyArray<ValueExpressionType>,
  operator: LogicalBooleanOperatorType
) => BooleanExpressionSqlTokenType;

Boolean expression.

sql`
  SELECT ${sql.booleanExpression([3, 4], 'AND')}
`;

Produces:

{
  sql: 'SELECT $1 AND $2',
  values: [
    3,
    4
  ]
}

Boolean expressions can describe SQL tokens (including other boolean expressions), e.g.

sql`
  SELECT ${sql.booleanExpression([
    sql.comparisonPredicate(sql.identifier(['foo']), '=', sql.raw('to_timestamp($1)', 2)),
    sql.booleanExpression([
      3,
      4
    ], 'OR')
  ], 'AND')}
`;

Produces:

{
  sql: 'SELECT ("foo" = to_timestamp($1) AND ($1 OR $2))',
  values: [
    2,
    3,
    4
  ]
}

Note: Do not use sql.booleanExpression when expression consists of a single predicate. Use sql.comparisonPredicate.

sql.comparisonPredicate

(
  leftOperand: ValueExpressionType,
  operator: ComparisonOperatorType,
  rightOperand: ValueExpressionType
) => ComparisonPredicateSqlTokenType;

A comparison predicate compares two expressions using a comparison operator.

sql`
  SELECT ${sql.comparisonPredicate(3, '=', 4)}
`;

Produces:

{
  sql: 'SELECT $1 = $2',
  values: [
    3,
    4
  ]
}

Comparison predicate operands can describe SQL tokens, e.g.

sql`
  SELECT ${sql.comparisonPredicate(sql.identifier(['foo']), '=', sql.raw('to_timestamp($1)', 2))}
`;

Produces:

{
  sql: 'SELECT "foo" = to_timestamp($1)',
  values: [
    2
  ]
}

sql.assignmentList

(
  namedAssignmentValueBindings: NamedAssignmentType
) => AssignmentListSqlTokenType

Creates an assignment list, e.g.

await connection.query(sql`
  UPDATE foo
  SET ${sql.assignmentList({
    bar: 'baz',
    qux: 'quux'
  })}
`);

Produces:

{
  sql: 'UPDATE foo SET bar = $1, qux = $2',
  values: [
    'baz',
    'quux'
  ]
}

Assignment list can describe other SQL tokens, e.g.

await connection.query(sql`
  UPDATE foo
  SET ${sql.assignmentList({
    bar: sql.raw('to_timestamp($1)', ['baz']),
    qux: sql.raw('to_timestamp($1)', ['quux'])
  })}
`);

Produces:

{
  sql: 'UPDATE foo SET bar = to_timestamp($1), qux = to_timestamp($2)',
  values: [
    'baz',
    'quux'
  ]
}

Snake-case normalization

By default, sql.assignmentList converts object keys to snake-case, e.g.

await connection.query(sql`
  UPDATE foo
  SET ${sql.assignmentList({
    barBaz: sql.raw('to_timestamp($1)', ['qux']),
    quuxQuuz: sql.raw('to_timestamp($1)', ['corge'])
  })}
`);

Produces:

{
  sql: 'UPDATE foo SET bar_baz = to_timestamp($1), quux_quuz = to_timestamp($2)',
  values: [
    'qux',
    'corge'
  ]
}

This behaviour can be overriden by constructing a custom sql tag and configuring normalizeIdentifier, e.g.

import {
  createSqlTag
} from 'slonik';

const sql = createSqlTag({
  normalizeIdentifier: (identifierName) => {
    return identifierName;
  }
});

With this configuration, the earlier code example produces:

{
  sql: 'UPDATE foo SET "barBaz" = to_timestamp($1), "quuxQuuz" = to_timestamp($2)',
  values: [
    'qux',
    'corge'
  ]
}

sql.json

(
  value: SerializableValueType
) => JsonSqlTokenType;

Serializes value and binds it as an array, e.g.

await connection.query(sql`
  SELECT (${sql.json([1, 2, 3])})
`);

Produces:

{
  sql: 'SELECT $1',
  values: [
    '[1,2,3]'
  ]
}

This is a convenience function equivalent to:

await connection.query(sql`
  SELECT (${JSON.stringify([1, 2, 3])}})
`);