CodePark/ questions / ORMs & the N+1 problem
START PRACTICINGPRACTICE
Backend question · Databases & SQL › ORMs & the N+1 problem

What performance pitfalls do ORMs like TypeORM and Prisma commonly introduce, and when would you write raw SQL instead?

The common ORM performance pitfalls are:

  • N+1 queries: the code loads a relation inside a loop, so it runs 1 query for the list plus 1 query per item.
  • Over-fetching: the query loads every column when you need only two. Use select to fix it.
  • Row explosion: joining several one-to-many relations at once multiplies the number of rows.
  • Row-by-row bulk work: the code loads thousands of entities into memory and changes them one by one, instead of running a single UPDATE.

The deeper problem is not looking at the SQL the ORM generates. So turn on query logging in development. Check slow queries with EXPLAIN ANALYZE, which runs the query and shows the plan the database used.

Raw SQL is the better tool for:

  • window functions
  • recursive CTEs (Common Table Expressions: named sub-queries that can refer to themselves)
  • complex upserts (insert, or update if the row exists)
  • bulk updates
  • reporting queries
  • anything that needs a specific query plan

Both ORMs support raw SQL. For example, Prisma has the $queryRaw tagged template, and TypeORM has its query builder or query(). Keep using parameters, not string concatenation, because concatenation opens the door to SQL injection.

  • ORMs often cause N+1 queries and over-fetch columns.
  • Joining many one-to-many relations at once multiplies the rows.
  • Bulk work is often done row by row in memory instead of in one query.
  • Log the generated SQL and check it with EXPLAIN.
  • Use raw SQL with parameters for complex queries.