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