ORMs: what they do for you, and what they hide

An ORM — Object-Relational Mapper — lets you work with database rows as objects in your language, instead of writing SQL and mapping the results by hand. Django's ORM, SQLAlchemy, Prisma, TypeORM, Sequelize, ActiveRecord: most application code you meet talks to the database through one.

They are genuinely useful and they genuinely hide things. This lesson is both halves, because an ORM you understand is a tool and an ORM you do not is a source of mysterious slowness — and the one thing every experienced developer will tell you is that you must know the SQL underneath.

What it does for you

# raw
cur.execute("SELECT id, name, email FROM members WHERE membership = %s", ("student",))
members = [Member(*row) for row in cur.fetchall()]

# ORM (Django)
members = Member.objects.filter(membership="student")

Four real wins, and they are worth having.

Mapping. Rows become objects and back, so you stop hand-writing the boilerplate that turns a tuple into a Member and a Member into an INSERT.

Parameterisation by default. filter(name=user_input) is parameterised for you. The most common SQL-injection hole is closed by default — a real safety win, and the strongest everyday argument for using one.

Migrations. Most ORMs generate schema migrations from your model definitions, so the model and the schema stay in step.

Portability, in theory. The same code against PostgreSQL, MySQL or SQLite. Real for simple queries; leaky the moment you use anything database-specific, so do not over-value it.

What it hides — every item here is a real incident

It hides how many queries you run

This is the big one, and it is the N+1 problem from the performance module wearing ORM clothing:

for book in Book.objects.all():          # 1 query
    print(book.author.name)              # + 1 query PER BOOK

One innocent-looking loop, N + 1 queries. The SQL is invisible, so the cost is invisible — until it is a two-second page. The fix is the same as before (select_related, prefetch_related, .includes, joinedload), but you cannot fix a cost you cannot see.

So make it visible. This is the habit that separates people who use an ORM well from people who are surprised by it:

# Django: print the SQL a queryset will run
print(Member.objects.filter(membership="student").query)

# Django: count queries in a block
from django.test.utils import CaptureQueriesContext
from django.db import connection
with CaptureQueriesContext(connection) as ctx:
    render_the_page()
print(len(ctx))          # how many queries that page ACTUALLY ran

# SQLAlchemy: see every statement
engine = create_engine(url, echo=True)

// Prisma: log every query
new PrismaClient({ log: ['query'] })

Turn query logging on early and keep it on in development. Most ORM performance problems are obvious the instant you can see the SQL, and invisible until then.

It hides expensive queries behind cheap-looking attributes

book.author            # looks like a field access; is a SELECT
member.loans.count()   # looks like len(); is a SELECT COUNT
order.total            # a @property that sums 500 rows every time you touch it

In an ORM, a property access and a database round trip look identical in the code. A .count() inside a template loop is an N+1 that the person writing the HTML cannot even see.

It generates SQL you would not write

For a complex query the ORM's SQL can be genuinely bad — an extra join, a subquery where a join would do, a sort it did not need. EXPLAIN on ORM-generated SQL surprises people regularly.

The escape hatch exists for this. When a query matters and the ORM fights you, drop to SQL:

Member.objects.raw("SELECT ... complex query ...")     # Django
session.execute(text("SELECT ..."), {"tier": tier})   # SQLAlchemy
prisma.$queryRaw`SELECT ...`                            # Prisma

