sql-server upsert on composite unique index failing maybe because of UID primary key in the model

[spacem]

What are you doing?

var fields = {
  userId: { type: Sequelize.UUID, allowNull: false, unique: 'UQ_Pref_userId_type' },
  type: { type: Sequelize.STRING, allowNull: false, unique: 'UQ_Pref_userId_type' },
  prefs: { type: Sequelize.TEXT, allowNull: false },
  uuid: { type: SequelizeStatic.UUID, primaryKey: true, defaultValue: SequelizeStatic.UUIDV1 }
}

var model = sequelize.define('prefs', fields).sync();
model.upsert({ userId: userId, type: type, prefs: 'test' });

What do you expect to happen?

Upsert should update existing value not try to insert new value

What is actually happening?

SequelizeUniqueConstraintError: Validation error
at Query.formatError (c:\work\project1\node_modules\sequelize\lib\dialects\mssql\query.js:304:14)

The sql below is matching on the PK not the constraint. I believe this is because of the default UUID value in the model

Output, either JSON or SQL

MERGE INTO [Prefs] WITH(HOLDLOCK) AS [Prefs_target]
USING (VALUES(N'690a83fab3f88451c0ea2922', N'hierarchy-expand',N'test', N'696beb00-c9a0-11e7-acdb-816c33e54a12', 0, '2017-11-15 01:00:45.877 +00:00', '2017-11-15 01:00:45.877 +00:00'))
AS [Prefs_source]([userId], [type], [prefs], [uuid], [isDeleted], [createdAt], [updatedAt])
ON [Prefs_target].[uuid] = [Prefs_source].[uuid]
WHEN MATCHED THEN UPDATE SET
[Prefs_target].[userId] = N'690a83fab3f88451c0ea2922',
[Prefs_target].[type] = N'hierarchy-expand',
[Prefs_target].[prefs] = N'test',
[Prefs_target].[updatedAt] = '2017-11-15 01:00:45.877 +00:00'
WHEN NOT MATCHED THEN INSERT
([userId], [type], [prefs], [uuid], [isDeleted], [createdAt], [updatedAt])
VALUES(N'690a83fab3f88451c0ea2922', N'hierarchy-expand', N'test', N'696beb00-c9a0-11e7-acdb-816c33e54a12', 0, '2017-11-15 01:00:45.877 +00:00', '2017-11-15 01:00:45.877 +00:00')
OUTPUT $action, INSERTED.*;

Dialect: mssql
Dialect version: ?
Database version: ?
Sequelize version: 4.22.6
Tested with master branch: ?

[sushantdhiman]

Duplicate #8552

[sushantdhiman]

For MSSQL we are still using MERGE WITH WHERE statement combination. Which is still not fixed by #12301