Sequelize create object with association not working

[PeterSawyer]

See this StackOverflow for original reference, but I think this is a better place to get a response at the moment

I am trying to create a record (Location) with an association (Weather) (insert with foreign key) and every which way I try I end up with a NULL value in the foreign key field weatherId.

I have seen examples in the help with creating both the primary and secondary entities at the same time, but in this case I have preloaded the Weather table and the user is limited to selecting an item from a select list.

I have found similar issues but none have answered the problem.

Sequelize [Node: 4.2.2, CLI: 2.2.1, ORM: 2.0.0-rc1, mysql: ^2.10.0]

My models are as per follows and I am using migrations if this has anything to do with it: -

Location

'use strict';
module.exports = function(sequelize, DataTypes) {
    var Location = sequelize.define('Location', {
        id: DataTypes.INTEGER,
        locationName: DataTypes.STRING
    }, {
        classMethods: {
            associate: function(models) {
                Location.hasMany(models.Rig);
                Location.belongsTo(models.Weather);
            }
        },
        freezeTableName: true,
        tableName: 'Location',
        timestamps: true
    });
    return Location;
};

MySQL describe for Location

mysql> desc Location;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| locationName | varchar(255) | YES  |     | NULL    |                |
| weatherId    | int(11)      | YES  |     | NULL    |                |
| createdAt    | datetime     | NO   |     | NULL    |                |
| updatedAt    | datetime     | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Weather model

'use strict';
module.exports = function(sequelize, DataTypes) {
    var Weather = sequelize.define('Weather', {
        id: DataTypes.INTEGER,
        weatherDescription: DataTypes.STRING
    }, {
        classMethods: {
            associate: function(models) {
                Weather.hasMany(models.Location);
            }
        },
        freezeTableName: true,
        tableName: 'Weather',
        timestamps: true
    });
    return Weather;
};    

MySQL describe for Weather

mysql> desc Weather;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| weatherDescription | varchar(255) | YES  |     | NULL    |                |
| createdAt          | datetime     | NO   |     | NULL    |                |
| updatedAt          | datetime     | NO   |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

Attempts

First attempt which fails with NULL weatherId

models.Location.create({
    locationName: locationName,
    weatherId: weatherId

}).then(function(location) {
    res.redirect('/location');

}).catch(function(reason) {
    console.log(reason);
});

Second attempt which fails with NULL weatherId

models.Location.create({
    locationName: locationName

}).then(function(location) {
    models.Weather.find({where: { id: weatherId } }).then(function(weather) {
        location.setWeather([weather]).then(function(location) {

            res.redirect('/location');

        }).catch(function(reason) {
            console.log(reason);
        });

    }).catch(function(reason) {
        console.log(reason);
    });

}).catch(function(reason) {
    console.log(reason);
});

Yet when I do an update this works: -

models.Location.find({
    where: {
        id: locationId
    }
}).then(function(location) {
    if (location) {
        location.setWeather([weatherId]).then(function(location) {
            location.updateAttributes({
                locationName: locationName
            }).success(function() {
                res.redirect('/location');
            });
        });
    }
}).catch(function(reason) {
    console.log(reason);
    res.send(reason);
})

There are no errors in the logs, but still weatherId is NULL.

The SQL in the log does not include the weatherId as per: -

INSERT INTO `Location` (`id`,`locationName`,`createdAt`,`updatedAt`) VALUES (NULL,'test me','2016-01-04 02:33:04','2016-01-04 02:33:04');

Can anyone help me with this, have spent so much time on this..

Peter

[mickhansen]

Looks like Location is not getting the foreign key as an attribute, can you try logging Object.keys(Location.rawAttributes)?

[PeterSawyer]

Howdy mate,

As requested ran

console.log(Object.keys(location.rawAttributes));

and got

[ 'id',
  'locationName',
  'createdAt',
  'updatedAt',
  'WeatherId' ]

So weatherId in table is WeatherId in Model.

First attempt - Take 2

weatherId: weatherId
to
WeatherId: weatherId

and it works.

Thanks for the help
I am new to this and have done a lot of stumbling around, is this the right place to ask this question.

Thanks again
Peter

[mickhansen]

The default foreign key name is based on the model name, but you can overwrite it with Location.belongsTo(models.Weather, {foreignKey: 'weatherId'});

[PeterSawyer]

@mickhansen heeding you tip I have refactored all my 5 entities, dropped db, recreated and re-ran fixtures, then when she fires up I get

Possibly unhandled SequelizeDatabaseError: Error: ER_DUP_FIELDNAME: Duplicate column name 'RigId'
    at module.exports.Query.formatError (/Users/peter/gofish/node_modules/sequelize/lib/dialects/mysql/query.js:84:16)
    at Query._callback (/Users/peter/gofish/node_modules/sequelize/lib/dialects/mysql/query.js:40:23)
    at Query.Sequence.end (/Users/peter/gofish/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/Users/peter/gofish/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/Users/peter/gofish/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/Users/peter/gofish/node_modules/mysql/lib/protocol/Parser.js:73:12)
    at Protocol.write (/Users/peter/gofish/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/peter/gofish/node_modules/mysql/lib/Connection.js:96:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)

assuming its my Fish table as per SQL

Executing (default): CREATE TABLE IF NOT EXISTS `Fish` (`id` INTEGER, `length` INTEGER, `weight` INTEGER, `timeCaught` INTEGER, `notes` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `rigId` INTEGER, `speciesId` INTEGER, `RigId` INTEGER, `SpeciesId` INTEGER) ENGINE=InnoDB;

mySql says this

mysql> desc Fish;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| rigId      | int(11)  | YES  |     | NULL    |                |
| speciesId  | int(11)  | YES  |     | NULL    |                |
| length     | int(11)  | YES  |     | NULL    |                |
| weight     | int(11)  | YES  |     | NULL    |                |
| timeCaught | int(11)  | YES  |     | NULL    |                |
| notes      | text     | YES  |     | NULL    |                |
| createdAt  | datetime | NO   |     | NULL    |                |
| updatedAt  | datetime | NO   |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

My model says this

'use strict';
module.exports = function(sequelize, DataTypes) {
    var Fish = sequelize.define('Fish', {
        id: DataTypes.INTEGER,
        length: DataTypes.INTEGER,
        weight: DataTypes.INTEGER,
        timeCaught: DataTypes.INTEGER,
        notes: DataTypes.TEXT
    }, {
        classMethods: {
            associate: function(models) {
                // associations can be defined here
                Fish.belongsTo(models.Rig, {foreignKey: 'rigId'});
                Fish.belongsTo(models.Species, {foreignKey: 'speciesId'});
            }
        },
        freezeTableName: true,
        tableName: 'Fish',
        timestamps: true
    });
    return Fish;
};

[mickhansen]

You'll need to add the foreign key option to both sides of the association (if you've defiend Rig hasMany Fish that needs the option aswell)

[PeterSawyer]

Fantastic @mickhansen you saved me from drowning in the deep end. 👍

Now have time to finish the fish logging app before the trip starts in 3 weeks.