Skip to content

Instantly share code, notes, and snippets.

@phr3nzy

phr3nzy/knexPostgresFullTextSearch.js

Forked from cameronblandford/knexPostgresFullTextSearch.js
Created March 3, 2020 17:00
Show Gist options
  • Save phr3nzy/444080873c842cefd2c88d78daee0b3a to your computer and use it in GitHub Desktop.
Save phr3nzy/444080873c842cefd2c88d78daee0b3a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @cameronblandford cameronblandford renamed this gist Jul 16, 2018. 1 changed file with 0 additions and 0 deletions.
    0 knexFullTextSearch.js → knexPostgresFullTextSearch.js
    View file
    File renamed without changes.
  2. @cameronblandford cameronblandford revised this gist Jul 16, 2018. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion knexFullTextSearch.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,7 @@
    // 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.
    // 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;
  3. @cameronblandford cameronblandford created this gist Jul 16, 2018.
    41 changes: 41 additions & 0 deletions knexFullTextSearch.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    // 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),
    ]);