How to populate sequelize associations - EDITED

Question

I am trying to use Sequelize for the first time but after hours of reading documentation, blogs and issues I am still quite confused and need some help to point me in the right path to understand how associations works. This is what I have done:

EDIT: I remove from models the associate function because it works only with an index.js like this. I define the association directly in index.js before sync(). After that querying the sqlite schema confirm the field UserId is added to the table.

DB connection module:

const Sequelize = require('sequelize');
const db = new Sequelize({
  host: 'localhost',
  dialect: 'sqlite',
  storage: './nodeapp.db.sqlite'
});    
db
  .authenticate()
  .then(() => {
    console.log('Connection has been established successfully.');
    db.models.User.hasMany(db.models.Order);
    // db.models.Order.belongsTo(db.models.User); // THIS WORKS TOO
    db.sync();
    console.log('Database synchronized successfully.');
  })
  .catch(err => {
    console.error('Unable to connect to the database:', err);
  });
module.exports = db;

User model definition:

const DataTypes = require('sequelize');
const db = require('./index');
const User = db.define('User', {
    username: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
   },
...
});
/* REMOVED
User.associate = (models) => {
  models.User.hasMany(models.Order);
};
*/
module.exports = User;

Order model definition:

const DataTypes = require('sequelize');
const db = require('./index');
const Order = db.define('Order', {
  order_num: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
...
});
/* REMOVED
Order.associate = (models) => {
  models.Order.belongsTo(models.User);
};
*/
module.exports = Order;

Router module:

router.post('/order',(req,res) => {
  Order
    .create(req.body.order)
    .then(order => {

      // HERE IS WHERE I'AM TRYING TO POPULATE THE RELATION WITH SET ...
      order.setUser(req.body.user).then((order) => {
        res.send(order);
      })          

  .catch(error => {
    res.send(error);
  });
});

All the CRUD operations are working correctly if I don't consider the relations. I can't figure it out how to populate the user reference in the order model, my questions are:

• Defining the association in the model only add the foreign key field?
• I have to manually populate the foreign key with id value or I can use user Object?

I need some help figuring out where I've gone wrong here because I know I am not doing something new.

Answer 1

Thanks to some comments and similar issues I finally make it working. At the moment I found my mistake it is useful this piece of documentation. My mistakes were in the definition of the db and probbly this is not the best solution but I'am working on it and any suggestion or best practice will be accepted.

DB Connection module

...
db
.authenticate()
.then(() => {
  console.log('Connection has been established successfully.');
  const User = require('./user');
  const Account = require('./account');
  const Order = require('./order');
  // At this point all models needed for associations are defined
  Account.belongsTo(User);
  Order.belongsTo(User);
  Order.belongsTo(Account);
  Order.belongsTo(Account, {as: 'Shipto'});
  Order.belongsTo(Account, {as: 'Billto'});
  db.sync();
  console.log('Database synchronized successfully.');
})
...

The model definition files remain the same.

Router module

router.post('/order',(req,res) => {
 db.models.Order
 .create(req.body.order)
 .then((order) => {
   order.setUser(req.body.order.UserId);
   order.setAccount(req.body.order.AccountId);
   order.setShipto(req.body.order.ShiptoId);
   order.setBillto(req.body.order.Billtoid);
   res.send(order);
 })
 .catch(error => {
   res.send(error);
 });
});

Answer 2

1. You must declare a primary key in your model definitions:

   const User = db.define('User', {
     id: {
       type: Sequelize.INTEGER,
       primaryKey: true,
       autoIncrement: true,
     },
     username: {
       type: DataTypes.STRING,
       allowNull: false,
       unique: true
      }
   });
   
   const Order = db.define('Order', {
     id: {
       type: Sequelize.INTEGER,
       primaryKey: true,
       autoIncrement: true,
     },
     userId: {
       type: Sequelize.INTEGER,
     },
     order_num: {
       type: DataTypes.STRING,
       allowNull: false,
       unique: true
     }
   });
   

2. You must include all fields, including primary and foreign keys, in your migration files.

   module.exports = {
     up: function(queryInterface, Sequelize) {
       return queryInterface.createTable('orders', {
             id: {
               type: Sequelize.INTEGER,
               primaryKey: true,
               autoIncrement: true,
             },
             userId: {
               type: Sequelize.INTEGER,
             },
             order_num: {
               type: DataTypes.STRING,
               allowNull: false,
               unique: true
             }
       });
     },
   
     down: function(queryInterface) {
       ...
     }
   };
   

3. Keep in mind ORM operations are asyncronous. Consider using asyc/await:

   router.post('/order', async (req,res) => {
     Order
       .create(req.body.order)
       .then(order => {
   
         // you must fetch the user instance from req.user.id
         const userInstance = models.User.findByPk(req.user.id)
   
         // HERE IS WHERE I'AM TRYING TO POPULATE THE RELATION WITH SET ...
         await order.setUser(userInstance, {save: false});
         order.save();
         res.send(order);
       })
     .catch(error => {
       res.send(error);
     });
   });
   

Answer 3

I think you need to create association like this, I have these associations in my models and they are working fine.

so just change the associations from this

User.associate = (models) => {
  models.User.hasMany(models.Order);
};

to this,

User.associate = (models) => {
  User.hasMany(models.Order, { foreignKey: 'user_id' });
};