Sequelize Associations - Fetching Associations

This blog exclusively deals with how to fetch associations in sequelize. Primary knowledge of NodeJS, Express and SequelizeJS is recommended.

Sequelize is an Object relational library (ORM) that helps to communicate between database (Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server) and programming models on the NodeJS end.

Refer to Part 1 - Creating Associations for a tutorial on how to create associations.

Associations Recap

There are three main types of associations that can be represented in Sequelize:

• One to One relationship
• One to Many relationship
• Many to Many relationship

All three of these are fetched with similar semantics.

Fetch techniques

Sequlize offers two different ways to fetch associations of a model. These are:

• Eager Loading
• Lazy loading

Eager Loading

This technique is most used to fetch associations. Eager loading loads the associations and related models in a single request. To eager load, we can use include property in find clause.

To fetch User with Profile information:

const user = User.find({
  include: {
    model: Profile,
  },
});

If User has Profile information, this will return data of the form:

{
  ...userProperties,
  "profile": {
    ...profileProperties,
  }
}

Note that profile is singular because they share a 1-1 relationship.

How do we select attributes to be returned?

Just like the normal find command, the include can have an attributes array including column names to be returned.

const user = User.find({
  include: {
    model: Profile,
    attributes: ["firstName", "lastName"], // return only these columns
  },
});

What if we need to go deeper? How do we fetch the Location model connected to Profile?

Let's nest it.

const user = User.find({
  include: {
    model: Profile,
    include: {
      model: Location,
    },
  },
});

What if we need a where clause on the association?

A where clause, you say.

const user = User.find({
  include: {
    model: Profile,
    where: {
      age: {
        [Op.gt]: 18,
      },
    },
  },
});

How to fetch only the users with profiles?

Sequlize uses Outer Joins by default. To turn it into an Inner Join (only if the associated model is also present):

const user = User.find({
  include: {
    model: Profile,
    required: true,
  },
});

Note that the where clause that we specified in the last example automatically turns the query into an inner join.

What if it's not a 1-1 relationship?

The plurality changes. The syntax remains the same, but the result with have a plural version of key name.

const userWithComments = User.find({
  include: {
    model: Comment,
  },
});

will result in:

{
  ...userProperties,
  "comments": [{
    ...commentProperties,
  }],
}

My association is aliased. How do I specify the alias?

You can specify the alias with as keyword, similar to how you define it.

const userWithFavBooks = User.find({
  include: {
    model: Book,
    as: "FavBooks",
  },
});

How to get the junction table in case of Many-Many association?

The junction table is automatically fetched by Sequelize when you fetch a Many-Many association.

You can apply other properties we mentioned like where clause using the through key.

const userWithFavBooks = User.find({
  include: {
    model: Book,
    as: "FavBooks",
    through: {
      where: {
        rating: {
          [Op.gt]: 3,
        },
      },
    },
  },
});

Can we fetch multiple associations at once?

Sure, let's turn include into an array.

const user = User.find({
  include: [
    {
      model: Profile,
    },
    {
      model: Comment,
    },
  ],
});

Lazy Loading

Lazy Loading makes use of Sequelize's ability to add functions to Models with it's associations.

For eg. If User and Profile are connected with 1-1 association, Sequelize automatically adds a function called getProfile to User and getUser to Profile (There are several functions like this, addProfile, updateProfile etc.)

const user = User.findOne({
  where: {
    id: 1,
  },
});
let profile = null;
if (user.active) {
  profile = user.getProfile();
}

Note that it makes two database requests here, but the Profile information is fetched only if User found is active.

There is a lot more to cover on creating these relationships like customising keys or changing how update or delete affects these relationships. I do plan to cover them, but documentation should be your best guide if you understand these relationships better.

Have fun 🎉