General issue:
I've been having a hard time trying to build a simple CRUD app using JavaScript + node JS + express + sequelize (MySQL).
Contextualization about the project
My CRUD app is being developed to manage students from a particular English teacher.
There are some table models created: Alunos - English: Students, Boletos - English: Pay Order, Aulas - English: Classes, and others that are not mandatory to explain for this issue for now.
Specific issue:
There is a post route that takes some of my body contents and inserts a new student to table "Alunos". After the row containing student data is created, I need to create registries for "Boletos", it would be 12 month of Pay Orders registered into this table. There are two problems with this part: the first I register a student, it works fine, but I could not get the auto-increment id generated by the model to insert in the foreign key "AlunoId", so the foreign key for "Boletos" table is set to null. The other problem is that the both entries (1 into "Alunos" and 12 into "Boletos") works fine at first, to registry the first student, but after the page is refreshed and I try to registry another student, node JS throws an error:
(node:5720) UnhandledPromiseRejectionWarning: SequelizeUniqueConstraintError: Validation error
at Query.formatError (D:\ProgramacaoEstudos\ProjetoCRUD2\node_modules\sequelize\lib\dialects\mysql\query.js:242:16)
at Query.run (D:\ProgramacaoEstudos\ProjetoCRUD2\node_modules\sequelize\lib\dialects\mysql\query.js:77:18)
at processTicksAndRejections (internal/process/task_queues.js:93:5)
at async D:\ProgramacaoEstudos\ProjetoCRUD2\node_modules\sequelize\lib\sequelize.js:619:16
at async MySQLQueryInterface.insert (D:\ProgramacaoEstudos\ProjetoCRUD2\node_modules\sequelize\lib\dialects\abstract\query-interface.js:749:21)
at async model.save (D:\ProgramacaoEstudos\ProjetoCRUD2\node_modules\sequelize\lib\model.js:3954:35)
at async D:\ProgramacaoEstudos\ProjetoCRUD2\routes\admin.js:101:30
(node:5720) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
Code:
// Create Aluno table model and export to be called to other file
module.exports = (sequelize, DataTypes) => {
const Aluno = sequelize.define('Aluno', {
name: {
type: DataTypes.STRING,
allowNull: false,
},
surname: {
type: DataTypes.STRING,
allowNull: false,
},
birth: {
type: DataTypes.DATEONLY,
allowNull: false,
},
phone_number: {
type: DataTypes.STRING,
allowNull: true,
unique: true,
},
mobile_number: {
type: DataTypes.STRING,
allowNull: false,
},
email: {
type: DataTypes.STRING,
allowNull: false,
},
residential_address: {
type: DataTypes.STRING,
allowNull: false,
},
profession: {
type: DataTypes.STRING,
allowNull: false,
},
company: {
type: DataTypes.STRING,
allowNull: false,
},
num_classes_week: {
type: DataTypes.INTEGER,
allowNull: false,
},
classes_day: {
type: DataTypes.STRING,
allowNull: false,
},
classes_time: {
type: DataTypes.TIME,
allowNull: false,
},
starts_at: {
type: DataTypes.DATEONLY,
allowNull: false,
},
value: {
type: DataTypes.FLOAT,
allowNull: false,
},
discount: {
type: DataTypes.FLOAT,
defaultValue: 0,
},
due_date: {
type: DataTypes.DATEONLY,
allowNull: false,
},
});
Aluno.associate = (models) => {
Aluno.hasMany(models.Aula, {
onDelete: 'CASCADE',
});
};
Aluno.associate = (models) => {
Aluno.hasMany(models.Boleto, {
onDelete: 'NO ACTION',
});
};
Aluno.associate = (models) => {
Aluno.hasMany(models.Assiste_Aula, {
onDelete: 'NO ACTION',
});
};
return Aluno;
};
module.exports = (sequelize, DataTypes) => {
const Boleto = sequelize.define('Boleto', {
value: {
type: DataTypes.FLOAT,
allowNull: false
},
due_date: {
type: DataTypes.DATEONLY,
allowNull: false
},
status: {
type: DataTypes.INTEGER,
defaultValue: 0,
allowNull: false
}
})
Boleto.associate = (models) => {
Boleto.belongsTo(models.Aluno, {
foreignKey: 'AlunoId'
})
}
return Boleto
}
Routes - Post - registry:
// Post Routes
router.post('/realizado', async (req, res) => {
// Create Aluno
// Compute values of some attributes
var cls_day = req.body.classes_day;
var num_cls = cls_day.length;
var dias;
for (let i = 0; i < num_cls; i++) {
if (i + 1 < num_cls) {
dias += cls_day[i] + '-';
} else {
dias += cls_day[i];
}
}
// Instantiate Aluno model
const aluno = db.Aluno.build({
name: req.body.name,
surname: req.body.surname,
birth: req.body.birth,
phone_number: req.body.phone_number,
mobile_number: req.body.mobile_number,
email: req.body.email,
residential_address: req.body.residential_address,
profession: req.body.profession,
company: req.body.company,
num_classes_week: num_cls,
classes_day: dias,
classes_time: req.body.classes_time,
starts_at: req.body.starts_at,
value: req.body.value,
discount: req.body.discount,
due_date: req.body.due_date,
});
// Insert into database
const newAluno = await aluno.save();
// Create boleto
var objList = [];
for (var i = 0; i < 12; i++) {
var firstDt = new Date(req.body.due_date);
// Compute current date
var dt = firstDt.setMonth(firstDt.getMonth() + i);
//dt.setDate(dt.getMonth() + i)
// Build boleto object
var boleto;
boleto = db.Boleto.build({
value: req.body.value,
due_date: dt,
alunoId: newAluno.id,
});
objList.push(boleto);
}
for (var i = 0; i < objList.length; i++) {
try {
await objList[i].save();
} catch (err) {
console.log(err);
}
}
res.render('realizado');
});
Final Considerations:
Since I'm new with node JS and JavaScript I didn't know about the Promises and the syntactic sugar await/async. I've already studied with a bunch of videos and I think I got the basics concepts about it, but I'm not being able to apply it to the project.
You need to either use db.Aluno.create() or set db.Aluno.build({...}, { isNewRecord: true }) to let Sequelize know it's an insert and not a record with a primary key value of 0. Your DB likely sees the ID of 0 and either inserts it or sets it to 1, either way you will get a conflict on the second insert.
It's also a good idea to wrap your router/controller code in a try/catch to handle any errors. Use a transaction that is passed to all your queries/inserts so that you can roll them all back if there is an error at any stage - const transaction = await sequelize.transaction(); const aluno = await db.Aluno.create({...}, { transaction });. Commit at the end with await transaction.commit() or in the catch block roll back with await transaction.rollback().
Don't use await in a for loop - its the same as blocking for each call which is inefficient and slow. Instead you can pass an array of promises to Promise.all() and resolve them concurrently.... objList.push(boleto.save()); (don't await here) and then await Promise.all(objList);.
Some last notes - it's good to use const when a variable won't change and let when it might to make your results more consistent. You should also try to have an explicit return from arrow functions.
Here is your code with the changes applied.
// Post Routes
router.post('/realizado', async (req, res) => {
// we may or may not be able to create a transaction, so use let
let transaction;
try {
// start a new transaction an pass to all the create() calls
transaction = await sequelize.transaction();
// Compute values of some attributes
var cls_day = req.body.classes_day;
var num_cls = cls_day.length;
var dias;
for (let i = 0; i < num_cls; i++) {
if (i + 1 < num_cls) {
dias += cls_day[i] + '-';
} else {
dias += cls_day[i];
}
}
// Create Aluno model, use const since it won't change
const aluno = await db.Aluno.create(
{
name: req.body.name,
surname: req.body.surname,
birth: req.body.birth,
phone_number: req.body.phone_number,
mobile_number: req.body.mobile_number,
email: req.body.email,
residential_address: req.body.residential_address,
profession: req.body.profession,
company: req.body.company,
num_classes_week: num_cls,
classes_day: dias,
classes_time: req.body.classes_time,
starts_at: req.body.starts_at,
value: req.body.value,
discount: req.body.discount,
due_date: req.body.due_date,
},
{
// use the transaction
transaction,
}
);
// Create boleto insert promise array
// Use const because we will be adding items, but into the same const array
const promises = [];
for (let i = 0; i < 12; i++) {
const firstDt = new Date(req.body.due_date);
// put the promise into the promises array
promises.push(
// the create call here will start the insert but not wait for it to complete
db.Boleto.create(
{
value: req.body.value,
due_date: firstDt.setMonth(firstDt.getMonth() + i),
alunoId: aluno.id,
},
{
// use the transaction so we can rollback if there are errors
transaction,
}
)
);
}
// await the result of all the boleto inserts
await Promise.all(promises);
// no errors, we can commit the transaction
await transaction.commit();
return res.render('realizado');
} catch (err) {
console.log(err);
if (transaction) {
await transaction.rollback;
}
return res.status(500).send(err.message);
}
});