Define Relationships Between Tables in Sequelize

In Sequelize, the definition of a relationship between models allows you to represent the associations between their underlying SQL tables and facilitate the querying and management of related data, much like foreign keys and joins.

These relationships are established through the use of built-in model methods, including hasOne, belongsTo, hasMany, and belongsToMany.

These methods are usually declared in pair and allow you to create three primary types of relationships:

• One-to-one relationships using the association of hasOne and belongsTo.
• One-to-many relationships using the association of hasMany and belongsTo.
• Many-to-many relationships using the association of two belongsToMany (not covered in this lesson).

Define one-to-one relationships

In a one-to-one relationship, each record in the first table is associated with exactly one record in the second table through the use of a foreign key.

To create a one-to-one relationship between two models, you can use the hasOne and belongsTo methods of the model instances:

Source.hasOne(Target);
Target.belongsTo(Source);

Where:

• Source is the model instance containing the primary key.
• Target is the model instance containing the foreign key.

When executed, Sequelize will automatically:

1. Add a new property to the Target model named sourceId inferred from the name of the Source model.

2. Add a foreign key column with the same name to the corresponding Target table, which is equivalent to this SQL statement:

   CREATE TABLE target (
     id INT AUTO_INCREMENT PRIMARY KEY,
     -- additional columns
     sourceId INT,
     FOREIGN KEY (sourceId) REFERENCES source(id) ON DELETE SET NULL ON UPDATE CASCADE
   );
   

Note: By default, Sequelize will set the ON DELETE constraint to SET NULL and the ON UPDATE constraint to CASCADE, which means that:

• If a record from the corresponding Source table is deleted, the sourceId column of the target table will be set to NULL.
• If the primary key in the corresponding Source table is updated, the foreign key in the corresponding Target table is also updated.

Example

Let's consider this script, that defines a one-to-one relationship between two models named User and Profile:

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

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

    await database.authenticate();
  
    const User = database.define('user', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      }
    }, {
      timestamps: false
    });
  
    const Profile = database.define('profile', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      }
    }, {
      timestamps: false
    });
  
    User.hasOne(Profile);
    Profile.belongsTo(User);

    await database.sync();

    await database.close();
  } catch(error) {
    console.error(error.toString());
  }
})();

When executed, Sequelize will:

1. Add a new property named userId to the Profile model.

2. Create the users table in the database using this SQL statement:

   CREATE TABLE users (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
   );
   

3. Create the profiles table in the database with an additional foreign key named userId using this SQL statement:

   CREATE TABLE profiles (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     userId INT DEFAULT NULL,
     KEY userId (userId),
     FOREIGN KEY (userId) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE
   );
   

Enforce foreign key uniqueness

As shown in the SQL statements above, Sequelize does not automatically enforce the one-to-one relationship at the SQL level.

This means that when using the hasOne and belongsTo methods to define a one-to-one relationship, it is technically possible for a primary key to be referenced in multiple rows of the target table.

To prevent this and ensure the uniqueness of the foreign key in the target table, you can add a UNIQUE constraint on the foreign key at the SQL level through the foreignKey.unique property of the model:

Source.hasOne(Target, {
  foreignKey: {
    // name: 'keyName',
    unique: true
  }
});

Target.belongsTo(Source);

Which translates to this SQL statement:

CREATE TABLE target (
  id INT AUTO_INCREMENT PRIMARY KEY,
  -- additional columns
  sourceId INT UNIQUE,
  FOREIGN KEY (sourceId) REFERENCES source(id) ON DELETE SET NULL ON UPDATE CASCADE
);

Define one-to-many relationships

In a one-to-many relationship, a record in one table is associated with multiple records in another table.

To create a one-to-many relationship between two models, you can use the hasMany and belongsTo methods of the model instances:

Source.hasMany(Target);
Target.belongsTo(Source);

When executed, Sequelize will perform the same actions as to when defining a one-to-one relationship.

Example

Let's consider this script, that defines a one-to-many relationship between two models named User and Profile:

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

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

    await database.authenticate();

    const Company = database.define('company', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true
      }
    }, {
      timestamps: false
    });

    const Employee = database.define('employee', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataTypes.STRING(120),
        allowNull: false
      },
      department: {
        type: DataTypes.ENUM('Engineering', 'Human Resources', 'Marketing', 'Sales'),
        allowNull: false
      }
    }, {
      timestamps: false
    });

    Company.hasMany(Employee);
    Employee.belongsTo(Company);

    await database.sync();

    await database.close();
  } catch(error) {
    console.error(error.toString());
  }
})();

When executed, Sequelize will:

1. Add a new property named companyId to the Employee model.

2. Create the companies table in the database using this SQL statement:

   CREATE TABLE companies (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(50) NOT NULL UNIQUE
   );
   

3. Create the employees table in the database with an additional foreign key named companyId using this SQL statement:

   CREATE TABLE employees (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(120) NOT NULL,
     department enum('Engineering','Human Resources','Marketing','Sales') NOT NULL,
     companyId INT DEFAULT NULL,
     KEY companyId (companyId),
     FOREIGN KEY (companyId) REFERENCES companies (id) ON DELETE SET NULL ON UPDATE CASCADE
   );
   

Define an explicit foreign key

To explicitly define the name of the foreign key that is added to the target model, you can use the foreignKey.name property:

Source.hasOne(Target, {
  foreignKey: {
    name: 'keyName'
  }
});

Target.belongsTo(Source);

Where 'keyName' is the name of the foreign key in the target table.

Tip: Defining the foreignKey property in the target model instead of the source model will have the same effect.

Change foreign key constraints

To change the behavior of the ON DELETE and ON UPDATE constraints on the foreign key, you can respectively use the onDelete and onUpdate properties:

Source.hasOne(Target, {
  onDelete: 'CONSTRAINT',
  onUpdate: 'CONSTRAINT',
});

Target.belongsTo(Source);

Where 'CONSTRAINT' can be one of:

• 'CASCADE': Deletes the associated child records when the parent record is deleted or updates the foreign key in child records to reflect the updated primary key of the parent record.
• 'RESTRICT': Prevents the parent record from being deleted or updated if any child records reference it.
• 'NO ACTION': Behaves similarly to 'RESTRICT' but allows deferring constraint checks until the end of the transaction (if supported by the database).
• 'SET DEFAULT': Sets the foreign key in the child table to its default value when the parent record is deleted or updated.
• 'SET NULL': Sets the foreign key in the child table to NULL when the parent record is deleted or updated.

Summary

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

• One-to-one relationships are defined using the association of the hasOne and belongsTo model methods.
• One-to-many relationships are defined using the association of the hasMany and belongsTo model methods.
• Many-to-many relationships are defined using the association of the belongsToMany model method.
• Foreign key names can be explicitly defined through the foreignKey.name property.
• Foreign key uniqueness can be enforced through the foreignKey.unique property.
• Foreign key ON DELETE and ON UPDATE constraints can be changed through the onDelete and onUpdate properties.