How to create join table with foreign keys with sequelize or sequelize-cli

When working with Sequelize or Sequelize CLI to handle database models and their relationships, creating a join table with foreign keys is a common requirement. The following steps and examples demonstrate how to create such a join table using both tools.

1. Defining Models with Sequelize

First, define the models involved. Assume you have two models: User and Project, with a many-to-many relationship. You need a join table to handle this relationship; we can name this join table UserProjects.

User Model (User.js):

javascript
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    username: DataTypes.STRING
  });
  return User;
};

Project Model (Project.js):

javascript
module.exports = (sequelize, DataTypes) => {
  const Project = sequelize.define('Project', {
    title: DataTypes.STRING
  });
  return Project;
};

Join Table Model (UserProject.js):

javascript
module.exports = (sequelize, DataTypes) => {
  const UserProject = sequelize.define('UserProject', {
    role: DataTypes.STRING  // e.g., the role of the user in the project
  });
  return UserProject;
};

2. Setting Relationships

In your model definitions, define the relationships between models and specify foreign keys.

javascript
// index.js or other file initializing Sequelize
const User = sequelize.import('./User');
const Project = sequelize.import('./Project');
const UserProject = sequelize.import('./UserProject');

User.belongsToMany(Project, { through: UserProject });
Project.belongsToMany(User, { through: UserProject });

This code sets up the many-to-many relationship between User and Project using the UserProject join table.

3. Generating Models and Migration Files with Sequelize CLI

If you are using Sequelize CLI, you can generate models and migration files using the following commands:

bash
sequelize model:create --name User --attributes username:string
sequelize model:create --name Project --attributes title:string
sequelize model:create --name UserProject --attributes userId:integer,projectId:integer,role:string

In the generated migration file, define the foreign key relationships:

javascript
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('UserProjects', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      userId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'Users',
          key: 'id'
        },
        onDelete: 'CASCADE'
      },
      projectId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'Projects',
          key: 'id'
        },
        onDelete: 'CASCADE'
      },
      role: {
        type: Sequelize.STRING
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('UserProjects');
  }
};

Conclusion

By defining models, setting relationships, and configuring migration files, you can create a join table with foreign keys using Sequelize and Sequelize CLI. This approach ensures data integrity and relationships, and also supports database maintenance and scalability.