Sequelize Op.iLike throwing Error with MySQL

Question

Error: Invalid value { undefined: 'w%' }

This is my query:

  results = await models.Record.findAll({
          where: {
            
            name: {
              [Op.iLike]: prefix + "%", //causing problems
            },
          },
          order: [["createdAt", "DESC"]],
          limit: num,
        });

name is a String field in my MySQL table.

You have an error in your SQL syntax near 'ILIKE' 'james'. It seems the ORM converts the query to ILIKE, which is not valid.

I suggest that you log the final SQL query. It's tough to debug generated code by staring at code that generates it. See https://stackoverflow.com/questions/21427501/how-can-i-see-the-sql-generated-by-sequelize-js/26171518 — Bill Karwin, Jun 14 '21 at 02:18
^ you can do that buy adding `log: true` to your query which will print the SQL to the console. — doublesharp, Jun 14 '21 at 03:25
Sequelize ILIKE is for postgres only. MySQL doesn't support ILIKE. Instead, the regular LIKE in MySQL is case insensitive search by default. https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html — Emma, Jun 14 '21 at 21:06
They actually do. `[Op.like]: prefix + '%'` yet, if you are searching by `prefix`, you can use `Op.startsWith` as well. It internally translates into LIKE statement. — Emma, Jun 21 '21 at 04:32

nbk · Accepted Answer · 2022-07-06T21:32:15.470

sequelize should work with ILike for mysql see link at the end

you ca use instead Op.startsWith

    results = await models.Record.findAll({
      where: {
        
        name: {
          [Op.startsWith]: prefix, 
        },
      },
      order: [["createdAt", "DESC"]],
      limit: num,
    });

Further string functions see manual

  [Op.like]: '%hat',                       // LIKE '%hat'
  [Op.notLike]: '%hat',                    // NOT LIKE '%hat'
  [Op.startsWith]: 'hat',                  // LIKE 'hat%'
  [Op.endsWith]: 'hat',                    // LIKE '%hat'
  [Op.substring]: 'hat',                   // LIKE '%hat%'
  [Op.iLike]: '%hat',                      // ILIKE '%hat' (case insensitive) (PG only)
  [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (PG only)
  [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
  [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
  [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (PG only)
  [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (PG only)

Thanks. Frustrating that the platform specific operations aren't detailed in the API docs. — Mr5o1, Oct 16 '21 at 20:33

Sequelize Op.iLike throwing Error with MySQL

1 Answers1