← Database Design

ORMs & Data-Access Patterns

Why an ORM Exists

Applications model the world as objects — a Customer with a list of Orders, each holding LineItems. Relational databases model the world as flat tables joined by keys. An Object-Relational Mapper (ORM) sits between these two worlds, translating rows into objects and method calls into SQL.

# Without an ORM: you write SQL strings and hydrate objects by hand
rows = db.execute(
    "SELECT id, name, email FROM customers WHERE country = %s", ("USA",)
).fetchall()
customers = [Customer(id=r[0], name=r[1], email=r[2]) for r in rows]

# With an ORM: you describe the mapping once and query in your own language
customers = session.query(Customer).filter_by(country="USA").all()

An ORM buys you three things:

• Productivity — CRUD, joins, and pagination become method calls instead of hand-written SQL and result parsing.
• Portability — the same code can target PostgreSQL, MySQL, or SQLite because the ORM emits dialect-specific SQL.
• Safety — parameters are bound automatically, which closes off most SQL-injection vectors (see Transactions & Concurrency for the security discussion).

It also has a cost: the abstraction hides what SQL actually runs, and a careless query in object-space can explode into thousands of database round-trips. The rest of this page is about getting the productivity without paying that cost.

The Object-Relational Impedance Mismatch

The “impedance mismatch” is the structural friction between the object model and the relational model. The two were designed under different assumptions, and the ORM has to bridge each gap:

Mapping inheritance

Inheritance is the sharpest corner of the mismatch. Suppose Employee has subtypes Manager and Engineer. Three classic strategies:

1. Single-table inheritance (STI) — one table with all columns plus a discriminator. Fast (no joins) but sparse: columns that belong only to Manager are NULL for every engineer.
2. Joined-table (class-table) inheritance — a base employees table plus one table per subtype, joined on the shared key. Normalized but every read of a subtype needs a join.
3. Concrete-table inheritance — one independent table per concrete class with all columns duplicated. No joins, but querying “all employees” requires a UNION.

# SQLAlchemy single-table inheritance
class Employee(Base):
    __tablename__ = "employees"
    id   = Column(Integer, primary_key=True)
    type = Column(String(20))          # discriminator column
    name = Column(String(100))
    __mapper_args__ = {"polymorphic_on": type, "polymorphic_identity": "employee"}

class Manager(Employee):
    reports_to = Column(Integer)        # NULL for non-managers
    __mapper_args__ = {"polymorphic_identity": "manager"}

There is no universally correct choice — it is a normalization-vs-join-cost trade-off, exactly the tension covered in Data Modeling & Normalization.

The Major ORMs at a Glance

ORMs cluster into two design philosophies, named by Martin Fowler:

• Active Record — a model object is a row and knows how to save itself (user.save()). Simple and discoverable; couples domain objects to persistence.
• Data Mapper — a separate session/repository moves data between plain objects and the database. More ceremony, cleaner domain model.

The same query in four of them:

# SQLAlchemy (2.0 style)
session.execute(
    select(Order).where(Order.total > 1000)
).scalars().all()

# Django ORM
Order.objects.filter(total__gt=1000)

// Sequelize
await Order.findAll({ where: { total: { [Op.gt]: 1000 } } });

// Prisma
await prisma.order.findMany({ where: { total: { gt: 1000 } } });

All four emit essentially the same SQL — SELECT ... FROM orders WHERE total > 1000 — but Django and Sequelize objects can also save themselves, while SQLAlchemy and Prisma keep persistence in a separate session/client.

Query Builders vs Raw SQL

Most ORMs expose a query builder: a fluent, composable API that produces SQL programmatically. It sits between full ORM object-mapping and raw strings.

# SQLAlchemy Core query builder — returns rows, not mapped objects
stmt = (
    select(Customer.name, func.sum(Order.total).label("ltv"))
    .join(Order, Order.customer_id == Customer.id)
    .where(Customer.country == "USA")
    .group_by(Customer.id)
    .having(func.sum(Order.total) > 1000)
)

A query builder is the right tool when you need dynamic SQL — filters, sorts, or joins assembled at runtime — because you compose Python/JS objects instead of concatenating strings (which is both unsafe and unreadable):

stmt = select(Product)
if max_price is not None:
    stmt = stmt.where(Product.price <= max_price)   # conditionally add clauses
if category:
    stmt = stmt.where(Product.category == category)
stmt = stmt.order_by(Product.name)

Raw SQL is still the right tool when:

