NestJS Data Layer

This skill consolidates ORM patterns for the three most common choices in NestJS projects: TypeORM, Prisma, Mongoose. Detect which is in use, then apply the matching section.

Detection

Marker (in dependencies)	ORM
@nestjs/typeorm + typeorm	TypeORM
@prisma/client + prisma	Prisma
@nestjs/mongoose + mongoose	Mongoose
(multiple)	Hybrid project — match each feature's existing pattern
(none)	Raw driver (pg, mysql2) — flag in DECISIONS

TypeORM

Module setup

// src/app.module.ts
@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        type: 'postgres',
        url: config.get<string>('DATABASE_URL'),
        autoLoadEntities: true,
        synchronize: false,                 // NEVER true in prod — use migrations
        migrations: ['dist/migrations/*.js'],
        migrationsRun: false,
        logging: config.get<string>('NODE_ENV') !== 'production',
      }),
    }),
  ],
})

Entity

// src/users/entities/user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn, Index, OneToMany } from 'typeorm';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Index({ unique: true })
  @Column({ length: 255 })
  email!: string;

  @Column({ select: false })            // hash never selected by default
  passwordHash!: string;

  @Column({ type: 'jsonb', default: {} })
  metadata!: Record<string, unknown>;

  @CreateDateColumn()
  createdAt!: Date;

  @UpdateDateColumn()
  updatedAt!: Date;

  @OneToMany(() => Order, (order) => order.user)
  orders!: Order[];
}

• select: false for sensitive columns (passwords, tokens) — must explicitly .addSelect('user.passwordHash').
• @Index({ unique: true }) instead of @Column({ unique: true }) for clarity at index level.
• Use relations array on find options (not eager: true) — eager loading is hard to opt out of later.

Repository injection

// src/users/users.module.ts
@Module({
  imports: [TypeOrmModule.forFeature([User])],
  controllers: [UsersController],
  providers: [UsersService],
  exports: [UsersService],
})
export class UsersModule {}

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

  async findByEmail(email: string): Promise<User | null> {
    return this.users.findOne({ where: { email } });
  }
}

Query builder for complex queries

async findActiveAdmins(after: Date) {
  return this.users
    .createQueryBuilder('user')
    .innerJoin('user.roles', 'role')
    .where('role.name = :name', { name: 'admin' })
    .andWhere('user.lastLoginAt > :after', { after })
    .orderBy('user.email', 'ASC')
    .limit(100)
    .getMany();
}

Always parameterize (:name, :after) — never string-concat user input.

Transactions

Use EntityManager.transaction or DataSource.transaction:

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

  async placeOrder(userId: string, items: OrderItem[]): Promise<Order> {
    return this.dataSource.transaction(async (manager) => {
      const order = manager.create(Order, { userId, status: 'pending' });
      await manager.save(order);
      for (const item of items) {
        await manager.save(manager.create(OrderItem, { ...item, orderId: order.id }));
      }
      await manager.update(User, userId, { lastOrderAt: new Date() });
      return order;
    });
  }
}

Pass manager explicitly down — never reach for the global repository inside a transaction.

Migrations

# generate from current entity diff
npx typeorm-ts-node-commonjs migration:generate -d src/data-source.ts src/migrations/AddUserMetadata

# run pending
npx typeorm-ts-node-commonjs migration:run -d src/data-source.ts

# revert last
npx typeorm-ts-node-commonjs migration:revert -d src/data-source.ts

Each migration is timestamped — never edit after merge. Write a follow-up.

Common pitfalls

• N+1: users.find({ relations: ['orders'] }) runs ONE query with JOIN. users.find() then .orders accessed in a loop runs N queries. Always declare relations upfront.
• save vs insert: save does INSERT or UPDATE based on PK presence — silent. insert always inserts; update always updates. Use the explicit method.
• synchronize: true in prod — drops/recreates schema on entity change. Catastrophic.
• Cascading deletes — @OneToMany({ cascade: ['remove'] }) only cascades through TypeORM, not at DB level. Combine with onDelete: 'CASCADE' on the FK side.
• Lazy relations — Promise<User> on a property looks neat but causes implicit queries. Avoid.

Prisma

Service setup

// src/prisma/prisma.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect();
  }
}

// src/prisma/prisma.module.ts
@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

Schema (prisma/schema.prisma)

generator client { provider = "prisma-client-js" }
datasource db { provider = "postgresql"; url = env("DATABASE_URL") }

model User {
  id           String   @id @default(uuid())
  email        String   @unique
  passwordHash String
  metadata     Json     @default("{}")
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  orders       Order[]

  @@index([email])
  @@map("users")
}

Service usage

@Injectable()
export class UsersService {
  constructor(private readonly prisma: PrismaService) {}

