Retrieve Records From Multiple Tables in Sequelize

In Sequelize, the data of associated models can essentially be fetched in two ways, through eager loading and lazy loading.

Eager loading refers to querying all the data of a model and its associated model at once, which in SQL translates to a LEFT OUTER JOIN query.

Lazy loading, on the other hand, refers to querying the data of a model, and later on, querying the data of its associated model only when you really need it.

Eager loading

In Sequelize, eager loading is done by querying a model using query methods, such as findOne() and findAll(), and specifying the associated model through the include property of the configuration object:

await ModelA.findOne|findAll({
  include: {
    model: ModelB
  }
});

Where:

• ModelA is a model instance associated with another model through the hasOne() or hasMany() method.
• ModelB is a model instance associated with another model through the belongsTo() method.

This will cause the query method to return an object containing the fields of the main model, as well as the fields of the associated models.

Note: By default, Sequelize uses the name of the associated model as the name of the property under which the fields of the associated model are stored. However, in case of a one-to-many relationship, the property's name will be automatically pluralized.

Example

Let's consider these tables named citizens and passports, where the passports.citizenId foreign key column references the citizens.id primary key column:

mysql> SELECT * FROM citizens;
+----+--------------+
| id | name         |
+----+--------------+
|  4 | Paul Higgins |
|  5 | Ethan Dale   |
+----+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM passports;
+----+-----------+------------+------------+-----------+
| id | number    | issueDate  | expiryDate | citizenId |
+----+-----------+------------+------------+-----------+
|  1 | 596CDFC1K | 2024-02-22 | 2034-02-21 |         4 |
|  2 | OLI8F236Q | 2023-06-12 | 2033-06-11 |         5 |
+----+-----------+------------+------------+-----------+
2 rows in set (0.00 sec)

Let's consider this script, that defines a one-to-one relationship between the Citizen model and the Passport model and uses eager loading to perform an SQL join on both tables:

const { Sequelize, DataTypes } = require('sequelize');

(async () => {
  const db = new Sequelize(/* ... */);

  try {
    await db.authenticate();

    const Citizen = db.define('citizen', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      name: {
        type: DataTypes.STRING(50),
        allowNull: false,
      },
    }, {
      timestamps: false,
    });

    const Passport = db.define('passport', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      number: {
        type: DataTypes.STRING(9),
        allowNull: false,
        unique: true,
      },
      issueDate: {
        type: DataTypes.DATEONLY,
        allowNull: false,
      },
      expiryDate: {
        type: DataTypes.DATEONLY,
        allowNull: false,
      },
    }, {
      timestamps: false,
    });

    Citizen.hasOne(Passport, {
      foreignKey: {
        allowNull: false,
        unique: true
      }
    });

    Passport.belongsTo(Citizen);

    const results = await Citizen.findAll({
      include: {
        model: Passport
      }
    });
    
    console.log(JSON.stringify(results, null, 2));
  } catch(error) {
    console.error(error.toString());
  } finally {
    await db.close();
  }
})();

Which will produce this output:

[
  {
    "id": 4,
    "name": "Paul Higgins",
    "passport": {
      "id": 1,
      "number": "596CDFC1K",
      "issueDate": "2024-02-22",
      "expiryDate": "2034-02-21",
      "citizenId": 4
    }
  },
  {
    "id": 5,
    "name": "Ethan Dale",
    "passport": {
      "id": 2,
      "number": "OLI8F236Q",
      "issueDate": "2023-06-12",
      "expiryDate": "2033-06-11",
      "citizenId": 5
    }
  }
]

Which corresponds to this SQL statement:

mysql> SELECT * FROM citizens INNER JOIN passports ON citizens.id = passports.citizenId;
+----+--------------+----+-----------+------------+------------+-----------+
| id | name         | id | number    | issueDate  | expiryDate | citizenId |
+----+--------------+----+-----------+------------+------------+-----------+
|  4 | Paul Higgins |  1 | 596CDFC1K | 2024-02-22 | 2034-02-21 |         4 |
|  5 | Ethan Dale   |  2 | OLI8F236Q | 2023-06-12 | 2033-06-11 |         5 |
+----+--------------+----+-----------+------------+------------+-----------+
2 rows in set (0.00 sec)

Filter associated models

When eager loading, you can filter the columns and values of associated models using the attributes and where properties, just like for any other model.

await <model>.findAll({
  include: {
    model: <model>,
    attributes: [<column>, ...],
    where: {
      <attribute>: {
        [Op.<operator>]: <value>
      }
    }
  }
});

