Database & ORM Questions

Persistence is where most NestJS interviews get concrete: which ORM you reach for and why, how the repository pattern fits Nest’s DI model, the difference between lazy and eager relations, how to wrap multiple writes in a transaction, and how to spot and kill N+1 queries. The answers below favor decisions you can defend with trade-offs, backed by runnable NestJS 10/11 and TypeScript code. Knowing the API is table stakes; explaining when and why is what separates a junior answer from a senior one.

How do you choose between TypeORM and Prisma?

Both are first-class in the Nest ecosystem, so the right answer is “it depends on the team and the data model,” not dogma. TypeORM is a classic Data Mapper/Active Record ORM that integrates through @nestjs/typeorm, lets you inject repositories directly, and is comfortable for people coming from Hibernate. Prisma is schema-first: you describe models in schema.prisma, run a generator, and get a fully typed client. Prisma’s type safety and migration story are excellent; TypeORM’s decorator entities feel more native to Nest’s decorator-heavy style.

Aspect	TypeORM	Prisma
Modeling	Decorator entities (classes)	`schema.prisma` DSL
Type safety	Good, runtime-reflective	Excellent, generated client
Migrations	CLI + generated SQL	`prisma migrate`, declarative
Relations	Lazy + eager support	Explicit `include`/`select`
Nest integration	`@nestjs/typeorm`, `@InjectRepository`	Custom `PrismaService`

Tip: There is no official Prisma module from Nest. The idiomatic approach is a thin PrismaService extends PrismaClient that connects on module init and is provided/exported by a PrismaModule.

What is the repository pattern and why use it?

The repository pattern places a collection-like abstraction between your business logic and the database. Your service talks to a repository’s methods (find, save, delete) instead of writing SQL inline, which keeps persistence concerns out of the domain layer and makes services easy to unit-test by swapping the repository for a mock.

With TypeORM, Nest hands you the repository through @InjectRepository:

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private readonly users: Repository<User>,
  ) {}

  findActive(): Promise<User[]> {
    return this.users.find({ where: { active: true } });
  }

  create(email: string): Promise<User> {
    const user = this.users.create({ email, active: true });
    return this.users.save(user);
  }
}

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
import { UsersService } from './users.service';

@Module({
  imports: [TypeOrmModule.forFeature([User])],
  providers: [UsersService],
})
export class UsersModule {}

For complex queries you can extend the base repository or define a custom repository, keeping all User-related data access in one cohesive class.

Lazy vs eager relations — what’s the difference?

An eager relation is loaded automatically every time you fetch the parent. A lazy relation is declared as a Promise and only hits the database when you await it. Eager is convenient but risks over-fetching; lazy gives control but is easy to forget to await. In practice, leave relations off by default and load them explicitly per query — that is the most predictable for performance.

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Post } from './post.entity';

@Entity()
export class User {
  @PrimaryGeneratedColumn() id: number;
  @Column() email: string;

  // Eager: always loaded with the user
  @OneToMany(() => Post, (post) => post.author, { eager: true })
  posts: Post[];
}

To load on demand instead, omit eager and request the relation per query:

this.users.find({ relations: { posts: true } });

How do you run a transaction?

A transaction guarantees that a group of writes either all succeed or all roll back. In TypeORM the cleanest API is DataSource.transaction, which gives you an EntityManager bound to a single connection so every operation participates in the same transaction.

import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { Account } from './account.entity';

@Injectable()
export class TransferService {
  constructor(private readonly dataSource: DataSource) {}

  async transfer(fromId: number, toId: number, amount: number): Promise<void> {
    await this.dataSource.transaction(async (manager) => {
      const from = await manager.findOneByOrFail(Account, { id: fromId });
      const to = await manager.findOneByOrFail(Account, { id: toId });

      from.balance -= amount;
      to.balance += amount;

      await manager.save([from, to]); // both committed or both rolled back
    });
  }
}

Output:

[Nest] LOG  Transfer of 100 from account 1 to account 2 committed.

Prisma’s equivalent is prisma.$transaction([...]) for a batch of independent operations, or the interactive callback form prisma.$transaction(async (tx) => { ... }) when later writes depend on earlier reads.

How do you manage migrations?

Never use synchronize: true outside local development — it can silently drop columns. Generate versioned migrations from entity changes and run them as a deliberate deploy step.

npx typeorm migration:generate ./src/migrations/AddUserActive -d ./dist/data-source.js
npx typeorm migration:run -d ./dist/data-source.js

With Prisma the workflow is declarative: edit schema.prisma, then npx prisma migrate dev --name add_user_active in development and npx prisma migrate deploy in CI/production.

How do you avoid the N+1 query problem?

N+1 happens when you fetch a list (1 query) and then trigger a separate query per row to load a relation (N queries). The fix is to fetch the related data in a single round trip. With TypeORM, use a QueryBuilder with leftJoinAndSelect (or the relations option) so the join happens in one SQL statement.

const usersWithPosts = await this.users
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .where('user.active = :active', { active: true })
  .getMany();

With Prisma the cure is include or select, which compiles to an efficient batched query rather than per-row lookups.

Warning: Eager relations across many entities can themselves cause N+1-style fan-out. Profile with query logging (logging: ['query'] in TypeORM) before assuming a fix worked.

Best Practices

Disable synchronize in any shared or production environment and rely on generated, reviewed migrations instead.
Keep persistence behind repositories or a PrismaService so business logic stays testable and database-agnostic.
Prefer loading relations explicitly per query over eager relations to avoid silent over-fetching.
Wrap multi-write operations in a transaction using a single EntityManager (TypeORM) or interactive $transaction (Prisma).
Hunt N+1 queries with join-based loading (leftJoinAndSelect / Prisma include) and verify with query logging.
Use connection pooling and set sensible pool limits; long-lived request-scoped database work should release connections promptly.