Function sql

Properties

attribute cast col fn identifier join jsonPath list literal unquote uuidV1 uuidV4 where

Properties

attribute

attribute: (
    attributeName: string,
) => AssociationPath | Attribute | DialectAwareFn | Cast | JsonPath

Type declaration

    • (
          attributeName: string,
      ): AssociationPath | Attribute | DialectAwareFn | Cast | JsonPath

    • Used to represent the attribute of a model. You should use the attribute name, which will be mapped to the correct column name. This attribute name follows the same rules as the attribute names in POJO where options. As such, you can use dot notation to access nested JSON properties, and you can reference included associations.

      If you want to use a database name, without mapping, you can use Identifier.

      Parameters

      • attributeName: string

      Returns AssociationPath | Attribute | DialectAwareFn | Cast | JsonPath

      Example

      Let's say the class User has an attribute firstName, which maps to the column first_name.

      User.findAll({
       where: sql`${attribute('firstName')} = 'John'`
      });

      Will generate:

      SELECT * FROM users WHERE first_name = 'John'

      Example

      Let's say the class User has an attribute data, which is a JSON column.

      User.findAll({
       where: sql`${attribute('data.registered')} = 'true'`
      });

      Will generate (assuming the dialect supports JSON operators):

      SELECT * FROM users WHERE data->'registered' = 'true'

cast

cast: (val: unknown, type: DataType) => Cast

Type declaration

    • (val: unknown, type: DataType): Cast

    • Creates an object representing a call to the cast function.

      Parameters

      • val: unknown

        The value to cast

      • type: DataType

        The type to cast it to

      Returns Cast

col

col: (...identifiers: string[]) => Col

Type declaration

    • (...identifiers: string[]): Col

    • Creates an object which represents a column in the DB, this allows referencing another column in your query. This is often useful in conjunction with sql.fn, sql.where and sql which interpret strings as values and not column names.

      Col works similarly to sql.identifier, but "*" has special meaning, for backwards compatibility.

      ⚠️ We recommend using sql.identifier, or sql.attribute instead.

      Parameters

      • ...identifiers: string[]

        The name of the column

      Returns Col

fn

fn: (fnName: string, ...args: readonly unknown[]) => Fn

Type declaration

    • (fnName: string, ...args: readonly unknown[]): Fn

    • Creates an object representing a database function. This can be used in search queries, both in where and order parts, and as default values in column definitions. If you want to refer to columns in your function, you should use sql.attribute (recommended), sql.identifier, or sql.col (discouraged) otherwise the value will be interpreted as a string.

      ℹ️ This method is usually verbose and we recommend using the sql template string tag instead.

      Parameters

      • fnName: string

        The SQL function you want to call

      • ...args: readonly unknown[]

        All further arguments will be passed as arguments to the function

      Returns Fn

      Example: Convert a user's username to upper case

      instance.update({
        username: fn('upper', col('username'))
      });

identifier

identifier: (...values: TableOrModel[]) => Identifier

Type declaration

    • (...values: TableOrModel[]): Identifier

    • Used to represent a value that will either be escaped to a literal, or a bind parameter. Unlike sql.attribute and sql.col, this identifier will be escaped as-is, without mapping to a column name or any other transformation.

      This method supports strings, table structures, model classes (in which case the identifiers will be the model schema & table name), and model definitions (same behavior as model classes)

      Parameters

      • ...values: TableOrModel[]

        The identifiers to escape. Automatically joins them with a period (.).

      Returns Identifier

      Example

      sequelize.query(sql`SELECT * FROM users WHERE ${identifier('firstName')} = 'John'`);

      Will generate (identifier quoting depending on the dialect):

      SELECT * FROM users WHERE "firstName" = 'John'

join

join: (parts: unknown[], separator: string | BaseSqlExpression) => Literal

Type declaration

    • (parts: unknown[], separator: string | BaseSqlExpression): Literal

    • A version of Array.join, but for SQL expressions. Using Array.join directly would not work, because the end result would be a string, not a SQL expression.

      Parameters

      • parts: unknown[]

        The parts to join. Each part can be a SQL expression, or a value to escape.

      • separator: string | BaseSqlExpression

        A raw SQL string, or a SQL expression to separate each pair of adjacent elements of the array.

      Returns Literal

      A SQL expression representing the concatenation of all parts, interspersed with the separator.

jsonPath

jsonPath: (expression: unknown, path: readonly (string | number)[]) => JsonPath

