I am adding a new column "companyCode" to an existing table "company", and it shouldn't be null. The value of this column will vary from one raw to another as it takes the first three letters of the company's name (there is a column "name" includes company's name), I tried using Sequelize.litereal('//a query within') and read from "name" column but it did not accept it, So I am trying to let default value takes a function, but still does not work, is there another way to solve this issue? here is my code:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface
.addColumn('company', 'companyCode', {
type: Sequelize.STRING(3),
allowNull: false,
defaultValue: Sequelize.fn(() => {
let query = 'SELECT name FROM company';
return queryInterface.sequelize.query(query, {type: Sequelize.QueryTypes.SELECT})
.then(companies => {
console.log(companies);
return companies.map(company => {
return {
companyCode: company.name.slice(0, 3).toUpperCase(),
};
});
})
.then(data => {
if (!data.length) return;
return queryInterface.bulkInsert('company', data);
});
}),
});
},
down: (queryInterface) => {
return queryInterface.removeColumn('company', 'companyCode');
}
};```
I would create all of it and then run a script to insert inside the problem that might be from the async way of working.
After trying many times, It could be solved with a promise that holds a query as following:
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.addColumn(
'company',
'companyCode',
{
defaultValue: '',
allowNull: false,
type: Sequelize.STRING(3)
}
).then(function (result) {
return queryInterface.sequelize.query('UPDATE company SET "companyCode"=LEFT("name",3)');
}
);
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('company', 'companyCode');
}
};```