In which API layer (repository/service) to place custom SQL queries logic?

Question

I'm building an API in which I need data that differs a lot from how it is persisted (for each access to the DB, I'm doing 5-10 joins and I can't perform them via code because it takes too long).

I'm using a controller-service-repository architecture and I have read in several sources that repositories should contain only basic CRUD operations.

Given this:

• should they be at the repository or service level?
• is there any other way of structuring code that fits better in this use case?

Answer 1

with the standard N-Tier architecture (in your case N=3, the most common), "custom" SQL code goes of course into the lowest Tier, the repository.

My suggestion now is to not keep those SQL queries in your code but to move them into stored procedures. You'll get tons of benefits, not just performance.

Repositories should contain the methods you need. Create the proper abstractions and live free :)

Another option would be to use CQRS, which I always strongly encourage to use when the system goes past simple CRUD.