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),
]);

Thank you so much for this gist. It's so helpful.

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

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

Hi. Really diamond in dev ocean. Thank you for this example. I have a few questions.

Why do you use in some columns quotes?

to_tsvector(NEW."displayName" || ' ' || NEW.email || ' ' || NEW.bio || ' ' || NEW."firstName" || ' ' || NEW."lastName")

lowerCamel reason?
Also could you show how to use search queries in knex or objection.js? Could you show some example pls ;) Thank you.

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