Postgres Sequelize Invalid value Error: while executing Find Query

educcess database, node.js, Programming, sequelize errors database-errors, Errors, sequelize, sequelize-errors 0 Comments

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&#91;"where"] = {};
const orConditions = &#91;];
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&#91;"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&#91;"where"] = {};
const orConditions = &#91;];
if (noticeParams.noticeTo) {
      orConditions.push({
        noticeTo: {
          &#91;Op.eq]: noticeParams.noticeTo
        }
      });
    }
    if (noticeParams.classId) {
      orConditions.push({
        classs: {
          &#91;Op.eq]: noticeParams.classId
        }
      });
    }
    if (noticeParams.roleId) {
      orConditions.push({
        roleId: {
          &#91;Op.eq]: noticeParams.roleId
        }
      });
      orConditions.push({
        isForAllStaff: {
          &#91;Op.eq]: true
        }
      });
    } else if (noticeParams.isForAllStaff) {
      orConditions.push({
        isForAllStaff: {
          &#91;Op.eq]: noticeParams.isForAllStaff
        }
      });
    }
    query&#91;'where'] = {
      &#91;Op.or]: orConditions
    }</code>