Search for a string with spaces in sequelize for postgres

Question

I am looking for a way to search for a string with spaces in postgres using sequelize.

I have a database named library with a books table. This has a title and author column in there.I have tried using iLike but this wasn't very fruitful, the search is returning nothing once I include a space.

So I used this article and this too and came up with the following:

// migration file - seems to work fine
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    var sequelize = queryInterface.sequelize;
    var searchFields = ["title", "author"];
    var vectorName = "vector";
    var tableName = "books";

    await sequelize
      .query(`ALTER TABLE ${tableName} ADD COLUMN ${vectorName} TSVECTOR;`)
      .then(function () {
        console.log("Column added: Adding updating values");
        return sequelize
          .query(
            `UPDATE ${tableName} SET ${vectorName} = to_tsvector('english', ${searchFields.join(
              " || ' ' || "
            )});`
          )
          .catch(console.log);
      })
      .then(function () {
        console.log("Values added: Creating Index");
        return sequelize
          .query(
            `CREATE INDEX ${tableName}_search ON ${tableName} USING gin(${vectorName});`
          )
          .catch(console.log);
      })
      .then(function () {
        console.log("Index created: Adding trigger");
        return sequelize
          .query(
            `CREATE TRIGGER ${tableName}_vector_update
            BEFORE INSERT OR UPDATE ON ${tableName}
            FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(${vectorName}, 'pg_catalog.english', ${searchFields.join(
              ", "
            )});`
          )
          .catch(console.log);
      })
      .then(function () {
        console.log("Everything worked!");
      })
      .catch(console.log);
  },

  async down(queryInterface, Sequelize) {
    var sequelize = queryInterface.sequelize;
    var searchFields = ["title", "author"];
    var vectorName = "vector";
    var tableName = "books";

    await sequelize
      .query(`DROP TRIGGER ${tableName}_vector_update ON ${tableName};`)
      .then(function () {
        console.log("removed trigger");
        return sequelize
          .query(`DROP INDEX ${tableName}_search;`)
          .catch(console.log);
      })
      .then(function () {
        console.log("removed index");
        return sequelize
          .query(`ALTER TABLE ${tableName} DROP COLUMN ${vectorName};`)
          .catch(console.log);
      })
      .then(function () {
        console.log("removed column");
      })
      .catch(console.log);
  },
};

The books table is created, seeds and migration runs ok and populate the vector column. To make the query I initially had, when using iLike:

exports.Suggestbooks = class Suggestbooks {
  constructor(options, app) {
    this.app = app;
    this.options = options || {};
  }

  async find(data, params) {
      const query = data?.query
      
      const results = await this.app.service("books").find({
         query: {
          ...query,
          $sort: {
            updatedAt: -1,
          },
        },      
      })
      .catch((err) => {
          console.log({hj4jkl6j5lll4: err})
      });

      return results;
   
  }
};

So now how do I make the query?

Answer 1

To anyone curious, here's how I finally solved this

const Sequelize = require("sequelize");

/* eslint-disable no-unused-vars */
exports.Suggestbooks = class Suggestbooks {
  constructor(options, app) {
    this.app = app;
    this.options = options || {};
  }

  async find(data, params) {
    const query = data?.query?.title;

    const sequelizeClient = this.app.get("sequelizeClient");

    return new Promise(async (resolve, reject) => {
      await sequelizeClient
        .query(`SELECT * FROM books WHERE vector @@ to_tsquery('${query}:*')`)
        .then(async (res) => {
          const newMap = await res[0]?.reduce(async (init, curr) => {
            let newArray = await init;
 
            const books = await this.app
              .service("books")
              .find({
                query: {
                  id: curr.id,
                  deleted: false,
                  allowed: true,
                  $sort: {
                    updatedAt: -1,
                  },
                },
              })
              .catch((err) => {
                console.log({ hj4jkl6j5lll4: err });
              });

            if (!!books) newArray.push(books.data[0]);

            return init;
          }, Promise.resolve([]));

          resolve(newMap);
        });
    });
  }
};