Proper Methods for Updating Database Records Using Sequelize ORM in Node.js

Problem Background and Common Error Analysis

When developing RESTful APIs with Node.js, Express, and Sequelize, many developers encounter situations where update operations execute INSERT queries instead of UPDATE queries. This typically occurs when incorrectly instantiating new model instances rather than finding existing ones.

Consider the following erroneous example: a developer builds a new Locale model instance and attempts to update it using the updateAttributes method:

const loc = Locales.build()

loc.updateAttributes({
  locale: req.body.name
})

This operation causes Sequelize to execute an INSERT query instead of an UPDATE query because the build() method creates a new instance with no corresponding record in the database.

Correct Update Methods

To properly update existing records, you must first find the target record and then modify and save it. Below are the best practices based on Sequelize:

Using the Find Method to Locate Records

First, use the model's find method to search for existing records based on conditions:

Project.find({ where: { title: 'aProject' } })
  .then(function (project) {
    // Check if record exists
    if (project) {
      // Update the record
      project.update({
        title: 'a very different title now'
      })
      .then(function () {
        // Success handling
      })
    }
  })
  .catch(function (error) {
    // Error handling
  })

Using Modern Promise Syntax

With updates to Sequelize versions, using Promise syntax is recommended over traditional success/error callbacks:

Project.find({ 
  where: { 
    id: req.params.id 
  } 
})
.then(project => {
  if (!project) {
    throw new Error('Record not found')
  }
  
  return project.update({
    locale: req.body.name
  })
})
.then(updatedProject => {
  res.json({
    success: true,
    data: updatedProject
  })
})
.catch(error => {
  res.status(500).json({
    success: false,
    error: error.message
  })
})

Using Async/Await Syntax

For modern JavaScript development, using async/await syntax can make code clearer:

exports.update = async function (req, res) {
  try {
    if (!req.body.name) {
      throw new Error('Data not provided')
    }

    const project = await Project.find({ 
      where: { 
        id: req.params.id 
      } 
    })

    if (!project) {
      return res.status(404).json({
        success: false,
        error: 'Record not found'
      })
    }

    const updatedProject = await project.update({
      locale: req.body.name
    })

    res.json({
      success: true,
      data: updatedProject
    })
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    })
  }
}

Differences Between save() and update() Methods

Sequelize provides two ways to update records: save() and update(), each with different use cases and behavioral characteristics.

Updating with save() Method

The save() method saves all changed attributes on the instance:

const jane = await User.create({ name: 'Jane' })
// The user is currently named "Jane" in the database
jane.name = 'Ada'
// The name is still "Jane" in the database
await jane.save()
// Now their name has been updated to "Ada" in the database

It's important to note that the save() method cannot detect changes to nested objects. For arrays or other nested structures, you should replace the entire object rather than modifying it directly:

// Incorrect: save cannot detect this change
jane.role.push('admin')
await jane.save()

// Correct: replace the entire object
jane.role = [...jane.role, 'admin']
await jane.save()

Updating with update() Method

The update() method only updates specified fields and does not save other changes made to the instance:

const jane = await User.create({ name: 'Jane' })
jane.favoriteColor = 'blue'
await jane.update({ name: 'Ada' })
// The database now has "Ada" for name, but still has the default "green" for favorite color
await jane.save()
// Now the database has "Ada" for name and "blue" for favorite color

Bulk Update Operations

Sequelize also provides a static update method for updating multiple records in bulk:

// Change everyone without a last name to "Doe"
await User.update(
  { lastName: 'Doe' },
  {
    where: {
      lastName: null,
    },
  },
)

Incrementing and Decrementing Numeric Values

To avoid concurrency issues, Sequelize provides increment and decrement instance methods for safely modifying numeric values:

const jane = await User.create({ name: 'Jane', age: 100 })
const incrementResult = await jane.increment('age', { by: 2 })
// To increment by 1, you can omit the by option: user.increment('age')

You can also increment multiple fields simultaneously:

await jane.increment({
  age: 2,
  cash: 100,
})

Best Practices Summary

When updating records with Sequelize, follow these best practices:

1. Always find existing records first and confirm their existence before performing update operations

2. Choose the appropriate update method based on requirements: save() for saving all changes, update() for updating only specified fields

3. For nested objects, use replacement rather than modification to ensure changes are properly detected

4. Use modern Promise or async/await syntax to handle asynchronous operations

5. Implement proper error handling and validation logic

By following these principles, you can avoid common update errors and build more robust and reliable database operation logic.