Sequelize保存/创建方法仅在CRUD应用程序上第一次工作

Question

一般问题：
我一直在努力使用JavaScript+nodeJS+express+sequelize(MySQL(构建一个简单的CRUD应用程序。

项目的上下文化
我的CRUD应用程序正在开发中，用于管理特定英语老师的学生
创建了一些表模型：Alunos-英语：Students、Boletos-英语：Pay Order和Aulas-English:Classes，以及其他目前不必解释此问题的表模型。

具体问题：
有一个post路由，它占用了我的一些身体内容，并将一个新学生插入到表中"Alunos"；。在创建了包含学生数据的行之后，我需要为"学生"创建注册表；Boletos"；，这将是在该表中登记的12个月的支付订单。这部分有两个问题：第一次我注册了一个学生，它工作得很好，但我无法获得模型生成的自动递增id来插入外键"AlunoId"；，所以"的外键；Boletos"表设置为null另一个问题是，这两个条目("Alunos"中的1个条目和"Boletos"的12个条目(起初运行良好，可以注册第一个学生，但在刷新页面并尝试注册另一个学生后，节点JS抛出错误：

(node:5720) UnhandledPromiseRejectionWarning: SequelizeUniqueConstraintError: Validation error
at Query.formatError (D:ProgramacaoEstudosProjetoCRUD2node_modulessequelizelibdialectsmysqlquery.js:242:16)
at Query.run (D:ProgramacaoEstudosProjetoCRUD2node_modulessequelizelibdialectsmysqlquery.js:77:18)
at processTicksAndRejections (internal/process/task_queues.js:93:5)
at async D:ProgramacaoEstudosProjetoCRUD2node_modulessequelizelibsequelize.js:619:16
at async MySQLQueryInterface.insert (D:ProgramacaoEstudosProjetoCRUD2node_modulessequelizelibdialectsabstractquery-interface.js:749:21)
at async model.save (D:ProgramacaoEstudosProjetoCRUD2node_modulessequelizelibmodel.js:3954:35)
at async D:ProgramacaoEstudosProjetoCRUD2routesadmin.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)

代码：

1. 型号-Alunos：

// 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;
};

2. 型号-Boletos：

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
}

路线-后注册：

// 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');
});

最后的注意事项：由于我是节点JS和JavaScript的新手，所以我不知道Promises和语法糖await/async。我已经学习了很多视频，我想我已经掌握了关于它的基本概念，但我无法将其应用到项目中。

Answer 1

您需要使用db.Aluno.create()或设置db.Aluno.build({...}, { isNewRecord: true })，让Sequelize知道这是一个插入，而不是主键值为0的记录。您的DB可能会看到ID为0，然后插入或将其设置为1，无论哪种方式，您都会在第二次插入时发生冲突。

将路由器/控制器代码封装在try/catch中以处理任何错误也是一个好主意。使用一个传递给所有查询/插入的事务，以便在任何阶段出现错误时都可以将它们全部回滚-const transaction=wait sequelize.transaction((；const aluno=等待数据库。Aluno.create(｛…｝，｛transaction｝(；。在末尾使用waittransaction.commit()提交，或者在catch块中使用await transaction.rollback()回滚。

不要在for循环中使用wait——这与阻塞每个调用一样，效率低且速度慢。相反，您可以将一系列Promise传递给Promise.all((并同时解析它们。。。。objList.push(boleto.save());(不要在这里等待(，然后是await Promise.all(objList);。

最后要注意的是，当变量不会改变时，最好使用const，当可能使结果更加一致时，可以使用let。您还应该尝试从箭头函数显式返回。

这是应用了更改的代码。

// 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);
}
});