Introduction to ORMs

An ORM (Object-Relational Mapper) lets you work with a SQL database using Python objects.

Instead of writing SQL like:

sql

SELECT id, username FROM users WHERE id = 1;

You write Python like:

python

user = User.query.get(1)

diagram the N+1 query problem, and the option that fixes it mermaid

  flowchart TD
  A["Author.query.all()"] --> B["1 SELECT for the authors"]
  B --> C["for author in authors:"]
  C --> D["author.books -- not loaded yet"]
  D --> E["a SELECT per author"]
  E --> F["3 authors -> 4 SELECTs total"]
  G["Author.query.options(joinedload(Author.books))"] --> H["1 SELECT with a LEFT OUTER JOIN"]
  H --> I["the collections arrive already populated"]
  I --> J["3 authors -> 1 SELECT"]

A relationship is lazy by default: the list of children is not fetched until you touch it. Loop over parents and touch each list, and you have issued one query for the parents plus one per parent. Asking for the relationship up front turns the whole thing into a single join.

Three authors, two books each, with SQLAlchemy echoing its statements:

Loading strategy	SELECTs issued
default (lazy)	4 — one for the authors, then one per author’s `books`
`joinedload(Author.books)`	1

The pattern is 1 + N, so the cost grows with the number of parent rows. Three authors is four queries and looks fine; a page listing two hundred is two hundred and one, and that is the version that reaches production.

It is hard to spot because the code that triggers it looks innocent — a loop in a template rendering author.books has no query in it anywhere. Turn on SQLALCHEMY_ECHO and count.

joinedload is right for a to-one or a small collection; selectinload is usually better for a large one, because a join multiplies the parent rows by the children.

What an ORM gives you

Python classes represent tables
Instances represent rows
Methods/queries generate SQL
Safe parameter binding (helps prevent SQL injection)
Relationships (foreign keys) become Python attributes

Tradeoffs

ORMS are great, but not magic:

You still need to understand SQL concepts (indexes, joins, transactions)
Bad queries are still possible (N+1 query problem)
For complex reporting, raw SQL can be clearer

Flask + SQLAlchemy

In Flask apps, a common stack is:

Flask-SQLAlchemy: app integration + db helper
SQLAlchemy: the ORM engine

You’ll define models and query them, but it helps to keep SQL fundamentals in mind.

Introduction to ORMs

What an ORM gives you

Tradeoffs

Flask + SQLAlchemy

🧪 Try It Yourself

Exercise 1 – Create a Flask App

Exercise 2 – Dynamic Route

Exercise 3 – Return JSON

pch.feedbackHeading