Creating Associations with sequelize and mysql

Question

I am trying to create seqeulize models in nodejs app on mysql db. However, When I run the codes, foreign keys are not being created as intended.

This are my models:

Category Model

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define("Category", {
    category_id: {
      type: DataTypes.STRING(255),
      unique: true,
      primaryKey: true,
    },
    name: { type: DataTypes.STRING, unique: true, allowNull: false },
    slug: { type: DataTypes.STRING, unique: true, allowNull: false },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("CURRENT_TIMESTAMP"),
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal(
        "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
      ),
    },
  });

  Category.associate = (models) => {
    Category.hasMany(models.Product);
  };

  return Category;
};

Product Model

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Product = sequelize.define("Product", {
    product_id: {
      type: DataTypes.STRING(255),
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    slug: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    sku: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    short_description: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    description: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    price: {
      type: DataTypes.DECIMAL,
      allowNull: false,
      min: 1,
    },
    discount: {
      type: DataTypes.DECIMAL,
      allowNull: true,
    },
    quantity: {
      type: DataTypes.INTEGER,
    },
    vat: {
      type: DataTypes.DECIMAL,
      allowNull: true,
    },
    tags: {
      type: DataTypes.STRING(255),
      allowNull: true,
    },
    rate: {
      type: DataTypes.INTEGER,
      allowNull: true,
    },
    points: {
      type: DataTypes.INTEGER,
      allowNull: true,
    },
    new: {
      type: DataTypes.VIRTUAL,
      get() {
        var msPerDay = 8.64e7;

        // Copy dates so don't mess them up
        var x0 = new Date(this.getDataValue("created_at"));
        var x1 = new Date();

        // Set to noon - avoid DST errors
        x0.setHours(12, 0, 0);
        x1.setHours(12, 0, 0);

        // Round to remove daylight saving errors
        return Math.round((x1 - x0) / msPerDay) <= 30;
      },
      set(value) {
        throw new Error("Can't set Product.new property.");
      },
    },
    thumb_image: {
      type: DataTypes.STRING,
      allowNull: true,
      get() {
        if (this.getDataValue("thumb_image")) {
          return JSON.parse(this.getDataValue("thumb_image"));
        }
        return [];
      },
      set(val) {
        this.setDataValue("thumbImage", JSON.stringify(val));
      },
    },
    images: {
      type: DataTypes.STRING,
      allowNull: true,
      get() {
        if (this.getDataValue("images")) {
          return JSON.parse(this.getDataValue("images"));
        }
        return [];
      },
      set(val) {
        this.setDataValue("images", JSON.stringify(val));
      },
    },
    featured: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("CURRENT_TIMESTAMP"),
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal(
        "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
      ),
    },
  });
  Product.associate = (models) => {
    Product.belongsTo(models.Category, {
      foreignKey: "category_id",
    });
    Product.belongsTo(models.Brand, {
      foreignKey: "brand_id",
    });
    Product.belongsTo(models.CartItem, {
      foreignKey: "cart_id",
    });
  };
  return Product;
};

Brand Model

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Brand = sequelize.define("Brand", {
    brand_id: {
      type: DataTypes.STRING(255),
      unique: true,
      primaryKey: true,
    },
    name: { type: DataTypes.STRING, unique: true, allowNull: false },
    slug: { type: DataTypes.STRING, unique: true, allowNull: false },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal("CURRENT_TIMESTAMP"),
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal(
        "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
      ),
    },
  });

  Brand.associate = (models) => {
    Brand.hasMany(models.Product);
  };
  return Brand;
};

And these is my Models/index.js file

/* eslint-disable no-undef */
"use strict";

const fs = require("fs");
const path = require("path");
const Sequelize = require("sequelize");
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || "development";
const config = require(__dirname + "/../../config/config.js")[env];
const db = {};

let sequelize;

if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(
    config.database,
    config.username,
    config.password,
    config
  );
}

db.sequelize = sequelize;
db.Sequelize = Sequelize;

fs.readdirSync(__dirname)
  .filter((file) => {
    return (
      file.indexOf(".") !== 0 && file !== basename && file.slice(-3) === ".js"
    );
  })
  .forEach((file) => {
    const model = require(path.join(__dirname, file))(
      sequelize,
      Sequelize.DataTypes
    );
    db[model.name] = model;
  });

Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize
  .sync({ force: process.env.NODE_ENV === "development" })
  .then(() => {
    console.log("Drop and re-sync db.");
  });

module.exports = db;

Now when I run the server, I expected the products table to have the fields category_id and brand_id. However, am getting additional fields brand_brand_id and category_category_id on the products table.

Besides, I cannot retrieve the categories, brands and products with the include properties when fecthing data.

What I want is to have the products table properly created in the database with the required foreign keys

user89M5 · Answer

Since you have customized foreign keys you have to indicate them in both paired associations:

Category.associate = (models) => {
    Category.hasMany(models.Product, { foreignKey: "category_id" });
};
Product.associate = (models) => {
    Product.belongsTo(models.Category, {
      foreignKey: "category_id",
    });
    Product.belongsTo(models.Brand, {
      foreignKey: "brand_id",
    });
    Product.belongsTo(models.CartItem, {
      foreignKey: "cart_id",
    });
};
Brand.associate = (models) => {
    Brand.hasMany(models.Product, { foreignKey: "brand_id" });
};

Creating Associations with sequelize and mysql

1 Answers