A lot of ORM layers, as well as SQLAlchemy, try to make the effort to delay the loading of data for as long as possible. Usually, data is loaded only when the object is actually accessed by the application. This technique of delaying the loading of data until there is an attempt to access that data is known as lazy loading.

This technique is really helpful for reducing the response times of an application, since the entire data is not loaded in one go but is instead loaded on demand. This optimization comes at the expense of running a few more SQL queries, which will retrieve the actual data as the request is made. But is there some way we can have explicit control over this technique?

The answer to this will differ for every ORM solution, but quite a lot of them actually allow you to enable or disable lazy loading behavior. So, how do we control this in SQLAlchemy?

Taking a look at the user model modification we made in the previous section, we can explicitly tell SQLAlchemy to lazy load the data from our role model by adding an extra attribute in our role field, as can be seen in the following snippet:

role = relationship("Role", lazy_load='select')

This extra lazy_load attribute defines the technique SQLAlchemy uses to load the data from our role model. The following example shows the flow of a request during a lazy load:

>>> Session = sessionmaker(bind=engine)
>>> db_session = Session()
>>> user_record = db_session.query(User).first()
INFO sqlalchemy.engine.base.Engine SELECT users.username AS users_username, users.id AS users_id, users.role_id AS users_role_id 
FROM users 
 LIMIT %(param_1)s
INFO sqlalchemy.engine.base.Engine {'param_1': 1}
>>> role = user_record.role
INFO sqlalchemy.engine.base.Engine SELECT roles.id AS roles_id, roles.role_name AS roles_role_name, roles.role_permissions AS roles_role_permissions 
FROM roles 
WHERE roles.id = %(param_1)s
INFO sqlalchemy.engine.base.Engine {'param_1': 1}

As we can see from this example, SQLAlchemy dosen't make an attempt to load the data of the role model until and unless we try to access it. As soon as we try accessing the data from the role model, SQLAlchemy makes a SELECT query to the database, fetches the results in the object, and returns the populated object, which we can use now.

In contrast to the technique of loading data on demand, we can also ask SQLAlchemy to load all the data as soon as the first request is made. This can save us waiting those few extra milliseconds that the application will wait until the ORM layer fetches the data on demand from the database.

This technique is called eager loading, as we will explain in the upcoming section.