Data Access & the ORM Layer: How It Actually Works

Web Framework Internals

Chapter 6 · Data Access & the ORM Layer: How It Actually Works

Chapters 2–5 covered how a request finds its way to a handler, and how that handler's own output gets turned into HTML. Neither one ever asked where the data in that output actually comes from. This chapter builds a real, minimal ORM (object-relational mapper) from scratch, against a genuine SQLite database, and runs into two problems every real ORM has to solve: a query-building vulnerability directly analogous to Chapter 4's own XSS bug, and a performance trap that's specific to this layer alone.

The Basic Job: Mapping a Row to a Real Object

An ORM's core job is translation: a database row is a tuple of raw values with no names attached beyond column position; a Python object is a set of named attributes. A minimal version needs a way to declare, once per class, which attribute maps to which column — a small descriptor class does that:

class Field:
    def __init__(self, column):
        self.column = column

class Model:
    _table = None
    _fields = None

    def __init_subclass__(cls, **kwargs):
        super().__init_subclass__(**kwargs)
        # collect every Field declared on the subclass, once, when the class itself is defined
        cls._fields = {
            name: val.column for name, val in vars(cls).items()
            if isinstance(val, Field)
        }

    def __init__(self, **kwargs):
        for name in self._fields:
            setattr(self, name, kwargs.get(name))

    @classmethod
    def get(cls, conn, id):
        columns = list(cls._fields.values())
        sql = f"SELECT {', '.join(columns)} FROM {cls._table} WHERE id = ?"
        row = conn.execute(sql, (id,)).fetchone()
        if row is None:
            return None
        return cls(**dict(zip(cls._fields.keys(), row)))

Declaring a real model is now two lines of class body, no separate schema file to keep in sync by hand:

class Author(Model):
    _table = "authors"
    id = Field("id")
    name = Field("name")

a = Author.get(conn, 1)   # conn is a real sqlite3 connection
print(a.id, a.name)
# 1 Ann

Verified directly against a real SQLite database

Author.get(conn, 1) genuinely queries a real, on-disk-format database (an in-memory SQLite connection, but the identical real SQL engine) and returns a real Author instance with .id and .name populated from the actual row. Nothing here is mocked or simulated — __init_subclass__ runs once, the moment the Author class itself is defined, well before any query is ever issued, and every later call to .get() reuses that already-collected field mapping.

The Real SQL Injection Problem Naive Query-Building Creates

Model.get() above already uses a parameter placeholder — but a lookup by name, written the more tempting, "obvious" way with an f-string, doesn't:

def find_author_naive(conn, name):
    sql = f"SELECT id, name FROM authors WHERE name = '{name}'"
    return conn.execute(sql).fetchall()

print(find_author_naive(conn, "Ann"))
# [(1, 'Ann')]

That works perfectly for an ordinary name. The moment the input itself contains a single quote, the value stops being data and starts being SQL:

malicious_input = "x' OR '1'='1"
print(find_author_naive(conn, malicious_input))
# [(1, 'Ann'), (2, 'Bo'), (3, 'Cy')]  -- every row in the table

Verified directly — the actual SQL string that runs is genuinely different from what was intended

The real query executed against the database, character for character, is SELECT id, name FROM authors WHERE name = 'x' OR '1'='1'. The input's own embedded quote closes the string literal early, and OR '1'='1' is a condition that's always true — so the WHERE clause matches every row regardless of what name actually contains. This is exactly Chapter 4's own XSS bug, one layer down: naive string interpolation trusted a value to stay data, and the value stopped being data.

Parameterized Queries: Closing the Gap

The fix mirrors Chapter 4's own SafeString discipline exactly — separate the fixed structure of the query from the untrusted value, and hand the value to the database driver as data, never as text spliced into the SQL string itself:

def find_author_safe(conn, name):
    sql = "SELECT id, name FROM authors WHERE name = ?"
    return conn.execute(sql, (name,)).fetchall()

