Why is Sequelize executing the insert twice

[Djeimi Fipke]

I'm trying to execute a insert, and sequelize is executing the insert twice.

First insert: only with userid, updatedAt, createdAt, 

Second Insert: the other fields

Console Result

 #Insert car: {"name":"CarTest","description":"desc","myNumber":123,"id":"","UserId":1}

   Executing (undefined): INSERT INTO `Cars` (`UserId`,`updatedAt`,`createdAt`) VALUES (1,'2014-09-17 22:31:49','2014-09-17 22:31:49');

   Executing (undefined): INSERT INTO `Cars` (`id`,`name`,`description`,`myNumber`,`createdAt`,`updatedAt`,`UserId`) VALUES (DEFAULT,'CarTest','desc',123,'2014-09-17 22:31:49','2014-09-17 22:31:49',1);

   saved: {"name":"CarTest","description":"desc","myNumber":123,"id":39,"UserId":1,"updatedAt":"2014-09-17T22:31:49.000Z","createdAt":"2014-09-17T22:31:49.000Z"}

My model Car.js

    module.exports = function(sequelize, DataTypes) {

       var Car = sequelize.define('Car', {

           name: {

               type: DataTypes.STRING,

           },

           description: {

               type: DataTypes.STRING,

           },

           myNumber: {

               type: DataTypes.BIGINT,

           },

       },{

             classMethods: {

             associate: function(models) {

                 Car.belongsTo(models.User,{ foreignKeyConstraint: true})

             }

         }

       });

       return Car;

   };

My controller

    var mycar =  db.Car.build(req.body);

   mycar.setUser(req.user);

   

   console.log("#Insert car: "+ JSON.stringify(mycar));

   mycar.save().success(function(entity) {

       res.json(entity);

       console.log("saved: "+ JSON.stringify(entity));

   }).error(function(error){

       console.log("error: "+ error);

   });

package.json

    "mysql": "^2.5.0",

    "connect-session-sequelize": "^2.0.0",

What I'm doing wrong?

Why is sequelize executing the save in separated inserts?

[Mick Hansen]

There is curently no support for a non executing setAssociation.
So when you call setAssociation it updates the model.
You need to either wait for save() or setUser() to finish before doing the other.

--

Mick Hansen

@mhansendev
mhansen.io

[Glenn Boysko]

I got stuck on this as well. Instead of simply waiting, can we have a {save: false} option for the setAssociation() call? Or allow it to pass in a transaction ({transaction: t}) to group calls together?

One other question related to associations: can you make them non-nullable? I tried to set {allowNull: false} in the belongsTo call, but that doesn't seem to be recognized.

[Mick Hansen]

https://github.com/sequelize/sequelize/issues/2582

{save: false} is supported for belongsTo relations now - Still needs to be released though.
All association methods take a transaction call too.

For non-null fields you should define the foreign key on the model as an attribute and then reference that key with foreignKey: 'attribute'

[Glenn Boysko]

You say that "{save: false}" is supported, but not yet released. If I have 2.0.0-rc4, should this work for me? 

Because I have 2.0.0-rc4 (as per "npm list sequelize") and it does not work. That is, I have the call to setAssocation with "{save: false}" as the second parameter and there is still an INSERT with just that foreign key assignment before the SAVE call.

If I step into the code, I don't see any check for save. 

What am I missing?

[Glenn Boysko]

Sorry--I misread your change log. Looks like the changes for {save: false} are in the "next" release, not in 2.0.0-rc4. My mistake.

Keep up the good work. 

Thanks,

Glenn

[Mick Hansen]

As you discovered, no it's on in rc4 but will be in the next (rc5 or 2.0.0 final depending on our confidence about bug fixes)

[Glenn Boysko]

Regarding support for non-null fields, this is what I have done:

var Foo = sequelize.define('Foo', { bar_id: { type: Sequelize.INTEGER, allowNull: false } });

var Bar = sequelize.define('Bar', { name: Sequelize.STRING });

Foo.belongsTo(Bar, { foreignKey: 'bar_id' });

When I run this, I get this error:

Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'schema.Foos' (errno: 150)

After doing some searching, it appears that the problem might be related to the lack of an index? Not sure if I have correctly implemented your suggestion.

On Thursday, January 1, 2015 10:52:26 AM UTC-5, Mick Hansen wrote:

[Mick Hansen]

Your definition looks right - however the error seems pretty generic, any more information?

[Glenn Boysko]

I can give you the call stack and the full DDL. I can even reproduce it in a simple file. What would help? Here's the call stack:

Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'schema.Foos' (errno: 150)

    at module.exports.Query.formatError (/home/glenn/projects/finances/node_modules/sequelize/lib/dialects/mysql/query.js:141:16)

    at Query._callback (/home/glenn/projects/finances/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)

    at Query.Sequence.end (/home/glenn/projects/finances/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)

    at Query.ErrorPacket (/home/glenn/projects/finances/node_modules/mysql/lib/protocol/sequences/Query.js:93:8)

    at Protocol._parsePacket (/home/glenn/projects/finances/node_modules/mysql/lib/protocol/Protocol.js:271:23)

    at Parser.write (/home/glenn/projects/finances/node_modules/mysql/lib/protocol/Parser.js:77:12)

    at Protocol.write (/home/glenn/projects/finances/node_modules/mysql/lib/protocol/Protocol.js:39:16)

    at Socket.<anonymous> (/home/glenn/projects/finances/node_modules/mysql/lib/Connection.js:82:28)

    at Socket.emit (events.js:95:17)

    at Socket.<anonymous> (_stream_readable.js:765:14)

[Mick Hansen]

Err 150 is a constraint error.
In what order are you syncing your models, are you using sequelize.sync and are you using force: true?

[Glenn Boysko]

Is there a SQL log which records all calls being made to the RDBMS?

[Mick Hansen]

You can pass `logging: console.log` to the Sequelize constructor

[Glenn Boysko]

I am using sequelize.sync, but was not using {force: true}. I wasn't sure when this was necessary. I'll do that now...

[Mick Hansen]

It's not always necessary, but when prototyping it's more convenient since it will drop the tables and create with your definition.
sync without force: true will only create tables that don't already exist (and not upgrade existing tables)

[Glenn Boysko]

Adding {force: true} to the sync call didn't help. Here's the full set of SQL calls made:

Executing (default): DROP TABLE IF EXISTS `Foos`;

Executing (default): DROP TABLE IF EXISTS `Bars`;

Executing (default): DROP TABLE IF EXISTS `Bars`;

Executing (default): CREATE TABLE IF NOT EXISTS `Bars` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

Executing (default): SHOW INDEX FROM `Bars`

Executing (default): DROP TABLE IF EXISTS `Foos`;

Executing (default): CREATE TABLE IF NOT EXISTS `Foos` (`id` INTEGER NOT NULL auto_increment , `bar_id` INTEGER NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`bar_id`) REFERENCES `Bars` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

[Mick Hansen]

That's a lot of drop table statements, weird.
The statements look alright, can you try running them manually and see if there's any more info? (remember to drop the tables first)