Many to Many Relationships in MYSQL Database Using Sequelize ORM

Before we start building many-to-many relationships, let’s take a step back and discuss what those relationships actually are. To do that we will use the example of movies and actors. A movie can have many actors and one actor can work in several movies. This means each actor is connected to many movies and each movie is connected to many actors.

Here we will start with database design. If you would like to learn to set up your sequelize project you can check out my previous article, One to One Relationships in MYSQL Database Using Sequelize ORM.

Question: Why can’t you just put a foreign key in either the movies table or actors table?

Well, it’s quite simple if you think about it. Let’s say you put the foreign key on the movie table and it is like this.

Now, as you can see, we are repeating data in different rows and it will create a huge mess. To solve this we are going to use something called a junction table. This will be a third table other than the actor and movie table. And our final tables will look something like this.

Movies Table

Actor Table

Junction Table to show the relation between actors and movies.

As you can clearly see, we have created a many-to-many relationship in our schema using a third junction table. Now all that is left to do is create it using sequelize.

Fire up your vs code, or whatever code editor you prefer, set up a sequelize project, and connect it to the database. I have already provided a link to my previous article where you can see how to do that. Also, run the command nodemon app.js, this will keep your server running.
Let’s start by creating our movie model first.
Go to the models folder in the root directory and create a file called movie.js. This file will be our model’s definition, it will have an auto-incrementing ID that will be used as a primary key and movie name. To do this, paste the following code in the file you just created.

1
2
3
4
5
6
7
8
9
10
11
12
13
module.exports = (sequelize, DataTypes) => {
  const Movie = sequelize.define("Movie", {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING,
    },
  });
  return Movie;
};

Now let’s create the actor table. In the same folder, create a file called actor.js, and paste the following code.

1
2
3
4
5
6
7
8
9
10
11
12
13
module.exports = (sequelize, DataTypes) => {
  const Actor = sequelize.define("Actor", {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING,
    },
  });
  return Actor;
};

If you now go to your MySQL workbench and refresh the tables then you will be able to see both the tables.

Let’s create some APIs to populate these tables with data and after this, we will create the junction table.

This is the API to create data in the movies table.

1
2
3
4
5
6
7
8
9
app.post("/movie", async (req, res) => {
  const data = req.body;
  try {
    const Movie = await db.Movie.create(data);
    res.send(Movie);
  } catch (err) {
    res.send(err);
  }
});

Now hit the post request at the url http://localhost:3000/movie and send the following data in the body.

1
2
3
{
  "name": "Harry Potter"
}

You will see the following value in the output

1
2
3
4
5
6
{
  "id": 1,
  "name": "Harry Potter",
  "updatedAt": "2021-09-29T16:32:11.061Z",
  "createdAt": "2021-09-29T16:32:11.061Z"
}

Repeat this process for as many movies as you want. In the end, our movie table looks like this.

Let’s make an API to add data to the actor table.

1
2
3
4
5
6
7
8
9
10
app.post('/actor', async (req, res) => {

  const data = req.body;
  try {
    const Actor = await db.Actor.create(data);
    res.send(Actor);
  } catch (err) {
    res.send(err);
  }
})

Now hit the post request at the URL http://localhost:3000/actor and send the following data in the body.

1
2
3
{
  "name": "Emma Watson"
}

The output will look somewhat like this.

1
2
3
4
5
6
{
  "id": 1,
  "name": "Emma Watson",
  "updatedAt": "2021-09-29T16:38:12.655Z",
  "createdAt": "2021-09-29T16:38:12.655Z"
}

Repeat this process for as many actors as you want. In the end, our actor table looks like this.

I am writing my complete app.js file here for your reference

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
const express = require("express");
const app = express();
const PORT = 3000;
const db = require("./models");
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));

app.post("/movie", async (req, res) => {
  const data = req.body;
  try {
    const Movie = await db.Movie.create(data);
    res.send(Movie);
  } catch (err) {
    res.send(err);
  }
});

app.post('/actor', async (req, res) => {

  const data = req.body;
  try {
    const Actor = await db.Actor.create(data);
    res.send(Actor);
  } catch (err) {
    res.send(err);
  }
})

db.sequelize
  .sync()
  .then((result) => {
    app.listen(3000, () => {
      console.log("Server started");
    });
  })
  .catch((err) => {
    console.log(err);
  });

Let’s now create a junction table. Create a junction.js file in your model file. It will have an auto-incrementing id, movieId, actorId, and it will have two associations, one to the movie table through movieId and other to actor table through actorId. Paste the following code in junction.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
module.exports = (sequelize, DataTypes) => {
  const Junction = sequelize.define("Junction", {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    movieId: {
      type: DataTypes.INTEGER,
    },
    actorId: {
      type: DataTypes.INTEGER,
    },
  });
  Junction.associate = models => {
    Junction.belongsTo(models.Actor, {
      foreignKey: 'actorId'
    });
    Junction.belongsTo(models.Movie, {
      foreignKey: 'movieId'
    });
  }
  return Junction;
};

