Myujth

I have two tables; userPermissions & permissionItems. userPermissions has a permissionItemId as a foreign key, defined as

CONSTRAINT `fk_userPermissions_permissionItemId`
 FOREIGN KEY (`permissionItemId`)
 REFERENCES `mydb`.`permissionItems` (`permissionItemId`)

Their Sequelize definitions are

const PermissionItem = db.define('permissionItems', 
 permissionItemId: 
 type: Sequelize.INTEGER,
 autoIncrement: true,
 primaryKey: true
 
);


const UserPermission = db.define('userPermissions', 
 userPermissionsId: 
 type: Sequelize.INTEGER,
 autoIncrement: true,
 primaryKey: true
 ,
 permissionItemId: 
 type: Sequelize.INTEGER,
 references: 'permissionItems',
 referencesKey: 'permissionItemId'
 
);
UserPermission.hasOne(PermissionItem);

I am then trying to join those two with the and view the results with

UserPermission.findAll(
 include: [
 model: PermissionItem
 ]
).then(userPermission => 
 console.log('userPermission', userPermission);
);

My expectation here is to get a list of all UserPermissions and outer left join PermissionItem on permissionItemId. Instead, I receive the below error

Unhandled rejection SequelizeDatabaseError: Unknown column
'permissionItem.userPermissionUserPermissionsId' in 'field list'

Have I constructed the query incorrectly? Or the Sequelize definitions?

I have tried flipping these around and including the reverse, and applying the keys backward, in every combination I could think of, all to no avail

In your userPermissions model,
Instead of this

UserPermission.hasOne(PermissionItem);

try this

UserPermission.belongsTo(PermissionItem,foreignKey:'permissionItemId');

HasOne and BelongsTo insert the association key in different models
from each other. HasOne inserts the association key in target model
whereas BelongsTo inserts the association key in the source model.

I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call

UserPermission = db.define('userPermissions', 
 userPermissionsId: 
 type: Sequelize.INTEGER,
 autoIncrement: true,
 primaryKey: true
 ,
 permissionItemId: 
 type: Sequelize.INTEGER
 
);
UserPermission.hasMany(PermissionItem, foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' );