Four Sequelize Associations You Should Know

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. Joins in the database tables are very common to get all the related pieces of information together. In the previous post, we have already covered the Basics of Sequelize.

In today's post, we are going to learn about Sequelize associations, eager loading & lazy loading. But before that let's learn what are joins in SQL.

SQL Joins

Joins in SQL are used to combine two or more tables in the query result. As it's not possible to store everything on a single table. Using Joins, you can connect tables using the concept of foreign key & primary key. There are 4 types of joins,

• INNER JOIN: Returns records that have matching values in both tables

• LEFT OUTER JOIN: Returns all records from the left(from) table, and the matched records from the right(Joining) table & Null if not found

• RIGHT OUTER JOIN: Returns all records from the right(Joining) table, and the matched records from the left(from) table & Null if not found

• FULL OUTER JOIN: Returns all records when there is a match in either the left or right table

Joins in SQL

Sequelize associations

Associations in Sequelize refer to the relationship between two tables. There are 4 types of associations,

• The “HasOne” association

• The “BelongsTo” association

• The “HasMany” association

• The "BelongsToMany" association

Before starting with associations let's consider there are 3 tables

1. User (Store user info like name, gender, country, email, uId, UUID, etc.)
2. Posts (Store post info)
3. Account (store account info like paid, free, monthly, yearly user, etc.)

To create associations, there should be some kind of relationship that should exist between tables. There are basically three types of relations that exist between tables 1. One to One 2. One to Many & 3. Many to Many. Now each relationship defines the associations between table like

1. One to One relationship always has hasOne & belongTo association. 2.** One to Many** relationships always has hasMany & belongsTo association.
2. Many to Many relationships always has two belongsToMany associations.

one to one/many relationship diagram

One-To-One relationships:

Let's say a user can only have one account. To identify which will have hasOne & which table will have belongsTo association, ask a question: Does an account exist without a user/ user exist without an account? The answer is a user can exist without an account, which means a user acts as the parent table & an account acts like a child table. A user profile is first created & then based on the plan account is created. This means an account table will hold the primary key of a user table as a foreign key for eg. uId is a primary key in the user table. So association will be User.hasOne(Account) & Account.belongsTo(User)

const User = sequelize.define('User', { name: DataTypes.STRING }); const Account = sequelize.define('Account', { packageType: DataTypes.STRING });
User.hasOne(Account);
Account.belongsTo(User);

One-To-Many relationship:

Let's say a user can post multiple articles. To identify which will have hasMany & which table will have belongsTo association, ask a question: Does a post exist without a user/ user exist without a post? The answer is a user can exist without a post, which means a user acts as the parent table & a post acts like a child table. A user profile is first created & then a user can publish a post. This means a post table will hold the primary key of a user table as a foreign key for eg. uId is a primary key in the user table. So association will be User.hasMany(Post) & Post.belongsTo(User)

const User = sequelize.define('User', { name: DataTypes.STRING }); const Post = sequelize.define('Post', { title: DataTypes.STRING });
User.hasMany(Post);
Post.belongsTo(User);

many to many relationship diagram

**Many-To-Many relationship: **Many-To-Many associations connect one source with multiple targets, while all these targets can, in turn, be connected to other sources beyond the first. This cannot be represented by adding one foreign key to one of the tables like the other relationships did. Instead, the concept of a Junction Model is used. This will be an extra model (and extra table in the database) which will have two foreign key columns and will keep track of the associations. The junction table is also sometimes called a join table or *through a table or *a pivot table.

const User = sequelize.define('User', { name: DataTypes.STRING }); const Project = sequelize.define('Project', { name: DataTypes.STRING });
User.belongsToMany(Project, { through: 'UserProjects' }); Project.belongsToMany(User, { through: 'UserProjects' });

In the above example, a user can be part of more than one project & a project can have more than 1 user.

**Fetching Associations: **Fetching an association happens in two ways 1. eager loading & 2. lazy loading. Eager loading means the association is included in the main query so everything is fetched once. Whereas lazy loading refers to fetch association only if it's required.

For eg. eager loading

const userDetail = await User.findOne({
   where: {
    email: "testuser@example.co"
   },
   include: Account
 });

For eg. lazy loading

const userDetail = await User.findOne({
   where: {
    email: "testuser@example.co"
   },
 });
const userAccount = await userDetail.getAccount();

• Note: the getAccount() instance method used above is one of the methods Sequelize automatically adds to User instances.

Why it's important to specify association in the parent & child table? When a Sequelize association is defined between two models, only the “source**”** model knows about it. for eg. In the above example when you define User.hasOne(Account) User is the source model and only the user table knows the relationship with an account, whereas without defining Account.belongsTo(User) account table is unaware of relationship with User. If you don't define a relationship in both tables and try to fetch User info with an Account table, Sequelize will give you an error “SequelizeEagerLoadingError: User is not associated to Account!". That's why it is necessary to define a relationship in pairs

Now let's see how SQL query is generated on eager loading.

// Inner where, with default `required: true`
await User.findAll({
  include: {
    model: Post,
    as: 'Posts',
    where: {
      status: { [Op.ne]: 'draft' }
    }
  }
});

SELECT * FROM `users` AS `user`
INNER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
  AND `Posts`.`status` != 'draft';

// Inner where, `required: false`
await User.findAll({
  include: {
    model: Post,
    as: 'Posts',
    where: {
      status: { [Op.ne]: 'draft' }
    },
    required: false
  }
});

SELECT * FROM `users` AS `user`
LEFT OUTER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
  AND `Posts`.`status` != 'draft';


// Top-level where, with default `required: false`
await User.findAll({
  where: {
    '$Posts.status$': { [Op.ne]: 'draft' }
  },
  include: {
    model: Post,
    as: 'Posts'
  }
});

SELECT * FROM `users` AS `user`
LEFT OUTER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
WHERE `Posts`.`status` != 'draft';

// Top-level where, `required: true`
await User.findAll({
  where: {
    '$Posts.status$': { [Op.ne]: 'draft' }
  },
  include: {
    model: Post,
    as: 'Posts',
    required: true
  }
});

SELECT * FROM `users` AS `user`
INNER JOIN `posts` AS `Posts` ON
  `user`.`uId` = `Posts`.`uId`
WHERE `Posts`.`status` != 'draft';

User.findAll({
 include: [{
  model: Post,
  where: {
   status: {
    [Op.ne]: 'draft'
   }
  },
  required: false
  right: true // will create a right join
 }]
});

Sequelize automatically sets the “required” option to “true”. This means that instead of an “Outer Join” an “Inner Join” is done, returning only the parent models with at least one matching child. Note also that the where option used was converted into a condition for the ON clause of the inner join.

You can define associations with an alias if a table contains two foreign keys of the same parent table. And while including the model use the same alias.

That's it, this is the basics of how associations work in Sequelize. You can learn more about it in the official documentation here.

Thanks for reading.