Is there a way to stop sequelize sync() from adding specific foreign keys?

Question

Here is the gist of the problem.

I have a table "Boxes". There are multiple tables of items that can be associated with a box ("Widgets", "Dohickies", "Thingamabobs").

I have a relational table, "ItemsInBox", with BoxId, ItemId, itemType.

In my models, I create the associations (belongsToMany, hasMany) using the "ItemsInBox" as a "through" table, but there are no actual foreign keys associated with the various items tables as it would cause foreign key conflicts. All of that works fine.

The problem is that when I am writing tests for the models, I use sequelize.sync() to generate the tables. Sync() automatically adds foreign keys for all of the associations.

I can't use the "references" property in the model definitions to create the associations because the "Boxes" table would need to be able to reference 3 different tables, but the references property cannot be an array as far as I can tell.

Is there a way to tell sequelize.sync() to skip adding foreign keys for specific associations?

Note: Currently using sequelize v3, but working on upgrading to v5.

bmelton bmelton · Accepted Answer · 2020-06-11T01:07:16

Figured out how to do this.

In your initialization script, you have to loop through all of the models and do [model].associate(models).

Something like

const tableNames = Object.keys(models)
    for (let modelName of tableNames) {
        let model = models[modelName]
        if (typeof model.associate === 'function') model.associate(db)
    }

In your models you have an associate function.

in v3/v4

classMethods: {
    associate: function (models) {
      ...
    }
}

in v5

[model].associate(){
}

or if you are using class definitions instead of define you have an associate property.

Create a second function to create non-foreign-key associations.

in v3/v4

classMethods: {
    associate: function (models) {
      ...
    },
    nfkAssociate: function (models) {
      [model].belongsTo(...)
    }
}

in v5

[model].nfkAssociate = function (models) {
    [model].belongsTo(...)
}

Then in your initialization script

const tableNames = Object.keys(models)
for (let modelName of tableNames) {
    let model = models[modelName]
    if (typeof model.associate === 'function'){ 
       model.associate(db)
    }
}
db.doNFKAssociations = function () {
    for (let modelName of tableNames) {
        const model = db[modelName]
        if (model.nfkAssociate) {
            model.nfkAssociate(db)
        }
    }
}
if (!config.delayNFKAssociations) {
    db.doNFKAssociations()
}

By default, you are running both associate() and nfkAssociate() immediately. If you are in a situation, such as running automated tests, you add an extra property "delayNFKAssociations" into your initialization config which will prevent the execution of the secondary associations.

Where you are using sync():

await sqldb.sequelize.sync({force: true }).then(() => {
  // create all of the associations that shouldn't have foreign keys associated
  sqldb.doNFKAssociations()
})

Now you are creating your associations after the sync() method has run and you don't get unwanted foreign keys.

Is there a way to stop sequelize sync() from adding specific foreign keys?

1 Answers