Now, we finally have our junction table ready, and it’s time to fill in the data using the API. Create the following API in app.js

1
2
3
4
5
6
7
8
9
10
app.post('/junction', async (req, res) => {

  const data = req.body;
  try {
    const Junction = await db.Junction.create(data);
    res.send(Junction);
  } catch (err) {
    res.send(err);
  }
})

Now hit the post request at the URL http://localhost:3000/junction and send the following data in the body.

1
2
3
4
{
  "movieId": 1,
  "actorId": 1
}

You will see the following output,

1
2
3
4
5
6
7
{
  "id": 1,
  "movieId": 1,
  "actorId": 1,
  "updatedAt": "2021-09-29T16:52:34.453Z",
  "createdAt": "2021-09-29T16:52:34.453Z"
}

Now repeat this process to link the corresponding movies and actors. This is what my junction table looks like in the end.

If you try to add an actorId (say 10) that does not exist, then you will get an error like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
{
  "name": "SequelizeForeignKeyConstraintError",
  "parent": {
    "code": "ER_NO_REFERENCED_ROW_2",
    "errno": 1452,
    "sqlState": "23000",
    "sqlMessage": "Cannot add or update a child row: a foreign key constraint fails (`fakedb`.`junctions`, CONSTRAINT `junctions_ibfk_2` FOREIGN KEY (`actorId`) REFERENCES `actors` (`id`) ON UPDATE CASCADE)",
    "sql": "INSERT INTO `Junctions` (`id`,`movieId`,`actorId`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?);",
    "parameters": [
            6,
            10,
            "2021-09-29 16:54:36",
            "2021-09-29 16:54:36"
        ]
  },
  "original": {
    "code": "ER_NO_REFERENCED_ROW_2",
    "errno": 1452,
    "sqlState": "23000",
    "sqlMessage": "Cannot add or update a child row: a foreign key constraint fails (`fakedb`.`junctions`, CONSTRAINT `junctions_ibfk_2` FOREIGN KEY (`actorId`) REFERENCES `actors` (`id`) ON UPDATE CASCADE)",
    "sql": "INSERT INTO `Junctions` (`id`,`movieId`,`actorId`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?);",
    "parameters": [
            6,
            10,
            "2021-09-29 16:54:36",
            "2021-09-29 16:54:36"
        ]
  },
  "sql": "INSERT INTO `Junctions` (`id`,`movieId`,`actorId`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?);",
  "parameters": [
        6,
        10,
        "2021-09-29 16:54:36",
        "2021-09-29 16:54:36"
    ],
  "fields": [
        "actorId"
    ],
  "table": "actors",
  "value": 10,
  "index": "junctions_ibfk_2",
  "reltype": "child"
}

This means our constraints are working fine. Now let’s create our final API to fetch this combination of movies and actors using the junction.
Create the following API in app.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
app.get("/junction", async (req, res) => {
  try {
    const junction = await db.Junction.findAll({
      attributes: ['id'],
      include: [{
        model: db.Movie,
        attributes: ['name'],
      }, {
        model: db.Actor,
        attributes: ['name'],
      }],
    });
    res.send(junction);
  } catch (err) {
    res.send(err);
  }
});

Here we have selected only the attributes that we need to see using the attributes key.
Now, let’s hit this API. Go to this URL http://localhost:3000/junction and make a GET request. This is the result.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
[
  {
    "id": 1,
    "Movie": {
      "name": "Harry Potter"
    },
    "Actor": {
      "name": "Emma Watson"
    }
    },
  {
    "id": 2,
    "Movie": {
      "name": "Harry Potter"
    },
    "Actor": {
      "name": "Daniel Radcliff"
    }
    },
  {
    "id": 3,
    "Movie": {
      "name": "The Matrix"
    },
    "Actor": {
      "name": "Keanu Reaves"
    }
    },
  {
    "id": 4,
    "Movie": {
      "name": "Beauty and the beast"
    },
    "Actor": {
      "name": "Emma Watson"
    }
    },
  {
    "id": 5,
    "Movie": {
      "name": "Pirates of Caribbean"
    },
    "Actor": {
      "name": "Johnny Depp"
    }
    },
  {
    "id": 6,
    "Movie": {
      "name": "Crimes of Grindelwaldld"
    },
    "Actor": {
      "name": "Johnny Depp"
    }
    }
]

Our movies and actors are connected perfectly and the relationship is ready.

Goodbye ✌