Postgres Sequelize Invalid value Error: while executing Find Query
I have a Model Defined as
<code>const sequelizeHelper = require("../helpers/sequelizeHelper"); const db = sequelizeHelper.db; const sqlOperator = sequelizeHelper.sqlOperator; const NoticesModel = db.define( "notices", { noticeId: { field: "noticeId", type: sqlOperator.UUID, primaryKey: true, defaultValue: sqlOperator.UUIDV4 }, title : { field: "title", type: sqlOperator.STRING(250), }, notice : { field: "notice", type: sqlOperator.TEXT, }, isBroadcast : { field : "isBroadcast", type: sqlOperator.BOOLEAN, defaultValue: false }, isDeleted : { field : "isDeleted", type: sqlOperator.BOOLEAN, defaultValue: false }, isForAllStaff : { field : "isForAllStaff", type: sqlOperator.BOOLEAN, defaultValue: false } }, { tableName: "notices", timestamps: true } ); NoticesModel.associate=()=>{ const UserModel = require("./UserModel"); NoticesModel.belongsTo(UserModel, { foreignKey : { name : "noticeBy", allowNull: false }, // as : "noticeBy", //This is needed as there are multiple user mapping so sequelize will not get to know which one to use targetKey: "userId" }); NoticesModel.belongsTo(UserModel, { foreignKey: "noticeTo", // as: "noticeTo", //This is needed as there are multiple user mapping so sequelize will not get to know which one to use targetKey: "userId" }); const MasterNoticesStateModel = require("./MasterNoticesStateModel"); NoticesModel.belongsTo(MasterNoticesStateModel,{ foreignKey : { name : "noticeState", allowNull: false }, targetKey : "stateId" }); const MasterClassModel = require("./MasterClassModel"); NoticesModel.belongsTo(MasterClassModel,{ foreignKey: "classs", targetKey: "classId" }); //Associating Notice to school const SchoolModel = require("./SchoolModel"); NoticesModel.belongsTo(SchoolModel,{ foreignKey: "schoolId", targetKey: "schoolId" }); const MasterUserRolesModel = require("./MasterUserRolesModel"); NoticesModel.belongsTo(MasterUserRolesModel,{ foreignKey: "roleId", targetKey: "roleId" }); NoticesModel.sync({ alter: true }); }; module.exports = NoticesModel;</code>
And a Sequelize $or conditioned Query I have is as follows
<code>const query["where"] = {}; const orConditions = []; if (noticeParams.noticeTo) { orConditions.push({ noticeTo: { $eq: noticeParams.noticeTo } }); } if (noticeParams.classId) { orConditions.push({ classs: { $eq: noticeParams.classId } }); } if (noticeParams.roleId) { orConditions.push({ roleId: { $eq: noticeParams.roleId } }); orConditions.push({ isForAllStaff: { $eq: true } }); } else if (noticeParams.isForAllStaff) { orConditions.push({ isForAllStaff: { $eq: noticeParams.isForAllStaff } }); } query["where"] = { $or: orConditions }</code>
Postgres Sequelize Exception while executing Find Query
Error: Invalid value { noticeTo: { ‘$eq’: ’02faac43-a777-4bb8-902b-da0eebbcd70e’ } }
at Object.escape (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/sql-string.js:65:11)
at PostgresQueryGenerator.escape (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:986:22)
at _joinKeyValue.value.map.item (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2486:69)
at Array.map ()
at PostgresQueryGenerator._whereParseSingleValueObject (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2486:52)
at PostgresQueryGenerator.whereItemQuery (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2268:19)
at Utils.getComplexKeys.forEach.prop (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2173:25)
at Array.forEach ()
at PostgresQueryGenerator.whereItemsQuery (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2171:35)
at PostgresQueryGenerator.getWhereConditions (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2583:19)
at PostgresQueryGenerator.selectQuery (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1315:28)
at QueryInterface.select (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/query-interface.js:1122:27)
at Promise.try.then.then.then (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/model.js:1759:34)
at tryCatcher (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:547:31)
at Promise._settlePromise (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:604:18)
at Promise._settlePromise0 (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:649:10)
at Promise._settlePromises (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:729:18)
at _drainQueueStep (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/async.js:93:12)
at _drainQueue (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/async.js:86:9)
at Async._drainQueues (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/async.js:102:5)
at Immediate.Async.drainQueues as _onImmediate
at runCallback (timers.js:705:18)
at tryOnImmediate (timers.js:676:5)
at processImmediate (timers.js:658:5)
at process.topLevelDomainCallback (domain.js:126:23)
Solution for Postgres Sequelize Invalid value Error:
Very strange is the query written above is as per the standards of sequelize library, however its throwing a strange exception. I fixed by changing the query as follows
NOTE: instead of using $or and $eq, I am using sequelize op ( operator )
Update: As per latest sequelize library v4.x, For better security Sequelize recommends dropping alias operators $ (e.g $and, $or …) – Reference
<code>const Op = require('sequelize').Op const query["where"] = {}; const orConditions = []; if (noticeParams.noticeTo) { orConditions.push({ noticeTo: { [Op.eq]: noticeParams.noticeTo } }); } if (noticeParams.classId) { orConditions.push({ classs: { [Op.eq]: noticeParams.classId } }); } if (noticeParams.roleId) { orConditions.push({ roleId: { [Op.eq]: noticeParams.roleId } }); orConditions.push({ isForAllStaff: { [Op.eq]: true } }); } else if (noticeParams.isForAllStaff) { orConditions.push({ isForAllStaff: { [Op.eq]: noticeParams.isForAllStaff } }); } query['where'] = { [Op.or]: orConditions }</code>