How to update table row data with Sequelize - Code example included

Sequelize provide several methods that you can use to update your table row.

Depending on how you want the update to be performed, here are those methods:

• Model.update() - Performs an update to existing rows
• Model.upsert() - Update one matching row or create a new row when no matching row exists
• Model.set() or reassing new value to existing properties to an instance of your model

This tutorial will help you learn the methods mentioned above. Let’s start with the update() method.

The Model.update() method

The Sequelize Model.update() method will update your table rows with the values you passed as its first argument.

The syntax of the method is as shown below:

Model.update(
  {
    // values
  },
  {
    // options
  }
);

The method accepts two parameters:

• The first is an object containing the column names and values you want to update.
• The second parameter is the options to modify the behavior of the generated statement.

Let’s see an example of the update() method in action. Suppose you have a Users table with the following data:

+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  1 | Nathan    | NULL     |
|  2 | John      | NULL     |
|  3 | Jane      | NULL     |
+----+-----------+----------+

First, create a model of the table named User:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  timestamps: false,
});

Then, call the update() method from the created model as follows:

const updatedRows = await User.update(
  {
    lastName: "Sebhastian",
  },
  {
    where: { lastName: null },
  }
);
console.log(updatedRows); // [ 3 ]

When you run the above code, the values of the Users table should be as shown below:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | Sebhastian |
|  3 | Jane      | Sebhastian |
+----+-----------+------------+

The update method returns an array of one or two elements. The first element is the number of affected rows (3 in case of the example above.)

The second element is the data of the affected rows. It’s only returned when you use PostgreSQL and set the returning option to true.

If you don’t need the return value, you can omit capturing it:

await User.update(
  {
    lastName: "Sebhastian",
  },
  {
    where: { lastName: null },
  }
);

// your next code ...

The update() method requires you to pass the where option, or it will throw an AssertionError as shown below:

AssertionError [ERR_ASSERTION]: Missing where attribute in the options parameter

And that’s how the update() method works.

The Model.upsert() method

The Model.upsert() method is a new method added in Sequelize v6 that allows you to perform an update statement only when a row with matching values already exist.

To update a row, you need to specify the primary key of the row, which is the id column in case of the Users table.

await User.upsert({
  id: 3,
  lastName: "Doe",
});

Because you already have a row with the id value of 3, the above code will UPDATE the existing row:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | Sebhastian |
|  2 | John      | Sebhastian |
|  3 | Jane      | Doe        |
+----+-----------+------------+

For more information on the upsert() method, you can visit my other tutorial:

Sequelize upsert method - Tutorial with example JS code

Let’s learn about the set() method next.

The Model.instance.set() method

The set() method allows you to update the values of a Model instance. Instead of calling the method from the Model directly, you need to call it from an instance of your Model.

The example below shows how to use the set() method:

let user = await User.create({
  firstName: "Mary",
  lastName: "Elizabeth",
});

user.set({
  firstName: "Sarah",
  lastName: "Jackson",
});

user = await user.save();
console.log(user.firstName); // Sarah
console.log(user.lastName); // Jackson

First, you get an instance of the Model by calling the create() or findOne() method.

Once you have the instance, you call the set() method to change the values of the instance.

Then, you need to call the save() method on the instance to persist the changes to your table row.

You can also modify the instance property directly instead of using the set() method as shown below:

const user = await User.findOne({ where: { firstName: "Sarah" } });

if (user) {
  user.firstName = "Silvana";
  user.lastName = "Gomez";

  await user.save();
} else {
  console.log("User not found");
}

The set() method may be useful when you have a specific case where you need to execute an update only when a matching row is found.

You can also use the update() method and check on the returned values to see if an update is executed or not:

const [updatedRows] = await User.update(
  {
    lastName: "Sebhastian",
  },
  {
    where: { lastName: null },
  }
);

if (updatedRows) {
  console.log(`Updated rows: ${updatedRows}`);
} else {
  console.log("User not found");
}

Still, the update() method might update more than a single row.

By using the findOne() and set() methods, you ensure that only a single row is updated.

A forgot password process is one example where you may use the set() method.

When a forgot password request is sent by the user with his/ her email, you check if there’s a matching row with the email value.

When there’s a matching row, send an email to the user’s email on how to reset the password. You may also update some values in the row like the forgot password request expiration time.

Anyway, now you’ve learned how to perform an UPDATE statement using Sequelize. Nice work! 👍