How to implement PostgresQL tsvector for full-text search using Sequelize?

Question

I was going through this tutorial: https://www.digitalocean.com/community/tutorials/how-to-use-full-text-search-in-postgresql-on-ubuntu-16-04

Where it is recommended to add a document column as tsvector and applying an index to it.

ALTER TABLE news ADD "document" tsvector;
CREATE INDEX idx_fts_search ON news USING gin(document);
SELECT title, content FROM news WHERE document @@ to_tsquery('Travel | Cure');

How do I implement the document column with sequelize? There is no tsvector data type: http://docs.sequelizejs.com/variable/index.html

(Would this be a good time to try out Knex.js?)

You should be able to specify the type as a string. You don't have to use the builtin types. You'll just have to do a bit of work to get the values from the database. For reference, I have used CITEXT as a type in Postgres and never had an issue. — Dylan Aspden
– Dylan Aspden, Commented Aug 23, 2017 at 17:47

mtkopone · Accepted Answer · 2021-04-10 07:54:16Z

6

Sequelize version 6.5.0+ has support for the TSVECTOR datatype. But there is as of yet no documentation anywhere that I can find, so:

Declare it:

sequelize.define('User', {
  myVector: { type: DataTypes.TSVECTOR },
  ...
})

Populate it:

User.myVector = sequelize.fn('to_tsvector', 'My Content About Travel and Apparently Some Cures')

Use it in a query:

User.findAll({
  where: { 
    myVector: { [Op.match]: sequelize.fn('to_tsquery', 'Travel | Cure') }
  }
})

Explore the pull request for more details: https://github.com/sequelize/sequelize/pull/12955

answered Apr 10, 2021 at 7:54

mtkopone

6,4832 gold badges33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitry Stolbov · Accepted Answer · 2018-03-21 14:35:25Z

2

Try UPDATE news before CREATE INDEX:

ALTER TABLE news ADD COLUMN document tsvector;

UPDATE news SET document = to_tsvector(coalesce(title,'') || ' ' || coalesce(content,''));

CREATE INDEX idx_fts_search ON news USING gin(document);
SELECT title, content FROM news WHERE document @@ to_tsquery('Travel | Cure');

answered Mar 21, 2018 at 14:35

Dmitry Stolbov

2,8272 gold badges27 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to implement PostgresQL tsvector for full-text search using Sequelize?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related