Sling Academy
Home/Node.js/How to use INNER JOIN in Sequelize.js

How to use INNER JOIN in Sequelize.js

Last updated: December 29, 2023

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.

Next Article: How to Use Column Aliases in Sequelize.js

Previous Article: How to use LEFT JOIN in Sequelize.js

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates