Simple example of many-to-many relation using Sequelize

Question

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.

Answer 1

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.

Note: when creating migration files remember about timestamps fields - createdAt, updatedAt and, eventually, deletedAt.

Sync

Personally I use sync() only when I run the tests - this may be shown in three steps

1. perform sequelize.sync({ force: true }) in order to synchronize all models
2. run some database seeds (also can be done via sequelize-cli),
3. run tests.

This is very comfortable because allows you to clean the database before running tests, and, in order to distinguish development from tests, tests can use different database e.g. project_test, so that the development database stays intact.

Many-to-many

Now let's move on to your problem - m:n relation between two models. First of all, due to the fact that you perform Promise.all(), the sync can run in different order than you add the functions in it. In order to avoid this situation I suggest you use mapSeries feature of Bluebird promise, which Sequelize uses and exposes under sequelize.Promise (this is also the reason of your last error about deleting parent row - you try to delete mentees which is referenced from menteequestion).

sequelize.Promise.mapSeries([
    Mentee.sync({ force: true })
  , Question.sync({ force: true })
  , MenteeQuestion.sync({ force: true })
], (model) => { return model.destroy({ where: {} }); }).then(() => {

});

First parameter of mapSeries is array of promises, however the second one is a function which is run with the result of each previously defined promise. Due to the fact that Model.sync() results in the Model itself, we can perform model.destroy() at each iteration.

After that you can insert some data to the database via create(), just as in the example. Now time to fix the Error: mentee is not associated to menteequestion! error. It occurs because you have associated Mentee with Question but there is no association between MenteeQuestion and Mentee (or Question). In order to fix that, after belongsToMany, you can add

MenteeQuestion.belongsTo(Mentee, { foreignKey: 'menteeId' });
MenteeQuestion.belongsTo(Question, { foreignKey: 'questionId' });

Now you are able to add include: [Mentee, Question] when querying MenteeQuestion. You would also run on another error while doing toJSON(), because you do findAll which returns array of instances. You could do forEach()

menteeQuestions.forEach(menteeQuestion => {
    console.log(menteeQuestion.toJSON());
});