Database access comparison
ORM vs Raw SQL
Should you let a library generate your SQL, or write it yourself? This debate comes up in every backend project — in code reviews, architecture meetings, and performance discussions. Understanding both sides and the vocabulary around them makes you a better communicator and a better engineer.
TL;DR
- ORM (Object-Relational Mapper) — a library that maps database rows to in-memory objects and generates SQL for you. Faster to write, type-safe, handles migrations. Can generate inefficient queries for complex cases.
- Raw SQL — you write the SQL directly. Full control over every query, easy to optimise, easy to use database-specific features. More verbose; schema changes are manual.
- In practice, most teams use both — ORM for standard CRUD, raw SQL or a query builder for complex reporting and performance-critical paths.
Side-by-side comparison
| Aspect | ORM | Raw SQL |
|---|---|---|
| Learning curve | Higher initially — must learn the ORM API | Lower if you know SQL; SQL is the API |
| Performance | Good for simple queries; can be poor for complex ones | Optimal — you control every byte of the query |
| SQL complexity | ORMs struggle with complex JOINs, CTEs, window functions | Native — any SQL feature is available |
| Portability | High — switch databases by changing the adapter | Low — SQL dialects differ (LIMIT vs TOP, etc.) |
| Debugging | Harder — must inspect generated SQL to understand queries | Direct — the query you wrote is the query that runs |
| Type safety | Strong (Prisma, TypeORM) — query results are typed | Weak unless you use a typed query builder |
| Migrations | Built-in tools generate and run migrations | Manual or via a standalone migration tool (Flyway, Liquibase) |
| SQL injection risk | Low — parameterised by default | High if string interpolation is used; must always use parameters |
When to use an ORM
- Standard CRUD operations. Creating, reading, updating, and deleting simple records is what ORMs are optimised for — less boilerplate, typed results, automatic parameterisation.
- Rapid development. Prisma or Django ORM let you go from schema to working API endpoints in minutes, with type-safe queries and auto-generated migrations.
- Team with mixed SQL experience. The ORM's type system catches many mistakes at compile time that would otherwise surface as runtime SQL errors.
- Multi-database portability. If you need to support both PostgreSQL and SQLite (e.g., production vs. tests), an ORM adapter handles the dialect differences.
When to use raw SQL
- Complex analytics queries. Window functions, recursive CTEs, and multi-step aggregations are painful to express through an ORM and much clearer in SQL.
- Performance tuning. When you need to use specific indexes, hints, or query shapes that the ORM won't generate, raw SQL is the only option.
- Database-specific features. PostgreSQL's JSONB operators, full-text search, or
COPYbulk import require raw SQL. - Large batch operations. ORMs typically load objects into memory; raw SQL bulk inserts and updates are far more efficient for millions of rows.
English phrases engineers use
ORM conversations
- "The ORM generates inefficient queries for this report — let's drop to raw SQL."
- "The N+1 problem was causing latency — I added eager loading."
- "The migration ran successfully on staging; ready for production."
- "We need to include the related records in the query to avoid N+1."
- "Prisma's type safety caught that schema mismatch at compile time."
Raw SQL conversations
- "We dropped down to raw SQL for this query — the ORM couldn't express it."
- "Always use parameterised queries — never interpolate user input into SQL."
- "The EXPLAIN ANALYZE shows a sequential scan — we need an index."
- "I used a CTE to make the query more readable."
- "The bulk insert uses COPY — it's 100x faster than row-by-row."
Key vocabulary
- ORM — Object-Relational Mapper; a library that maps table rows to language objects and generates SQL.
- N+1 problem — issuing N extra queries to fetch related data for N records, instead of one JOIN or batch query.
- Eager loading — instructing the ORM to fetch associated records in the same query, avoiding N+1.
- Migration — a versioned script that alters the database schema in a controlled, repeatable way.
- Query builder — a programmatic SQL construction API that is type-safe but does not map results to objects (e.g., Knex, Drizzle).
- Parameterised query — a query where user input is passed as a separate parameter, not interpolated into the SQL string, preventing SQL injection.
- CTE (Common Table Expression) — a named temporary result set defined with
WITH, making complex SQL more readable.
Quick decision tree
- Standard CRUD on well-defined models → ORM
- Complex report with multiple JOINs and aggregations → Raw SQL
- Need database-specific features (JSONB, COPY, full-text) → Raw SQL
- Want type-safe queries and auto-migrations → ORM
- Bulk import of millions of rows → Raw SQL
- Team has mixed SQL experience → ORM with raw SQL escape hatch
Frequently asked questions
Can you mix ORM and raw SQL in the same project?
Yes, and this is a common pattern. Most ORMs provide an escape hatch for raw queries — Prisma has $queryRaw, SQLAlchemy has text(), Sequelize has query(). The usual approach is to use the ORM for standard CRUD and let raw SQL handle complex reporting queries, full-text search, or performance-critical paths that the ORM generates poorly for.
What is the N+1 problem?
The N+1 problem occurs when fetching a list of N records and then issuing one additional query per record to fetch related data — resulting in N+1 total queries instead of one joined query. ORMs are prone to this when lazy-loading associations. The fix is eager loading: tell the ORM to JOIN or use a second bulk query for related records. In SQL you just write the JOIN yourself.
Which ORMs are most popular?
By language: JavaScript/TypeScript — Prisma, TypeORM, Sequelize, Drizzle; Python — SQLAlchemy, Django ORM, Tortoise ORM; Java — Hibernate (JPA); Ruby — ActiveRecord (Rails); PHP — Eloquent (Laravel), Doctrine; Go — GORM, Ent. Prisma and SQLAlchemy are widely considered best-in-class for developer experience and query control respectively.
Do ORMs handle database migrations?
Most do, yes. Prisma has its own migration system (prisma migrate); Sequelize and TypeORM have migration runners; Django and SQLAlchemy (with Alembic) do too. Migrations generated by ORMs are generally safe for straightforward schema changes, but complex migrations (data backfills, renaming columns, splitting tables) often need handwritten SQL to run safely on large tables.
Is raw SQL faster than ORM queries?
Raw SQL can be faster, but the gap is usually small for simple queries. The real performance difference appears in complex queries: an ORM may generate a suboptimal query with unnecessary JOINs or missing indexes. A developer who knows the schema and the query planner can often write SQL that is significantly faster. However, poorly written raw SQL can be slower than what a good ORM generates.
What is a query builder?
A query builder sits between a raw SQL string and a full ORM. It gives you a programmatic, type-safe API to construct SQL queries without mapping results to model objects. Examples: Knex.js (Node.js), JOOQ (Java), Drizzle (TypeScript). Query builders offer more control than ORMs and less ceremony than raw SQL strings, making them popular for complex reporting or when you want type safety without a full object model.