• The query uses dialect-specific features the ORM cannot express (window functions with complex frames, recursive CTEs, LATERAL joins, vendor JSON operators).
• You are tuning a hot path and need exact control over the plan.
• The query is reporting/analytics-shaped and maps poorly onto objects.

Every mature ORM provides a safe escape hatch that still binds parameters — never interpolate user input into the string:

# SQLAlchemy — bound parameters via :name placeholders
rows = session.execute(
    text("SELECT * FROM orders WHERE total > :min ORDER BY total DESC"),
    {"min": 1000},
).all()

// Prisma — tagged template binds ${} as parameters, NOT string interpolation
const orders = await prisma.$queryRaw`
  SELECT * FROM "Order" WHERE total > ${minTotal}
`;

The cardinal rule. Use the ORM/query builder for the 95% of queries that are routine CRUD and simple joins; reach for parameterized raw SQL for the 5% that are performance-critical or use features the ORM can’t express. Never build SQL by string concatenation of user input — that is the classic SQL-injection bug.

The N+1 Query Problem

The single most common ORM performance bug is the N+1 problem. It appears when you load a list of N parent objects and then touch a related attribute inside a loop, triggering one extra query per parent.

# 1 query to load the customers...
customers = session.query(Customer).all()
for c in customers:
    # ...then N MORE queries, one per customer, hidden behind attribute access
    print(c.name, len(c.orders))     # each c.orders fires its own SELECT

If customers returns 1,000 rows, this executes 1 + 1,000 = 1,001 queries. Each query is fast, but the round-trip latency dominates: at 1 ms per round-trip that loop spends a full second waiting on the network, almost all of it avoidable.

Why it hides so well

The fix-free version works correctly and is fast in development with ten test rows. It only collapses in production with real data volume, which is what makes N+1 so insidious. The cause is lazy loading: by default many ORMs defer loading a relationship until the attribute is first accessed, and an access inside a loop becomes a query inside a loop.

Eager loading: the fix

Eager loading tells the ORM to fetch the related rows up front, collapsing N+1 into a small constant number of queries. Two main mechanics:

• JOIN-based — one query with a JOIN returns parents and children together. Best for to-one relationships; can multiply rows for to-many.
• Second-query (IN / SELECT IN) — load parents, collect their keys, then WHERE child.parent_id IN (...) in one follow-up query. Best for to-many relationships; total queries = 2 regardless of N.

# SQLAlchemy: selectinload issues 2 queries total (parents, then children IN keys)
from sqlalchemy.orm import selectinload
customers = (
    session.query(Customer)
    .options(selectinload(Customer.orders))
    .all()
)
for c in customers:
    print(c.name, len(c.orders))     # no extra queries — orders already loaded

# joinedload instead emits a single LEFT JOIN
from sqlalchemy.orm import joinedload
session.query(Customer).options(joinedload(Customer.orders)).all()

# Django: select_related (JOIN, to-one) and prefetch_related (2nd query, to-many)
customers = Customer.objects.prefetch_related("orders")        # to-many -> 2 queries
orders   = Order.objects.select_related("customer")            # to-one  -> 1 JOIN

// Sequelize: include eager-loads the association
await Customer.findAll({ include: [{ model: Order }] });

// Prisma: include is explicit and required (Prisma has no lazy loading)
await prisma.customer.findMany({ include: { orders: true } });

Choosing JOIN vs second-query loading

A common mistake is joinedload on a to-many relationship: a customer with 50 orders is returned 50 times in the result set, inflating bytes transferred and forcing the ORM to deduplicate. Prefer second-query loading for collections.

Detecting N+1

You cannot fix what you cannot see. Make the SQL visible:

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

# Django: read connection.queries (DEBUG=True), or use django-debug-toolbar
from django.db import connection
print(len(connection.queries))      # spikes reveal N+1

Many test suites add an assertion that a given endpoint runs at most K queries, turning a silent performance regression into a failing test.

Transactions in ORMs

A transaction groups operations so they commit or roll back together. ORMs wrap transactions in two related concepts:

• The session / persistence context / unit of work — an in-memory staging area that tracks which objects are new, dirty, or deleted.
• Flush vs commit — flush emits the pending SQL (INSERT/UPDATE/DELETE) within the transaction; commit makes it durable and ends the transaction.

# SQLAlchemy: the context manager commits on success, rolls back on exception
with Session(engine) as session:
    with session.begin():                  # one transaction
        account_a.balance -= 100
        account_b.balance += 100
        # dirty objects are flushed and the transaction commits at block exit
# any exception inside the block triggers ROLLBACK automatically