Using raw SQL for the 5% of queries that need it is not defeat; it is the ORM working as intended. But parameterise it — the raw door is exactly as injectable as raw psycopg, so pass values as parameters (text("... :tier"), Prisma's tagged template), never by interpolation. This is the SQL-injection lesson again, one layer up.

It hides transaction boundaries

member.save()      # is this in a transaction? with what else? committed when?

Some ORMs autocommit each save(); some wrap a request in a transaction; some do neither until you ask. Two .save() calls that you assumed were atomic may be two separate transactions, so a crash between them leaves half-written data. Know your ORM's default, and use its explicit transaction block (with transaction.atomic():, session.begin(), prisma.$transaction) when two writes must succeed together — the atomicity lesson from module 6, which does not go away because you stopped writing BEGIN.

It hides the connection and the pool

The ORM manages connections for you, which is why the pooling multiplication in the last lesson sneaks up on people: the pool size is a config value you set once and forget, and then you deploy four instances of it. Know where your ORM's pool size lives, and put it in the multiplication.

When an ORM is the wrong tool

Be honest about the cases where it fights you:

Complex analytical queries — heavy aggregation, window functions, CTEs. Write SQL. The ORM expression for these is often longer and slower than the SQL.
Bulk operations. for x in qs: x.save() is thousands of round trips. Use the ORM's bulk path (bulk_update, bulk_create) or a single UPDATE ... FROM. A set-based operation in one statement beats a Python loop by orders of magnitude — the whole point of a relational database.
Reporting and dashboards. These are SQL's home turf; an ORM adds friction and hides the cost.
Performance-critical paths, where you need to control the exact query and its plan.

The mature codebase uses the ORM for the 90% of ordinary create-read-update-delete work where it saves real effort, and hand-written SQL for the 10% where it matters. It is not one or the other.

The rule that ties the module together

Learn SQL first. Then use an ORM, knowing what it generates.

Someone who learned the ORM without the SQL cannot read the query log, cannot tell an N+1 from a join, cannot explain why a page is slow, and cannot drop to raw SQL when they must. Someone who knows the SQL sees the ORM for what it is — a convenience over a thing they understand — and reaches past it exactly when they should. This course taught SQL first for this reason.

An ORM does not save you from learning databases. It is most useful precisely to the person who least needed it.

Check your work

What an ORM is. A layer mapping database rows to objects in your language, and back.

Four things it does for you. Object mapping, parameterisation by default, migrations, and (leaky) portability.

Its strongest everyday safety benefit. Parameterisation by default closes the common injection hole.

The biggest thing it hides. How many queries you run — the N+1 problem, now invisible.

How to make query count visible. .query / query-count capture in Django, echo=True in SQLAlchemy, log: ['query'] in Prisma — turned on in development.

Why a property access can be expensive. In an ORM a field access and a round trip look identical, so .count() or a lazy relation is a hidden SELECT.

Why the raw-SQL escape hatch is not defeat. Using SQL for the queries that need it is the ORM working as intended.

The catch with the raw door. It is as injectable as raw psycopg — parameterise it.

What it hides about transactions. Whether and when save() commits, and whether two writes are atomic — use the explicit transaction block.

Why the pooling trap hides behind an ORM. The pool size is set-once config, then multiplied by every instance.

Four cases where an ORM is the wrong tool. Complex analytics, bulk operations, reporting, and performance-critical paths.

The rule. Learn SQL first, then use an ORM knowing what it generates.

Practice

Write a query in your ORM and print the SQL it generates. Compare with what you would write.
Run an N+1 loop, capture the query count, then fix it with eager loading and capture again.
Turn on query logging and load a real page. Count the queries and find any duplicates.
Find a model property or method that issues a query, and call it inside a loop. Count the round trips.
EXPLAIN the SQL from a moderately complex ORM query and look for anything you would not have written.
Rewrite that query as raw SQL and compare the plan and the timing.
Take the raw query and pass a user value into it — first by interpolation (note the hole), then parameterised.
Update 10,000 rows with a for loop of .save(), then with the bulk method. Time both.
Find your ORM's default transaction behaviour for a single save(). Then wrap two writes in an explicit transaction and make the second fail.
Find where your ORM's connection pool size is configured, and add it to the pooling multiplication from the last lesson.
Take one analytical query (an aggregate with a window function) and try to express it in the ORM, then in SQL. Compare length and clarity.

Official documentation

Django — Making queries — And the queryset .query attribute for seeing the SQL.
Django — Database transactions — atomic() and the autocommit default.
SQLAlchemy — ORM Querying Guide — And echo=True for statement logging.
Prisma — Raw queries — The safe, parameterised raw escape hatch.
Prisma — Query logging — Seeing the SQL.
PostgreSQL — EXPLAIN — Which you now point at your ORM's output.

Next: seed data and throwaway test databases.