print(find_author_safe(conn, "Ann"))
# [(1, 'Ann')]

print(find_author_safe(conn, malicious_input))
# []  -- treated as a literal name nobody has, not as SQL

Verified directly — the identical malicious string is now completely inert

Rerunning the exact same malicious_input from the previous section through find_author_safe() returns an empty result, not every row in the table. The ? placeholder tells the database driver exactly where the query's own structure ends and a value begins; the driver sends the value to the database separately from the SQL text, so no character the value happens to contain can ever be interpreted as part of the query itself.

The N+1 Query Problem: A Real, Measured Performance Bug

A parameterized single-row lookup is safe, but calling it once per related object adds up fast. Listing every post along with its own author's name, written the straightforward way — fetch all posts, then look up each post's author one at a time — costs far more than it looks like it should:

posts = all_posts(conn)                          # 1 query
for post in posts:
    author = Author.get(conn, post.author_id)     # 1 query PER post

print(f"{len(posts)} posts, {conn.query_count} total queries")
# 20 posts, 21 total queries

Verified directly, with a real query counter wrapping a real SQLite connection

20 posts genuinely cost 21 real queries against the database — 1 to fetch the posts themselves, plus 1 more for every single post's own author lookup, even though only 2 distinct authors actually exist across all 20 posts. This is a real, named, extremely common ORM pitfall — SQLAlchemy's own documentation gives it an exact, official name: "the N plus one problem, which states that for any N objects loaded, accessing their lazy-loaded attributes means there will be N+1 SELECT statements emitted."

The fix is to load every needed author in one extra, batched query instead of one query per post — collecting the distinct author IDs first, then fetching all of them at once with a single IN clause:

posts = all_posts(conn)                                    # 1 query
author_ids = list({p.author_id for p in posts})
placeholders = ', '.join(['?'] * len(author_ids))
sql = f"SELECT id, name FROM authors WHERE id IN ({placeholders})"
authors = conn.execute(sql, author_ids).fetchall()          # 1 more query, batched
authors_by_id = {row[0]: row for row in authors}

for post in posts:
    author = authors_by_id[post.author_id]   # no query at all -- already loaded

print(f"{len(posts)} posts, {conn.query_count} total queries")
# 20 posts, 2 total queries

Verified directly — the exact same 20 posts, a real 10.5× fewer queries

Batching the author lookup into one IN-clause query, measured against the identical 20-post dataset the naive version used, drops the real query count from 21 down to 2. Django's own documentation describes precisely this pattern, by name, for exactly this kind of relationship: "prefetch_related, on the other hand, does a separate lookup for each relationship, and does the 'joining' in Python" — its own worked example states the payoff directly: "One query for pizzas, one query for all related toppings." SQLAlchemy's own selectinload(), described in its own docs as "generally the best loading strategy to use" for exactly this shape of relationship, works by the identical mechanism — one batched IN-clause query in place of N separate ones.

A second real eager-loading strategy exists for a genuinely different relationship shape — a single, one-to-one or many-to-one link, rather than a collection. Django's select_related() and SQLAlchemy's joinedload() both solve that case with a real SQL JOIN instead of a second query, per Django's own documentation: "select_related works by creating an SQL join and including the fields of the related object in the SELECT statement… select_related gets the related objects in the same database query," explicitly "limited to single-valued relationships — foreign key and one-to-one," specifically "to avoid the much larger result set that would result from joining across a 'many' relationship." Two real ORMs, independently built, reach the identical two-strategy split: a JOIN for one related object per row, a batched second query for many.

Active Record vs. Data Mapper: Two Real Architectural Philosophies

This chapter's own Model class made one real design choice worth naming: a model instance saves itself. That's not the only real way to build an ORM — checking Django's and SQLAlchemy's own documentation directly against each other shows two genuinely different, well-established answers to "where does persistence logic actually live?"

