Sequelize association belongsTo incorrect foreign key

Question

I'm currently running sequelize V6 migration and I'm not able to establish the correct association between tables.

User Model:

'use strict';
module.exports = function(sequelize, DataTypes){
  var user = sequelize.define('user', {
    email: {
      type: DataTypes.STRING,
    },
  })

  user.associate = function(models){
    user.belongsTo(models.person, { foreignKey: 'personId' });
  return user
}

User table migration:

"use strict";
module.exports = {
  up: (queryInterface, Sequelize) => {
    const common = require("../db/util/commonFields")(Sequelize);
    let fields = Object.assign(common, {
      email: {
        type: Sequelize.STRING,
      },
      personId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'person',
          key: 'id',
        }
      },
    
    return queryInterface.createTable("User", fields);
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("User");
  },
};

Person Model:

'use strict';
module.exports = function(sequelize, DataTypes){
  var Person = sequelize.define('person', {
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
  })
  Person.associate = function(models){
    Person.hasOne(models.user);
  }
  return Person
}

Person table migration:

"use strict";
module.exports = {
  up: async (queryInterface, Sequelize) => {
    const common = require("../db/util/commonFields")(Sequelize);
    let fields = Object.assign(common, {
      firstName: {
        type: Sequelize.STRING,
      },
      lastName: {
        type: Sequelize.STRING,
      }
    })
    return queryInterface.createTable("Person", fields);
  },
  down: async (queryInterface, Sequelize) => {
    return queryInterface.dropTable("Person");
  },
}

model/index.js

'use strict';

var fs        = require('fs');
var path      = require('path');
var Sequelize = require('sequelize');
var basename  = path.basename(__filename);
var db        = {};
const { DataTypes } = require("sequelize");

module.exports = function(sequelize){
  fs
    .readdirSync(__dirname)
    .filter(file => {
      return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
    })
    .forEach(file => {
      var model = require(path.join(__dirname, file))(sequelize, DataTypes);
      db[model.name] = model;
    });
  
  Object.keys(db).forEach(modelName => {
    if (db[modelName].associate) {
      db[modelName].associate(db);
    }
  });
  
  db.sequelize = sequelize;
  db.Sequelize = Sequelize;
  return db;
}

And I'm trying to do the following

const models = require('../models')(sequelize, DataTypes);
const User = models.user;
const Person = models.person;
const result = await User.findAll({
  raw: true, 
  nest: true,
  include: [Person]
});

But I got the following errors

error: Failed to lift app: SequelizeDatabaseError: Unknown column 'person.personId' in 'on clause'
    at Query.formatError (/Users/echen/WeeCollege/WCApi/node_modules/sequelize/lib/dialects/mysql/query.js:265:16)
    at Query.run (/Users/echen/WeeCollege/WCApi/node_modules/sequelize/lib/dialects/mysql/query.js:77:18)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async /Users/echen/WeeCollege/WCApi/node_modules/sequelize/lib/sequelize.js:619:16
    at async MySQLQueryInterface.select (/Users/echen/WeeCollege/WCApi/node_modules/sequelize/lib/dialects/abstract/query-interface.js:953:12)
    at async Function.findAll (/Users/echen/WeeCollege/WCApi/node_modules/sequelize/lib/model.js:1753:21)
    at async Object.findAll (/Users/echen/WeeCollege/WCApi/db/companyDao/UserDao.js:91:20)
    at async Object.main [as bootstrap] (/Users/echen/WeeCollege/WCApi/config/bootstrap.js:263:15)

According to the Sequelize documentation, it states

The A.belongsTo(B) association means that a One-To-One relationship exists between A and B, with the foreign key being defined in the source model (A).

Now in my case, personId is defined in User so belongsTo should be using user.personId join on person.id. However, now it's looking for person.personId.

So I did a little digging and I found that when sequelize is generating join, it determines if association is an instance of BelongsTo like the following

const attrLeft = association instanceof BelongsTo ?
  association.identifier :
  association.sourceKeyAttribute || left.primaryKeyAttribute;

https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js#L1662

And it seems like the association is not an instanceof BelongsTo so it defaults to the primary key which is id.

Here's what association looks like in the debugger:

{
  source: class extends Model {},
  target: class extends Model {},
  options: {
    foreignKey: "personId",
    hooks: {
    },
    useHooks: false,
    timestamps: true,
    validate: {
    },
    freezeTableName: true,
    underscored: false,
    paranoid: false,
    rejectOnEmpty: false,
    whereCollection: null,
    schema: null,
    schemaDelimiter: "",
    defaultScope: {
    },
    scopes: {
    },
    indexes: [
    ],
    name: {
      plural: "people",
      singular: "person",
    },
    omitNull: false,
    sequelize: {...},
  },
  scope: undefined,
  isSelfAssociation: false,
  as: "person",
  associationType: "BelongsTo",
  isSingleAssociation: true,
  foreignKeyAttribute: {
  },
  foreignKey: "personId",
  identifier: "personId",
  identifierField: "personId",
  targetKey: "id",
  targetKeyField: "id",
  targetKeyIsPrimary: true,
  targetIdentifier: "id",
  associationAccessor: "person",
  accessors: {
    get: "getPerson",
    set: "setPerson",
    create: "createPerson",
  },
}{
  source: class extends Model {},
  target: class extends Model {},
  options: {
    foreignKey: "personId",
    hooks: {
    },
    useHooks: false,
    timestamps: true,
    validate: {
    },
    freezeTableName: true,
    underscored: false,
    paranoid: false,
    rejectOnEmpty: false,
    whereCollection: null,
    schema: null,
    schemaDelimiter: "",
    defaultScope: {
    },
    scopes: {
    },
    indexes: [
    ],
    name: {
      plural: "people",
      singular: "person",
    },
    omitNull: false,
    sequelize: {...},
  },
  scope: undefined,
  isSelfAssociation: false,
  as: "person",
  associationType: "BelongsTo",
  isSingleAssociation: true,
  foreignKeyAttribute: {
  },
  foreignKey: "personId",
  identifier: "personId",
  identifierField: "personId",
  targetKey: "id",
  targetKeyField: "id",
  targetKeyIsPrimary: true,
  targetIdentifier: "id",
  associationAccessor: "person",
  accessors: {
    get: "getPerson",
    set: "setPerson",
    create: "createPerson",
  },
}

The association looks right to me but why is it not an instanceof BelongsTo? Or am I using the association wrong?

Answer 1

Perhaps because I used sqlite rather than mysql I never replicated your error, but it looks like the problem lied with: user.belongsTo(models.person, { foreignKey: 'personId' }); some of the associations try to add foreign keys to your database tables. here sequelize thinks Person.personId already exists and assumes that when it writes queries. If you don't provide a foreign key it tries to auto-generate one. I find the manual obtuse and figure it out by watching the queries sequelize outputs to the console.
Here is similar working code: assuming:

npm init -y
npm install --save-dev sequelize-cli
npm install sequelize -save
npx sequelize init
npx sequelize-cli model:generate --name Person  --attributes firstName:string,lastName:string  //also something to get a user model

I haven't found a way to add foreign keys and assosciations other than editing the migration and model files so change the appropriate files to:

'use strict';
// user migration
//removed const common as I had no idea what it did or why
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: { //migrations create tables so need primary keys
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      email: {
        type: Sequelize.STRING,
      },
      personId: { //each user is a person relationship 
        type: Sequelize.INTEGER,
        references: {
          model: 'person',
          key: 'id',
        }}
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

'use strict';
//person migration
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('People', {
      id: {  //migrations create tables so need primary keys 
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('People');
  }
};

'use strict';
//person.js
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Person extends Model {
      static associate(models) {
      this.hasOne(models.User,{foreignKey: "personId"});  //see https://sequelize.org/master/manual/assocs.html //creates functions and foreign key in user
    }
  };
  Person.init({
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING
  }, {
    sequelize,
    timestamps: false, // stops sequelize from trying to get created columns see https://stackoverflow.com/questions/20386402/sequelize-unknown-column-createdat-in-field-list
    modelName: 'Person'
  });
  return Person;
};

'use strict';
//user.js
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
      static associate(models) {
      this.belongsTo(models.Person, {foreignKey: "personId"}); //creates assosciation functions without creating a foreign key
    }
  };
  User.init({
    email: DataTypes.STRING,
    personId: { 
      type: DataTypes.INTEGER,
      references: {
        model: 'person',
        key: 'id',
      }}
  }, {
    sequelize,
    timestamps: false,
    modelName: 'User'
  });
  return User;
};

//index.js in main file to test code
const db = require('./models');
const asyncouterFunction = async function() {
const User = db.User;
const Person = db.Person;
await Person.create({firstName: "James", lastName:"Kirk"});
await Person.create({firstName: "Leonard", lastName:"Mcoy"});
const People = await Person.findAll();
console.log(JSON.stringify(People))
}
asyncouterFunction();