Friday, 17 March 2017

Simple example of many-to-many relation using Sequelize

I'm trying to build a simple example of many-to-many relation between tables using Sequelize. However, this seems to be way trickier than I expected.

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

When running this I get:

Cannot add foreign key constraint

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:

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!

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

DROP TABLE IF EXISTS mentees;

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

How to solve these?

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.



via Ionică Bizău

No comments:

Post a Comment