Django's own documentation, quoted directly

"Each model is a Python class that subclasses django.db.models.Model… With all of this, Django gives you an automatically-generated database-access API." Persisting a change means calling a method directly on the instance itself; overriding that method, per Django's own docs, means "if you forget to call the superclass method, the default behavior won't happen and the database won't get touched." The object is the thing that knows how to save itself — this chapter's own Model.save() follows the identical shape.

SQLAlchemy's own documentation, quoted directly

"The ORM considers the user-defined class, the associated table metadata, and the mapping of the two to be entirely separate… any arbitrary Python class can be mapped to a database table or view." A mapped class in SQLAlchemy doesn't have to inherit from any particular base at all — persistence is handled by a separate object (a Session) that tracks changes and issues the real SQL, described in SQLAlchemy's own docs as "modeled after Fowler's 'Unit of Work' pattern."

Two real, named architectural patterns, verified against each project's own real docs

Django's own quoted design — a class that must inherit a specific base, whose own instances know how to save themselves — is the real, textbook Active Record pattern. SQLAlchemy's own quoted design — an ordinary class kept "entirely separate" from its own table mapping, with a distinct object tracking and issuing changes — is the real, textbook Data Mapper pattern (both named directly in Martin Fowler's own Patterns of Enterprise Application Architecture). This chapter's own hand-built Model class, with its self.save(conn) method, took the Active Record path without stating so explicitly — naming it now makes the alternative, Data Mapper shape a deliberate choice rather than an unstated default the next time an ORM gets designed from scratch.

Where This Course Is Headed

Chapter 7 takes every concept built here — row-to-object mapping, parameterized queries, N+1 and eager loading, Active Record vs. Data Mapper — and checks how five real ORMs actually implement them: Django's own ORM, SQLAlchemy, Ruby's ActiveRecord (the library the pattern above is directly named after), Laravel's Eloquent, and Prisma's genuinely different, schema-file-and-generated-client design for Node.js.

Hands-On Exercises

Exercise 1

Add an update() method to this chapter's own Model base class (distinct from save(), which uses INSERT OR REPLACE) that updates an existing row's own columns by id using a real SQL UPDATE statement, and verify it against a real change to an existing author's name, confirming the change is re-fetchable afterward and that a different row is left untouched.

📄 View solution

Exercise 2

Extend this chapter's own find_author_naive() function into a login_naive(conn, name, password) function checking both a name AND a password column with the identical naive string-interpolation style, and construct a real, working login-bypass injection that returns a matching row without knowing the real password at all.

📄 View solution

Exercise 3

Extend this chapter's own posts/authors setup with a third level — a comments table linked to posts — and measure, with this chapter's own query-counting connection, the real total query count for naive lazy loading across all three levels (authors, then each author's posts, then each post's comments) versus a fully batched, eager version using one IN-clause query per level.

📄 View solution

Chapter 6 Quick Reference

The basic job — a Field/Model layer mapping class attributes to real database columns, verified against a live SQLite database
The real SQL injection problem — naive f-string query building, verified returning every row in the table on a crafted input; the exact same underlying bug as Chapter 4's own XSS, one layer down
Parameterized queries — a ? placeholder separates SQL structure from untrusted data, verified turning the identical malicious input completely inert
The N+1 problem, measured — 20 posts, 21 real queries via naive lazy loading, down to 2 via a single batched IN-clause query — grounded in Django's prefetch_related()/select_related() and SQLAlchemy's selectinload()/joinedload(), both quoted directly
Active Record vs. Data Mapper — Django's own quoted design (a subclassed model that saves itself) vs. SQLAlchemy's own quoted design (an ordinary class kept "entirely separate" from a distinct persistence layer) — two real, named architectural patterns, not just implementation details
Next chapter: Data access compared across Django ORM, SQLAlchemy, ActiveRecord, Eloquent & Prisma