Building an HR Management API with NestJS and Prisma: Lessons From Real-World Database Problems

Building a backend application is a complex process that often involves encountering issues that cannot be solved with simple code snippets. I have been working on an HR management API using NestJS, TypeScript, Prisma, MySQL, and Swagger. One of the modules I worked on was an HR query-management system, which allowed authorized HR users to create queries, associate them with employees and organizations, record responses, resolve queries, and maintain an audit trail of important actions.

At first, the architecture seemed straightforward, with four layers: client, controller, service, and Prisma client, all linked to a MySQL database. However, as I progressed, I quickly realized that database relationships, application architecture, and debugging would be significant challenges.

One of the initial lessons I learned was the importance of understanding database relationships. For example, a query could be associated with an organization, an employee, an infraction type, and the user who issued the query. While it may seem tempting to create a record using just the IDs, Prisma's generated types might require a nested relationship based on how the schema is defined. This distinction between foreign key and relation in Prisma helps to understand and resolve errors more effectively.

Another crucial lesson was the significance of ensuring agreement between the database schema and the Prisma schema. If the Prisma schema specifies a required relation, but the actual database table does not contain that relation, it can lead to runtime errors. This highlights the importance of database migrations, schema synchronization, and generated clients in backend development.

Additionally, the design of the API itself becomes crucial when implementing business rules. For instance, different levels of access permissions should be granted based on user roles. An HR Admin would have the ability to create queries, respond, resolve, and view audit history, while a regular employee would only be able to view relevant information. This illustrates the need for role-based authorization in business applications.

Swagger proved to be a valuable tool during development, as it allowed for testing the API endpoints directly. This made it easier to determine whether a problem was originating from the frontend, API request validation, the service, Prisma, or the database.

In conclusion, the most significant lesson I learned was to adopt a holistic approach to system architecture. When encountering issues, it is essential to trace the problem through the entire architecture, from the request to the database, and ask questions such as "Where does the expectation conflict with the actual implementation?" This approach helped me to better understand and resolve problems in my HR management API.