How to use INNER JOIN in Sequelize.js

Overview

Sequelize.js is a popular Node.js ORM for managing relational databases like MySQL, PostgreSQL, SQLite, and MSSQL. One of the fundamental features Sequelize provides is the ability to join tables using various methods, including the commonly used INNER JOIN. This tutorial will guide you through the process of implementing INNER JOIN in Sequelize with multiple examples, ensuring you have a comprehensive understanding by the end of it.

Prerequisites:

• Basic knowledge of Node.js and SQL.
• Sequelize installed and a database setup.
• Understanding of relational database concepts and table associations.

Understanding INNER JOIN

Before diving into code examples, let’s refresh our understanding of INNER JOIN. INNER JOIN is a SQL operation that returns rows from both tables when the condition meets. It combines rows from two or more tables based on a related column and filters out the rows that do not match the condition.

Basic INNER JOIN in Sequelize

In Sequelize, associations between models are first defined to perform joins. You then use the include option in your query to specify an INNER JOIN. Here’s the simplest form of using INNER JOIN:

const User = sequelize.define('User', { /* ... */ });
const Project = sequelize.define('Project', { /* ... */ });

User.hasMany(Project);
Project.belongsTo(User);

const projectsWithUsers = await Project.findAll({
    include: [{
        model: User,
        required: true
    }]
});

In the above code snippet, we’ve associated Users with Projects using Sequelize’s hasMany and belongsTo methods. The findAll method with the include option performs an INNER JOIN to get all projects with their associated users.

Specifying Conditions in INNER JOIN

To add conditions to your INNER JOIN, Sequelize uses the where attribute inside the include:

const projectsWithActiveUsers = await Project.findAll({
    include: [{
        model: User,
        required: true,
        where: {
            isActive: true
        }
    }]
});

This query will only return projects with active users because of the where condition.

Joining Multiple Tables

An INNER JOIN can also be used to connect more than two tables. Here’s how you can perform a multi-table join in Sequelize:

// Consider a third model, Task
const Task = sequelize.define('Task', { /* ... */ });

User.hasMany(Project);
Project.belongsTo(User);
Project.hasMany(Task);
Task.belongsTo(Project);

const projectsWithUsersAndTasks = await Project.findAll({
    include: [
        {
            model: User,
            required: true
        },
        {
            model: Task,
            required: true
        }
    ]
});

This example demonstrates how to join Projects with both Users and Tasks. The required: true option ensures that it performs an INNER JOIN rather than a left outer join, which is the default when required is not specified.

Conclusion

In this tutorial, we’ve explored how to use INNER JOIN in Sequelize.js, starting from the basics and progressing to more complex scenarios. We’ve covered setting up relationships between models, performing simple and conditional joins, as well as joining multiple tables together. By now, you should be comfortable with using INNER JOIN in your Sequelize projects, which is a significant step towards mastering Sequelize.js for relational database interactions.