Function sql

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.

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'`
});
Copy

Will generate:

SELECT * FROM users WHERE first_name = 'John'
Copy

Example

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

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

Will generate (assuming the dialect supports JSON operators):

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

Creates an object representing a call to the cast function.

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 fn, where and sql which interpret strings as values and not column names.

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

⚠️ We recommend using Identifier, or Attribute instead.

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 Attribute (recommended), Identifier, or 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.

Example: Convert a user's username to upper case

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

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

Example

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

Will generate (identifier quoting depending on the dialect):

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

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

Example

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

will produce

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

Example

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

will produce

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

Example

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

will produce

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

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.

Example

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

Will generate:

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

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

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 fn, col, 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.

Example

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

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

Example

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

produces

SELECT * FROM users WHERE "id" = 1;
Copy