Best Practices for Managing and Maintaining Raw SQL Queries in Codebase Alongside an ORM

My company is currently using an Object-Relational Mapping (ORM) system for handling database operations. However, I've noticed that for some complex queries, fetching a small amount of data (like 20 rows) results in a surprisingly high number of queries (over 80) due to the ORM loading related tables separately instead of using JOIN operations.

To address this, I suggested implementing the Repository pattern. Under this approach, we would use raw SQL for complex queries, while still utilizing the ORM for standard CRUD operations. However, my team raised concerns that incorporating raw SQL could lead to maintenance issues down the line.

I argued that potential maintenance difficulties could be mitigated with the appropriate measures, such as implementing comprehensive unit testing for our SQL queries. However, they remained hesitant, with their primary counter-argument being that our current ORM-based setup is adequately serving our needs as of now (given that we are not experiencing high loads).

I'm reaching out to the community to gain a broader perspective on this issue. How do you manage raw SQL in your codebase alongside an ORM? What strategies do you employ to ensure maintainability? Is unit testing sufficient, or do you have additional documentation or other best practices to share? I'd greatly appreciate any insights you can provide.

Sometimes ORMs generate highly inefficient SQL queries and repository pattern allows to separate SQL queries from other layers by dividing responsibilities among other layers. Having stored raw SQL queries in a separate layer, we have the following benefits:

• the maintaining of sql queries is easier because of great separation of concerns.

• it is easier to tune raw sql queries than SQL query generated by ORM

• raw SQL queries are usually more readable and less verbose