How to use SQL "LIKE" in Sequelize?

Question

I am developing using sequelize but as it is my first time using this ORM I am with some doubts.

I have the code below to make a query in the database through an input type text but I am using the attribute in Where that refers to the same and I would like one that would contain it or check if any of the information in the search input contains in the username field.

My current code is this:

User.findAll({ where: { US_USERNAME: req.query.search } }).then(users => {
                            res.render('main/users', {
                                title: "Usuarios",
                                usuario: users,
                            });
                        });

I would like to exchange Where equal for a like to check whether the word consulted on req.query.search } contains in my bank’s username field and is not the same. In short I want to exchange the same of Where for a like in sequelize

Answer 1

You can use the operator Op.like from Sequelize to make more complex queries. It would look like this:

const Op = Sequelize.Op;              // biblioteca de operadores
const query = `%${req.query.search}`; // string de consulta

User.findAll({ where: { US_USERNAME: { [Op.like]: query } } })
  .then(users => {
    res.render('main/users', {
      title: "Usuarios",
      usuario: users,
    });
  });

For example, imagine a value for req.query.search = 'andre', query would look like this:

query = '%andre'

this will make a query in the bank of the type:

SELECT * FROM users WHERE us_username LIKE '%andre';

User.findAll({ where: { US_USERNAME: { [Op.like]: '%andre' } } })
  .then(users => {
    res.render('main/users', {
      title: "Usuarios",
      usuario: users,
    });
  });

See more about:

• Operators in the Sequelize