[bug?] Creating with associations

[alekbarszczewski]

I want to create Company instance along with associated Address:

var Sequelize = require('sequelize')

var sequelize = new Sequelize('postgres://localhost/db', { logging: console.log })

var Company = sequelize.define('Company', {
  AddressId: {
    type: Sequelize.INTEGER,
    allowNull: false,
    unique: true
  }
})

var Address = sequelize.define('Address', {
  street: Sequelize.TEXT
})

Company.belongsTo(Address)

sequelize.sync({ force: true })
.then(function () {
  return Company.create({
    Address: { street: 'street1' }
  }, {
    include: [
      Address
    ]
  })
})

Output:

Unhandled rejection SequelizeValidationError: notNull Violation: AddressId cannot be null
    at /Users/alek/Desktop/pros/node_modules/sequelize/lib/instance-validator.js:72:14
    at tryCatcher (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/util.js:26:23)
    at Promise._settlePromiseFromHandler (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:507:31)
    at Promise._settlePromiseAt (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:581:18)
    at Promise._settlePromiseAtPostResolution (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:245:10)
    at Async._drainQueue (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/async.js:128:12)
    at Async._drainQueues (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/async.js:133:10)
    at Async.drainQueues (/Users/alek/Desktop/pros/node_modules/sequelize/node_modules/bluebird/js/main/async.js:15:14)
    at process._tickCallback (node.js:442:13)

Quick workaround:

var Sequelize = require('sequelize')

var sequelize = new Sequelize('postgres://localhost/db', { logging: console.log })

var Company = sequelize.define('Company', {
  AddressId: {
    type: Sequelize.INTEGER,
    allowNull: false,
    unique: true
  }
})

var Address = sequelize.define('Address', {
  street: Sequelize.TEXT
})

Company.belongsTo(Address)

sequelize.sync({ force: true })
.then(function () {
  return Company.create({
    AddressId: -1, // <---------------------------------------- Fake ID
    Address: { street: 'street1' }
  }, {
    include: [
      Address
    ]
  })
})

Output (works!):

Executing (default): INSERT INTO "Addresses" ("id","street","updatedAt","createdAt") VALUES (DEFAULT,'street1','2015-10-22 14:08:05.306 +00:00','2015-10-22 14:08:05.306 +00:00') RETURNING *;
Executing (default): INSERT INTO "Companies" ("id","AddressId","updatedAt","createdAt") VALUES (DEFAULT,1,'2015-10-22 14:08:05.312 +00:00','2015-10-22 14:08:05.312 +00:00') RETURNING *;

[mickhansen]

Known issue i'm afraid, not totally sure how we fix it since validations run before the nested creates..

[mickhansen]

Could possible move the first step of nested create to before validation.
Users should be wrapping all nested creates in a transaction in any case (but i'm not sure everyone are, i sometimes forget).

[alekbarszczewski]

As for transaction, I removed it for exmaple simplicity, but anyway even with transaction it does not work, if you meant that. Maybe it would be possible to just use the trick with "fake id", but behind the scenes? When building Model with options.include = [...] just set appropriate foreign key fields to some fake values (like -1).

Or maybe it would be possible to just exclude those fields from validation by using standard options?

[alekbarszczewski]

This is my idea (it works for fixed 'AddressId' and example above):

Model.prototype.create = function(values, options) {
  options = optClone(options || {});

  var instance = this.build(values, {
    isNewRecord: true,
    attributes: options.fields,
    include: options.include,
    raw: options.raw,
    silent: options.silent
  })
  options.skip = options.skip || [];
  options.skip.push('AddressId'); // <------------- skip foreign key from validation
  return instance.save(options);
};

However I am not familiar with Associations yet and I am not sure how to get appropriate foreign keys from options.include = [...]

[mickhansen]

Skipping validations for fields we know will be supplied could be a good "quick" fix.

[SnareChops]

I have also encountered this issue myself and have created a repro for it here also before finding this I had written a very detailed question on the issue on Stack Overflow here. I feel that this is a pretty fatal flaw in the library and trying to find a solution or workaround is very difficult. I will go with the above suggestions for now, but this really seems like a big issue to fix.

Also in my case I am using TypeScript to help with static analysis and error checking. Adding in a fake property requires me to change the interface defining my model to include the fake property which then breaks the concept of using the interface. Needing to have a fake property that is nullable in the production code but required in the test code means having to remember that it is a fake property...

[mickhansen]

It's definitely a flaw, but nested create is a rather new feature - And contributions are certainly always welcome :)

Should really just come down to looping over belongsTo nested creates before validate and flagging them for skip.

[sushantdhiman]

Got hit by this :)

