m-n Associations in Sequelize

Not so long ago, I got myself into a “many-to-many associations” soup (Yes, me is noob 😓). Hence, I am composing this tutorial as a follow up to my previous post in order to help anybody avoid the certain common pitfalls.

But, before we begin:

Disclaimer: It is assumed that anybody reading this article works with nodeJS, sequelize and has a good understanding of how databases work.

To begin with, let me explain a (highly) simplified version of the issue I faced.
Let us assume that we are to maintain, create and fetch records for a certain vehicles showroom. We need to track the vehicles we have and the parts in each of these vehicles (In case certain parts of the vehicle break down).
Naturally, we would require a vehicles and a parts table.

Problem

Now, we could have multiple vehicles in our vehicles table. Similarly, our parts table could also be filled with multiple parts and if it isn’t obvious already, a single vehicle could be made up of multiple parts; some of all of which may also be used in other vehicles.

The path I went down (TL;DR)

I’d known about sequelize many-to-many maps and through tables but their documentation was just so difficult to comprehend that I went ahead with the worst of solutions. Since I was dealing with many-to-many mappings, I naturally had through tables (vehicleparts)that would store the ids from both tables.
The mistake committed was that while creating the associations, I used the sequelize hasMany functionality to associate the vehicles table to the vehicleparts table which was in-turn mapped to the parts table. the resulting findAll query?

const allVehicles = Models.vehicles.findAll({
include: [{
model: Models.vehicleParts,
as: 'vehicleParts',
include: [{
model: Models.parts,
as: 'parts'
}]
}]
})

I know, Yuck!

Needless to say, a lot of parsing went into getting the response correct and data insertion, a nightmare.

The (My) Solution

I dug a little deeper into the sequelize docs and stackoverflow; found a way to find all the vehicles with the parts they are made up of.

How, you ask?
So we can create join tables in sequelize and associate one entity with another through the join table. And that’s exactly what I did.
Let me show you my sequelize models:

The vehicles model:

module.exports = (sequelize, DataTypes) => {
const vehicles = sequelize.define('vehicles', {
name: DataTypes.STRING,
type: DataTypes.STRING,
manufacturer: DataTypes.STRING,
}, {});
vehicles.associate = (models) => {
vehicles.belongsToMany(models.parts, {
foreignKey: 'vehicleId',
through: models.vehicleparts,
as: 'parts'
});
};
return vehicles;
};

The parts model:

module.exports = (sequelize, DataTypes) => {
const parts = sequelize.define('parts', {
name: DataTypes.STRING,
size: DataTypes.STRING,
manufacturer: DataTypes.STRING,
}, {});
parts.associate = (models) => {
parts.belongsToMany(models.vehicles, {
foreignKey: 'partId',
through: models.vehicleparts,
as: 'vehicles'
});
};
return parts;
};

And finally, the vehicleparts join table:

module.exports = (sequelize, DataTypes) => {
const vehicleparts = sequelize.define('vehicleparts', {
vehicleId: DataTypes.INTEGER,
partId: DataTypes.INTEGER,
quantity: DataTypes.INTEGER,
}, {});
vehicleparts.associate = (models) => {};
return vehicleparts;
};

Notice that we have both our associations only on the individual entity ( vehicle and parts) tables. This is because, according to sequelize, we don’t always provide the join table in our models. We can just provide a string in the belongsToMany association and sequelize will assume the table exists. I created a separate models because I have an extra attribute (quantity) in the vehicleparts table.

Querying

Now that we have our models and associations set up, let me show you the existing data in the tables and function to find all the vehicles and the parts in them

Data:

const getAllVehicles = async () => {
try {
const result = await Models.vehicles.findAll({
include: [{
model: Models.parts,
as: 'parts',
through: {
model: Models.vehicleparts,
attributes: ['quantity']
}
}]
});
}
}

