Sequelize upsert在复合唯一键场景下失效问题的排查与解决

背景信息 #

数据库表结构 #

create table if not exists "Service" (
    _id uuid not null primary key,
    service text not null,
    "count" integer not null,
    "date" timestamp with time zone,
    team uuid,
    organisation uuid,
    "createdAt" timestamp with time zone not null,
    "updatedAt" timestamp with time zone not null,
    unique (service, "date", organisation),
    foreign key ("team") references "Team"("_id"),
    foreign key ("organisation") references "Organisation"("_id")
);

执行的Upsert代码 #

Service.upsert({ team, date, service, organisation, count }, { returning: true })

触发的错误 #

error: duplicate key value violates unique constraint "Service_service_date_organisation_key"
Key (service, date, organisation)= (xxx, 2022-12-30 01:00:00+01, 12345678-5f63-1bc6-3924-517713f97cc3) already exists.

预期行为（基于Sequelize文档） #

Postgres用户注意：如果Upsert的负载包含主键字段，则会使用主键作为冲突目标。否则会选择第一个唯一约束作为冲突键。

排查方向 #

1. 验证模型的复合唯一约束定义：检查Sequelize的Service模型是否正确声明了service、date、organisation的复合唯一约束。如果模型里仅定义了单个字段的唯一约束，Sequelize无法自动识别表级的复合约束作为冲突目标。
2. 检查日期精度差异：PostgreSQL的timestamp with time zone会保留毫秒级精度，若传入的date值与数据库中存储的值存在细微精度差（比如毫秒不同），会被判定为不同键，触发唯一约束冲突。
3. 查看生成的SQL语句：开启Sequelize的日志功能（设置logging: console.log），查看实际执行的Upsert SQL，确认是否正确使用了ON CONFLICT (service, "date", organisation)子句。如果SQL中用主键作为冲突目标，说明Sequelize未识别到复合唯一约束。

解决方法 #

1. 显式指定冲突字段 #

在Upsert选项中通过conflictFields强制指定复合唯一键，确保Sequelize使用该约束处理冲突：

Service.upsert(
  { team, date, service, organisation, count },
  { 
    returning: true,
    conflictFields: ['service', 'date', 'organisation']
  }
);

2. 修正模型的复合唯一约束定义 #

确保Service模型中正确配置复合唯一索引，让Sequelize能自动识别该约束：

const Service = sequelize.define('Service', {
  _id: {
    type: DataTypes.UUID,
    primaryKey: true,
    defaultValue: DataTypes.UUIDV4
  },
  service: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  count: {
    type: DataTypes.INTEGER,
    allowNull: false
  },
  date: DataTypes.DATE,
  team: DataTypes.UUID,
  organisation: DataTypes.UUID,
  createdAt: {
    type: DataTypes.DATE,
    allowNull: false
  },
  updatedAt: {
    type: DataTypes.DATE,
    allowNull: false
  }
}, {
  indexes: [
    {
      unique: true,
      fields: ['service', 'date', 'organisation']
    }
  ]
});

3. 统一日期精度 #

如果是日期精度导致的冲突，在传入Upsert前对date进行截断处理，消除毫秒级差异：

// 截断到小时，也可根据需求截断到分钟/秒
const normalizedDate = new Date(date);
normalizedDate.setMinutes(0, 0, 0);

Service.upsert(
  { team, date: normalizedDate, service, organisation, count },
  { returning: true }
);

内容的提问来源于stack exchange，提问作者rap-2-h