Skip to content
Velloreth
Programming 6 min read

ORM vs Raw SQL: Choosing a Data Access Layer

An ORM trades control for convenience. Learn the trade-offs, the N+1 trap, and the hybrid approach most teams settle on.

By

What an ORM does

An ORM maps a table to a class and a row to an instance.

const user = await User.findByPk(42);
user.email = 'new@example.com';
await user.save();

The ORM generates the SELECT and the UPDATE.

The advantages:

  • Less boilerplate for a common operation.
  • A migration tool in the same package.
  • A relationship expressed in the model, with a lazy or eager load.
  • A type in a typed language, so a field is checked at compile time.
  • A parameterised query by default, which prevents SQL injection.

The disadvantages:

  • An abstraction over SQL, which hides the query that runs.
  • The N+1 problem, which is easy to introduce and hard to notice.
  • A poor plan for a complex query.
  • A leaky abstraction: you still need to know SQL to use it well.

The N+1 problem

This is the most common ORM performance issue.

const articles = await Article.findAll();

for (const article of articles) {
  const author = await article.getAuthor();  // one query per article
}

A list of 100 articles triggers 101 queries.

The fix is eager loading:

const articles = await Article.findAll({
  include: [{ model: Author }],
});

This is one query with a join, or two queries with an IN clause.

The trap: the loop looks harmless, and the problem only appears with a large list. A development database with ten rows hides it.

The detection: log the queries in development, and count them per request. A request that runs 50 queries is a red flag.

Where an ORM is weak

A complex query. A report with several joins, a window function, and a group by is clearer in SQL than in an ORM’s API.

A bulk operation. An ORM that loads a row, modifies it, and saves it is far slower than a single UPDATE.

-- One statement
UPDATE users SET active = false WHERE last_login < '2025-01-01';
// Loads every row, then updates each
const users = await User.findAll({ where: { lastLogin: { [Op.lt]: '2025-01-01' } } });
for (const user of users) {
  user.active = false;
  await user.save();
}

A database-specific feature. A partial index, a LATERAL join, a RETURNING clause, or a full-text search may not be expressible.

A performance-critical query. When the plan matters, you want to write the SQL.

Raw SQL

const result = await db.query(
  'SELECT u.name, COUNT(o.id) AS orders FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id',
);

The advantages:

  • Full control over the query and the plan.
  • No abstraction to learn or to leak.
  • A database-specific feature is available.
  • The query is visible, which makes it reviewable.

The disadvantages:

  • More boilerplate for a common operation.
  • Manual mapping from a row to an object.
  • SQL injection if a parameter is concatenated rather than bound.
  • No type safety without a generator.

The injection rule: always use a parameterised query.

// Safe: the driver binds the parameter
db.query('SELECT * FROM users WHERE email = $1', [email]);

// Unsafe: string concatenation
db.query(`SELECT * FROM users WHERE email = '${email}'`);

A query builder

A query builder composes a query programmatically without mapping rows to objects.

const rows = await db('users')
  .leftJoin('orders', 'orders.user_id', 'users.id')
  .groupBy('users.id')
  .select('users.name', db.raw('COUNT(orders.id) AS orders'));

The advantages:

  • The SQL is visible, so the plan is predictable.
  • Composition without string concatenation.
  • Parameterised by default.
  • No N+1, because there is no lazy relationship.

The disadvantages:

  • No object mapping, so you map the rows yourself.
  • Less convenient for a simple CRUD operation.

A query builder is a good middle ground for a team that wants control without string concatenation.

The hybrid approach

Most teams settle on a mix.

An ORM for the common case: a create, a read by id, an update, a delete.

A query builder or raw SQL for the complex case: a report, a bulk update, a query with a window function.

The rule: use the ORM until it gets in the way, then drop to SQL for that query. The two can coexist in the same codebase.

Keep the raw SQL in one place, such as a repository module, so it is reviewable and testable.

Choosing

Use an ORM when:

  • The application is CRUD-heavy.
  • The team is more comfortable with objects than SQL.
  • The queries are simple.
  • You want the migration tool and the type safety.

Use a query builder when:

  • You want control over the SQL.
  • The queries are moderately complex.
  • You do not need object mapping.

Use raw SQL when:

  • The query is complex or performance-critical.
  • You need a database-specific feature.
  • You are doing a bulk operation.

The honest default: an ORM for the common case and raw SQL for the complex one. The choice is not exclusive.

Common mistakes

The N+1 query. The most common ORM performance issue. Use eager loading.

A bulk operation through the ORM. Load, modify, save is far slower than one UPDATE.

String concatenation in raw SQL. Use a parameterised query.

Trusting the ORM’s generated SQL. Read the query log, and check the plan for a slow query.

An ORM for a data-heavy application. A reporting application with complex queries is better served by SQL.

No query logging in development. The N+1 problem is invisible without it.

Practical guidance

  • Use an ORM for the common case.
  • Use eager loading to avoid N+1.
  • Log the queries in development.
  • Drop to raw SQL for a complex query.
  • Use a parameterised query always.
  • Use a bulk statement for a bulk operation.
  • Keep the raw SQL in one place.
  • Read the plan for a slow query.

For the related guides, see sql-joins-explained and database-indexing-explained. For the broader context, rest-api-design-principles and caching-strategies-explained.

Quotable summary

An ORM maps a table to an object, which removes boilerplate and adds a layer of abstraction, and the N+1 query problem is the most common ORM performance issue, with eager loading as the fix. An ORM is weakest at a complex query, where raw SQL is clearer and faster, and a query builder is a middle ground that composes a query without hiding the SQL. The pragmatic answer is an ORM for the common case and raw SQL for the complex one, because the choice is not exclusive and the two can coexist in the same codebase. Always use a parameterised query with raw SQL, never string concatenation, because the driver binds the parameter and a value cannot change the query structure.

Frequently asked questions

What is an ORM?
An object-relational mapper, which maps a database table to a class and a row to an instance. It generates the SQL for a common operation.
What is the N+1 problem?
Loading a list of N items and then loading a related object for each, which is N+1 queries. Eager loading fetches the related objects in one query.
Is an ORM slower than raw SQL?
For a simple query, the difference is small. For a complex query, an ORM may generate a poor plan, and raw SQL is faster and clearer.
What is a query builder?
A library that composes a SQL query programmatically without mapping rows to objects. It keeps the SQL visible while avoiding string concatenation.
Should I use an ORM for a new project?
For a CRUD-heavy application, yes, because it removes a lot of boilerplate. For a data-heavy application with complex queries, a query builder or raw SQL may be a better fit.
How do I avoid SQL injection with raw SQL?
Use parameterised queries, never string concatenation. The driver escapes the parameters, so a value cannot change the query structure.