In order to use Postgres' built in full-text search functionality with the Sequelize.js ORM there are just a few things that need to be done:
- Add a TSVector as a column on the relevant model
- Add a text search index using the vector that was just created
- Update the vector whenever the model is changed
- Add a 'search' method to our model
This can be done with a fairly small amount of code. If you just want to skip to the end result, check out this gist.
Adding the Vector
Let's take a blog post as an example. With sequelize, traditionally one would create a file called Post.js, and it would look something like this:
We want to add the vector using sequelize's raw `query` method, but in order to make this repeatable, we can wrap this in a class method on the Post object.
The Postgres SQL syntax would look like
allowing us to put the vector on as many fields as we would like. Translated to sequelize syntax, we get something like
Adding the Index
To add the index we'll just extend the function that we used to create the vector, trying to execute SQL that looks like
Converted to sequelize, the completed function looks like
We will want to run this function after sequelize has synced all the tables in the database.
Adding an accessor method
We want to be able to conveniently search against this index, so to keep the syntax like the rest of sequelize, I suggest adding another classMethod called search
that can then be called like
which should look familiar to sequelize users.
Putting it all together
I've added a github gist that ties this all together, showing how the Post model should look, and how one would create the index after sequelize has synced itself