sequelize model, migration file and foreign key. which one is right?

Question

At first time when I started studying sequelize I just had no idea, just copy and pasted the code when it comes to make relations between two models.

It really confused me whether I have to insert foreign keys both to the model file and migration file or for only migration file.

I knew that the migrations are files that have commands for changing your database.

so definitely we have to manually insert foreign keys to the migration file so that database can create them.

In sequelize doc, if we add relations like has many and belongs to, the sequelize will automatically add foreign keys.

so I was really confused whether I have to add them or not.

some questions I asked before the answers were fifty-fifty.

some says that we don`t have to manually add foreign keys to the model because sequelize will automatically add them.

but some says that we have to manually add foreign keys to the model because we have to match(sync) columns between models and migration files.

even worse, the articles explaining about sequelize relations are differ from each other.

so, which one is right??

I really want to get clear answer.

It will be really thankful to get some reasons (if we have to add foreign key to the model)

not exaggerated, I have been curious of this issue for about six month.

Answer 1

While create the migration you should write the foreign keys also. Also write it in the models, So it will be easy to handle the queries. We can see it with example. Consider their are two table Item And Item_Types

1 Item_type has many Items so,

Migration File for Item_type (migration_create_item_type.js)

'use strict';
module.exports = {
up: function (queryInterface, Sequelize) {
    return queryInterface.createTable('item_types', {
        id: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: Sequelize.INTEGER
        },
        item_type: {
            type: Sequelize.STRING
        },
        type_desc: {
            type: Sequelize.STRING
        },
        createdAt: {
            allowNull: true,
            type: Sequelize.DATE,
            defaultValue: Sequelize.NOW
        },
        updatedAt: {
            allowNull: true,
            type: Sequelize.DATE,
            defaultValue: Sequelize.NOW
        }
    });
},
down: function (queryInterface, Sequelize) {
    return queryInterface.dropTable('item_types');
}
};

Migration file for item (migration_create_item.js)

'use strict';
module.exports = {
up: function (queryInterface, Sequelize) {
    return queryInterface.createTable('items', {
        id: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: Sequelize.INTEGER
        },
        item_name: {
            type: Sequelize.STRING
        },
        item_desc: {
            type: Sequelize.STRING
        },
        item_type_id: {
            type: Sequelize.INTEGER,
            references: {
                model: 'item_types',
                key: 'id'
            }
        },
        createdAt: {
            allowNull: false,
            type: Sequelize.DATE
        },
        updatedAt: {
            allowNull: false,
            type: Sequelize.DATE
        }
    });
},
down: function (queryInterface, Sequelize) {
    return queryInterface.dropTable('items');
}
};

Please note that always create the Parent table 1st then others i.e. create all the tables that doen't have a foreign key and then others

Model file the Item_type (item_type.js)

'use strict';
module.exports = function (sequelize, DataTypes) {
var item_type = sequelize.define('item_type', {
    item_type: DataTypes.STRING,
    type_desc: DataTypes.STRING
});
item_type.associate = function (models) {
    item_type.hasMany(models.item, {foreignKey: 'item_type_id'});
};
return item_type;
};

Model File the item (item.js)

'use strict';
 var Logger = require('./../utils/logger');

 var log = new Logger('item_type_factory');
 module.exports = function (sequelize, DataTypes) {
 var item = sequelize.define('item', {
    item_name: DataTypes.STRING,
    item_desc: DataTypes.STRING
 });
 item.associate = function (models) {
    item.item_type = item.belongsTo(models.item_type, {foreignKey: 'id', target_key: 'item_type_id'});
    item.order_details = item.hasMany(models.order_details);
    item.user = item.belongsToMany(models.user, {through: 'supplier_items'})
};

item.addNewItem = function (data) {
    return item.create(data, {include: [{association: item.item_type}]});
};

item.findAndCreate = function (data, item_name) {
    return new Promise(function (resolve, reject) {
        item.findOrCreate({
            where: {'item_name': item_name}, defaults: data
        }).spread(function (record_data, created) {
            resolve(record_data);
        }).catch(function (insert_error) {
            reject(insert_error);
        });
    });
};

item.findAllItems = function () {
    return item.findAll({
        include: [{association: item.item_type}]
    });
};
return item;
};

For sequlize basics you can refer the following article, Getting started with Sequelize For the basics only.

Answer 2

How should we create DB schema

We have two options here. Migrations or Sequelize using sync. Always prefer migrations over sync. Migrations are more powerful, you can undo, redo and much more with it. sync does not reflect table alterations. For example, you define a certain model say User and forgot to add gender. Now if you want to add this column with Sequelize, you would have to use force:true which would drop all of your User data which is not desirable in production.

Who should define the foreign key constraints

From software design principles, your database constraints and validations should always be in place irrespective whether application logic (Sequelize) implements the same logic or not. For example, a new developer can write a raw query and can mess up your whole database if you do not have the right constraints.

However we also want to use sequelize to make the right queries with all the associations. The only way sequelize can do this if it knows what associations exists in the db and what should be the foreign key.

So, foreign key constrains should be defined at both migration as well as sequelize level.

Example of db constraints

As you can see in the image above, the constraints are defined on my database schema.

Example of sequelize constraints

module.exports = (sequelize, DataTypes) => {
  const Designation = sequelize.define('designation', {
    doctorId: {
      type: DataTypes.STRING,
      allowNull: false,
      field: 'doctor_id',
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  }, {});
  Designation.associate = (models) => {
    models.designation.belongsTo(models.doctor, {
      onDelete: 'cascade',
    });
  };