What is lazy-loading in Flask-SQLAlchemy

Lazy-loading is a design pattern commonly used in computer programming and primarily in web development. It defers the initialization of an object until the point at which it is needed. This can contribute significantly to efficiency in the program’s operation if properly and appropriately used. In the context of Flask-SQLAlchemy, lazy loading controls how and when the data is loaded from the database into the memory.

Let’s explore the concept of lazy-loading in SQLAlchemy and its different types using our ongoing e-commerce example.

Understanding Lazy-Loading

In the world of SQLAlchemy, the concept of lazy loading refers to the just-in-time loading of data from the database. This means that the data isn’t loaded when the parent object is loaded, but only when the child object’s data is accessed.

Taking our e-commerce example, consider the relationship between User and Order:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(120), unique=True)
    orders = db.relationship('Order', backref='user', lazy='select')

class Order(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    quantity = db.Column(db.Integer)

In this case, User and Order have a one-to-many relationship, where one user can have many orders. If we fetch a user from the database, SQLAlchemy, by default, won’t immediately load all the related orders for that user due to the lazy='select' option in the db.relationship(). Only when we access user.orders, SQLAlchemy will execute another query to fetch the related orders from the database. This is lazy loading.

Lazy loading can be a boon when dealing with large data sets. But like everything else, it comes with its trade-offs. For one, it can lead to the “N+1 problem”, where you end up making N additional queries to fetch related data for N parent objects, which can hurt performance.

Types of Lazy Loading in Flask-SQLAlchemy

Flask-SQLAlchemy offers different strategies for lazy loading. The strategy can be specified using the lazy parameter in db.relationship(). The options are select (default), joined, subquery, and dynamic.

Select Loading (lazy='select')

Select loading is the default lazy loading behavior. With this setting, SQLAlchemy will load the data as necessary in one additional query when you first access the attribute. This is what happened in our earlier example.

Joined Loading (lazy='joined')

Joined loading means that SQLAlchemy will load the relationship in the same query as the parent using a JOIN statement.

class User(db.Model):
    # ...
    orders = db.relationship('Order', backref='user', lazy='joined')

This strategy can be helpful when you know you will be accessing the related data for all the parents. However, it might fetch unnecessary data if you do not use the child objects.

Subquery Loading (lazy='subquery')

Subquery loading works similarly to joined loading, but instead of a JOIN, it uses a subquery.

class User(db.Model):
    # ...
    orders = db.relationship('Order', backref='user', lazy='subquery')

This can be more efficient than joined loading for certain types of queries, especially when dealing with complex relationships, such as those involving inheritance hierarchies.

Dynamic Loading (lazy='dynamic')

Dynamic loading does not immediately load the items but instead provides a query object that you can further refine before loading items.

class User(db.Model):
    # ...
    orders = db.relationship('Order', backref='user', lazy='dynamic')

With this setting, user.orders will not be a list of orders but a query object that you can further filter (user.orders.filter(...)) before loading the items.

However, dynamic relationships have some limitations. For example, they can’t be used in one-to-one relationships or many-to-many relationships.

Setting lazy='dynamic' on a relationship in SQLAlchemy can change the way the relationship works in a very fundamental way.

Typically, when you access a relationship attribute, SQLAlchemy automatically issues a query to load the related objects, and the attribute gives you a list (or a single object, for many-to-one relationships) that you can iterate over, modify, etc.

When you set lazy='dynamic', however, the relationship attribute gives you a query object, not a list. This query object allows you to append further filter criteria, ordering, etc. before finally issuing a query to load the objects.

Let’s see an example:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    orders = db.relationship('Order', backref='user', lazy='dynamic')

# Let's fetch a User
user = User.query.get(1)

# With `lazy='dynamic'`, this does not issue a query. Instead, it returns a query object.
orders_query = user.orders

# Now we can add further conditions or ordering to this query
big_orders = orders_query.filter(Order.total > 1000).order_by(Order.date.desc())

# This finally issues the query and returns the results

While lazy='dynamic' gives you this extra flexibility, it has a downside. Since the relationship attribute is now a query object, you can’t use it directly as a list of related objects. For example, you can’t do len(user.orders), user.orders[0], or for order in user.orders:. Instead, you have to do user.orders.count(), user.orders[0], or for order in user.orders.all():.

So, while lazy='dynamic' can be powerful, it might be confusing if you’re not aware of these differences. Use it when you need the extra flexibility it provides, but be careful with it.


Lazy loading is a powerful concept that allows you to optimize the way you load data. By understanding different loading strategies and when to use them, you can significantly improve your application’s performance. However, as with any tool, it’s important to use it judiciously and understand the trade-offs. Always benchmark and profile your application to ensure that your loading strategy is, in fact, providing the performance benefits you expect.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top