Type declaration

    • (expression: unknown, path: readonly (string | number)[]): JsonPath

    • Use this to access nested properties in a JSON column. You can also use the dot notation with sql.attribute, but this works with any values, not just attributes.

      Parameters

      • expression: unknown

        The expression to access the property on.

      • path: readonly (string | number)[]

        The path to the property. If a number is used, it will be treated as an array index, otherwise as a key.

      Returns JsonPath

      Example

      sql`${jsonPath('data', ['name'])} = '"John"'`

      will produce

      -- postgres
      "data"->'name' = '"John"'
      -- sqlite, mysql, mariadb
      JSON_EXTRACT("data", '$.name') = '"John"'

      Example

      // notice here that 0 is a number, not a string. It will be treated as an array index.
      sql`${jsonPath('array', [0])}`

      will produce

      -- postgres
      "array"->0
      -- sqlite, mysql, mariadb
      JSON_EXTRACT(`array`, '$[0]')

      Example

      // notice here that 0 is a string, not a number. It will be treated as an object key.
      sql`${jsonPath('object', ['0'])}`

      will produce

      -- postgres
      "object"->'0'
      -- sqlite, mysql, mariadb
      JSON_EXTRACT(`object`, '$.0')

list

list: (values: unknown[]) => List

Type declaration

    • (values: unknown[]): List

    • Used to represent an SQL list of values, e.g. WHERE id IN (1, 2, 3). This ensure that the array is interpreted as an SQL list, and not as an SQL Array.

      Parameters

      • values: unknown[]

        The members of the list.

      Returns List

      Example

      sequelize.query(sql`SELECT * FROM users WHERE id IN ${list([1, 2, 3])}`);

      Will generate:

      SELECT * FROM users WHERE id IN (1, 2, 3)

literal

literal: (val: string | (string | BaseSqlExpression)[]) => Literal

Type declaration

    • (val: string | (string | BaseSqlExpression)[]): Literal

    • Creates an object representing a literal, i.e. something that will not be escaped. We recommend using sql for a better DX.

      Parameters

      • val: string | (string | BaseSqlExpression)[]

        literal value

      Returns Literal

unquote

unquote: (...args: unknown[]) => DialectAwareFn

uuidV1

uuidV1: SqlUuidV1

uuidV4

uuidV4: SqlUuidV4

where

where: {
    (leftOperand: unknown, whereAttributeHashValue: any): Where;
    (whereOptions: WhereOptions): Where;
    (
        leftOperand: unknown,
        operator: keyof WhereOperators<any>,
        rightOperand: unknown,
    ): Where;
}

Type declaration

    • (leftOperand: unknown, whereAttributeHashValue: any): Where

    • A way of writing an SQL binary operator, or more complex where conditions.

      This solution is slightly more verbose than the POJO syntax, but allows any value on the left hand side of the operator (unlike the POJO syntax which only accepts attribute names). For instance, either the left or right hand side of the operator can be sql.fn, sql.col, sql.literal etc.

      If your left operand is an attribute name, using the regular POJO syntax ({ where: { attrName: value }}) syntax is usually more convenient.

      ⚠️ Unlike the POJO syntax, if the left operand is a string, it will be treated as a value, not an attribute name. If you wish to refer to an attribute, use Attribute instead.

      Parameters

      • leftOperand: unknown

        The left operand

      • whereAttributeHashValue: any

        The POJO containing the operators and the right operands

      Returns Where

      Example

      where(attribute('id'), { [Op.eq]: 1 });
      where(attribute('id'), {
        [Op.or]: {
          [Op.eq]: 1,
          [Op.gt]: 10,
        },
      });
    • (whereOptions: WhereOptions): Where

    • This version of where is used to opt back into the POJO syntax. Useful in combination with sql.

      Parameters

      Returns Where

      Example

      sequelize.query(sql`
        SELECT * FROM users WHERE ${where({ id: 1 })};
      `)

      produces

      SELECT * FROM users WHERE "id" = 1;
    • (
          leftOperand: unknown,
          operator: keyof WhereOperators<any>,
          rightOperand: unknown,
      ): Where

    • Parameters

      • leftOperand: unknown

        The left operand

      • operator: keyof WhereOperators<any>

        The operator to use (one of the different values available in the Op object)

      • rightOperand: unknown

        The right operand

      Returns Where

      Example

      where(col('id'), Op.eq, 1)

      Example

      // Using a column name as the left operand.
      // Equal to: WHERE first_name = 'Lily'
      where(col('first_name'), Op.eq, 'Lily');

      Example

      // Using a SQL function on the left operand.
      // Equal to: WHERE LOWER(first_name) = 'lily'
      where(fn('LOWER', col('first_name')), Op.eq, 'lily');

      Example

      // Using raw SQL as the left operand.
      // Equal to: WHERE 'Lily' = 'Lily'
      where(literal(`'Lily'`), Op.eq, 'Lily');

Settings

Member Visibility

On This Page

Properties