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.

Answer 1

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)