In this post, I wanted to examine a common but painful side effect of using Object Relational Mappers (ORMs). I’ll first explain ORMs purpose in life if you’re new to web development or haven’t been acquainted with ORMs. Then, I’ll get into a recurring ORM issue along with some strategies to mitigate the issue.

Purpose of ORMs

First, let’s quickly talk about what ORMs try to accomplish. ORM stands for Object Relational Mappers. Practically, they allow you to interact with a database from your object-oriented programming language. You can define classes that more naturally represent tables, queries, databases, relationships, etc. Instead of interacting your database with a low-level database driver, like:

def all_posts():
		conn = psycopg2.connect(
	    database=config.database,
			user=config.user,
		  password=config.password,
			host=config.host,
			port=config.port,
		)

		query_text = 'SELECT * FROM posts;'

		return conn.execute(query_text)

You could instead use an ORM to write something more natural, like:

def all_posts():
		return Session().query(Post).all()

Interfacing with a database from your application’s programming language can be very powerful. However, ORMs are easy to misuse. As smart folks have written, ORMs are a leaky abstraction. It is hard to represent databases as object-oriented code. Therefore ORMs, which seek to simplify, can have an adverse effect: more naive interactions with your database.

The N + 1 problem

The following code snippets will all be in Python, using SQLAlchemy. However, the core concepts apply to other ORMs.

The N + 1 problem happens when an ORM makes extra queries for each child object in a collection of related objects. This is called “lazy loading,” as each query is dispatched when the related objects are accessed for the first time.

Following our posts example above, if you want to query for authors and their posts, the ORM might execute one query to get the author and another for each post belonging to the author:

class Post(BaseModel):
		id: int
		body: str

        # By default, relationships use lazy loading in SQLAlchemy
		author = relationship('Author')


class Author(BaseModel):
		id: int
		name: str

# Executes 1 query
authors = session.query(Author).all()

for post in authors.posts:
        # Executes N queries, where N == len(author.posts)
		do_something(post)

This increases the number of database round-trips, which considerably slows down your application:

_{📝 Each purple sliver represents a round-trip to the database}

Solutions

The best way to avoid this issue is to:

1. Be aware of the loading technique used when querying objects with relationships.

2. Configure loading techniques on a per-query basis as needed.

However, this is not always straightforward because your database and models may change over time, and different developers may access objects at different layers in your application. So, a lazy load that seems harmless today could lead to an N + 1 query sprawl tomorrow. Despite our best intentions, guarding against all N + 1 regressions has been difficult. As a result, my focus has shifted to finding ways to identify these regressions faster.

_{📝 A fair thought you might have is: Should lazy loads be allowed at all? Some people think not. And, to be fair, SQLAlchemy enables users to raise exceptions when objects are lazy-loaded if you believe lazy loading has no place in your application.}

Spotting the sprawl

Here’s the fun part: SQLAlchemy gives developers an events API to hook into interesting points in the ORM life cycle. We can use this API to count queries in any code path we want. Check out this example decorator we can use to wrap some code and count the number of queries it emits:

from sqlalchemy import event
from sqlalchemy.orm import ORMExecuteState

from app.db import Session


class QueryCounter:
    '''
    A context manager that records the number of queries executed by the ORM,
    along with their execution states.

    Example:
    >>> with QueryCounter(session) as query_counter:
    ...     session.query(Booking.id).first()
    ...     assert query_counter.query_count == 1
    '''

    def __init__(self, session: Session):
        self.session = session
        self.query_count = 0
        self.execution_states = []

    def __enter__(self):
        event.listen(self.session, 'do_orm_execute', self._record_query)
        return self

    def __exit__(self, *args):
        event.remove(self.session, 'do_orm_execute', self._record_query)
        return self

    def _record_query(self, orm_execute_state: ORMExecuteState):
        self.query_count += 1  

_{📝 Check out this project that expands on the above example: https://github.com/tatari-tv/query-counter}

Leveraging this API improves our visibility into ORM code that can spiral out of control and enables creative possibilities like:

• Aborting requests that exceed a MAX_QUERIES_PER_REQUEST limit to protect resources from out-of-control N + 1 queries

• Making query count assertions in integration tests

• Writing a profiling program for quick query counting in your REPL, like timeit.

Conclusion

I hope this post helped you better understand ORMs and one of the warts of dealing with them. By being aware of the loading technique used when querying objects with relationships and configuring loading techniques on a per-query basis, you can mostly avoid N + 1s. For trickier cases, you can take some inspiration from this post to improve your visibility into these query sprawls.