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
CodePudding user response：
Refer to the queryInterface API here addConstraint removeConstraint
A transaction was made to ensure that all the queries succeed together. The name field in the second parameter of queryInterface.addConstraint can be anything. If any two columns have the same name of constraint, then a composite constraint will be created.
module.exports = {
async up(queryInterface, Sequelize) {
return queryInterface.sequelize.transaction((transaction) => {
return Promise.all([
queryInterface.addConstraint("Products", {
fields: ["barcode"],
type: "unique",
name: "Products_barcode_uk",
transaction,
}),
queryInterface.addConstraint("Products", {
fields: ["sku"],
type: "unique",
name: "Products_sku_uk",
transaction,
}),
]);
});
},
async down(queryInterface, Sequelize) {
return queryInterface.sequelize.transaction((transaction) => {
return Promise.all([
queryInterface.removeConstraint("Products", "Products_barcode_uk", {
fields: ["barcode"],
transaction,
}),
queryInterface.removeConstraint("Products", "Products_sku_uk", {
fields: ["sku"],
transaction,
}),
]);
});
},
};