Foreign Key with Sequelize not working as expected

sequelize create with foreign key
sequelize migration foreign key
sequelize composite foreign key
sequelize include foreign key
sequelize belongstomany
sequelize create with association
sequelize custom foreign key
sequelize polymorphic

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;

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.

foreignKey option on associations definition not working as , Example: module.exports = function(sequelize, DataTypes) { var foreignKey option on associations definition not working as expected #3336. When setting the foreignKey option to specify the column name that will be created on the association table, it's created but not used. Example: module . exports = function ( sequelize , DataTypes ) { var SearchProcess = sequelize . define ( ' SearchProcess ' , { code : { type : DataTypes .

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

hasMany with "as" alias not working as expected · Issue #6881 , the foreignKey generated does not use the as alias name like it should. This issue is caused here: if (!this.foreignKey) { this.foreignKey = Utils. Oop, I don't know why when I change file 00-User on the top file of migration => It's worked! But when I change the position to 08-User => Foreign key constraint (Note: 01 => 07 Do not have any constraint with User Table)

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,
  }],
};
*/

Relations/Associations, BelongsTo associations are associations where the foreign key for the A simple example would be a User being part of a team Team with the foreign key on user. Whether the attributes are camelcase or not depends on the two models the alias should be singular, while for many associations (has many) it should be  I'm not super great with MySQL yet so im struggling with why this is happening. I had everything working fine and then I dropped the DB to start fresh and now the code no longer works. Here&#39

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.

Tutorial | Sequelize, BelongsTo associations are associations where the foreign key for the one-to-​one before you define the association, and then tell sequelize that it should use that However, this might not always work for irregular or non-english words. The the db, both tables are generated with fields as expected, and a foreign key is placed on the GpsEvent table (event_id) however no primary key is generated on this table. If I define a separate field on this table for the primary key (eg. gps_event_id) and then both primary key (gps_event_id) and foreign key (event_id) are generated correctly. It appears sequelize doesn't generate the PK when an FK is defined on the same column.

SequelizeJS, Constraints & Circularities · Enforcing a foreign key reference without You'll also have to manually install the driver for your database of choice: The findByPk method obtains only a single entry from the table, using the provided primary key. Also, recall that since timestamps is true by default, we should expect the  Been working with sequelize for a couples days now. Awesome. :) I was using the "references" option to define foreign keys (postgres), but then found the association functions. I'm finding, however, that while the functions add the column, they're not adding the constraints (and, obviously cascading).

Sequelize targetKey not working - node.js - html, However, targetKey is not working as expected. belongsTo(Houseds, {​foreignKey: 'ResidenceCity', targetKey: 'id'}); Then i have created this function: function  You can also define the foreign key, e.g. if you already have an existing database and want to work on it: */ Project.hasOne(User, { foreignKey: 'initiator_id' }) /* Because Sequelize will use the model's name (first parameter of define) for the accessor methods, it is also possible to pass a special option to hasOne: */ Project.hasOne(User

So you want to use Typescript with Sequelize?, How to setup and use Typescript with Sequelize v4 in your Node The problem with that approach is that there's no way to maintain type checking. The foreignKey option allows you to manually specify the name of the  One-To-One associations are associations between exactly two models connected by a single foreign key. BelongsTo. BelongsTo associations are associations where the foreign key for the one-to-one relation exists on the source model. A simple example would be a Player being part of a Team with the foreign key on the player.

Comments
  • Your code sample is valid. I just tried and was able to create a field Company in the users table. Could you share your full model definition as well are your DB config?
  • I have added it. I am using sequelize version ^4.41.0.
  • Please clarify via post edits, not comments.
  • Thanks for sharing the code samples. I have updated the post with what I have done (similar to your code), but I don't see the foreign key appear when I look at the table. I am using sequelize version ^4.41.0
  • Are you running Sequelize.sync()? The new column won't be automatically created...
  • Yes, I am calling the sync() function. I thought it would create the new column. What should I do then?
  • If I call associate in this way, it fails because the associate function is expecting models and we are sending nothing, and thus gets treated as undefined.
  • @NikhilBaliga I've updated my answer to include more details
  • Thanks for the updated code and the additional note, @mcranston18. However, I am calling the associate function. I have updated the query with the index.js file and if you notice, I am calling the associate function at the end. I had picked this code up as a standard way of doing it. I added console.log statements and found that the associate function is being called. But the relations are not getting created.