Implement full text search using Knex + Objection

knexPostgresFullTextSearch.js

// Because we're using an ORM (Objection), it's a pain to add a tsvector when inserting, 
// since tsvectors and FTS aren't supported by Objection. Instead, I've added a hook that 
// fires on insert which auto-generates the tsvector field for each newly inserted entry. 
// This is an example knex migration file for said behavior.

const addUserIndex = `
ALTER TABLE public.user ADD "document" tsvector;

CREATE FUNCTION my_trigger_function()
RETURNS trigger AS $$
BEGIN
  NEW.document := to_tsvector(NEW."displayName" || ' ' || NEW.email || ' ' || NEW.bio || ' ' || NEW."firstName" || ' ' || NEW."lastName");
  RETURN NEW;
END $$ LANGUAGE 'plpgsql';

CREATE TRIGGER my_trigger
BEFORE INSERT ON public.user
FOR EACH ROW
EXECUTE PROCEDURE my_trigger_function();
CREATE INDEX idx_fts_user ON public.user USING gin(document);
`;

const removeUserIndex = `
  DROP FUNCTION IF EXISTS my_trigger_function();
`;

exports.up = knex => Promise.all([
  knex.schema.createTable('user', (t) => {
    t.increments('id').primary();
    t.string('email').notNullable();
    t.string('displayName').notNullable();
    t.string('firstName');
    t.string('lastName');
    t.text('bio');
    t.timestamps(false, true);
  }).then(() => {
    return knex.schema.raw(addUserIndex);
  })
]);

exports.down = knex => Promise.all([
  knex.schema.dropTableIfExists('user'),
  knex.schema.raw(removeUserIndex),
]);

I'm so glad you've been able to use it!

Yeah. Thanks, I was able to tweak it for my use-case. You really did a great job writing this straight-forward and easy-to-understand gist. I appreciate, man. I had to clearly mention you and as well add a link to this gist in the section of the codebase where I implemented your idea.

thanks a lot , kudos!

Very helpful, thank you.

hi this was very helpful, one thing i found, is i use knex seed, the tsvector col is inserted, but is i use objection with transaction and insertGraph it doesnt trigger the tsvector function, dont know is someone has the same issues

You saved my day, thanks buddy