Hi I'm new to migrations in sequelize, and I'm not sure how to add field/property for attributes. So my scenario is that I have two attributes sku & barcode, but I forgot to add unique: true. Now I need to edit the table, I have tried with addColumn, changeColumn and addIndex, but nothing works. I don't know if my approach is correct or not, please help.
Here my migration approach
"use strict";
module.exports = {
async up(queryInterface, Sequelize) {
/**
* Add altering commands here.
*
* Example:
* await queryInterface.createTable('users', { id: Sequelize.INTEGER });
*/
// *********** received duplicate error ***********
// await queryInterface.addColumn("Products", "sku", {
// unique: true,
// type: Sequelize.STRING,
// allowNull: false,
// validate: {
// notNull: {
// msg: "Product SKU cannot be empty",
// },
// notEmpty: {
// msg: "Product SKU cannot be empty",
// },
// },
// });
// await queryInterface.addColumn("Products", "barcode", {
// unique: true,
// type: Sequelize.STRING,
// allowNull: false,
// validate: {
// notNull: {
// msg: "Product barcode cannot be empty",
// },
// notEmpty: {
// msg: "Product barcode cannot be empty",
// },
// },
// });
// ***************** received Cannot create property 'fields' ***********
// await queryInterface.addIndex("Products", "sku", {
// unique: true,
// });
// await queryInterface.addIndex("Products", "barcode", {
// unique: true,
// });
// **************** received Validation error *************
await queryInterface.changeColumn("Products", "sku", {
unique: true,
type: Sequelize.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product SKU cannot be empty",
},
notEmpty: {
msg: "Product SKU cannot be empty",
},
},
});
await queryInterface.changeColumn("Products", "barcode", {
unique: true,
type: Sequelize.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product barcode cannot be empty",
},
notEmpty: {
msg: "Product barcode cannot be empty",
},
},
});
},
async down(queryInterface, Sequelize) {
/**
* Add reverting commands here.
*
* Example:
* await queryInterface.dropTable('users');
*/
// await queryInterface.removeColumn("Products", "sku");
// await queryInterface.removeColumn("Products", "barcode");
},
};
Here how my model looks like:
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class Product extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
Product.belongsTo(models.Brand, {
foreignKey: "brandId",
});
Product.belongsTo(models.Category, {
foreignKey: "categoryId",
as: "Category",
});
Product.belongsTo(models.Category, {
foreignKey: "subcategoryId",
as: "Subcategory",
});
Product.belongsToMany(models.Tag, {
through: models.ProductTag,
foreignKey: "productId",
});
Product.hasMany(models.ProductInfo, {
foreignKey: "productId",
});
Product.hasMany(models.ProductPhoto, {
foreignKey: "productId",
});
Product.hasMany(models.ProductStockHistory, {
foreignKey: "productId",
});
Product.hasMany(models.Trolley, {
foreignKey: "productId",
targetKey: "id",
});
Product.hasMany(models.Wishlist, {
foreignKey: "productId",
targetKey: "id",
});
Product.belongsToMany(models.PromoPrice, {
through: models.ProductPromoPrice,
foreignKey: "ProductId",
});
Product.hasOne(models.ProductPromoPrice, {
foreignKey: "ProductId",
as: "SpecialPrice",
});
}
}
Product.init(
{
sku: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product SKU cannot be empty",
},
notEmpty: {
msg: "Product SKU cannot be empty",
},
},
},
barcode: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product barcode cannot be empty",
},
notEmpty: {
msg: "Product barcode cannot be empty",
},
},
},
name: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product name cannot be empty",
},
notEmpty: {
msg: "Product name cannot be empty",
},
},
},
description: {
type: DataTypes.TEXT,
allowNull: false,
validate: {
notNull: {
msg: "Product description cannot be empty",
},
notEmpty: {
msg: "Product description cannot be empty",
},
},
},
brandId: {
type: DataTypes.INTEGER,
allowNull: false,
validate: {
notNull: {
msg: "Product brandId cannot be empty",
},
notEmpty: {
msg: "Product brandId cannot be empty",
},
},
},
categoryId: {
type: DataTypes.INTEGER,
allowNull: false,
validate: {
notNull: {
msg: "Product categoryId cannot be empty",
},
notEmpty: {
msg: "Product categoryId cannot be empty",
},
},
},
subcategoryId: {
type: DataTypes.INTEGER,
allowNull: false,
validate: {
notNull: {
msg: "Product subcategoryId cannot be empty",
},
notEmpty: {
msg: "Product subcategoryId cannot be empty",
},
},
},
unit: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product unit cannot be empty",
},
notEmpty: {
msg: "Product unit cannot be empty",
},
},
},
price: {
type: DataTypes.INTEGER,
allowNull: false,
validate: {
notNull: {
msg: "Product price cannot be empty",
},
notEmpty: {
msg: "Product price cannot be empty",
},
},
},
notes: DataTypes.STRING,
photoURL: {
type: DataTypes.STRING,
allowNull: false,
validate: {
notNull: {
msg: "Product photo URL cannot be empty",
},
notEmpty: {
msg: "Product photo URL cannot be empty",
},
isUrl: {
msg: "Invalid product photo URL",
},
},
},
videoURL: {
type: DataTypes.STRING,
validate: {
isUrl: {
msg: "Invalid product video URL",
},
},
},
isActive: {
type: DataTypes.BOOLEAN,
allowNull: false,
validate: {
notNull: {
msg: "Product isActive cannot be empty",
},
notEmpty: {
msg: "Product isActive cannot be empty",
},
},
},
stock: {
type: DataTypes.INTEGER,
allowNull: false,
validate: {
notNull: {
msg: "Product stock cannot be empty",
},
notEmpty: {
msg: "Product stock cannot be empty",
},
},
},
reservedStock: DataTypes.INTEGER,
sold: {
type: DataTypes.INTEGER,
allowNull: false,
defaultValue: 0,
},
firestoreId: DataTypes.STRING,
buyPrice: {
type: DataTypes.INTEGER,
defaultValue: 0,
},
},
{
hooks: {
beforeCreate(Product, options) {
Product.stock = Product.stock || 0;
Product.reservedStock = 0;
},
},
sequelize,
modelName: "Product",
// edited here
paranoid: true,
}
);
return Product;
};
I just need to add unique: true in sku & barcode, please help. Thanks
EDITED
here is what I received in terminal when I use addConstraint
