Relations and the N+1 problem

What it is

A relation in the schema (author: { type: Author, reverse_name: posts }) becomes two things in Dart:

a foreign-key field on the model (post.authorId), which is just a value; and
relation objects (Post.author, Author.posts) that describe how to load the other side.

Models never load related rows lazily. Nothing hits the database when you read post.authorLoaded; it returns whatever was preloaded, or null. Loading is always explicit, through Query.preload.

The N+1 problem

The naive way to show 50 authors with their posts:

final list = await authors().limit(50).all(ctx);           // 1 query
for (final a in list) {
  final ps = await posts().where(PostTable.authorId.eq(a.id!)).all(ctx); // 50 queries
}

That’s 1 + N round trips. With a few milliseconds per round trip it becomes the slowest part of the request, and it grows with the page size.

With preloading:

final list = await authors().limit(50).preload([Author.posts]).all(ctx); // 2 queries
for (final a in list) {
  print(a.postsList.length);
}

The second query is SELECT ... FROM posts WHERE author_id IN ($1, ..., $50); the preloader groups the results by author_id and attaches each group to its author.

When to use it

Any list view that shows related data.
Serialising a model with nested children for an API response.
Several levels deep: Author.posts.then(Post.comments) is one query per level.

When not to use it

You need only a few children per parent (the latest 3 comments), or children filtered or sorted. Preloading loads all matching children with no filter, order or limit. Write a query, or use a window function in raw SQL.
You only need a count or an aggregate of children. Use a grouped query instead of loading every child.
One parent. Preloading still works, but a direct query for the children (posts().where(PostTable.authorId.eq(id))) is just as cheap and lets you filter and sort.
Flat result rows (post title with author name). A join in raw SQL returns that in one query.

How it works

Relation kind	Generated for	Batched query
belongs-to	every foreign key (`Post.author`)	`WHERE id IN (<parent fks>)`
has-many	the target of a foreign key with `reverse_name` (`Author.posts`)	`WHERE author_id IN (<parent ids>)`
has-one	same, when the foreign key is `is_unique: true`	same; keeps the first match
many-to-many	`many_to_many: true` columns and their `reverse_name`	junction lookup, then `WHERE id IN (...)`

Each relation node costs one query (two for many-to-many), however many parents there are. Siblings in the preload list add a query each; .then adds a level.
Keys are de-duplicated and sent in batches of 10,000 to stay below Postgres’s parameter limit.
Loaded values are stored on each model (via the Preloadable mixin) and read back through generated accessors: postsList for collections, authorLoaded for single values.
The preloader runs on the same DbContext as the main query, so inside a transaction it sees uncommitted writes.

Inverse relations are opt-in

The has-many side exists only if the foreign key names it with reverse_name. Without it, Author.posts isn’t generated. Add reverse_name for every relation you want to traverse from the target side.

Writing relations

Relations are read-only. To link a post to an author, write the foreign key: posts().insert({'title': 'Hi', 'author_id': author.id}). Many-to-many links are rows in the junction table, written with raw SQL; see Work with relations.

Example

final list = await posts()
    .where(PostTable.publishedAt.isNotNull)
    .orderBy(PostTable.publishedAt, desc: true)
    .limit(20)
    .preload([Post.author, Post.comments.then(Comment.author)])
    .all(db.context());

// 1 (posts) + 1 (authors) + 1 (comments) + 1 (comment authors) = 4 queries
for (final p in list) {
  print('${p.title} by ${p.authorLoaded?.name}');
  for (final c in p.commentsList) {
    print('  ${c.authorLoaded?.name}: ${c.body}');
  }
}