Foreign Key with Sequelize not working as expected

Question

I was trying to create an association between two tables and I wanted to add a foreign key.

The two models are User and Companies

User.associate = (models) => { User.belongsTo(models.Companies, { foreignKey: 'Company' }); };

My expectation of the code above was that a Company ID field gets added in the user table which references the Company ID of the Companies table.

On running the code above, I don't see any additional columns getting created. I tried checking if a foreign key association is created in the DB and that also is missing.

However, if I try to add a column with the same name while keeping the association code, I get a name conflict. This seems to suggest that the association is getting created but I am unable to see it.

Could someone help me understand what I am doing wrong? Thanks for the help!

models/company.js

module.exports = (sequelize, DataTypes) => {
    var Company = sequelize.define('company', {
        company: { type: DataTypes.STRING, primaryKey: true },
    });

    Company.associate = (models) => {
        Company.hasMany(models.user, { as: 'users' });
    };

    Company.sync();

    return Company;
};

models/user.js

const uuid = require('uuid/v4');

'use strict';
module.exports = (sequelize, DataTypes) => {
    var User = sequelize.define('user', {
        id: { type: DataTypes.UUID, primaryKey: true },
        name: { type: DataTypes.STRING, allowNull: false }
    });

    User.associate = (models) => {
        User.belongsTo(models.company);
    };

    User.beforeCreate((user, _ ) => {
        user.id = uuid();
        return user;
    });

    User.sync();

    return User;
};

models/index.js

'use strict';

var fs        = require('fs');
var path      = require('path');
var Sequelize = require('sequelize');
var basename  = path.basename(__filename);
var env       = process.env.NODE_ENV || 'development';
// var config    = require(__dirname + '/../config/config.js')[env];
var db        = {};

// if (config.use_env_variable) {
//   var sequelize = new Sequelize(process.env[config.use_env_variable], config);
// } else {
//   var sequelize = new Sequelize(config.database, config.username, config.password, config);
// }

const sequelize = new Sequelize('postgres://postgres:user@localhost:5432/mydb');

fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
      var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Answer 1

Your model is fine! you must remove sync from models file , then check migration file for models with foreign key that foregin key is there,

for Migration User :

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable('Users', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.UUID
            },
            name: {
                type: Sequelize.STRING
            },
            companyId: {
                type: Sequelize.UUID,
                references: {
                    model: 'Company',// company migration define
                    key: 'id'
                }
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE
            },
            updatedAt: {
                allowNull: false,
                type: Sequelize.DATE
            }
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('Users');
    }
};

for create automate table from index.js and models you must install sequelize-cli

by type npm install --save sequelize-cli

then you must run this command for create models table in db

sequelize db:migrate

Answer 2

I was able to get this resolved.

The issue was with regard to the sequence in which the sync was called. In my original code, I was calling sync inside each model. Even though I added the options force and alter, I think the foreign keys were not getting added. So, I removed the sync code from inside the models, and added it in a separate loop inside index.js.

This gave me a new issue. Tables were getting created in an order that is not consistent with the order in which tables should be created for foreign keys to work since tables should pre-exist. I resolved it by manually providing the sequence of sync and now I see the columns getting created.

To summarise: model defn -> model association -> model sync in sequence

Thank you for your suggestions, members of SO.

Answer 3

My guess is that the associate method is not getting called, and therefore, your association does not get created. Keep in mind that associate is not a built-in Sequelize method, but it is just a pattern used by the community. (More info on this thread)

There are various approaches to handle calling associate, here is one example. You have a models.js file that handles your association and you initialize that inside your main app.js file.

// app.js (aka your main application)
const models = require('./models')(sequelize, DataTypes);

// models.js
module.exports = (sequelize, DataTypes) => {
    const models = {
        user: require('./userModel')(sequelize, DataTypes),
        company: require('./companyModel')(sequelize, DataTypes)
    };

    Object.keys(models).forEach(key => {
        if (models[key] && models[key].associate) {
            models[key].associate(models);
        }
    });
};

// companyModel.js
module.exports = (sequelize, DataTypes) => {
    var Company = sequelize.define('company', {...});

    Company.associate = (models) => {
        Company.hasMany(models.user, { as: 'users' });
    };

    Company.sync();

    return Company;
};

// userModel.js
module.exports = (sequelize, DataTypes) => {
    var User = sequelize.define('user', {...});

    User.sync();

    return User;
};

Also, FYI, You probably know this but sync should only be used for experimenting or testing, not for a production app.

Answer 4

By using foreignKey: 'Company' you are telling it to associate with a column named Company. You typically also want to use singular table names, so company with an association of companies. By default Sequelize will use the primary key for the association, so you only need to specify foreignKey if you want to change it or set other parameters.

const User = sequelize.define(
  'user',
  { /* columns */ },
  { /* options */ }
);
User.associate = (models) => {
    User.belongsTo(models.Company);
};

const Company = sequelize.define(
  'company',
  { /* columns */ },
  { /* options */ }
);
Company.associate = (models) => {
    Company.hasMany(models.User, { as: 'users' });
};

This will create the following tables Company (id) and User (id, company_id).

Query all User records associated to a single Company:

const user = await User.findAll({ include: { model: Company } });
/*
user = {
  id: 1,
  company_id: 1,
  company: {
    id: 1,
  },
};
*/

Query all Company records with multiple associated User records via users:

const company = await User.findAll({ include: { model: User, as: 'users' } });
/*
company = {
  id: 1,
  users: [{
    id: 1
    company_id: 1,
  }],
};
*/