Unable to insert/upsert record in MS SQL DB when a composite key has a falsey value

[PrakharAkgec]

Issue Description

While inserting a record in MS SQL DB using sequelize, if the table has a group of Primary Keys (Composite key), and the record to be inserted/upserted has a "falsey" value (false/0), then the record is not inserted/upserted.

What are you doing?

I was using sequelize for inserting/ upserting data in MS SQL DB, in which there was a composite key column that has a falsy value (eg. false/0).

It was throwing an error:

Error: Primary Key or Unique key should be passed to upsert query

Here is the link to the SSCCE for this issue: LINK-HERE

//model
module.exports = (sequelize, DataTypes) => {
    var Table_Test = sequelize.define("test_table", {
        Name: {
            type: DataTypes.STRING,
            primaryKey: true
        },
        Age: {
            type: DataTypes.INTEGER
        },
        IsOnline: {
            type: DataTypes.BOOLEAN,
            primaryKey: true
        }
    }, {
        freezeTableName: true,
        timestamps : false
    });
    return Table_Test;
};

//query
async function saveMessage(dbObj, schema) {
    return db[schema].upsert(dbObj)
        .then(() => {
            console.log(`:::::successfully saved data to mssql:::::`);
            return true;
        })
        .catch((err) => {
            return Promise.reject(err.parent);
        });
}

saveMessage({
    "Name": "Charlie",
    "Age": 24,
    "IsOnline": false
});

What do you expect to happen?

The record must be upserted in DB.

Output: :::::successfully saved data to mssql:::::

What is actually happening?

An error is returned stating, we can not skip the value for a primary key.

Returning this error:

{ ERROR: Error: Primary Key or Unique key should be passed to upsert query
    at MSSQLQueryGenerator.upsertQuery (/home/abc/node_modules/sequelize/lib/dialects/mssql/query-generator.js:462:13)
    ...
}

Additional context

Add any other context or screenshots about the feature request here.

Environment

• Sequelize version: 5.15.0
• Node.js version: v8.11.1
• Operating System: Linux
• If TypeScript related: TypeScript version: XXX

Issue Template Checklist

How does this problem relate to dialects?

• I think this problem happens regardless of the dialect.
• I think this problem happens only for the following dialect(s): MSSQL
• I don't know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[papb]

Hello, thanks for the report.

Yes, I have the time and I know how to start.

Are you still up for this? Let me know, thanks

[PrakharAkgec]

Hello, thanks for the report.

Yes, I have the time and I know how to start.

Are you still up for this? Let me know, thanks

Yes, I am.... Would love to contribute....

[papb]

Thank you 😬 that's great. The first thing I'd like to ask is for you to take the code you wrote in your first post and make a complete
sequelize-sscce out of it, can you do that? Thanks!!

[soryy708]

#311

[patrickcarnahan]

@papb i see this has had no traction in quite some time. we are currently hitting this while attempting to use an mssql dialect for azurite, an azure storage emulator. i'm happy to take up this work if it's not on any one else's plate.

[sushantdhiman]

🎉 This issue has been resolved in version 6.3.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