Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save mattboutet/26e3ef111fae8da6fb9c33301f7a8df5 to your computer and use it in GitHub Desktop.
Save mattboutet/26e3ef111fae8da6fb9c33301f7a8df5 to your computer and use it in GitHub Desktop.
Implement full text search using Knex + Objection
// 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),
]);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment