Raw SQL vs ORMs

Two ends of a spectrum

At one end, raw SQL: maximum control, maximum verbosity, every query handcrafted. At the other, full ORMs (Django ORM, ActiveRecord, Hibernate): map rows to objects, generate SQL from method calls, abstract the database away. Most healthy projects use both — raw SQL for hot/complex paths, an ORM or query builder for the boring CRUD.

What ORMs are good at

Boring CRUD — INSERT/UPDATE/DELETE on a single row.
Schema migrations — generating DDL from model changes.
Type safety in statically-typed languages.
Abstracting away which database you're using (rarely actually useful).

What ORMs are bad at

Complex joins, window functions, CTEs — fighting the ORM is slower than just writing SQL.
The N+1 query problem — see Operations track.
Bulk operations — every ORM hides COPY.
Postgres-specific features — JSONB operators, FTS, pgvector all need raw SQL or driver-level support.

The pragmatic answer

Use the ORM for what it's good at; drop down to raw SQL (via the same driver/connection) for everything else. Both can coexist in one project — and almost always should.

Code

ORM for CRUD (SQLAlchemy)·python

from sqlalchemy.orm import Session
from .models import User

with Session(engine) as session:
    user = User(name="Alice", email="alice@example.com")
    session.add(user)
    session.commit()
    print(user.id)

Raw SQL for the same engine + complex query·python

# Same connection pool, same transaction, but expressive Postgres
result = session.execute(
    text("""
        SELECT u.id, u.name,
               COUNT(o.id) AS order_count,
               SUM(o.total) AS spent
        FROM users u
        LEFT JOIN orders o ON o.user_id = u.id
        WHERE u.created_at >= now() - interval '30 days'
        GROUP BY u.id, u.name
        HAVING COUNT(o.id) >= :min_orders
        ORDER BY spent DESC
        LIMIT :n
    """),
    {"min_orders": 3, "n": 50},
)
for row in result.mappings():
    print(row["name"], row["spent"])

Two ends of a spectrum

What ORMs are good at

What ORMs are bad at

The pragmatic answer

Code

External links

Exercise

Progress

Comments 0