Eager Loading

Eager loading solves the N+1 query problem by loading all related data in advance with minimal database queries. Instead of making a separate query for each record’s relationships, ServiceSQL fetches all related data in just a few optimized queries.

The N+1 Problem

Without eager loading, you can easily create performance problems:

// ❌ N+1 Problem: 1 query for posts + N queries for authors
const posts = Post.all();
posts.each(post => {
  const author = User.find(post.user_id); // Separate query for EACH post!
  Logger.log(`${post.title} by ${author.name}`);
});

If you have 100 posts, this creates 101 queries (1 for posts + 100 for authors)!

Using Eager Loading

Load Single Relationships

Use the with() method to eager load relationships:

// ✅ Efficient: 2 queries total
const posts = Post.with("author").get();

posts.each(post => {
  Logger.log(`${post.title} by ${post.author.name}`);
});

Load Multiple Relationships

// Load multiple relationships at once
const posts = Post.with("author", "comments").get();

posts.each(post => {
  Logger.log(post.title);
  Logger.log(`By: ${post.author.name}`);
  Logger.log(`Comments: ${post.comments.count()}`);
});

Load Nested Relationships

Use dot notation to load relationships of relationships:

// Load posts, their comments, and each comment's author
const posts = Post.with("comments.author").get();

posts.each(post => {
  Logger.log(`Post: ${post.title}`);
  post.comments.each(comment => {
    Logger.log(`  - ${comment.body} (by ${comment.author.name})`);
  });
});

Defining Relationships

For eager loading to work, you must define relationship methods in your models:

class Post extends Model {
  author() {
    return this.belongsTo(User, "user_id");
  }

  comments() {
    return this.hasMany(Comment);
  }

  tags() {
    return this.manyToMany(Tag, "PostTag");
  }
}

Post._table = "Posts";
Post.use(db);

See the Relationships documentation for details on defining hasOne, hasMany, belongsTo, and manyToMany relationships.

How It Works

From EagerLoader.js:1-138, ServiceSQL’s eager loading implementation:

Parse relationship names

The with() method parses relationship strings including nested relations:

// "comments.author" becomes {comments: ["author"]}
static _parseRelations(relations) {
  const parsed = {};
  for (const rel of relations) {
    const parts = String(rel).split(".");
    const base = parts[0];
    if (!parsed[base]) parsed[base] = [];
    if (parts.length > 1) {
      parsed[base].push(parts.slice(1).join("."));
    }
  }
  return parsed;
}

Load each relationship

For each relationship, ServiceSQL:

Gets the relationship definition from the model
Creates a relation instance (HasMany, BelongsTo, etc.)
Calls relation.load(instances) to fetch data
Assigns results to the parent instances

Recursively load nested relations

After loading a relationship, nested relationships are loaded on the related records:

// Load nested relations
if (nested.length) {
  const allRelated = Object.values(loaded).flat();
  if (allRelated.length) {
    this.load(relationInstance.related, nested, allRelated);
  }
}

Practical Examples

Blog System

class User extends Model {
  posts() {
    return this.hasMany(Post);
  }

  profile() {
    return this.hasOne(Profile);
  }
}

class Post extends Model {
  author() {
    return this.belongsTo(User, "user_id");
  }

  comments() {
    return this.hasMany(Comment);
  }
}

class Comment extends Model {
  post() {
    return this.belongsTo(Post, "post_id");
  }

  author() {
    return this.belongsTo(User, "user_id");
  }
}

// Load users with posts and profile
const users = User.with("posts", "profile").get();

users.each(user => {
  Logger.log(user.name);
  Logger.log(user.profile ? user.profile.bio : "No profile");
  Logger.log(`Posts: ${user.posts.count()}`);
});

// Load posts with author and comments with their authors
const posts = Post.with("author", "comments.author").get();

posts.each(post => {
  Logger.log(`${post.title} by ${post.author.name}`);
  post.comments.each(comment => {
    Logger.log(`  Comment by ${comment.author.name}: ${comment.body}`);
  });
});

E-commerce System

class Order extends Model {
  customer() {
    return this.belongsTo(User, "user_id");
  }

  items() {
    return this.hasMany(OrderItem);
  }
}

class OrderItem extends Model {
  order() {
    return this.belongsTo(Order, "order_id");
  }

  product() {
    return this.belongsTo(Product, "product_id");
  }
}

// Load orders with customer, items, and product details
const orders = Order.with("customer", "items.product").get();

