使用 Sequelize 的多对多关系的简单示例

This is the code I have currently (the ./db.js file exports the Sequelize connection instance).

const Sequelize = require("sequelize");
const sequelize = require("./db");

var Mentee = sequelize.define('mentee', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING
    }
});

var Question = sequelize.define('question', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    text: {
        type: Sequelize.STRING
    }
});

var MenteeQuestion = sequelize.define('menteequestion', {
//    answer: {
//        type: Sequelize.STRING
//    }
});

// A mentee can answer several questions
Mentee.belongsToMany(Question, { as: "Questions", through: MenteeQuestion });

// And a question can be answered by several mentees
Question.belongsToMany(Mentee, { as: "Mentees", through: MenteeQuestion });

let currentQuestion = null;
Promise.all([
    Mentee.sync({ force: true })
  , Question.sync({ force: true })
  , MenteeQuestion.sync({ force: true })
]).then(() => {
    return Mentee.destroy({where: {}})
}).then(() => {
    return Question.destroy({ where: {} })
}).then(() => {
    return Question.create({
        text: "What is 42?"
    });
}).then(question => {
    currentQuestion = question;
    return Mentee.create({
        name: "Johnny"
    })
}).then(mentee => {
    console.log("Adding question");
    return mentee.addQuestion(currentQuestion);
}).then(() => {
    return MenteeQuestion.findAll({
        where: {}
      , include: [Mentee]
    })
}).then(menteeQuestions => {
    return MenteeQuestion.findAll({
        where: {
            menteeId: 1
        }
      , include: [Mentee]
    })
}).then(menteeQuestion => {
    console.log(menteeQuestion.toJSON());
}).catch(e => {
    console.error(e);
});

运行时我得到:

不能添加外键约束

我认为这是因为 id 类型——但我不知道它出现的原因以及我们如何修复它.

I think that is because of the id type—however I have no idea why it appears and how we can fix it.

前一个错误不会出现时出现的另一个错误是:

Another error which appeared when the previous one won't appear was:

执行(默认):INSERT INTO menteequestions (menteeId,questionId,createdAt,updatedAt) VALUES (2,1,'2017-03-17 06:18:01','2017-03-17 06:18:01');

Executing (default): INSERT INTO menteequestions (menteeId,questionId,createdAt,updatedAt) VALUES (2,1,'2017-03-17 06:18:01','2017-03-17 06:18:01');

错误:被指导者与被指导者问题没有关联！

Error: mentee is not associated to menteequestion!

另外，我得到的另一个错误——我认为这是因为 sync 中的 force:true——是:

Also, another error I get—I think it's because of force:true in sync—is:

如果存在mentees，则删除表；

DROP TABLE IF EXISTS mentees;

ER_ROW_IS_REFERENCED:无法删除或更新父行:外键约束失败

ER_ROW_IS_REFERENCED: Cannot delete or update a parent row: a foreign key constraint fails

如何解决这些问题?

再次，我只需要一个多对多 crud 操作的最小示例(在这种情况下只是插入和读取)，但这似乎超出了我的理解.为此苦苦挣扎了两天.

Again, I only need a minimal example of many-to-many crud operations (in this case just insert and read), but this seems to be beyond my understanding. Was struggling for two days with this.

推荐答案

迁移

我建议你使用 sequelize migrations 而不是 sync() 在每个模型上.有一个模块 - sequelize.cli 可让您轻松管理迁移和种子.它以某种方式通过在项目的 /models 目录中创建初始化文件 index.js 来强制项目结构.它假定您的所有模型定义都将在此目录中.此脚本遍历所有模型文件(每个模型定义在单独的文件中，例如 mentee.js、question.js)并执行 sequelize.import() 以便将这些模型分配给 sequelize 实例 - 这允许您稍后通过 sequelize[modelName] 访问它们，例如sequelize.question.

Migrations

I would suggest you use sequelize migrations instead doing sync() on each model. There is a module - sequelize.cli that allows you to manage migrations and seeds easily. It, in some way, forces a project structure by creating initialization file index.js inside /models directory of the project. It assummes that all your model definitions will be in this directory. This script iterates through all the model files (each model definition is in separate file e.g. mentee.js, question.js) and performs sequelize.import() in order to assign those models to the sequelize instance - this lets you access them later via sequelize[modelName] e.g. sequelize.question.