hasMany and belongsTo pairs duplicates attempts to add the foreign key column.

[brianreeve]

I am trying to create a relationship where an Organization has many Users.

Sequelize documentation recommends specifying the relation in both models for one-to-many relationships. However, doing so creates duplicate attempts to add the foreign key column. Depending on how you do this, it manifests different results.

My intent is to have an Organization model class with:

  static associate(models) {
    Organization.hasMany(models.User, { foreignKey: `organization_id` });
  }

and a User model class with:

  static associate(models) {
    User.belongsTo(models.Organization);
  }

Scenario 1 - Adding associations to one model at a time

My first attempt I only defined the hasMany in the Organization model, generated a migration, and applied it.

Then I noticed the documentation's recommendation to also add the corresponding belongsTo in the User model. When I generated a migration, it also had an addColumn for the organization_id FK.

Migrating up again fails with the following:

Error: Migration 20211029204547_mig.js (up) failed: Original error: Duplicate column name 'organization_id'

Scenario 2 - Creating migration after both models and relations are defined

If I blow everything away, then try again with both models and their relations defined I can successfully generate and run migrations, but there are extraneous and slightly confusing artifacts as a result.

Generating the migration looks like:

...
> npx sequelize-mig migration:make --name mig

_current.json not found. first time running this tool
[Action #01] createTable() => "organizations", deps: []
[Action #02] createTable() => "users", deps: [organizations, organizations]

Note, the confusing duplicate deps for the users table. This lead me to look into the migration to see what that was all about.

It turns out, the migration contains a fn: "createTable" operation with two organization_id columns, like this:

        organization_id: {
          type: Sequelize.INTEGER,
          field: "organization_id",
          onUpdate: "CASCADE",
          onDelete: "SET NULL",
          references: { model: "organizations", key: "id" },
          allowNull: true,
        },
        OrganizationId: {
          type: Sequelize.INTEGER,
          field: "organization_id",
          onUpdate: "CASCADE",
          onDelete: "SET NULL",
          references: { model: "organizations", key: "id" },
          allowNull: true,
        },

In this scenario, sequelize-cli to migrate up does indeed work correctly, but the extraneous artifacts in the console output and migration are a bit confusing.