Learning Sequelize include option for JOIN query

The Sequelize include option is commonly added in a model finder method (findOne(), findAll(), etc.)

This option is used to add a JOIN clause to the generated SQL query, enabling you to retrieve data from multiple tables with one query.

For example, suppose you have two related SQL tables named Cities and Countries.

Each Cities row stores the information of which country it belongs to with the CountryId column as shown below:

# Cities
+----+----------+-----------+
| id | cityName | CountryId |
+----+----------+-----------+
|  1 | York     |         1 |
|  2 | Bristol  |         1 |
+----+----------+-----------+

# Countries
+----+----------------+
| id | countryName    |
+----+----------------+
|  1 | United Kingdom |
+----+----------------+

To retrieve data from these tables, you need to create the Sequelize models for the tables first:

const City = sequelize.define(
  "City",
  { cityName: Sequelize.STRING },
  { timestamps: false }
);

const Country = sequelize.define(
  "Country",
  { countryName: Sequelize.STRING },
  { timestamps: false }
);

With the models defined, you need to create the right relationship between the models using the Sequelize association method.

There are four association methods available in Sequelize:

• hasOne()
• hasMany()
• belongsTo()
• belongsToMany()

You need to choose the right association method for your tables to make it work properly.

For this example, A country can have many cities, while a city can only have one country.

The relationship should be defined as follows:

Country.hasMany(City);
City.belongsTo(Country);

Now that the models are associated, you can query the related model using include option.

For example, here’s how to fetch the Country model data from City model:

const city = await City.findByPk(1, {
  include: Country,
});

console.log(city.toJSON());

The call to findByPk() method above will cause Sequelize to generate and execute the following query:

SELECT
  `City`.`id`,
  `City`.`cityName`,
  `City`.`CountryId`,
  `Country`.`id` AS `Country.id`,
  `Country`.`countryName` AS `Country.countryName`
FROM
  `Cities` AS `City`
  LEFT OUTER JOIN `Countries` AS `Country` 
    ON `City`.`CountryId` = `Country`.`id`
WHERE
  `City`.`id` = 1;

The returned city object will have the following values:

{
  id: 1,
  cityName: 'York',
  CountryId: 1,
  Country: { id: 1, countryName: 'United Kingdom' }
}

You can also query the City model data from Country model like this:

const country = await Country.findByPk(1, {
  include: City,
});

console.log(country.toJSON());

The country object will have a Cities array as shown below:

{
  id: 1,
  countryName: 'United Kingdom',
  Cities: [
    { id: 1, cityName: 'York', CountryId: 1 },
    { id: 2, cityName: 'Bristol', CountryId: 1 },
  ]
}

Note that when you have a One-To-Many relationship, Sequelize returns related data as an array (Country to City)

When you have a Many-To-One relationship, Sequelize returns an object (City to Country)

You can also filter the include option using a where option as follows:

const country = await Country.findByPk(1, {
  include: {
    model: City,
    where: {
      cityName: "York",
    },
  },
});

console.log(country.toJSON());

The where option will be passed to the related model, returning only records that fulfill the where condition:

{
  id: 1,
  countryName: 'United Kingdom',
  Cities: [ { id: 1, cityName: 'York', CountryId: 1 } ]
}

The technique of using include option in finder methods is also known as Eager Loading in Sequelize.

For a more complex query, you can also perform a nested include query for a table that’s related to two other tables.

Great work on learning about Sequelize include option! 😉