  findByEmail(email: string) {
    return this.prisma.user.findUnique({ where: { email } });
  }

  create(input: Prisma.UserCreateInput) {
    return this.prisma.user.create({ data: input });
  }
}

Prisma's generated types (Prisma.UserCreateInput, User) are first-class. Use them in DTOs:

type CreateUserPayload = Pick<Prisma.UserCreateInput, 'email' | 'passwordHash'>;

Transactions

async transfer(fromId: string, toId: string, amount: number) {
  return this.prisma.$transaction(async (tx) => {
    await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
    await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
  });
}

For interactive transactions across services, pass tx down. Avoid $transaction([op1, op2]) array form when you need conditional logic — use the callback form.

Migrations

npx prisma migrate dev --name add_user_metadata    # dev: generate + apply
npx prisma migrate deploy                           # prod: apply only
npx prisma generate                                 # regenerate client after schema change

prisma migrate dev rewrites history if you change a migration before merging — fine in dev. After merge, only migrate deploy (apply forward).

Common pitfalls

• Forgetting prisma generate after schema change — TS types stale, runtime breaks.
• $queryRawUnsafe with user input — string interpolation = SQL injection. Use $queryRaw with template literal.
• Missing @unique on lookup field — findUnique requires a unique constraint; otherwise use findFirst.
• upsert race conditions — read-then-write inside a transaction or use unique constraint.
• Enabling previewFeatures casually — they break between Prisma versions.

Mongoose

Module setup

@Module({
  imports: [
    MongooseModule.forRootAsync({
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        uri: config.get<string>('MONGO_URL'),
      }),
    }),
  ],
})

Schema

// src/users/schemas/user.schema.ts
import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { HydratedDocument } from 'mongoose';

export type UserDocument = HydratedDocument<User>;

@Schema({ timestamps: true })
export class User {
  @Prop({ required: true, unique: true, index: true })
  email!: string;

  @Prop({ required: true, select: false })
  passwordHash!: string;

  @Prop({ type: Object, default: {} })
  metadata!: Record<string, unknown>;
}

export const UserSchema = SchemaFactory.createForClass(User);

Model injection

// src/users/users.module.ts
@Module({
  imports: [MongooseModule.forFeature([{ name: User.name, schema: UserSchema }])],
  providers: [UsersService],
})

// src/users/users.service.ts
@Injectable()
export class UsersService {
  constructor(@InjectModel(User.name) private readonly userModel: Model<UserDocument>) {}

  findByEmail(email: string) {
    return this.userModel.findOne({ email }).exec();
  }
}

Always .exec() for true Promise (otherwise it's a Mongoose Query — works in await but error stacks suffer).

Transactions (replica sets)

async transfer(fromId: string, toId: string, amount: number) {
  const session = await this.connection.startSession();
  try {
    session.startTransaction();
    await this.accountModel.updateOne({ _id: fromId }, { $inc: { balance: -amount } }, { session });
    await this.accountModel.updateOne({ _id: toId }, { $inc: { balance: +amount } }, { session });
    await session.commitTransaction();
  } catch (err) {
    await session.abortTransaction();
    throw err;
  } finally {
    session.endSession();
  }
}

Requires Mongo replica set (single-node cluster acceptable for dev with --replSet).

Common pitfalls

• No .exec() on queries — debugging stacks point inside Mongoose internals.
• Sub-document _id auto-generation — turn off with _id: false if you don't need it.
• save() vs updateOne() — save() runs validators and middleware; updateOne() doesn't (unless runValidators: true).
• findOneAndUpdate returns OLD doc by default — pass { new: true } for the updated version.
• Transactions on standalone Mongo — silently no-op. Check the deployment.

Choosing between ORMs (when starting fresh)

This is BA territory, not nest-architect's call. But for context:

• TypeORM — class-based entities; familiar to Java/C# devs; integrates well with NestJS DI; query builder. Schema-first feel.
• Prisma — schema-first DSL; generated types; best DX for migrations and tooling. Less Active-Record, more Data-Mapper-ish.
• Mongoose — only if Mongo is the answer. Avoid for relational data.

Anti-patterns (all ORMs)

• ❌ ORM operations from controllers (always go through services).
• ❌ Editing migrations after merge.
• ❌ synchronize: true / --accept-data-loss in prod scripts.
• ❌ String concatenation in queries ('WHERE id = ' + userId).
• ❌ Cascade-deleting through ORM only (without DB FK constraints) — partial deletes leave orphans on direct DB access.
• ❌ Loading entire collections without pagination (findAll with no take/skip).
• ❌ N+1 by accessing relations in loops without eager loading.
• ❌ Mixing transactional and non-transactional repositories in one operation.