Eager loading and lazy loading are two ways you can use queries to retrieve database data. Imagine you're baking a cake with ten ingredients. With eager loading, you get all ten ingredients at once, whereas with lazy loading, you get the ingredients one by one. At the end of the process, you have a cake regardless. However, if you have to make a lot of cakes, the lazy loading approach will slow you down.

To put it more technically: when eager loading data, all the necessary data is fetched in advance in a single query, whereas lazy loading will only retrieve data at the point its needed.

Eager loading is useful if you're frequently returning large amounts of specific and limited relational data. In contrast, lazy loading is better suited to situations where we need large and complex relational databases.

Examples of lazy and eager loading

A blog application has three database tables: articles, comments, and users, with each comment belonging to one article and user. Let's imagine that every article has one comment in this scenario.

We want to retrieve the first twenty articles from the blog, alongside their comments, so we make the following query:

SELECT * FROM Articles ORDER BY published_at ASC LIMIT 20;

Lazy loading

When we run this example query with lazy loading, our application makes one request for each of the following records:

• All articles
• The user the article belongs to
• All comments belonging to the article
• The user who has made the comment (this varies depending on how many comments an article has, but in this instance, we are pretending each article has one comment)

The resulting query looks something like this:

SELECT * FROM Articles ORDER BY published_at ASC LIMIT 20;
 
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 1;
SELECT * FROM Comments WHERE article_id = 1;
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 1;
 
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 2;
SELECT * FROM Comments WHERE article_id = 2;
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 2;
 
...
 
SELECT Users.* FROM Users JOIN Articles ON Users.id = Articles.user_id WHERE Articles.id = 20;
SELECT * FROM Comments WHERE article_id = 20;
SELECT Users.* FROM Users JOIN Comments ON Users.id = Comments.user_id WHERE Comments.id = 20;

In total, we make sixty-one queries.

Lazy loading is well suited for complex data sets and scenarios where we don't require all of the data at once. However, in this use case, we have a limited dataset, and we always require all articles and all of their comments and related users.

This is an N+1 query that risks becoming a bottleneck in our application.

Eager Loading

In eager loading, we fetch all our required data in advance in a single query because we know it'll be frequently queried. In this scenario, we make a single query:

SELECT Articles.*, Users.*, Comments.*
FROM Articles
JOIN Users ON Users.id = Articles.user_id
JOIN Comments ON Comments.article_id = Articles.id
ORDER BY Articles.published_at ASC
LIMIT 20;

Configuring eager loading

Eager loading is configured via your application's Object Relational Manager (ORM). An ORM is like an interpreter between our code and the database. It understands the relationships between objects in our code and the tables in the database.

Let’s say we ask for something like the comments related to an article object. The ORM automatically knows how to fetch those comments from the database based on the article's ID.

To eager load data, we need to modify how our application retrieves that data. We've included some examples of eager loading for:

• Ruby on Rails
• Elixir - Phoenix with Ecto
• Node.js with Prisma

Ruby on Rails

An example of lazy loading:

@articles = Article.order(published_at: :asc).limit(20)
 
@articles.each do |article|
  user = article.user # Lazy loading the user for each article
  comments = article.comments # Lazy loading the comments for each article
end

You can eager load data in Ruby on Rails using includes and joins, for example:

# includes
@articles = Article.includes(:user, :comments).order(published_at: :asc).limit(20)
 
# joins
@articles = Article.joins(:user, :comments).select('articles.*, users.*, comments.*').order(published_at: :asc).limit(20)

Elixir - Phoenix with Ecto

An example of lazy loading:

articles = from(a in Article, order_by: [asc: a.published_at], limit: 20)
           |> Repo.all()
 
articles_with_user = Enum.map(articles, fn article ->
  user = Repo.get(User, article.user_id)
  %{article | user: user}
end)
 
articles_with_comments = Enum.map(articles_with_user, fn article ->
  comments = Repo.all(from(c in Comment, where: c.article_id == ^article.id))
  %{article | comments: comments}
end)
 

Eager loading with preload and joins:

# preload
articles = from(a in Article, order_by: [asc: a.published_at], limit: 20)
           |> Repo.preload([:user, :comments])
           |> Repo.all()
 
# joins
articles = from(a in Article, join: u in assoc(a, :user), join: c in assoc(a, :comments),
                order_by: [asc: a.published_at], limit: 20)
           |> Repo.all()

Node.js with Prisma

An example of lazy loading:

const articles = await prisma.article.findMany({
  orderBy: { published_at: "asc" },
  take: 20,
});
 
const articlesWithUser = await Promise.all(
  articles.map(async (article) => {
    const user = await prisma.user.findUnique({
      where: { id: article.userId },
    });
    return { ...article, user };
  }),
);
 
const articlesWithComments = await Promise.all(
  articlesWithUser.map(async (article) => {
    const comments = await prisma.comment.findMany({
      where: { articleId: article.id },
    });
    return { ...article, comments };
  }),
);

An example of eager loading using include:

const articles = await prisma.article.findMany({
  orderBy: { published_at: "asc" },
  take: 20,
  include: { user: true, comments: true },
});

Eager monitoring with AppSignal

Ready to rid your applications of inefficient database queries using AppSignal? Be sure to sign up for a free trial, and don't be afraid to reach out to support if you need any help getting started.