Using `Returning` with Sequelize and Postgres

Returning-in-Sequelize.md

Using Returning with Sequelize and Postgres

Some Sequelize commands don't return anything (or at least not anything useful) by default. Sequelize uses an option called returning to specify which data returns from a commands like .destroy() or update().

Let's look at three common Sequelize commands and see how to handle the data returned by each.

Create

By default, .create() returns the newly created instance. This is convenient, because we can then send data to the user:

Albums.create(myAlbumDataObject)
.then(albumData => res.send(albumData));
.catch(console.error); // your error handler here

Destroy

It's less obvious that .destroy() returns the number of rows that were destroyed. Let's imagine we sent a DELETE request with an HTTP request to destroy a specific album.

Albums.destroy({
  where: { id: req.params.albumId } // destroy the album with an ID specified in the parameters of our HTTP request
})
.then(rowsDestroyed => rowsDestroyed ? res.send(204) : res.send(404)) // If rows were destroyed (not zero), tell the user. Otherwise send an error that nothing was found.
.catch(console.error); // your error handler here

Now we can have a slightly more intelligent response based on whether anything was deleted.

Update

The last and often most confusing is .update().

Albums.update(myAlbumDataObject, { // Update album instance with data sent from request
  where: { id: req.params.albumId } // Make sure we update the proper instance in our database
  returning: true,
});

By default, .update() returns an array with two possible objects. The first is the number of rows affected by the update. It's always included. The second element is an array of the data instances from the rows themselves. You must be using Postgres and have set returning: true for this second data to return.

Although this seems useful, these arrays can be quite annoying in practice. You'll often just want the newly-updated data to send to the user. Sequelize provides a great option for this: plain: true.

It looks like this:

Albums.update(myAlbumDataObject, {
  where: { id: req.params.albumId }
  returning: true,
  plain: true
});

Now, your call to update will return only the updated instance, rather than the usual array(s) with rows affected. If you are updating multiple rows, then you may omit plain and receive the full array of updated instances instead.

Happy modeling!

Thank you so much! You saved my project!

Great post, but I am using sequelize v6 and plain: true is not available, I have checked the documentation of v3, v7 as well, not sure which version is supporting that plain: true option? Thanks.

https://sequelize.org/v6/class/src/model.js~Model.html#static-method-update

@abdurrehmanamjad instead of passing plain: true, do raw: true. Hope it helps.

@kornelijussl This helped! thanks ☺

You can also provide an array for returning, which will return the object with only the provided fields.

Albums.update(myAlbumDataObject, { // Update album instance with data sent from request
  where: { id: req.params.albumId } // Make sure we update the proper instance in our database
  returning: ["id", "name' ],
});

why its not returning the updated record

 const {
        params: { orgId },
      } = req;

      const lookAndFeel = await LookAndFeels.update(
        {
          brandColor: "#D9007C",
          welcomeImageKey: "lookandfeel/default/welcome.png",
          autoPlayTopics: false,
        },
        {
          where: {
            orgId,
          },
          returning: true,
          plain: true,
          transaction,
        }
      );

      await transaction.commit();
      res.status(201).send({
        success: true,
        message: "look and feel reset successfully",
        data: lookAndFeel,
      });