orders.each(order => {
  Logger.log(`Order #${order.id} for ${order.customer.name}`);
  order.items.each(item => {
    Logger.log(`  ${item.quantity}x ${item.product.name}`);
  });
});

Combining with Query Builder

Eager loading works seamlessly with other query methods:

// Eager loading + where clauses
const posts = Post.with("author", "comments")
  .where("published", true)
  .orderBy("created_at", "desc")
  .limit(10)
  .get();

// Eager loading + scopes
const posts = Post.scope("published")
  .with("author")
  .get();

// Eager loading with first()
const post = Post.with("author", "comments")
  .where("slug", slug)
  .first();

Performance Comparison

Without Eager Loading (N+1 Problem)

// ❌ Bad: 101 queries for 100 posts
const posts = Post.all(); // 1 query
posts.each(post => {
  const author = User.find(post.user_id); // 100 queries!
});

Queries executed: 1 + N (where N = number of posts)

With Eager Loading

// ✅ Good: 2 queries total
const posts = Post.with("author").get();
posts.each(post => {
  Logger.log(post.author.name); // No additional queries!
});

Queries executed: 2 (one for posts, one for all authors)

Relationship Constraints

You can add constraints to relationships when defining them:

class User extends Model {
  publishedPosts() {
    return this.hasMany(Post, null, "id", (qb) =>
      qb.where("published", true).orderBy("created_at", "desc")
    );
  }
}

// This will only load published posts, sorted by date
const users = User.with("publishedPosts").get();

Best Practices

Always use eager loading for relationships

// ✅ Load relationships in advance
const posts = Post.with("author", "comments").get();

// ❌ Don't query in loops
const posts = Post.all();
posts.each(post => {
  const author = User.find(post.user_id);
});

Load nested relationships efficiently

// ✅ Use dot notation for nested relations
const posts = Post.with("comments.author").get();

// ❌ Don't load nested relations separately
const posts = Post.with("comments").get();
posts.each(post => {
  post.comments.each(comment => {
    const author = User.find(comment.user_id);
  });
});

Define relationships properly

// ✅ Return relationship definitions
posts() {
  return this.hasMany(Post);
}

// ❌ Don't execute queries directly
posts() {
  return Post.where("user_id", this.id).get();
}

Common Pitfalls

Avoid these mistakes:

Not defining relationships: Eager loading only works with properly defined relationship methods
Incorrect foreign keys: Make sure your foreign key names match between tables
Querying in loops: Never call find() or where() inside loops when iterating over results

Implementation Reference

From Model.js:213-224, the model’s eager loading integration:

static with(...relations) {
  return this.query().with(...relations);
}

static _eagerLoad(relations, instances) {
  try {
    EagerLoader.load(this, relations, instances);
  } finally {
    this._with = null;
  }
}

Summary

Method	Description	Example
`with("relation")`	Load single relationship	`Post.with("author")`
`with("rel1", "rel2")`	Load multiple relationships	`Post.with("author", "comments")`
`with("rel.nested")`	Load nested relationships	`Post.with("comments.author")`

Rule of thumb: If you’re accessing a relationship in a loop, load it with with() first. This simple practice will dramatically improve your application’s performance.

Getting Started

Core Concepts

Advanced Features

Guides

The N+1 Problem

Using Eager Loading

Load Single Relationships

Load Multiple Relationships

Load Nested Relationships

Defining Relationships

How It Works

Practical Examples

Blog System

E-commerce System

Combining with Query Builder

Performance Comparison

Relationship Constraints

Best Practices

Common Pitfalls

Implementation Reference

Summary

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Advanced Features

Guides

​The N+1 Problem

​Using Eager Loading

​Load Single Relationships

​Load Multiple Relationships

​Load Nested Relationships

​Defining Relationships

​How It Works

​Practical Examples

​Blog System

​E-commerce System

​Combining with Query Builder

​Performance Comparison

​Relationship Constraints

​Best Practices

​Common Pitfalls

​Implementation Reference

​Summary

Build docs developers (and LLMs) love

The N+1 Problem

Using Eager Loading

Load Single Relationships

Load Multiple Relationships

Load Nested Relationships

Defining Relationships

How It Works

Practical Examples

Blog System

E-commerce System

Combining with Query Builder

Performance Comparison

Relationship Constraints

Best Practices

Common Pitfalls

Implementation Reference

Summary