I'm working on a Node/Express/PSQL/Kysely backend. I have an employees route where a majority of the time the server is going to send the employee with the same object structure. Is it bad practice to call a DB function (getEmployeeById) for all of my returns? I feel like I'm making more database calls then needed but want my return value to be structured the same across all requests. EX: In my createEmployee and UpdateEmployee functions I already have access to the employee when running my insert or update query, I could technically get the values I need from there using "RETURNING" in my query but if I ever want to change the structure of that return I'd have to update it in every function. This is why I opted to use a getEmployeeById for my return value.
export async function createEmployee(employee: NewEmployee, roles: Roles) {
try {
if (!employee || !roles) {
throw new CustomError(400, 'Invalid data supplied');
}
const newEmployeeId = await db.transaction().execute(async (trx) => {
const newEmployee = await trx
.insertInto('employees')
.values(employee)
.returning(['id', 'name', 'email', 'username', 'create_date'])
.executeTakeFirstOrThrow();
for (let role in roles) {
if (roles[role as keyof Roles]) {
await trx
.insertInto('employee_roles')
.values({
employee_id: newEmployee.id,
role_id: role,
})
.execute();
}
}
return newEmployee.id;
});
return await getEmployeeById(newEmployeeId);
} catch (error) {
throw error;
}
}
export async function updateEmployee(id: number, employee: EmployeeUpdate, roles: Roles | null) {
try {
await db.transaction().execute(async (trx) => {
await trx
.updateTable('employees')
.set(employee)
.where('id', '=', id)
.returning(['id', 'name', 'email', 'username', 'create_date'])
.executeTakeFirstOrThrow();
if (!roles) return;
for (let role in roles) {
if (roles[role as keyof Roles]) {
const doesRoleExist = await trx
.selectFrom('employee_roles')
.select('role_id')
.where('role_id', '=', role)
.executeTakeFirst();
if (!doesRoleExist) {
throw new CustomError(401, `Role ${role} does not exist`);
}
const existingRole = await trx
.selectFrom('employee_roles')
.select(['employee_id', 'role_id'])
.where('employee_id', '=', id)
.where('role_id', '=', role)
.executeTakeFirst();
if (!existingRole) {
await trx
.insertInto('employee_roles')
.values({
employee_id: id,
role_id: role,
})
.execute();
}
} else {
await trx.deleteFrom('employee_roles').where('employee_id', '=', id).where('role_id', '=', role).execute();
}
}
});
return await getEmployeeById(id);
} catch (error) {
throw error;
}
}