Example

Let's consider this query, based on the previous example:

const results = await Citizen.findAll({
  attributes: ['name'],
  include: {
    model: Passport,
    attributes: ['number', 'expiryDate'],
    where: {
      issueDate: {
        [Op.ge]: '2024-01-01'
      }
    }
  }
});

Which will produce this output:

[
  {
    "name": "Paul Higgins",
    "passport": {
      "number": "596CDFC1K",
      "expiryDate": "2034-02-21"
    }
  }
]

Perform other joins

By default, Sequelize performs a LEFT OUTER JOIN query between the main model and the associated model when eager loading.

To perform an INNER JOIN query instead and only include the records which have an associated model, you can set the include.required property to true:

await ModelA.findOne|findAll({
  include: {
    model: ModelB,
    required: true
  }
});

To perform a RIGHT JOIN query instead, you can set the include.right property to true:

await ModelA.findOne|findAll({
  include: {
    model: ModelB,
    right: true
  }
});

Note: Note that right will be respected only if required is set to false.

Lazy loading

In Sequelize, lazy loading is done by first querying a model using query methods, such as findOne() and findAll(), and separately querying the associated model through special methods that are dynamically added to the model instance upon association.

When creating a relationship using the hasOne() and belongsTo() methods, model instances are extended with special getter methods prefixed with the get keyword concatenated to the capitalized name of the model (e.g., getModel()).

When creating a relationship using the hasMany() and belongsToMany() methods, on the other hand, the getter method is automatically pluralized (e.g., getModels()).

Example

Let's consider these tables named employees and tasks where the tasks.employeeId foreign key column references the employees.id primary key column:

mysql> SELECT * FROM employees;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Paul Higgins |
+----+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tasks;
+----+---------------------------------------------------------------------+----------+------------+------------+
| id | description                                                         | priority | dueDate    | employeeId |
+----+---------------------------------------------------------------------+----------+------------+------------+
|  1 | Hire 3 new developers on the Payment team                           | medium   | 2025-06-12 |          1 |
|  2 | Refactor the CI-CD pipeline to account for the backend stack change | high     | 2025-02-22 |          1 |
+----+---------------------------------------------------------------------+----------+------------+------------+
2 rows in set (0.00 sec)

Let's consider this script, that defines a one-to-many relationship between the Employee model and the Task model and uses lazy loading to perform two SQL queries on both tables:

const { Sequelize, DataTypes, Op } = require('sequelize');

(async () => {
  const db = new Sequelize(/* ... */);

  try {
    await db.authenticate();

    const Employee = db.define('employee', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      name: {
        type: DataTypes.STRING(50),
        allowNull: false,
      }
    }, {
      timestamps: false
    });

    const Task = db.define('task', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      description: {
        type: DataTypes.STRING(250),
        allowNull: false
      },
      priority: {
        type: DataTypes.ENUM('low', 'medium', 'high'),
        defaultValue: 'low',
        allowNull: false
      },
      dueDate: {
        type: DataTypes.DATEONLY,
        allowNull: false
      }
    }, {
      timestamps: false
    });

    Employee.hasMany(Task);

    Task.belongsTo(Employee);
    
    const employee = await Employee.findOne({
      where: {
        id: {
          [Op.eq]: 1
        }
      }
    });

    const tasks = await employee.getTasks();

    console.log(JSON.stringify(employee, null, 2));
    console.log(JSON.stringify(tasks, null, 2));
  } catch(error) {
    console.error(error.toString());
  } finally {
    await db.close();
  }
})();

Which will produce this output:

{
  "id": 1,
  "name": "Paul Higgins"
}
[
  {
    "id": 1,
    "description": "Hire 3 new developers on the Payment team",
    "priority": "medium",
    "dueDate": "2025-06-12",
    "employeeId": 1
  },
  {
    "id": 2,
    "description": "Refactor the CI-CD pipeline to account for the backend stack change",
    "priority": "high",
    "dueDate": "2025-02-22",
    "employeeId": 1
  }
]

Summary

Here's a summary of what you've learned in this lesson:

• Eager loading refers to querying all the data of a model and its associated model at once.
• Eager loading is done by passing a model to the include.model property of the configuration object of the findOne() and findAll() query methods.
• Lazy loading refers to querying the data of a model, and later on, querying the data of its associated model.
• Lazy loading is done by calling the getModel() or getModels() method of an associated model instance.