如何利用Sequelize bulkCreate实现批量更新？解决非空约束问题

我们有一个基于Node.js、Sequelize和PostgreSQL的项目，需要优化部分接口性能。代码中存在多处逐行更新数据的场景（每行更新数据不同），即便仅50行也会引发性能问题，因此尝试实现批量更新。

Sequelize.Model的bulkCreate方法支持updateOnDuplicate选项，仅传入已存在的行数据时可当作批量更新使用，比逐行更新高效得多，示例代码如下：

await models.Shipping.bulkCreate(
  shippings,
  {
    fields: ['packageId', 'labelUrl', 'custom'],
    updateOnDuplicate: ['label_url', 'custom']
  }
)

其中shippings是包含行数据的数组，该代码会更新数据库中对应行的label_url和custom字段。

但该方式需传入所有必要的行数据，若仅传入需更新的字段，例如：

shippings = [{
  labelUrl: 'example.com',
  custom: 'test'
}]

当其他字段存在NOT NULL约束时，由于插入数据中缺少这些字段，操作会失败。

请问是否有简洁的方法绕过该问题？或是否存在其他高效的多行差异化数据更新方案？

针对你的场景，有几个简洁高效的方案可以解决批量更新时的非空约束问题，同时保持更新性能：

1. 保留主键+补全必要非空字段值 #

既然bulkCreate需要满足表的非空约束，你可以在构造更新数据数组时，只保留主键（或唯一标识字段，比如packageId）和需要更新的字段，同时为其他非空字段补全它们的现有值或数据库默认值：

• 如果非空字段有数据库默认值，可直接在数据中省略，Sequelize会自动使用默认值；
• 如果没有默认值，建议先批量查询对应行的必要字段值，再合并到更新数据中：

// 1. 批量获取目标行的主键和必要非空字段
const targetRecords = await models.Shipping.findAll({
  attributes: ['packageId', 'requiredNonNullField1', 'requiredNonNullField2'],
  where: { packageId: [/* 需要更新的packageId列表 */] }
});

// 2. 构造符合约束的更新数据，合并现有非空字段与更新内容
const updateData = targetRecords.map(record => {
  const updateFields = yourUpdateList.find(item => item.packageId === record.packageId);
  return {
    packageId: record.packageId,
    requiredNonNullField1: record.requiredNonNullField1,
    requiredNonNullField2: record.requiredNonNullField2,
    ...updateFields // 覆盖要更新的字段
  };
});

// 3. 执行批量更新
await models.Shipping.bulkCreate(updateData, {
  fields: ['packageId', 'requiredNonNullField1', 'requiredNonNullField2', 'labelUrl', 'custom'],
  updateOnDuplicate: ['labelUrl', 'custom']
});

2. 执行PostgreSQL原生批量更新SQL #

PostgreSQL支持在UPDATE中结合VALUES子句实现多行差异化更新，这是性能最优的方案，无需依赖bulkCreate的插入逻辑，直接针对需更新字段操作：

const updateItems = [
  { packageId: 1, labelUrl: 'url1', custom: 'custom1' },
  { packageId: 2, labelUrl: 'url2', custom: 'custom2' }
];

// 构造VALUES子句的占位符和参数
const valuePlaceholders = updateItems.map((_, idx) => 
  `($${idx*3+1}, $${idx*3+2}, $${idx*3+3})`
).join(',');
const queryParams = updateItems.flatMap(item => [item.packageId, item.labelUrl, item.custom]);

// 执行原生SQL更新
await models.sequelize.query(`
  UPDATE "Shipping" 
  SET "labelUrl" = v."labelUrl", "custom" = v."custom"
  FROM (VALUES ${valuePlaceholders}) AS v("packageId", "labelUrl", "custom")
  WHERE "Shipping"."packageId" = v."packageId";
`, { replacements: queryParams });

3. 用upsert结合Promise.all（中小批量场景） #

如果更新行数在几百行以内，upsert方法结合Promise.all是代码最简洁的方案，upsert会自动处理“存在则更新、不存在则插入”的逻辑，且仅需传入主键和更新字段（已存在的非空字段会保留原值）：

await Promise.all(
  updateItems.map(item => 
    models.Shipping.upsert(item, {
      updateOnDuplicate: ['labelUrl', 'custom']
    })
  )
);

注意：该方式本质是并行执行单条更新语句，性能略逊于原生批量SQL，但胜在代码简洁易维护。

内容的提问来源于stack exchange，提问作者Daniel