How to delete associated rows with sequelize

Question

I am trying to delete rows associated with a row in a table, without deleting the main row (thus can't use CASCADE).

This is the raw PostgreSQL query that does what I want to achieve with SQL. Is sequelize able to generate such query:

DELETE FROM session USING user WHERE session.user_id = user.id AND user.username = 'bob'

The model is (not including irrelevant columns):

create table user (
  id uuid primary key default uuid_generate_v4(),
  username text UNIQUE NOT NULL
);

create table session (
  id uuid primary key default uuid_generate_v4(),
  user_id uuid NOT NULL references user(id) ON DELETE CASCADE
);

The association is defined in sequelize as:

Session.belongsTo(models.User, {
  foreignKey: "user_id"
});

User.hasMany(models.Session, {
  foreignKey: "user_id"
});

An alternative version of the query could be:

DELETE FROM session WHERE session.user_id = (SELECT user_id FROM user WHERE username = 'bob');

But I think sequelize doesn't handle subqueries yet?

I tried something along the lines:

return Session.destroy({
  include: [
    { model: User, where: { username: 'bob' }}
  ],
  truncate: false
});

However, sequelize complains:

Error: Missing where or truncate attribute in the options parameter of model.destroy.

Manu Manu · Accepted Answer · 2021-03-24T14:35:08

If anyone gets here, this is how I "delete associated rows with sequelize": little help from the library:

Read the user from db (Users.findOne({ ... }))
call the method setSessions(array) provided by sequelize (the name depends on your model) which returns a promise.

/** @param {string} username */
const clearUserSessions = async (username) {
  const userInstance = await Users.findOne({ 
    where: { username }, 
    include: ['sessions'] 
   })
   if (!userInstance) {
    /* user not found */
    return ...
   }
   await userInstance.setSessions([])
   /* removed every session from user */
   return ...
};

later:

try {
  await clearUserSessions('bob')
} catch(err) {
 ...
}

How to delete associated rows with sequelize

2 Answers