Is it bad practice to call a DB function for all of my returns?

73 Views Asked by At

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;
  }
}
0

There are 0 best solutions below