Sequelize belongsTo not adding foreign key constraints to database table

Question

I am using sequelize ORM for my node.js application and have created two simple models.

company.model.js
location.model.js

A company can have multiple locations and a location can belongs to one company.

Here are my models:

company.model.js

'use strict';

module.exports = function(sequelize, DataTypes) {
  var Company = sequelize.define('Company', {
    id: {
      type: DataTypes.INTEGER(3),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      comment: "Primary and auto incremented key of the table"
    },
    companyName: {
      field: "company_name",
      type: DataTypes.STRING(255),
      allowNull: false,
      comment: "Company Name"
    },
    companyCode: {
      field: "company_code",
      type: DataTypes.STRING(20),
      allowNull: false,
      unique: true,
      comment: "Company Code"
    },
    contactName: {
      field: "contact_name",
      type: DataTypes.STRING(100),
      allowNull: true,
      comment: "Name of contact person"
    },
    contactNo: {
      field: "contact_no",
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: "Company contact number"
    },
    fax: {
      field: "fax",
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: "Company fax number"
    },
    website: {
      field: "website",
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: "Company website url if exist for e.g. mumbai.spiderman.com",
      validate: {
        isUrl: true
      }
    },
    country: {
      field: "country_id",
      type: DataTypes.INTEGER(3),
      allowNull: false,
      comment: "Country for address purpose"
    },
    state: {
      field: "state_id",
      type: DataTypes.INTEGER(4),
      allowNull: true,
      comment: "State for address purpose"
    },
    city: {
      field: "city_id",
      type: DataTypes.INTEGER(5),
      allowNull: true,
      comment: "City for address purpose"
    },
    landmark: {
      field: "landmark",
      type: DataTypes.STRING(255),
      allowNull: true,
      comment: "Address landmark"
    },
    address: {
      field: "address",
      type: DataTypes.TEXT,
      allowNull: true,
      comment: "Company address"
    },
    status: {
      field: "status",
      type: DataTypes.ENUM('ACTIVE','INACTIVE','DELETED'),
      allowNull: false,
      defaultValue: 'ACTIVE',
      comment: "Wether a company is active, inactive or deleted"
    }
  },{

    underscored: true,
    freezeTableName:true,
    tableName:'company',
    classMethods:{
      associate:function(models){
        Company.hasMany(models.Location);
      }
    },

  });

  return Company;
};

location.model.js

'use strict';

module.exports = function(sequelize, DataTypes) {
  var Location = sequelize.define('Location', {
    id: {
      type: DataTypes.INTEGER(5),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      comment: "Primary and auto incremented key of the table"
    },
    locationType: {
      field: "location_type",
      type: DataTypes.ENUM('HEAD_OFFICE','REGIONAL_OFFICE','FRANCHISE'),
      allowNull: false,
      comment: "Type of location"
    },
    locationName: {
      field: "location_name",
      type: DataTypes.STRING(200),
      allowNull: false,
      comment: "Name of location"
    },
    locationCode: {
      field: "location_code",
      type: DataTypes.STRING(20),
      allowNull: false,
      unique: true,
      comment: "Location Code"
    },
    contactName: {
      field: "contact_name",
      type: DataTypes.STRING(100),
      allowNull: true,
      comment: "Name of contact person"
    },
    contactNo: {
      field: "contact_no",
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: "Location contact number"
    },
    fax: {
      field: "fax",
      type: DataTypes.STRING(20),
      allowNull: true,
      comment: "Location fax number"
    },
    website: {
      field: "website",
      type: DataTypes.STRING(50),
      allowNull: true,
      comment: "Location specific website url if exist for e.g. mumbai.spiderman.com",
      validate: {
        isUrl: true
      }
    },
    country: {
      field: "country_id",
      type: DataTypes.INTEGER(3),
      allowNull: false,
      comment: "Location country for address purpose"
    },
    state: {
      field: "state_id",
      type: DataTypes.INTEGER(4),
      allowNull: true,
      comment: "Location state for address purpose"
    },
    city: {
      field: "city_id",
      type: DataTypes.INTEGER(5),
      allowNull: true,
      comment: "Location city for address purpose"
    },
    landmark: {
      field: "landmark",
      type: DataTypes.STRING(255),
      allowNull: true,
      comment: "Location landmark"
    },
    address: {
      field: "address",
      type: DataTypes.TEXT,
      allowNull: true,
      comment: "Location address"
    },
    status: {
      field: "status",
      type: DataTypes.ENUM('ACTIVE','INACTIVE','DELETED'),
      allowNull: false,
      defaultValue: 'ACTIVE',
      comment: "Weather a location is active, inactive or deleted"
    },
    createdBy: {
      field: "created_by",
      type: DataTypes.INTEGER(11),
      allowNull: true,
      comment: "User ID who created this location"
    },
    updatedBy: {
      field: "updated_by",
      type: DataTypes.INTEGER(11),
      allowNull: true,
      comment: "User ID who updated this location"
    }
  },
  {
    timestamps: true,
    underscored: true,
    freezeTableName:true,
    tableName:'location',
    defaultScope: {
      where: {
        status: 'ACTIVE'
      }
    },
    classMethods:{
      associate:function(models){
        Location.belongsTo(models.Company, { foreignKey:'company_id', foreignKeyConstraint:true} );
      }
    }
  });

  return Location;
};

Now I expected that It will sequelize.sync({force:true}) will create both tables and add a foreign key index to the location table. But it is only create tables. There is no any column named company_id on location table.

Manish Jangir Manish Jangir · Accepted Answer · 2016-05-31T13:24:32

Resolved the problem at my own by writing the few lines of code in my singleton module of sequelize:

Object.keys(db).forEach(function(modelName) {
  if(db[modelName].hasOwnProperty('associate')) {
    db[modelName].associate(db);
  }
});

The code will iterate over all the models and call their associate methods directly as they have been defined as classMethods.

Sequelize belongsTo not adding foreign key constraints to database table

1 Answers