How to avoid the N+1 problem when working with SQLAlchemy?

The N+1 problem occurs when loading a list of objects from a database, and for each object, an additional query is executed for related data. In SQLAlchemy, this leads to many unnecessary queries, reducing performance.

To avoid N+1, eager loading is used with functions like joinedload, subqueryload, or selectinload from the sqlalchemy.orm module. They allow related objects to be loaded in advance with a minimal number of queries.

Example:

from sqlalchemy.orm import joinedload

# Suppose there are models User and related Address
users = session.query(User).options(joinedload(User.addresses)).all()

# Now, accessing user.addresses will not trigger additional queries
for user in users:
    print(user.name, user.addresses)

Thus, joinedload loads related data with a single JOIN query, preventing the N+1 problem.