# Django: atomic() is the transaction boundary
from django.db import transaction
with transaction.atomic():
    account_a.balance -= 100; account_a.save()
    account_b.balance += 100; account_b.save()
    # exception -> the whole block rolls back

// Sequelize: managed transaction commits/rolls back automatically
await sequelize.transaction(async (t) => {
  await accountA.decrement("balance", { by: 100, transaction: t });
  await accountB.increment("balance", { by: 100, transaction: t });
});

// Prisma: $transaction runs the array atomically
await prisma.$transaction([
  prisma.account.update({ where: { id: a }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: b }, data: { balance: { increment: 100 } } }),
]);

Dirty checking and the unit of work

Data-Mapper ORMs (SQLAlchemy, Hibernate) track loaded objects and compare them against their original state at flush time — dirty checking. You mutate objects in memory; the ORM works out the minimal set of UPDATEs and orders them to respect foreign keys. This is the Unit of Work pattern: changes accumulate and are written in one coordinated batch at commit.

order = session.get(Order, 42)
order.status = "shipped"        # no SQL yet — just a tracked change
session.commit()               # now: UPDATE orders SET status='shipped' WHERE id=42

Optimistic concurrency

Holding a database lock across a user’s “think time” does not scale (see the locking discussion in Transactions & Concurrency). ORMs instead offer optimistic locking: add a version column, and the UPDATE carries the version the app last read.

\(\text{UPDATE orders SET status = '?', version = version + 1}\) \(\text{WHERE id = ? AND version = ?}\)

If another transaction already bumped the version, the WHERE matches zero rows, the ORM detects the lost update, and raises a StaleDataError (SQLAlchemy) / OptimisticLockException (Hibernate) for the app to retry. The probability that a given write conflicts is roughly

for $W$ concurrent writers contending over $N$ equally-likely rows — low when writes are spread across many rows, high on a hot row, which is exactly when you should reconsider the data model.

When to Drop to SQL

ORMs are excellent at the common case and frustrating at the edges. Drop to SQL (or a thin query builder) when:

1. Bulk operations. Looping to update 100,000 rows one object at a time is 100,000 round-trips. A single UPDATE ... WHERE is one statement.

   # Bad: load, mutate, save each row (N round-trips)
   for p in session.query(Product).filter_by(discontinued=True):
       p.active = False
   # Good: one set-based statement
   session.query(Product).filter_by(discontinued=True).update({"active": False})
   

2. Analytical/reporting queries with window functions, ROLLUP/CUBE, or recursive CTEs that map awkwardly onto objects.
3. Vendor-specific features — PostgreSQL JSONB operators, full-text search, pgvector similarity, INSERT ... ON CONFLICT upserts.
4. Hot paths where you have profiled the ORM-generated SQL and need exact control over the join order or index usage. Read the plan with EXPLAIN ANALYZE (see Indexing & Query Execution).
5. Complex multi-table joins where the ORM’s eager-loading strategy generates a worse plan than a hand-written query.

The healthy posture is a spectrum, not a religion:

ORM objects  →  query builder  →  parameterized raw SQL  →  stored procedures
(most code)     (dynamic SQL)     (hot/edge queries)        (rare, DB-owned logic)

Stay as high on the ladder as the workload allows, and step down deliberately — with a profiler or EXPLAIN output justifying the move — rather than rewriting everything in SQL out of habit.

ORM Anti-Patterns and Pitfalls

Key Takeaways

• The mapping is leaky on purpose. An ORM bridges objects and tables but cannot erase the impedance mismatch — inheritance, identity, and collections all need explicit mapping decisions.
• Lazy loading causes N+1. Touching a relationship inside a loop fires one query per row. Eager-load related data up front to collapse 1+N queries into a small constant.
• JOIN for to-one, second-query for to-many. joinedload/select_related suit single related rows; selectinload/prefetch_related avoid row explosion for collections.
• The session is the unit of work. Dirty checking batches in-memory mutations into a minimal, correctly-ordered set of writes committed in one transaction.
• Stay high on the ladder, drop down deliberately. Use objects and the query builder for routine work; reach for parameterized raw SQL only for bulk, analytical, or vendor-specific queries — never concatenate user input.

See Also

• Data Modeling & Normalization — the schema the ORM maps onto, and the inheritance/normalization trade-offs.
• Indexing & Query Execution — reading EXPLAIN output to tune the SQL your ORM generates, including the N+1 discussion.
• Transactions & Concurrency — locking, MVCC, and isolation behind the ORM’s commit() and optimistic locking.
• Storage Engines & Recovery — what flush and commit actually do to pages and the write-ahead log.
• Database Design hub — the full deep-dive series.