MySQL FullText Search with Sequelize

Question

I want to implement MySQL full text search with sequelize. The version "sequelize": "^3.23.6". I tried to research about this but could not find =the documentation that guides how to implement this. Here is the link that says FullText is supported by sequelize: https://github.com/sequelize/sequelize/issues/2979

But there is not exact documentation on how to do it and how to do a full text search query with sequelize.

Any links advice would be helpful

Thanks !

Answer 1

Since we now have the error message in recent Sequelize that looks like this:

Unhandled rejection Error: Support for literal replacements in the where object has been removed.

The solution would be to provide replacements manually

Payments.findAll({
  where: Sequelize.literal('MATCH (SomeField) AGAINST (:name)'),
  replacements: {
    name: 'Alex'
  }
});

Use arrays for more complex conditions:

Payments.findAll({
  where: [
    { State: 'Paid' },
    Sequelize.literal('MATCH (SomeField) AGAINST (:name)')
  ],
  replacements: {
    name: 'Alex'
  }
});

Answer 2

Sequelize doesn’t fully support the full-text search feature. We can add a FULLTEXT index as easy as any other index. But operators supporting the MATCH (column) AGAINST (value) syntax haven’t been implemented.

My current solution to the problem consists of creating a regular model:

module.exports = (sequelize, DataTypes) => {
  const Book = sequelize.define('Book', {
    title: DataTypes.STRING,
    description: DataTypes.TEXT,
    isActive: DataTypes.BOOLEAN
  }, {
    indexes: [
      // add a FULLTEXT index
      { type: 'FULLTEXT', name: 'text_idx', fields: ['description'] }
    ]
  });

  return Book;
};

And using a raw query for querying:

const against = 'more or less';

models.Book.find({
  where: ['isActive = 1 AND MATCH (description) AGAINST(?)', [against]]
}).then((result) => {
  console.log(result.title);
});

Using only MySQL it's not possible to get correct results if you trying to search for inflectional words, synonyms etc. MySQL developers consider adding dictionaries for full-text search (https://dev.mysql.com/worklog/task/?id=2428), but who knows when we will see it.

If you have to stick with MySQL, I suggest to take a look at Sphinx. It works properly with synonyms and inflectional words.