[THEtheChad]

Dealing with this right now as well. Tried using a transaction but, as was pointed out earlier, that doesn't work =/

[ghs]

@mickhansen most probably I am doing something wrong but I am still getting the same error even on v.5.0.0-beta.9
This is what I have and get:

Items = db.define('items', {
  id: {
    type: Sequelize.INTEGER,
    allowNull: true,
    primaryKey: true
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },....
});

ItemSettings = db.define('settings', {
  item_id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true
  },
  photo: {
    type: Sequelize.STRING,
    allowNull: false
  },....
});

Settings = Items.hasOne(ItemSettings, {
  foreignKey: 'item_id',
  as: 'setting'
});


Item.create({
    name: 'test'
    setting: { photo: 'file.jpg' }
  }, {
    include: [
      Settings
    ]
  })

The response is : { SequelizeValidationError: notNull Violation: settings.item_id cannot be null
A record in items table is created correctly but fails for settings.

[stiofand]

This error seems to occur without associations also.

[Klavic]

Hi guys.
I was having the same problem.
I fix it by creating a attribute of the ForeignKey on the model and adding the "allowNull" there and removing the "allowNull" atribute of all my associations.

Before:
`
const user = sequelize.define('user', {
idUser: {
type: dataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
field: 'id_user',
},
name: {
type: dataTypes.TEXT,
field: 'name',
},
});

const email = sequelize.define('user', {
idEmail: {
type: dataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
field: 'id_email',
},
email: {
type: dataTypes.TEXT,
field: 'email',
},
});

user.hasMany(email, {
foreignKey: {
field: 'id_user',
allowNull: false,
},
});

email.belongsTo(user, {
foreignKey: {
field: 'id_user',
allowNull: false,
},
});
`

after:
`
const user = sequelize.define('user', {
idUser: {
type: dataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
field: 'id_user',
},
name: {
type: dataTypes.TEXT,
field: 'name',
},
});

const email = sequelize.define('user', {
idEmail: {
type: dataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
field: 'id_email',
},
email: {
type: dataTypes.TEXT,
field: 'email',
},
idUser: {
type: dataTypes.INTEGER,
field: 'id_user',
allowNull: false,
},
});

user.hasMany(email, {
foreignKey: 'id_user'
});

email.belongsTo(user, {
foreignKey: 'id_user',
});
`

Ps: I'm still testing because I own more than 40 tablelas, but the ones I tested worked without problems.

[stiofand]

Didn't work for me, there is other issues that haven't been addressed by Sequelize people either, so probably going to drop it all together, to much hassle. I don't have any associations

[Rest11]

@Klavic it's working for me, just removed property "allowNull" from an Association model. But a table was created with "null" default value. I think you have the same issue and this is a bug of sequelize library.

[timbowhite]

I'm experiencing the same issue as @ghs with nested/assocations create with sequelize@4.42.1, dialect = mysql.

If the assocation's foreignKey does not allow nulls (i.e. allowNull: false set in either the model definition or the association definition), performing a nested create results in the SequelizeValidationError: notNull Violation error. It seems validation is being performed on the object's foreignKey field before it is populated with an id.

[Rest11]

Maybe you are right but it still a bug in sequelize (

[calleufuzi]

Any solution for this issue? =S

[alexgilbertDG]

Still got this issue with sequelize@6.3.5... when I try to delete an association like Bar.deleteFoo() and Foo has barId as allowNull as false.

[rafaooliveira]

Hey, if you want to try to solve this problem without change the allowNull: true, you can try to apply this
autoIncrement: true to your Id model, maybe this can solve your probem

[juanvictorbascopecastro]

Try several solutions and it turns out that you were doing it wrong,
Solution:
return Company.create({
Address: { street: 'street1' }
}, {
include: [{
association: Address,
}]
})

Or if they want to create Address it would be as follows.

return Address.create({
street: 'street1' ,
company: {}
}, {
include: [Company]
})

you may have a nickname you must put the [address]: this.belongsTo(Address, {as: 'address', foreignKey: 'AddressId'});