I'm building an API using Node and Express, with Sequelize and PostgreSql.
I have employees who will belong to one and only one department. I have two tables employees and departments.
I have the following two models in my project, I created the models and migrations using the sequelize-cli
. The associations and foreign key generation appears to be working as expected, when I browse PostgreSql I see the fk constraints.
employee model
'use strict';
const {
Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class Employees extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
}
}
Employees.init({
first_name: {
type: DataTypes.STRING,
allowNull: false
},
last_name: {
type: DataTypes.STRING,
allowNull: false
},
departmentId: {
type: DataTypes.STRING,
allowNull: false
},
createdBy: {
type: DataTypes.STRING,
allowNull: false
},
updatedBy: {
type: DataTypes.STRING
}
}, {
sequelize,
modelName: 'Employees',
});
// UPDATED per suggestions
Employees.associate = function(models) {
Employees.belongsTo(models.Departments, {
foreignKey: 'departmentId',
as: "dept",
onDelete: 'SET NULL',
});
};
return Employees;
};
departments model
'use strict';
const {
Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class Departments extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
}
}
Departments.init({
name: {
type: DataTypes.STRING,
allowNull: false
},
description: {
type: DataTypes.STRING
},
createdBy: {
type: DataTypes.STRING,
allowNull: false
},
updatedBy: {
type: DataTypes.STRING
}
}, {
sequelize,
modelName: 'Departments',
});
// UPDATED per suggestions
Departments.associate = function(models) {
Departments.hasMany(models.Employees, {
foreignKey: 'departmentId',
as: "employees"
});
};
return Departments;
};
employees migration
departmentId: {
type: Sequelize.INTEGER,
onDelete: 'SET NULL',
references: {
model: {
tableName: 'Departments',
},
key: 'id',
},
},
This all appears to work well, if I attempt to create an employee without a valid department id, I get a constraint error as expected.
When I findAll employees using const employees = await this.db.employees.findAndCountAll();
I receive my response shown below
{
"count": 1,
"rows":
[
{
"id": 1,
"first_name": "jon",
"last_name": "doe",
"departmentId": 1,
"createdBy": "jane.dough"
}
]
}
This all works as expected.
TLDR;
However, when I try to include the Departments
model (to join the Department Name with the Department ID)
const employees = await this.db.employees.findAndCountAll({include: [{model: this.db.departments,as: 'dept'}]});
I get the following association error.
"SequelizeEagerLoadingError: Departments is not associated to Employees!"
How do I fix the association to join these together.
UPDATE: After applying the changes suggested by @nazrul-chowdhury, I'm still seeing the error.
I'm posting the relevant code of how I initialize the db and models... I'm thinking I might have something wrong in my process.
employeeRepository.js
const { connect } = require('../config/db.config');
class EmployeeRepository {
db = {};
constructor() {
this.db = connect();
}
async getEmployees() {
try {
const employees = await this.db.employees.findAndCountAll({include: [{model: this.db.departments,as: 'dept'}]});
db.config
const { Sequelize, Model, DataTypes } = require("sequelize");
// const logger = require('../logger/api.logger');
const connect = () => {
const hostName = process.env.HOST;
const userName = process.env.USER;
const password = process.env.PASSWORD;
const database = process.env.DB;
const dialect = process.env.DIALECT;
const sequelize = new Sequelize(database, userName, password, {
host: hostName,
dialect: dialect,
operatorsAliases: 0,
pool: {
max: 10,
min: 0,
acquire: 20000,
idle: 5000
},
logging: false
});
// turn off logging in production (log to console in all other environments)
// logging: process.env.NODE_ENV === 'production' ? false : console.log
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.departments = require("../models/departments.model")(sequelize, DataTypes, Model);
db.employees = require("../models/employees.model")(sequelize, DataTypes, Model);
return db;
}
module.exports = {
connect
}
You have defined a one-to-one association between Employees and Departments, but you actually want a one-to-many relationship where each employee belongs to one department, and each department can have multiple employees. To fix the association, you should make some changes to your models.
In the employee model, change the association definition to use belongsTo instead of hasOne to indicate that an employee belongs to a department. Also, you need to update the foreign key to match the field name in your model.
In the departments model, you should remove the belongsTo association since a department doesn't directly belong to an employee. Instead, if you want to retrieve employees associated with a department, you can use a hasMany association in the Departments model,