Postgres DatabaseError [SequelizeDatabaseError]: syntax error at or near "IN"

Question

I have end-point which is supposed to delete record from DB:

  delete: async(roleId, actionId) => {
    const actionrole = await ActionRoleModel.findAll({
      where: {
        roleId: roleId,
        actionId: actionId
      },
    });
    return await actionrole[0].destroy();
  }

That [0] has to be here, because actionrole looks like [{...}].And here is the model:

'use strict';
module.exports = (sequelize, DataTypes) => {
  var ActionRole = sequelize.define('actionroles', {
    actionId: {
      type: "UNIQUEIDENTIFIER",
      field: "actionid"
    },
    roleId: {
      type: "UNIQUEIDENTIFIER",
      field: "roleid"
    },
    createdAt: {
      field: "createdat",
      type: DataTypes.DATE
    },
    updatedAt: {
      field: "updatedat",
      type: DataTypes.DATE
    },
  }, {});
  ActionRole.associate = function(models) {
    // associations can be defined here
  };

  ActionRole.removeAttribute('id');

  return ActionRole;
};

But as an error in terminal I get

DatabaseError [SequelizeDatabaseError]: syntax error at or near "IN"

And here is SQL:

DELETE FROM "actionroles" 
WHERE  IN (
  SELECT  FROM "actionroles" 
  WHERE "roleid" = '53549d62-cd2a-497f-9d1c-1ee1901261ab' AND "actionid" = '6c70bf65-30fd-4640-91d0-8fbda85c4dd5' 
  LIMIT 1)

What's wrong? How can I fix that?

WHERE IN is wrong, expression is missing. The same thing with SELECT FROM. And BTW <expression> IN (<scalar subquery> limit 1) is the same as <expression> = (<scalar subquery> limit 1) — Stefanov.sm
– Stefanov.sm, Commented Aug 26, 2021 at 8:29

dokichan · Accepted Answer · 2021-08-26 08:36:26Z

1

For anyone using Sequelize version 3 and above it looks like:

Model.destroy({
    where: {
        // conditions
    }
})

So, in this case it would be look like this:

return await ActionRoleModel.destroy({
      where: {
        roleId: roleId,
        actionId: actionId
      }
    });

And it works!

answered Aug 26, 2021 at 8:36

dokichan

6535 gold badges23 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres DatabaseError [SequelizeDatabaseError]: syntax error at or near "IN"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related