Sequelize BelongsTo & HasMany Associations

This post shows use of Sequelize BelongsTo and HasMany associations with some querying examples.

Prerequisite knowledge: basics of JavaScript ES6, promises, and PostgreSQL.

The code used in this post can be found in this GitHub repo.

Make Your Models

Let’s say we have a couple of space fairing ships named Enterprise and Planet Express. We also have crew members that work on these ships. The crew members are Kirk, Spock, McCoy, Leela, Fry, and Bender. We would like for Kirk, Spock, and McCoy to be crew members of Enterprise and Leela, Fry, and Bender to be crew members of Planet Express.

First, we will need a database which we will name “ships_db” and we will use PostgreSQL with Sequelize ORM to run and manage it.

const Sequelize = require('sequelize');
const conn = new Sequelize(process.env.DATABASE_URL || 'postgres://localhost:5432/ships_db');

Let’s tell our database that we will have ships and members. For each ship we will want to have a name and a purpose. For each member we will want to have a name and a species. The following code sets up our Ship and Member models.

const Ship = conn.define('ship', {
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  purpose: {
    type: Sequelize.STRING,
    allowNull: false
  }
});
const Member = conn.define('member', {
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  species: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

Now our database knows that it will have two tables, one for ships and one for members. Let’s make these tables by running the following sync function:

conn.sync({ force: true });

And here are our tables:

Ships Table

Members Table

Wow such empty.

Put In Data

We should now make our tables useful. The following seed function will populate our tables with whatever data we want by using a create method:

const seed = () => {
  return Promise.all([
    Ship.create({ name: 'Enterprise', purpose: 'exploration' }),
    Ship.create({ name: 'Planet Express', purpose: 'delivery' }),
    Member.create({ name: 'Kirk', species: 'human' }),
    Member.create({ name: 'Spock', species: 'hybrid' }),
    Member.create({ name: 'McCoy', species: 'human' }),
    Member.create({ name: 'Leela', species: 'mutant' }),
    Member.create({ name: 'Fry', species: 'human' }),
    Member.create({ name: 'Bender', species: 'robot' })
  ])
  .catch(error => console.log(error));
};

And now let’s make our tables again by running our sync and seed functions:

conn.sync({ force: true })
  .then(() => seed());

This is what our tables look like now, all full of data:

Ships Table

Members Table

Notice that each table row automatically has a unique id number. PostgreSQL gave us these id numbers, we didn’t have to worry about making them. These will come in handy later.

Search Within A Model

We can now do some searching within our models, that is, within each table. For example, we can find the purpose of Planet Express:

//SEARCH FOR PUROPSE OF PLANET EXPRESS:
conn.sync({ force: true })
  .then(() => seed())
  .then(() => Ship.findOne({
    where: {
      name: 'Planet Express'
    }
  }))
  .then(ship => console.log(ship.purpose))
  .catch(error => console.log(error));

We can also find out which crew member is a robot:

//SEARCH FOR ROBOT:
conn.sync({ force: true })
  .then(() => seed())
  .then(() => Member.findOne({
    where: {
      species: 'robot'
    }
  }))
  .then(member => console.log(member.name))
  .catch(error => console.log(error));

The above searches will yield that Planet Express is a delivery ship and that our robot is Bender.

Make A BelongsTo Association

Our new tables have a bit of a problem in that they don’t know about each other. There is no way for us to find out which member belongs to which ship or which ship has a member who is a robot. To tie these two tables together we need to make associations.

Let’s think about the relationship between crew members and ships. Each crew member belongs to a specific ship. Let’s make it so by using a BelongsTo association.

Member.belongsTo(Ship);

What does this do? Let’s run our sync and seed functions again and take a look at our newly generated members table:

Notice how the members table now has a new column all the way on the right named “shipId”? That’s a foreign key gained by our use of the BelongsTo association. Now if we put numbers in the “shipId” column, those numbers will be id numbers of the ships. So if Kirk got a shipId of 1, that would mean that Kirk belongs to a ship whose id number is 1. Glancing back at our ships table, the ship with id number 1 is Enterprise!

So how do we get the ship id numbers into the shipId column? We do it by using a set method as shown below.

const seed = () => {
  return Promise.all([
    Ship.create({ name: 'Enterprise', purpose: 'exploration' }),
    Ship.create({ name: 'Planet Express', purpose: 'delivery' }),
    Member.create({ name: 'Kirk', species: 'human' }),
    Member.create({ name: 'Spock', species: 'hybrid' }),
    Member.create({ name: 'McCoy', species: 'human' }),
    Member.create({ name: 'Leela', species: 'mutant' }),
    Member.create({ name: 'Fry', species: 'human' }),
    Member.create({ name: 'Bender', species: 'robot' })
  ])
  .then(([enterprise, planetexpress, kirk, spock, mccoy, leela, fry, bender]) => {
    return Promise.all([
      kirk.setShip(enterprise),
      spock.setShip(enterprise),
      mccoy.setShip(enterprise),
      leela.setShip(planetexpress),
      fry.setShip(planetexpress),
      bender.setShip(planetexpress)
    ]);
  })
  .catch(error => console.log(error));
};

And the result is:

Our shipId column is now populated with ship id numbers! Kirk, Spock, and McCoy all have shipId of 1, which means that they all belong to Enterprise. Leela, Fry, and Bender all have shipId of 2, which means that they all belong to Planet Express.

Notice that we can put only one shipId number for each crew member. This means that each crew member can belong to only one ship. This is a one-to-one association. It works for us here but it’s not always applicable. For example, if we wanted to have friends of crew members in our members table, BelongsTo will not work, since Fry is friends with both Leela and Bender and we will not be able to squeeze two friend id numbers into the same cell of a friendId column.

Coming back to our members table, what is the usefulness of having a shipId? The answer is that our two tables are now linked to each other, which lets us do our querying jiu-jitsu (i.e., searching).

For example, if we wanted to know which ship is Leela’s, we can run the following query:

//SEARCH FOR LEELA'S SHIP
conn.sync({ force: true })
  .then(() => seed())
  .then(() => Member.findOne({
    where: {
      name: 'Leela'
    }
  }))
  .then(member => Ship.findById(member.shipId))
  .then(ship => console.log(ship.name))
  .catch(error => console.log(error));

The console.log will give us “Planet Express”!

We can also do the same query in a somewhat easier way. According to Sequelize documentation, the belongsTo method gives us access to a get method, which here would be “.getShip()” as shown below:

//SEARCH FOR LEELA'S SHIP, AN EASY WAY
conn.sync({ force: true })
  .then(() => seed())
  .then(() => Member.findOne({
    where: {
      name: 'Leela'
    }
  }))
  .then(member => member.getShip())
  .then(ship => console.log(ship.name))
  .catch(error => console.log(error));

The result is the same, we get “Planet Express” logged.

Make A HasMany Association

Let’s do a reverse search, going from the ships table to the members table. For example, let’s find all the crew members of Enterprise. Here is one way to do it:

//SEARCH FOR ENTERPRISE CREW
conn.sync({ force: true })
  .then(() => seed())
  .then(() => Ship.findOne({
    where: {
      name: 'Enterprise'
    }
  }))
  .then(ship => Member.findAll({
    where: {
      shipId: ship.id
    }
  }))
  .then(members => members.forEach( member => console.log(member.name)))
  .catch(error => console.log(error));

The console.log will read out “Kirk”, “Spock”, and “McCoy”.

But a better way would be to set up an additional association of HasMany, as shown below:

Member.belongsTo(Ship);
Ship.hasMany(Member);

What does the addition of HasMany association buy us? Per Sequelize documentation, we now again have a get method, just like we did earlier with BelongsTo. We can use this get method to get all the members of a specific ship by using “.getMembers()”. Let’s try this to get all the crew members of Enterprise:

//SEARCH FOR ENTERPRISE CREW, AN EASY WAY
conn.sync({ force: true })
  .then(() => seed())
  .then(() => Ship.findOne({
    where: {
      name: 'Enterprise'
    }
  }))
  .then(ship => ship.getMembers())
  .then(members => members.forEach( member => console.log(member.name)))
  .catch(error => console.log(error));

The console.log will again read out “Kirk”, “Spock”, and “McCoy”.

Note that HasMany is a one-to-many association. It works well when one of something can have many of something else. So one ship can have many crew members, one manager can have many subordinates, one photographer can have many photographs. This does not work though when relationships are more complex, for example, when an author can have many books and each book can have many authors. That would be an example of a many-to-many association, which we will not go into here.

Live Long And Prosper

I hope this was helpful. If you liked this post you may also like my post on eager loading.