Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 42 ยท Data and Persistence

Repositories, Entities and Relations

Model data in NestJS with entities and repositories, map one-to-many and many-to-many relations, avoid the N+1 query problem, paginate lists and separate DTOs from entities.

A database table by itself is just rows. Applications need entities (classes that stand for the things in your domain, such as an author or a post), relations between them (an author has many posts, a post has many tags), and a way to load and save them without writing every query by hand. This lesson covers those three ideas and the most common performance trap in this area, the N+1 query problem, plus pagination and the difference between an entity and the data you return from an API.

It continues from Connecting to a Database. The ORM code below uses TypeORM style and is read-only, because it needs a database server and npm packages. The relations and queries themselves are plain SQL concepts, so the runnable stand-ins use node:sqlite, the SQLite module in Node 24, to show the exact queries an ORM sends and to count them. The stand-ins are not Nest and not TypeORM.

Entities

An entity is a class mapped to a table. With TypeORM, decorators describe the mapping:

TypeScript
import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Author {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column()
  name: string;

  @OneToMany(() => Post, (post) => post.author)
  posts: Post[];
}

Entities describe storage. They often contain columns that must never reach a client (a password hash, internal flags) and relations that may be enormous. Keep that in mind for the DTO section below.

Relations

Three kinds of relation

One-to-one A user and their profile.
One-to-many An author and their posts.
Many-to-many Posts and tags, joined by a table.

The "many" side holds the foreign key.

1 / 2

The many side of a one-to-many relation stores the foreign key. A post has author_id:

TypeScript
@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @ManyToOne(() => Author, (author) => author.posts, { onDelete: 'CASCADE' })
  author: Author;

  @ManyToMany(() => Tag, (tag) => tag.posts)
  @JoinTable()
  tags: Tag[];
}

@ManyToMany needs a join table (here post_tags with a post_id and a tag_id), and @JoinTable() goes on the owning side to create it. An onDelete rule such as CASCADE says what the database does with posts when an author is removed; set it on purpose rather than leaving surprises.

Repositories

A repository gives you typed access to one entity. In the previous lesson you saw how to inject one; here is a service using it with relations:

TypeScript
@Injectable()
export class PostsService {
  constructor(@InjectRepository(Post) private readonly posts: Repository<Post>) {}

  recent() {
    return this.posts.find({
      relations: { author: true, tags: true },
      order: { id: 'DESC' },
      take: 20,
    });
  }
}

Relations are not loaded unless you ask: without relations, post.author is undefined. Be careful with eager: true on a relation; it makes every find join that table, which is often wasteful, and it can hide the true cost of a query. Prefer asking for relations where you need them, or use the query builder for custom joins (leftJoinAndSelect).

The N+1 problem

Suppose you list posts and show each author's name. A naive approach loads the posts (1 query) and then, for each post, loads its author (N queries). With 20 posts you send 21 queries; with 2,000 you send 2,001, and the response time grows with the data. Nothing looks wrong in the code, which is why N+1 is easy to introduce: it often hides behind a loop that touches a lazy relation, or a resolver that fetches per item.

There are two standard fixes:

  1. Join the related table so one query returns everything. Simple and fast, but rows for the "one" side repeat, and joins on large collections multiply rows.
  2. Batch the lookups: load the posts, collect the author ids, then load all those authors with a single WHERE id IN (...) and match them in memory. Two queries, no repeated rows.

Whichever you choose, check it. Turn on query logging in development (TypeORM has a logging option) and count queries per request.

Stand-in: counting queries

The first stand-in builds the schema in an in-memory SQLite database and loads posts with their authors in three ways, counting every query it sends. The helper q() is a stand-in for the ORM's query log.

Node.js N+1, join and batched loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

With 8 posts and 4 authors the naive version sends 9 queries, the other two send 1 and 2. The numbers grow with the post count for N+1 but stay fixed for the others. Notice that the batched version used only the 4 distinct author ids, not 8.

Many-to-many

A many-to-many relation is stored in a join table with one row per pair. Loading tags for posts uses the same strategies, and the join returns one row per post-tag pair, so you group them afterward. ORMs do this regrouping for you when you ask for relations: { tags: true }.

Pagination

Never return an unbounded list from an endpoint. There are two common styles:

  • Offset pagination (page and limit, or TypeORM's skip and take). It is simple and allows jumping to page 7, but deep pages are slow (the database still walks past all skipped rows), and rows can shift between pages while data changes.
  • Cursor (keyset) pagination: the client sends the last id it saw, and the query asks for rows after it (WHERE id < ? ORDER BY id DESC LIMIT ?). It stays fast on deep pages and is stable under inserts, but you cannot jump to an arbitrary page.

Either way, always add a deterministic order (usually the id as a tie-breaker). Without it, pages can repeat or skip rows. TypeORM's findAndCount() returns the page and the total in one call, at the cost of an extra count query. Cap the page size on the server (Math.min(limit, 100)) and validate it with a pipe.

DTO versus entity

An entity is your storage model. A DTO (data transfer object) is the shape you promise to clients. Returning entities directly is tempting but risky: you leak columns such as passwordHash, a new column becomes public the day you add it, and a relation can drag in huge object graphs or cycles (post to author to posts to author) that break serialization. Instead, map entities to response DTOs in the service or a small mapper function, listing the exposed fields explicitly. Nest's ClassSerializerInterceptor with @Exclude() can hide fields, but an explicit allow-list is harder to get wrong. The same logic applies in reverse: input DTOs with a validation whitelist stop clients from setting columns like isAdmin.

Stand-in: many-to-many, pagination and DTO mapping

The second stand-in joins posts and tags through a join table, groups the rows into objects, returns them with cursor pagination, and maps them to a DTO that leaves internal columns out.

Node.js Tags, cursor pages and a response DTO

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Each item has tags grouped from the join rows, and internal_notes never appears in the output because the DTO lists only id, title and tags. The query fetches size + 1 rows to learn whether another page exists without a count query, and the cursor is the id of the last item returned.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A list endpoint loads 50 posts, then runs one query per post to fetch its author. How many queries run?

    One query loads the posts and one more per post loads each author: 1 + N = 51. A join or one batched IN query fixes it.
  2. 2. Which table stores the pairs for a many-to-many relation between posts and tags?

    Relational databases model many-to-many with a join table. TypeORM creates it for the side marked with @JoinTable().
  3. 3. Why is returning entities straight from a controller risky?

    Entities model storage. A response DTO with an explicit field list controls exactly what clients see.
  4. 4. What is the main advantage of cursor pagination over offset pagination?

    Keyset queries seek directly to the next rows using an indexed column, so deep pages do not scan skipped rows. The trade-off is no random access to page N.

Summary

  • An entity maps a class to a table; relations (@OneToMany, @ManyToOne, @ManyToMany with a join table) describe how entities connect, and the "many" side holds the foreign key.
  • Relations load only when requested; avoid eager: true as a default.
  • N+1 means one query for a list plus one per item. Fix it with a join or a batched IN query, and count queries in development.
  • Always paginate lists with a deterministic order and a server-side limit; cursor pagination scales better than offset.
  • Map entities to explicit DTOs for responses, and validate input DTOs with a whitelist.

Next, make several writes succeed or fail together in Transactions and Migrations.