Skip to content

Instantly share code, notes, and snippets.

@mathisonian
Last active July 25, 2023 21:42
Show Gist options
  • Save mathisonian/8074838 to your computer and use it in GitHub Desktop.
Save mathisonian/8074838 to your computer and use it in GitHub Desktop.
postgres full text search in sequelize.js. see this blog post for more information http://www.mathisonian.com/weblog/postgres-full-text-search-with-sequelizejs
var Sequelize = require('sequelize');
module.exports = function(config) {
var models = {};
sequelize = new Sequelize(config.database, config.username, config.password, config.options);
// Bootstrap models
fs.readdirSync(__dirname).forEach(function (file) {
if (~file.indexOf('.js') && file.indexOf('index.js') < 0) {
var model = sequelize.import(file);
console.log(model.name);
models[model.name] = model;
}
});
sequelize.sync().done(function() {
models.Post.addFullTextIndex();
});
}
'use strict';
module.exports = function(sequelize, DataTypes) {
return sequelize.define('Post', {
'title': { type: DataTypes.STRING, required: true },
'content': { type: DataTypes.TEXT, required: true },
'slug': { type: DataTypes.STRING, required: true }
}, {
classMethods: {
getSearchVector: function() {
return 'PostText';
},
addFullTextIndex: function() {
if(sequelize.options.dialect !== 'postgres') {
console.log('Not creating search index, must be using POSTGRES to do this');
return;
}
var searchFields = ['title', 'content'];
var Post = this;
var vectorName = Post.getSearchVector();
sequelize
.query('ALTER TABLE "' + Post.tableName + '" ADD COLUMN "' + vectorName + '" TSVECTOR')
.success(function() {
return sequelize
.query('UPDATE "' + Post.tableName + '" SET "' + vectorName + '" = to_tsvector(\'english\', ' + searchFields.join(' || \' \' || ') + ')')
.error(console.log);
}).success(function() {
return sequelize
.query('CREATE INDEX post_search_idx ON "' + Post.tableName + '" USING gin("' + vectorName + '");')
.error(console.log);
}).success(function() {
return sequelize
.query('CREATE TRIGGER post_vector_update BEFORE INSERT OR UPDATE ON "' + Post.tableName + '" FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger("' + vectorName + '", \'pg_catalog.english\', ' + searchFields.join(', ') + ')')
.error(console.log);
}).error(console.log);
},
search: function(query) {
if(sequelize.options.dialect !== 'postgres') {
console.log('Search is only implemented on POSTGRES database');
return;
}
var Post = this;
query = sequelize.getQueryInterface().escape(query);
console.log(query);
return sequelize
.query('SELECT * FROM "' + Post.tableName + '" WHERE "' + Post.getSearchVector() + '" @@ plainto_tsquery(\'english\', ' + query + ')', Post);
}
}
});
};
@hobberwickey
Copy link

Here's an updated migration version for adding the fields / indexes and that works with newer versions of Seqelize

'use strict';

module.exports = {
  up: function (queryInterface, Sequelize) {
    var sequelize = queryInterface.sequelize,
        searchFields = ['name', 'description'],
        vectorName = "[column_name]",
        tableName = "[table_name]";

    return sequelize
      .query('ALTER TABLE "' + tableName + '" ADD COLUMN "' + vectorName + '" TSVECTOR')
      .then(function() {
        console.log("Column added: Adding updating values")
        return sequelize
                .query('UPDATE "' + tableName + '" SET "' + vectorName + '" = to_tsvector(\'english\', ' + searchFields.join(' || \' \' || ') + ')')
                .catch(console.log);
      }).then(function() {
        console.log("Values added: Creating Index")
        return sequelize
                .query('CREATE INDEX np_search_idx ON "' + tableName + '" USING gin("' + vectorName + '");')
                .catch(console.log);
      }).then(function() {
        console.log("Index created: Adding trigger");
        return sequelize
                .query('CREATE TRIGGER np_vector_update BEFORE INSERT OR UPDATE ON "' + tableName + '" FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger("' + vectorName + '", \'pg_catalog.english\', ' + searchFields.join(', ') + ')')
                .catch(console.log);
      }).then(function() {
        console.log("Everything worked!")
      }).catch(console.log);
  },

  down: function (queryInterface, Sequelize) {
    var sequelize = queryInterface.sequelize,
        searchFields = ['name', 'description'],
        vectorName = "[column_name]",
        tableName = "[table_name]";
        
    return sequelize
      .query('DROP TRIGGER np_vector_update ON "' + tableName + '"')
      .then(function(){
        console.log("removed trigger")
        return sequelize
                .query("DROP INDEX np_search_idx")
                .catch(console.log)
      }).then(function(){
        console.log("removed index")
        return sequelize
                .query('ALTER TABLE "' + tableName + '" DROP COLUMN "' + vectorName + '"')
                .catch(console.log)
      }).then(function(){
        console.log("removed column")
      }).catch(console.log)
  }
};

@slidenerd
Copy link

@hobberwickey interesting, you added the tsvector column to migration, what did you add in your model files, I cannot find anything called DataType.TS_VECTOR or DataType.DOCUMENT for Sequelize

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment