Insertion with association creates different sql between mysql and mssql

[briantan1979]

I have a sequelize data model that works fine in mysql but when I switched to mssql results in errors when it tries to insert records with associations defined.

Sequelize v4.38.0

A simplified reproduction documented below:

//'./schemas/MainTable.js'

var modelName = 'MainTable';
var tableName = 'main_table';

function model (sequelize, DataTypes)
{
    const model = sequelize.define(modelName,{
        counter: {
            type: DataTypes.BIGINT,
            autoIncrement: true,
            primaryKey: true
        },
        id: {
            type: DataTypes.UUID,
            unique: true,
            defaultValue: DataTypes.UUIDV4
        },
        documentNumber: {
            type: DataTypes.STRING,
            unique: true,
            allowNull: true
        }
    },
    {
        paranoid: true,
        underscored: false,
        tableName: tableName
    });
    return model;
}

module.exports = model;

//'./schemas/AssociatedTable.js'

var modelName = 'AssociatedTable';
var tableName = 'associated_table';

function model (sequelize, DataTypes)
{
    const model = sequelize.define(modelName,{
        someText: {
            type: DataTypes.STRING,
            allowNull: true
        }
    },
    {
        paranoid: true,
        underscored: false,
        tableName: tableName
    });
    return model;
}

module.exports = model;

// db.js
const Sequelize = require('sequelize');
const sequelize = new Sequelize(
    'dbname',
    'dbuser',
    'dbpassword',
    {
        host: 'localhost',
        port: 1433,
        dialect: 'mssql',
        define: {
            underscored: true
        }
    }
);

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.MainTable = require('./schemas/MainTable')(sequelize, Sequelize.DataTypes);
db.AssociatedTable = require('./schemas/AssociatedTable')(sequelize, Sequelize.DataTypes);

db.MainTable.hasOne(db.AssociatedTable, {as: 'associatedTable'});

// test.js
var db = require('./db);
var UUID4 = require('uuid/v4');
var newRecord = {
    id: UUID4()
    associatedTable: {
       someText: 'Testing'
    }
};

db.sequelize.transaction({isolationLevel: db.Sequelize.Transaction.ISOLATION_LEVELS.REPEATABLE_READ})
     .then(function (tran) {
                db.MainTable.create(newRecord, {
                    include: ['associatedTable'],
                    transaction: tran
                })
                    .then(data => {
                        console.log(data);
                    })
                    .catch(err => {
                        tran.rollback();
                        console.log(err);
                    });

The following sql statements are generated for mysql which are correct

INSERT INTO `main_table` (`counter`,`id`,`documentNumber`) VALUES (DEFAULT,'9c47db92-ff5f-40c2-8700-af4de0597af0','');
INSERT INTO `associated_table` (`someText`,`mainTableCounter`) VALUES ('Testing',1);

But the following sql insert statements are generated for mssql which would result in error "Error converting data type nvarchar to bigint" because it's trying to insert the uuid string into the bigint foreign key field.

INSERT INTO `main_table` (`id`,`documentNumber`) VALUES (DEFAULT,'9c47db92-ff5f-40c2-8700-af4de0597af0','');
INSERT INTO `associated_table` (`someText`,`mainTableCounter`) VALUES ('Testing','9c47db92-ff5f-40c2-8700-af4de0597af0');

A few questions:
a. Is this a bug in Sequelize ?
b. Do later versions of Sequelize e.g. v5.x or later have the same problem or is it already fixed in later versions ?
c. If no fixes are available, any recommended workaround ?

[sushantdhiman]

4.38.0 not supported

---

Please use Github Issue Tracker only for reporting bugs, requesting new features or discussions. Ask questions on Stackoverflow sequelize.js tag or Slack. While I would like to help answer any questions but it still take too much time.

If you are reporting a bug please isolate it as SSCCE, present it nicely and follow issue template. This will help maintainers and contributors understand your issue quickly.

If you are requesting a feature spend some time to properly present your use case with some examples, current and expected outcome.

( What is SSCCE ? You can find template for Sequelize here )

A few good example of SSCCE

1. ResourceRequest timed out - connection not reestablished after db restarted #8014 (comment)
2. removeAttribute('id') results in undefined: null data value #7318 (comment)
3. toJSON converting booleans to "t" (true) or "f" (false) #8749 (comment)

[briantan1979]

I have just tested on v5.21.6.
The same problem is reproduced.