When is it appropriate to write a direct SQL query instead of using an ORM layer?

It is advisable to write direct SQL queries instead of using ORM when:

• You need to perform a complex or specific query that is difficult or inefficient to express through ORM.
• You want to optimize performance, for example, by using complex JOINs, subqueries, or aggregate functions.
• You need to utilize database-specific features that ORM does not support.
• You need to perform bulk operations (bulk insert/update) that ORM executes slowly.

Example in Python using SQLAlchemy:

from sqlalchemy import text

# Direct SQL query for complex aggregation
result = session.execute(text("""
    SELECT user_id, COUNT(*) as orders_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
"""))

for row in result:
    print(row.user_id, row.orders_count)