Function sql

sql(rawSql: TemplateStringsArray, ...values: unknown[]): Literal
The template tag function used to easily create @sequelize/core!sql.literal.
Parameters
- rawSql: TemplateStringsArray
- ...values: unknown[]
Returns Literal
Example
```
sql`SELECT * FROM ${sql.identifier(table)} WHERE ${sql.identifier(column)} = ${value}`
```
- Defined in core/src/expression-builders/sql.ts:27

Index

Properties

attribute cast col fn identifier join jsonPath list literal random unquote uuidV1 uuidV4 uuidV7 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 @sequelize/core!sql.fn, @sequelize/core!sql.where and @sequelize/core!sql which interpret strings as values and not column names.
  
  Col works similarly to @sequelize/core!sql.identifier, but "*" has special meaning, for backwards compatibility.
  
  ⚠️ We recommend using @sequelize/core!sql.identifier, or @sequelize/core!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 @sequelize/core!sql.attribute (recommended), @sequelize/core!sql.identifier, or @sequelize/core!sql.col (discouraged) otherwise the value will be interpreted as a string.
  
  ℹ️ This method is usually verbose and we recommend using the @sequelize/core!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 @sequelize/core!sql.attribute and @sequelize/core!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 @sequelize/core!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 @sequelize/core!sql for a better DX.
  Parameters
  - val: string | (string | BaseSqlExpression)[]
    literal value
  Returns Literal

random

random: Random

unquote

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

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 @sequelize/core!sql.fn, @sequelize/core!sql.col, @sequelize/core!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 @sequelize/core!sql.
  Parameters
  - whereOptions: WhereOptions
  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');
```

Function sql

Parameters

Returns Literal

Example

Index

Properties

Properties

attribute

Type Declaration

Parameters

Returns AssociationPath | Attribute | DialectAwareFn | Cast | JsonPath

Example

Example

cast

Type Declaration

Parameters

Returns Cast

col

Type Declaration

Parameters

Returns Col

fn

Type Declaration

Parameters

Returns Fn

Example: Convert a user's username to upper case

identifier

Type Declaration

Parameters

Returns Identifier

Example

join

Type Declaration

Parameters

Returns Literal

jsonPath

Type Declaration

Parameters

Returns JsonPath

Example

Example

Example

list

Type Declaration

Parameters

Returns List

Example

literal

Type Declaration

Parameters

Returns Literal

random

unquote

uuidV1

uuidV4

uuidV7

where

Type Declaration

Parameters

Returns Where

Example

Parameters

Returns Where

Example

Parameters

Returns Where

Example

Example

Example

Example

Settings

On This Page