Comprehensive Guide to Sequelize Update Operations: Retrieving Results and Return Data

Basic Return Mechanism of Update Operations

In Sequelize, the Model.update() method returns a Promise that resolves to an array. The structure of this array depends on the database type and configuration options:

// Basic update operation example
models.People.update(
  { OwnerId: peopleInfo.newuser },
  { where: { id: peopleInfo.scenario.id } }
).then(function(result) {
  // result format: [affectedRowsCount] or [affectedRowsCount, updatedRecords]
  console.log(result);
});

For MySQL databases, the returned array contains only one element, which is the number of affected rows. When an update succeeds but doesn't actually modify any data (e.g., updating with the same values), the returned affected rows count might be 0, which doesn't indicate operation failure.

Retrieving Updated Data Records

To obtain complete updated data records, you need to configure the returning: true option in the update configuration. This option is particularly useful in PostgreSQL, as it supports returning complete updated records:

// Configure returning option to get updated data
db.connections.update({
  user: data.username,
  chatroomID: data.chatroomID
}, {
  where: { socketID: socket.id },
  returning: true,
  plain: true
}).then(function(result) {
  // PostgreSQL: result = [affectedRowsCount, updatedInstance]
  // Other databases: result = [affectedRowsCount]
  if (result[1]) {
    const updatedData = result[1].dataValues;
    console.log('Updated data:', updatedData);
  }
});

The plain: true option simplifies the returned data structure, directly returning the instance object instead of a complex structure containing metadata. This is more practical in most application scenarios.

Comparison Between Instance Updates and Bulk Updates

Sequelize provides two main update approaches: instance-level save() method and model-level update() method.

Instance Update Method:

const jane = await User.create({ name: 'Jane' });
jane.name = 'Ada';
// Save after detecting changes
await jane.save();
// Name in database has been updated to "Ada"

The save() method automatically detects changes to instance attributes but cannot detect changes to nested objects. For modifications to arrays or objects, you need to replace the entire property value:

// Wrong approach: changes cannot be detected
jane.role.push('admin');
await jane.save(); // Changes may not be saved

// Correct approach: replace entire array
jane.role = [...jane.role, 'admin'];
await jane.save(); // Changes will be properly saved

Bulk Update Method:

// Update all users with null last names
await User.update(
  { lastName: 'Doe' },
  {
    where: {
      lastName: null
    }
  }
);

Advanced Update Features

Selective Field Updates:

const jane = await User.create({ name: 'Jane' });
jane.name = 'Jane II';
jane.favoriteColor = 'blue';
// Only save changes to name field
await jane.save({ fields: ['name'] });

Increment and Decrement Operations:

const jane = await User.create({ name: 'Jane', age: 100 });
// Increase age by 2 years
const incrementResult = await jane.increment('age', { by: 2 });

// Multiple field increment
await jane.increment({
  age: 2,
  cash: 100
});

Error Handling and Best Practices

In practical applications, you should properly handle potential exceptions in update operations:

try {
  const result = await models.People.update(
    { OwnerId: peopleInfo.newuser },
    {
      where: { id: peopleInfo.scenario.id },
      returning: true,
      plain: true
    }
  );
  
  if (result[0] === 0) {
    // No records were updated
    console.log('No matching records found or data unchanged');
  } else if (result[1]) {
    // Successfully updated and returned data
    console.log('Update successful:', result[1].dataValues);
  } else {
    // Successfully updated but no data returned (non-PostgreSQL databases)
    console.log('Update successful, affected rows:', result[0]);
  }
} catch (error) {
  console.error('Update operation failed:', error);
  // Log error
  request.server.log(['error'], error.stack);
}

By properly configuring update options and correctly handling return results, you can ensure that your application can accurately determine the success status of update operations and obtain complete updated data when needed.