How to create JOIN queries with Sequelize

There are two ways you can create JOIN queries and fetch data from multiple tables with Sequelize:

• Create raw SQL query using sequelize.query() method
• Associate related Sequelize models and add the include option in your Sequelize query method

This tutorial will help you learn both methods.

Suppose you have two related tables in SQL named Invoices and Users as shown below:

# Users
+----+-----------+
| id | firstName |
+----+-----------+
|  1 | Nathan    |
|  2 | John      |
+----+-----------+

#Invoices
+----+--------+--------+
| id | amount | userId |
+----+--------+--------+
|  1 |    300 |      1 |
|  2 |    100 |      2 |
+----+--------+--------+

The Invoices table stores the userId of the user who created the invoice row.

Let’s see how you can create and run a raw SQL JOIN query next.

Create JOIN queries using Sequelize.query()

The first method to query both table data at once is by writing a raw SQL query using sequelize.query() method as shown below:

const [results, metadata] = await sequelize.query(
  "SELECT * FROM Invoices JOIN Users ON Invoices.userId = Users.id"
);

console.log(JSON.stringify(results, null, 2));

The output of the results variable above would be as follows:

[
  {
    "id": 1,
    "amount": 100,
    "userId": 1,
    "firstName": "Nathan"
  },
  {
    "id": 1,
    "amount": 300,
    "userId": 1,
    "firstName": "Nathan"
  }
]

The sequelize.query() method is a function that allows you to write and run a raw SQL query. You can learn more about sequelize.query() method here.

But since you’re using Sequelize, you may prefer to use the Sequelize way of creating JOIN queries.

Let’s learn about that in the next section.

Create JOIN queries through Sequelize association

Association is the Sequelize concept used to connect different models. When two Sequelize models are associated, you can ask Sequelize to fetch data from the associated models.

Under the hood, Sequelize will generate and execute a JOIN query, as you’ll see later.

There are four types of association methods that you can use in Sequelize:

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

These four methods can be used to create One-To-One, One-To-Many, and Many-To-Many relationships between your models.

In the example we have, one Users row can have many Invoices rows. This means you need to create a One-To-Many relation between the Users and the Invoices table.

First, define the models for both tables using Sequelize.define() as shown below:

const Invoice = sequelize.define(
  "Invoice",
  { amount: Sequelize.INTEGER },
  { timestamps: false }
);

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

Now that both models are created, you need to call the right association methods to form the relationship between your two models:

User.hasMany(Invoice);
Invoice.belongsTo(User);

With the relationship formed between the models, you can start querying the data of one model from the other by adding the include option in your query method.

For example, here’s how to query the Invoice model data from the User model:

const users = await User.findAll({ include: Invoice });

console.log(JSON.stringify(users, null, 2));

Notice how the findAll() method call above has the include: Invoice option.

This option modifies the behavior of Sequelize to generate and run the following SQL query:

SELECT
  `User`.`id`,
  `User`.`firstName`,
  `Invoices`.`id` AS `Invoices.id`,
  `Invoices`.`amount` AS `Invoices.amount`,
  `Invoices`.`UserId` AS `Invoices.UserId`
FROM
  `Users` AS `User`
  LEFT OUTER JOIN `Invoices` AS `Invoices` ON `User`.`id` = `Invoices`.`UserId`;

By default, the include option will cause Sequelize to generate an SQL query with the LEFT OUTER JOIN clause.

All rows from the main table Users will be retrieved even when the row has zero Invoices row.

The output of the users variable above would be as shown below:

[
  {
    "id": 1,
    "firstName": "Nathan",
    "Invoices": [
      {
        "id": 2,
        "amount": 100,
        "UserId": 1
      },
      {
        "id": 1,
        "amount": 300,
        "UserId": 1
      }
    ]
  },
  {
    "id": 2,
    "firstName": "John",
    "Invoices": []
  }
]

To change the JOIN clause from LEFT OUTER JOIN to INNER JOIN, you need to add the required: true option to the include option.

Here’s how you call the findAll() method to produce an INNER JOIN query:

const users = await User.findAll({
  include: { model: Invoice, required: true },
});

console.log(JSON.stringify(users, null, 2));

Now the generated SQL query will be as follows:

SELECT
  `User`.`id`,
  `User`.`firstName`,
  `Invoices`.`id` AS `Invoices.id`,
  `Invoices`.`amount` AS `Invoices.amount`,
  `Invoices`.`UserId` AS `Invoices.UserId`
FROM
  `Users` AS `User`
  INNER JOIN `Invoices` AS `Invoices` ON `User`.`id` = `Invoices`.`UserId`;

With an INNER JOIN, the data from the Users row will be excluded when there are no related Invoices data for that row.

The output from the users variable is as follows:

[
  {
    "id": 1,
    "firstName": "Nathan",
    "Invoices": [
      {
        "id": 2,
        "amount": 100,
        "UserId": 1
      },
      {
        "id": 1,
        "amount": 300,
        "UserId": 1
      }
    ]
  }
]

Now you’ve learned how to use Sequelize to form relationships between your tables and run a JOIN query.

This method of fetching data from multiple tables at once is also known as Sequelize eager loading.

There’s also another method called lazy loading, in which you can retrieve data from related models separately.

I hope this tutorial has been useful for you 🙏