As you may know, SQL query syntax has many operators that you can use to influence the action done by your query.
For example, the LIKE operator can be used in the WHERE clause to filter the data, showing only matching rows:
SELECT * FROM Users
WHERE `firstName` LIKE 'nathan';
When you use the Sequelize ORM for your JavaScript project, the SQL operators are included in the library in the form of the Op object.
You need to import the object from Sequelize to use it in your code:
const { Sequelize, Op } = require("sequelize");
When you call the query methods from your model, you can use the Op object properties to create SQL queries with operators.
For example, here’s how you use the findAll() method with the LIKE operator as in the previous example:
User.findAll({
where: {
firstName: {
[Op.like]: "Nathan",
},
},
});
The Sequelize code above will generate and execute the following SQL query:
SELECT
`id`,
`firstName`,
`status`
FROM
`Users` AS `User`
WHERE
`User`.`firstName` LIKE 'Nathan';
And that’s how you can use SQL operators in Sequelize.
Here’s the full list of available operators under the Sequelize Op object, taken from Sequelize v6.2.15.
Except for the first six operators, the rest are self-described:
const Op: OpTypes = {
eq: Symbol.for('eq'), // equal
ne: Symbol.for('ne'), // not equal
gte: Symbol.for('gte'), // greater than or equal
gt: Symbol.for('gt'), // greater than
lte: Symbol.for('lte'), // less than or equal
lt: Symbol.for('lt'), // less than
not: Symbol.for('not'),
is: Symbol.for('is'),
in: Symbol.for('in'),
notIn: Symbol.for('notIn'),
like: Symbol.for('like'),
notLike: Symbol.for('notLike'),
iLike: Symbol.for('iLike'),
notILike: Symbol.for('notILike'),
startsWith: Symbol.for('startsWith'),
endsWith: Symbol.for('endsWith'),
substring: Symbol.for('substring'),
regexp: Symbol.for('regexp'),
notRegexp: Symbol.for('notRegexp'),
iRegexp: Symbol.for('iRegexp'),
notIRegexp: Symbol.for('notIRegexp'),
between: Symbol.for('between'),
notBetween: Symbol.for('notBetween'),
overlap: Symbol.for('overlap'),
contains: Symbol.for('contains'),
contained: Symbol.for('contained'),
adjacent: Symbol.for('adjacent'),
strictLeft: Symbol.for('strictLeft'),
strictRight: Symbol.for('strictRight'),
noExtendRight: Symbol.for('noExtendRight'),
noExtendLeft: Symbol.for('noExtendLeft'),
and: Symbol.for('and'),
or: Symbol.for('or'),
any: Symbol.for('any'),
all: Symbol.for('all'),
values: Symbol.for('values'),
col: Symbol.for('col'),
placeholder: Symbol.for('placeholder'),
join: Symbol.for('join'),
match: Symbol.for('match')
} as OpTypes;
For more examples of how to use these operators, you can refer to the official Sequelize operators documentation.
And that’s how you can add operators in Sequelize. Nice work! 👍