Sequelize Update Returning Before and After Results

Question

I'm trying to create an Update API route using Sequelize that will:

1. Capture the record before the update (beforeRec)
2. Perform the update
3. Capture the updated record (updatedRec)
4. Return both the beforeRec and updatedRec

I'm having trouble with my promise chain, which is executing the before and after select queries before executing the update. I've tried several different ways of chaining and capturing results, but here's the latest code:

router.put('/:id', (req, res) => {
  const pk = req.params.id;

  const getBeforeRec = Master.findByPk(pk)
    .then(rec => {return rec})

  const updateRec = getBeforeRec
    .then(
      Master.update(
        req.body,
        { where: {id: pk} }
      )  
    )

  const getUpdatedRec = updateRec
    .then(
      Master.findByPk(pk)
        .then(rec => {return rec})
    );

  return Promise.all([getBeforeRec, updateRec, getUpdatedRec])
    .then( ([beforeRec, updateRes, afterRec]) => {
      return res.json({beforeRec, afterRec})
    })
    .catch(err => {
      return res.status(400).json({'error': err});
    });
});

Here's a sanitized example of how the results look:

{
    "beforeRec": {
        "id": 100,
        "updated_col_name": false,
    },
    "afterRec": {
        "id": 100,
        "updated_col_name": false,
    }
}

In the console, I can see that the update is executing last:

Executing (default): SELECT [id], [updated_col_name] FROM [master] WHERE [master].[id] = N'100';
Executing (default): SELECT [id], [updated_col_name] FROM [master] WHERE [master].[id] = N'100';
Executing (default): UPDATE [master] SET [updated_col_name]=1 WHERE [id] = N'106'

What's the best way to make the second select statement wait for the update?

Any help in clarifying how to chain promises while capturing results along the way will be greatly appreciated! Thanks.

Answer 1

Resurrecting an old question to help people in the future...

I've been using sequelize v6 with MySQL. I can't speak to other variances but assuming you just want the snapshot of the "previous" values, you can use the following method to create a copy the properties and their values before updating them

// then catch method
router.put('/:id', (req, res) => {
  const pk = req.params.id;

  let beforeRecord;
  const updateRec = Master.findByPk(pk).then(rec => {
    // .get() method is synchronous
    beforeRecord = rec.get({ plain: true });
    // calling .update on the model instance will also
    // call .reload on the instance as well.
    // Same thing happens when calling .save on the instance
    return rec.update(req.body);
  });

  updateRec.then(rec => {
    const afterRec = rec.get({ plain: true });
    return res.json({beforeRec, afterRec})
  }).catch(err => {
    return res.status(400).json({'error': err});
  });

});

// Async await method
router.put('/:id', async (req, res) => {
  const pk = req.params.id;

  try {
    /** @type{import('sequelize').Model} */ // rec equals a sequelize model instance
    const rec = await Master.findByPk(pk)
    // .get() method is synchronous and returns an object (NOT a sequelize model instance)
    const beforeRecord = rec.get({ plain: true });
    // calling .update on the model instance will also
    // call .reload on the instance as well.
    // Same thing happens when calling .save on the instance
    await rec.update(req.body); // after this call, rec contains the new updated values

    const afterRec = rec.get({ plain: true });
    return res.json({beforeRec, afterRec})
  } catch (err) {
    return res.status(400).json({'error': err});
  }

});

Answer 2

You can do that with , previous method of the instance that returned by update query :

Master.update( req.body , { where: {id: pk} }).then(master => {
    console.log(master.get());          // <---- Will give you latest values
    console.log(master.previous());     // <---- returns the previous values for all values which have changed
})  

For More Detail :

http://docs.sequelizejs.com/class/lib/model.js~Model.html#instance-method-previous

https://github.com/sequelize/sequelize/issues/1814

Answer 3

After trying a number of ways, it finally works with nesting:

router.put('/:id', (req, res) => {
  const pk = req.params.id;
  let beforeRec;

  Master.findByPk(pk)
    .then(rec => { beforeRec = rec; })
    .then(() => {
      Master.update(
        req.body,
        { where: {id: pk} }
      )
      .then(() => {
        Master.findByPk(pk)
          .then(rec => { return rec; })
          .then((afterRec) => {
            return res.json({beforeRec, afterRec})
          })
      })
    })  
    .catch(err => {
      return res.status(400).json({'error': err});
    });
});

If I don't nest the second Master.findByPk, then Master.update() ends up executing last. Also, while I can set beforeRec outside of the promise chain, it didn't work for afterRec.

I don't love it, since I'm still confused by promises, but it's returning the desired results. However, with this nesting mess, I'm not sure where the catch() belongs. Will it catch errors within the nested then()s? Only further testing will tell.

Answer 4

Give this a shot:

router.put('/:id', (req, res) => {
  const pk = req.params.id;
  let beforeRec, afterRec;

  Master.findByPk(pk)
    .then(rec => { beforeRec = rec; })
    .then(() => {
      Master.update(
        req.body,
        { where: {id: pk} }
      )
    })
    .then(() => {
      Master.findByPk(pk)
        .then(rec => { afterRec = rec; })
    })
    .then(() => {
      res.json({beforeRec, afterRec})
    })
    .catch(errror => {
      res.status(400).json({error});
    });
});