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

Question

I'm creating models and migrations for two types, Player and Team that have many to many relationship. I'm using sequelize model:create, but don't see how to specify foreign keys or join tables.

sequelize model:create --name Player --attributes "name:string"
sequelize model:create --name Team --attributes "name:string"

After the model is created, I add associations. In Player:

Player.belongsToMany(models.Team, { through: 'PlayerTeam', foreignKey: 'playerId', otherKey: 'teamId' });

In Team:

Team.belongsToMany(models.Player, { through: 'PlayerTeam', foreignKey: 'teamId', otherKey: 'playerId' });

Then the migrations are run with

sequelize db:migrate

There are tables for Player and Team but there's no join table (nor foreign keys) in the database. How can the foreign keys and join table be created? Is there a definitive guide on how to do this?

Answer 1

I also have the same question like you, I've searched, but no luck. This is the way what I did and I modify following your code. I create migration for join table manually. And I add compound index for both foreign keys.

module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.createTable('PlayerTeam', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
    playerId: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: {
        model: 'Player',
        key: 'id'
      },
      onUpdate: 'cascade',
      onDelete: 'cascade'
    },
    teamId: {
      type: Sequelize.INTEGER,
      allowNull: false,
      references: {
        model: 'Team',
        key: 'id'
      },
      onUpdate: 'cascade',
      onDelete: 'cascade'
    },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    }).then(() => {
      // Create Unique CompoundIndex
      let sql = `CREATE UNIQUE INDEX "PlayerTeamCompoundIndex"
              ON public."PlayerTeam"
              USING btree
              ("playerId", "teamId");
            `;
      return queryInterface.sequelize.query(sql, {raw: true});
      });
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('PlayerTeam');
  }
};