Attempting to use raw sql in sequelize db, not sure why it is not working.

Here is the controller with the query:

const db = require("../../models");
const Book = db.book;
const User = db.user;
const Sequelize = require('sequelize');
const { sequelize } = require('../../models/index.js');

exports.getAllBooks = async (req, res) => {
    console.log('query: ', req.query);
    let books;
    if (Object.keys(req.query).length === 0) {
        books = await Book.findAll();
        res.json(books);
    } else {
        [books, metadata] = await sequelize.query(`
            SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery(${req.query});
        `);
        res.json(books);
    }
};

get request to

http://localhost:8080/api/books?searchable=humor

the tsvector column in the db is named "searchable"

the log response:

Server is running on port 8080.
query:  { searchable: 'humor' }
Executing (default): SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery([object Object]);
node:internal/process/promises:279
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error
    at Query.run (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/sequelize/lib/dialects/postgres/query.js:50:25)
    at /Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/sequelize/lib/sequelize.js:314:28
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async exports.getAllBooks (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/app/controllers/book.controller.js:16:29) {
  name: 'SequelizeDatabaseError',
  parent: error: syntax error at or near "["
      at Parser.parseErrorMessage (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 90,
    severity: 'ERROR',
    code: '42601',
    detail: undefined,
    hint: undefined,
    position: '83',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'scan.l',
    line: '1180',
    routine: 'scanner_yyerror',
    sql: "SELECT ('title')\n"  
      '            FROM Book\n'  
      '            WHERE searchable @@ to_tsquery([object Object]);',
    parameters: undefined
  },
  original: error: syntax error at or near "["
      at Parser.parseErrorMessage (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/darius/IdeaProjects/node-jwt-auth-postgresql/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 90,
    severity: 'ERROR',
    code: '42601',
    detail: undefined,
    hint: undefined,
    position: '83',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'scan.l',
    line: '1180',
    routine: 'scanner_yyerror',
    sql: "SELECT ('title')\n"  
      '            FROM Book\n'  
      '            WHERE searchable @@ to_tsquery([object Object]);',
    parameters: undefined
  },
  sql: "SELECT ('title')\n"  
    '            FROM Book\n'  
    '            WHERE searchable @@ to_tsquery([object Object]);',
  parameters: {}
}
[nodemon] app crashed - waiting for file changes before starting...

CodePudding user response：

First, you passed the whole query object instead of the searchable prop only.
Second, it's better to use bind option to pass such values safely (remember about SQL injection!).

await sequelize.query(`
            SELECT ('title')
            FROM Book
            WHERE searchable @@ to_tsquery($searchable);
        `, {
   type: QueryTypes.SELECT,
   bind: {
     searchable: req.query.searchable
   }
})