With this, all the relevant data is retrieved. A thing to note here is that even the metadata in the vehicleparts table is fetched in the process. In this case, I have only used one attribute, quantity to denote the number of parts used in one vehicle. Similarly, multiple other attributes can be used.

Oh and btw, the query result:

[{
"id": 14,
"name": "Santro",
"type": "car",
"manufacturer": "Hyundai",
"createdAt": "2019-03-17T15:17:41.780Z",
"updatedAt": "2019-03-17T15:17:41.780Z",
"parts": [{
"id": 26,
"name": "Tyres",
"size": "small",
"manufacturer": "CEAT",
"createdAt": "2019-03-17T15:17:41.809Z",
"updatedAt": "2019-03-17T15:17:41.809Z",
"vehicleparts": {
"quantity": 4
},
},
{
"id": 25,
"name": "Engine",
"size": "small",
"manufacturer": "Hyundai",
"createdAt": "2019-03-17T15:17:41.808Z",
"updatedAt": "2019-03-17T15:17:41.808Z",
"vehicleparts": {
"quantity": 1
},
}
]
},
{
"id": 15,
"name": "Atlas",
"type": "truck",
"manufacturer": "Ford",
"createdAt": "2019-03-17T15:19:59.761Z",
"updatedAt": "2019-03-17T15:19:59.761Z",
"parts": [{
"id": 27,
"name": "Engine",
"size": "medium",
"manufacturer": "Ford",
"createdAt": "2019-03-17T15:19:59.817Z",
"updatedAt": "2019-03-17T15:19:59.817Z",
"vehicleparts": {
"quantity": 1
},
},
{
"id": 28,
"name": "Tyres",
"size": "medium",
"manufacturer": "CEAT",
"createdAt": "2019-03-17T15:19:59.817Z",
"updatedAt": "2019-03-17T15:19:59.817Z",
"vehicleparts": {
"quantity": 4
},
}
]
}
]

Inserting data

Now that we have discussed how to retrieve data from a many-to-many association in sequelize, let’s talk about how the entries got into the table in the first place.

Like you’d expect, it’s a normal create with the include key. it’s quite a simple function, with only the through key that’s extra. Sequelize creates entries in the parts table based on the number of parts you have in the vehicle object passed. It also Creates entries in the vehicleparts table; one for each vehicle-part mapping:

const createVehicle = async (vehicle) => {
await Models.vehicles.create(
vehicle,
{
include: [{
model: Models.parts,
as: 'parts',
through: {
model: Models.vehicleparts,
}
}]
}
)
}

The above function takes the following format for the vehicle param:

"vehicle": {
"name": "Atlas",
"type": "truck",
"manufacturer": "Ford",
"parts": [{
"name": "Engine",
"size": "medium",
"manufacturer": "Ford",
"quantity": 1
}, {
"name": "Tyres",
"size": "medium",
"manufacturer": "CEAT"
"quantity": 4
}]
}

Simple?

But how do we populate the quantity field in the vehicleparts table?

According to the sequelize docs, sequelize attaches a few functions for adding, setting and getting associated objects from the table. In this example, let’s use the addParts function. We can leverage this function to create an association between two entries of the vehicles and the parts table respectively like so:

The updated create function:

const createVehicle = async (vehicle) => {
await Models.vehicles.create(
vehicle, {
include: [{
model: Models.parts,
as: 'parts',
through: {
model: Models.vehicleparts,
}
}]
}
)
await Promise.all(result.parts.map((part, index) => result.addPart(part, {
through: {
quantity: payload.vehicle.parts[index].quantity
}
})));
}

Notice how we need to map over the result obtained to create the individual connections between the entities. All this only because the add, set methods accept only sequelize Models as parameters. (I know! 😢)

I’ll be adding the source code for the project on github soon. 🙂

I hope this helps you avoid the rookie mistakes I made. Also, please hit me up/drop a comment 👇 if someone finds a better way of doing the same.
Cheers! ☕️

@szanwar22 on twitter :D