Create with Include Sequelize

recently I discovered this on the sequelize documentation where you can create using include. Now I trying to do it on my program but only creates the records of the “parent” model and not for the children.

This is my model and my controller.

var MainMenu = sequelize.define('MainMenu', {
    Name: {
      type: DataTypes.STRING(50)      
    },
    Day: {
      type: DataTypes.DATE
    },
    RecordStatus:{
      type: DataTypes.BOOLEAN,
      defaultValue: true
    },    
    DeletedAt: {
      type: DataTypes.DATE
    }
  },
  {
    associate: function(models){
      models.MainMenu.hasMany(models.MainMeal, {as: 'Menu'});
    }
  }
);

exports.createIn = (req, res) => {

 let Menu = {
   Name: 'MenuTest',
   MainMeal: [{
     Type: 'Breakfast',
     Name: 'MealTest1'
   }, {
     Type: 'Lunch',
     Name: 'MealTest2'
   }]
 };

  db.MainMenu.create(Menu, {
    include: [{
      model: db.MainMeal,
      as: 'Menu'
    }]
  })
    .then( mainmenu => {
      if (!mainmenu) {
        return res.send('users/signup', {
          errors: 'Error al registrar el mainmenu.'
        });
      } else {
        return res.jsonp(mainmenu);
      }
    })
    .catch( err => {
      console.log(err);
      return res.status(400)
        .send({
          message: errorHandler.getErrorMessage(err)
        });
    });
};

On my case it only creates the MainMenu record and not the MainMeal records. What am I doing wrong?

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

Change your menu object, and include Menu array and not MainMeal

  • You have to give the aliased name in the object
let mainMenu = {
   Name: 'MenuTest',
   Menu: [{
     Type: 'Breakfast',
     Name: 'MealTest1'
   }, {
     Type: 'Lunch',
     Name: 'MealTest2'
   }]
 };

Now,

db.MainMenu.create(mainMenu, {
    include: [{
      model: db.MainMeal,
      as: 'Menu'
    }]
  })
    .then( mainmenu => {
      if (!mainmenu) {
        return res.send('users/signup', {
          errors: 'Error al registrar el mainmenu.'
        });
      } else {
        return res.jsonp(mainmenu);
      }
    })
    .catch( err => {
      console.log(err);
      return res.status(400)
        .send({
          message: errorHandler.getErrorMessage(err)
        });
    });

Solution 2

The main thing is of course the naming of Menu should be within the data passed to .create() itself, along with the arguments presented there and if you really need to specify the alias “twice”, which you do not. But there are some other things to be aware of.

I’d personally prefer storing the association as it’s own export and including that within the statement. This generally becomes a bit clearer when you understand the usage of that association later.

I would also strongly encourage that when you are “writing” things across multiple tables, then you implement transactions to ensure all related items are actually created and not left orphaned should any errors arise.

As a brief listing based on the example:

const Sequelize = require('sequelize');

const sequelize = new Sequelize('sqlite:menu.db',{ logging: console.log });

const MainMeal = sequelize.define('MainMeal', {
  Type: { type: Sequelize.STRING(50) },
  Name: { type: Sequelize.STRING(50) }
});

const MainMenu = sequelize.define('MainMenu', {
  Name: { type: Sequelize.STRING(50) }
});

MainMenu.Meals = MainMenu.hasMany(MainMeal, { as: 'Menu' });

(async function() {

  try {

    await sequelize.authenticate();
    await MainMeal.sync({ force: true });
    await MainMenu.sync({ force: true });

    let result = await sequelize.transaction(transaction => 
      MainMenu.create({
        Name: 'MenuTest',
        Menu: [
          { Type: 'Breakfast', Name: 'MealTest1' },
          { Type: 'Lunch', Name: 'MealTest2' }
        ]
      },{
        include: MainMenu.Meals,
        transaction
      })
    );

  } catch(e) {
    console.error(e);
  } finally {
    process.exit();
  }
})();

Which would output something like:

Executing (default): SELECT 1+1 AS result
Executing (default): DROP TABLE IF EXISTS `MainMeals`;
Executing (default): CREATE TABLE IF NOT EXISTS `MainMeals` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `Type` VARCHAR(50), `Name` VARCHAR(50), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `MainMenuId` INTEGER REFERENCES `MainMenus` (`id`) ON DELETE
SET NULL ON UPDATE CASCADE);
Executing (default): PRAGMA INDEX_LIST(`MainMeals`)
Executing (default): DROP TABLE IF EXISTS `MainMenus`;
Executing (default): CREATE TABLE IF NOT EXISTS `MainMenus` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `Name` VARCHAR(50), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`MainMenus`)
Executing (3d645847-56ca-435a-b786-6be62a05e8d5): BEGIN DEFERRED TRANSACTION;
Executing (3d645847-56ca-435a-b786-6be62a05e8d5): INSERT INTO `MainMenus` (`id`,`Name`,`createdAt`,`updatedAt`) VALUES (NULL,'MenuTest','2018-04-14 08:08:17.132 +00:00','2018-04-14 08:08:17.132 +00:00');
Executing (3d645847-56ca-435a-b786-6be62a05e8d5): INSERT INTO `MainMeals` (`id`,`Type`,`Name`,`createdAt`,`updatedAt`,`MainMenuId`)
VALUES (NULL,'Breakfast','MealTest1','2018-04-14 08:08:17.152 +00:00','2018-04-14 08:08:17.152 +00:00',1);
Executing (3d645847-56ca-435a-b786-6be62a05e8d5): INSERT INTO `MainMeals` (`id`,`Type`,`Name`,`createdAt`,`updatedAt`,`MainMenuId`)
VALUES (NULL,'Lunch','MealTest2','2018-04-14 08:08:17.153 +00:00','2018-04-14 08:08:17.153 +00:00',1);
Executing (3d645847-56ca-435a-b786-6be62a05e8d5): COMMIT;

The important part there being the transaction BEGIN and COMMIT wrapping all of those INSERT statements as data is created. Even without the transaction implemented, you still see both items being created along with the related “parent”. But the point of the argument is this is where you “should” be implementing transactions.

Also note that the “aliased” Menu as used in the data creation and for subsequent access, is not actually “required” to be included within the .create() method on the include option. It’s “optional” and is already defined under the .hasMany() arguments, so you don’t really need to do it again.

Even if you did, then that part would still be the “association” as used with the model argument:

{
  include: {
    model: MainMenu.Meals,
    as: 'Menu'
  },
  transaction
}

So that’s not to be confused with the original name of the model for the “table” which is referenced, which also might be another point of confusion.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply