尝试在sequelize db中使用原始sql,不确定为什么不起作用。
下面是带有查询的控制器:
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);
}
};
获取请求至
网址:
DB中的TSVector列被命名为“可搜索的
日志响应:
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...
1条答案
按热度按时间ldioqlga1#
首先,您传递了整个查询对象,而不是仅传递
searchable
prop。其次,最好使用
bind
选项来安全地传递这些值(记住SQL注入!)