Every engineering team has this argument once, and the two loudest positions are both lazy. The first says use the ORM everywhere because hand written SQL is dangerous, and it ships applications that generate nine hundred queries to render one page. The second says the ORM is a crutch and real developers write SQL, and it ships a codebase where every column addition is a multi day archaeology project. The mature position is neither: an ORM is the right tool for most of a typical application, raw SQL is the right tool for a measurable minority, and the boundary between them is a deliberate, documented decision rather than a personality trait. This article draws that boundary with real code, real failure modes and a working set of team rules.
What an ORM actually generates and where it hides the query plan
An ORM translates object operations into SQL, and the translation is where the value and the risk live together. The naive loop is the canonical demonstration. Fetching orders and then their line items:
orders = session.query(Order).filter(Order.status == "paid").all()
for order in orders:
total = sum(item.price for item in order.items)reads as one query for orders plus a lazy walk over items, and executes as one query for orders plus one query per order. Twenty orders on the page produce twenty one round trips, a thousand orders produce a thousand and one, and the pattern is invisible in the source because every line looks reasonable. The N plus one query problem is not an ORM bug, it is the direct consequence of an interface that hides statements behind attribute access, and it is also the direct consequence's cure: the same ORM eagerly fetches the relation in one join when told to, and the fixed version differs by one argument:
orders = session.query(Order)
.options(selectinload(Order.items))
.filter(Order.status == "paid").all()The tool hid the plan, and the tool reveals it again, but only for the developer who looks. That requirement, looking at the SQL the application actually sends, is the foundation under every rule in this article, and the query log is how it is satisfied in practice: statement logging in development, query counters in review, and an explain plan for anything on a hot path.
Where an ORM earns its keep without apology
The boring majority of a business application is single table CRUD with a handful of joins, and the ORM is excellent at exactly that. Creating a user with validation, inserting a payment row with a typed enum, loading an order by identifier and updating one field, mapping snake case columns to camel case objects without a single hand written result row: these operations are the daily traffic of most services, and writing them in raw SQL means writing the mapping layer again per repository, per language, per team.
The ORM also carries the schema with the code. Migrations that version control the DDL, entity classes that name the columns once, and compile time or test time errors when a rename misses a call site, these are correctness features that raw SQL delegates to review discipline. A third of production incidents in database backed services trace back to a mismatch between what the code assumes and what the schema contains, and the ORM's tight coupling is a machine enforced contract against that class of bug. The honest accounting: for create, read, update and delete by key with modest relations, the ORM is faster to write, faster to change, and no slower at runtime than the SQL it emits, because for that class of query it emits exactly the SQL a professional would write by hand.
The queries that belong in raw SQL
The minority of queries that carry the application's weight are exactly the ones the general purpose translation layer serves worst. Reporting is the first resident: window functions over grouped aggregates, recursive traversals for org charts and bills of materials, pivot shaped summaries for finance, queries where the shape of the result is the point. A revenue report with a moving twelve month window is one statement in SQL:
SELECT month,
sum(revenue) AS revenue,
avg(sum(revenue)) OVER (
ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS trailing_avg
FROM monthly_revenue
GROUP BY month ORDER BY month;and it is a half page of in memory iteration when written against entities, slower by orders of magnitude and wrong by one off by one error. Bulk operations are the second resident: loading ten million rows through an ORM session is a memory event with a session cache that must be flushed in batches, while COPY in PostgreSQL or LOAD DATA in MySQL streams the same rows as fast as the disk accepts, and the ORM's own documentation points at the native path for exactly this reason. The third resident is the tuned path: the query the pager is about, where the plan matters more than the mapping and every join hint and partial index predicate belongs in a reviewable statement rather than in an options chain three levels deep.
Every mainstream ORM knows this and ships the escape hatch: custom SQL with entity hydration in Hibernate, JdbcTemplate in Spring, Dapper beside Entity Framework, query functions in Ecto. The mature codebase uses the escape hatch loudly, in a repository method with a comment that says what the query is for, rather than smuggling SQL into string concatenations across the codebase.
N plus one detection and repair in a live codebase
The N plus one problem deserves its own discipline because it does not announce itself. The detection is mechanical: run the page rendering in development with the query log open, or assert on the query count in a test:
def test_orders_page_query_count(self):
with assert_max_queries(3):
response = self.client.get("/orders?status=paid")
assert response.status_code == 200The test fails the day a developer adds a lazy relation to a template, which is the day the fix is one eager load option instead of a production incident. Review practice picks up what the test cannot: the reviewer who sees a collection iterated with attribute access asks how the relation loads, and the team norm answers with the eager fetch, the batched fetch, or a comment explaining why lazy is deliberate.
The repair has three levels. Eager loading with a single join or a single batched secondary query is the first and most common. Projection is the second: selecting exactly the columns the view needs collapses the object graph into a flat result, which is both faster and immune to lazy loading because it never materializes entities at all. The third is architectural, moving the read into a query service that returns data transfer objects straight from SQL, which is the article's boundary expressed in code: the ORM owns the writes and the entity lifecycle, raw SQL owns the reads that carry the page.
Profiling the real time before blaming the tool
Arguments about ORM speed are settled with measurements, and the measurements live on the database side, where the ORM's reputation goes to be tested. The first instrument is the slow query log or statement log, which records every statement that exceeds a threshold and names the exact SQL the ORM hid, with the parameters that ran. The second is pg_stat_statements on PostgreSQL or the performance schema on MySQL, which aggregates execution counts and total time per statement across production traffic, converting an argument about tools into a ranked list of the ten statements that cost the fleet the most wall clock.
The ranked list is the productive place for the boundary review. A statement that shows up with a high execution count and a modest mean belongs to the ORM's territory, a CRUD query executed four million times a day that needs an index, not a rewrite. A statement with a high total time and a plan full of sorts and subqueries is the candidate for the raw SQL escape hatch, and the explain plan, read by a developer who wrote the SQL or by one who has to read the ORM's output, decides the fix. Teams that run this review monthly migrate their boundary gradually and on evidence rather than on argument: each cycle moves one or two heavy reads from entity land into reviewed SQL, and the ORM keeps the rest.
Transactions bulk writes and the ORM session boundaries
The session is the ORM's unit of work, and its boundaries decide both correctness and throughput. The correct transaction is the one that spans exactly the business operation, one order with its items, one transfer with its two balance rows, and the session flush is the moment the accumulated changes become one database transaction. The failure modes sit on both sides: a session held across a request, accumulating a thousand dirty entities and flushing as one giant surprise, and the opposite, an update loop that commits per row, turning a bulk job into a lock churn event.
Batched writes are the practical middle. Flush and clear every few hundred entities, keep the transaction bounded, and let the database see a stream of reasonable size. For real volume, the answer from the previous section stands: COPY, LOAD DATA, or a staging table plus a set based INSERT from the select, with the ORM's role ending at scheduling the job and recording its result. A nightly import written as an entity loop is the most common self inflicted outage in this category, and it converts a ten minute data load into an overnight memory profile with a transaction log that replicates badly.
The team rules that keep both tools honest
The boundary is maintained by convention, and the convention needs to be written down where new joiners read it. The working set fits in one list:
- Log every SQL statement in development and review the log before merging features;
- Write reporting, aggregation and bulk loading as reviewed SQL, not as entity iteration;
- Assert on query counts in tests for the pages that render collections;
- Eager load relations that templates touch, and let the query options document the intent;
- Bound every session to one business operation and flush batch jobs every few hundred rows;
- Explain plan anything on a hot path, and record the expected plan beside the query.
The choice between ORM and SQL is not ideological, it is a division of labor that follows the query. The ORM carries the application's daily traffic where its mapping and migration machinery pays for itself, raw SQL carries the heavy reads where the plan is the product, and the codebase that draws the line in writing gets both speeds without either failure mode.
The ORM is a translator, and like every translator it is judged on the sentences it produces rather than on its vocabulary. The team that reads the translated SQL regularly gets the productivity of the mapping layer and the performance of hand written statements, because for the small class of queries where the translation falls short, the escape hatch is one reviewed pull request away, and for everything else the translation is already as good as anything written by hand.
Testing against a real engine instead of a mock
The ORM's promise of interchangeable storage is weakest in the tests, and the repository test suite is where the promise gets audited. Mocking the ORM proves that the code calls the mapping layer, and catches nothing about the SQL the layer produces, the lazy loads the assertion triggers, or the queries that behave differently on real data volumes. The modern answer is the throwaway engine: Testcontainers starts a real PostgreSQL or MySQL per suite in a container, the migrations run against it, and the repositories under test execute genuine SQL against a genuine planner:
container = PostgresContainer("postgres:17")
engine = create_engine(container.get_connection_url())
run_migrations(engine)The suite that runs there catches the N plus one as a slow test, the type mismatch as a driver error, and the assertion on a computed column as a query that only works on the developer's machine. The cost is suite time, and the standard mitigations are the same for every team: one engine per suite run rather than per test, migrations applied once and data reset per test through transactions or truncate scripts, and the small query count assertions from the earlier section riding along on the same infrastructure. The raw SQL half of the codebase needs the same harness even more, because a window function with a wrong frame compiles fine and explains poorly until it runs against data shaped like production, and a fixture with ten rows hides the behavior that ten thousand reveal.