NestJS 数据库集成

TypeORM 与 Prisma 在 NestJS 中的实践

ORM 选型

特性	TypeORM	Prisma
范式	Active Record / Data Mapper	独立 Schema
类型安全	装饰器 + 泛型	自动生成类型
迁移	CLI 工具	完善的迁移系统
学习曲线	中等	低
性能	好	好
生态	成熟	快速发展

TypeORM 集成

配置

typescript

// app.module.ts
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        type: 'mysql',
        host: config.get('DB_HOST'),
        port: config.get('DB_PORT'),
        username: config.get('DB_USER'),
        password: config.get('DB_PASSWORD'),
        database: config.get('DB_NAME'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: false, // 生产环境禁用
        logging: config.get('NODE_ENV') === 'development'
      })
    })
  ]
})
export class AppModule {}

Entity 定义

typescript

// user.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, OneToMany, CreateDateColumn } from 'typeorm';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

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

  @Column()
  password: string;

  @Column({ nullable: true })
  name: string;

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

  @CreateDateColumn()
  createdAt: Date;
}

Repository 模式

typescript

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

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

  async findAll(): Promise<User[]> {
    return this.userRepository.find({
      relations: ['posts'],
      order: { createdAt: 'DESC' }
    });
  }

  async findOne(id: number): Promise<User | null> {
    return this.userRepository.findOne({
      where: { id },
      relations: ['posts']
    });
  }

  async create(data: CreateUserDto): Promise<User> {
    const user = this.userRepository.create(data);
    return this.userRepository.save(user);
  }

  async update(id: number, data: UpdateUserDto): Promise<User> {
    await this.userRepository.update(id, data);
    return this.findOne(id);
  }

  async remove(id: number): Promise<void> {
    await this.userRepository.delete(id);
  }
}

Query Builder

typescript

async findWithFilters(filters: UserFilters): Promise<User[]> {
  const qb = this.userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.posts', 'post');

  if (filters.email) {
    qb.andWhere('user.email LIKE :email', { email: `%${filters.email}%` });
  }

  if (filters.hasPost) {
    qb.andWhere('post.id IS NOT NULL');
  }

  return qb
    .orderBy('user.createdAt', 'DESC')
    .skip(filters.offset)
    .take(filters.limit)
    .getMany();
}

Prisma 集成

配置

typescript

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

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

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

// 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 = "mysql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  password  String
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

Service 实现

typescript

// users.service.ts
@Injectable()
export class UsersService {
  constructor(private readonly prisma: PrismaService) {}

  async findAll() {
    return this.prisma.user.findMany({
      include: { posts: true },
      orderBy: { createdAt: 'desc' }
    });
  }

  async findOne(id: number) {
    return this.prisma.user.findUnique({
      where: { id },
      include: { posts: true }
    });
  }

  async create(data: CreateUserDto) {
    return this.prisma.user.create({
      data,
      include: { posts: true }
    });
  }

  async update(id: number, data: UpdateUserDto) {
    return this.prisma.user.update({
      where: { id },
      data
    });
  }

  async remove(id: number) {
    return this.prisma.user.delete({ where: { id } });
  }
}

事务处理

TypeORM 事务

typescript

// 使用 QueryRunner
async transferMoney(fromId: number, toId: number, amount: number) {
  const queryRunner = this.dataSource.createQueryRunner();
  
  await queryRunner.connect();
  await queryRunner.startTransaction();
  
  try {
    await queryRunner.manager.decrement(Account, { id: fromId }, 'balance', amount);
    await queryRunner.manager.increment(Account, { id: toId }, 'balance', amount);
    
    await queryRunner.commitTransaction();
  } catch (error) {
    await queryRunner.rollbackTransaction();
    throw error;
  } finally {
    await queryRunner.release();
  }
}

// 使用装饰器（需配置）
@Transactional()
async createUserWithProfile(data: CreateUserDto) {
  const user = await this.userRepository.save(data);
  await this.profileRepository.save({ userId: user.id });
  return user;
}

Prisma 事务

typescript

// 交互式事务
async transferMoney(fromId: number, toId: number, amount: number) {
  return this.prisma.$transaction(async (tx) => {
    const from = await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } }
    });

    if (from.balance < 0) {
      throw new BadRequestException('Insufficient balance');
    }

    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } }
    });

    return { success: true };
  });
}

// 批量事务
async createBatch(users: CreateUserDto[]) {
  return this.prisma.$transaction(
    users.map(user => this.prisma.user.create({ data: user }))
  );
}

Repository 模式封装

通用 Repository

typescript

// base.repository.ts
export abstract class BaseRepository<T> {
  constructor(protected readonly prisma: PrismaService) {}

  abstract get model(): any;

  async findAll(args?: any): Promise<T[]> {
    return this.model.findMany(args);
  }

  async findOne(id: number): Promise<T | null> {
    return this.model.findUnique({ where: { id } });
  }

  async create(data: any): Promise<T> {
    return this.model.create({ data });
  }

  async update(id: number, data: any): Promise<T> {
    return this.model.update({ where: { id }, data });
  }

  async delete(id: number): Promise<T> {
    return this.model.delete({ where: { id } });
  }
}

// users.repository.ts
@Injectable()
export class UsersRepository extends BaseRepository<User> {
  constructor(prisma: PrismaService) {
    super(prisma);
  }

  get model() {
    return this.prisma.user;
  }

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

高频面试题

Q1: TypeORM 和 Prisma 如何选择？

场景	推荐
类型安全优先	Prisma
复杂查询多	TypeORM (Query Builder)
快速开发	Prisma
已有 TypeORM 经验	TypeORM

Q2: 如何处理 N+1 问题？

typescript

// TypeORM - 使用 relations
const users = await this.userRepository.find({
  relations: ['posts', 'posts.comments']
});

// Prisma - 使用 include
const users = await this.prisma.user.findMany({
  include: { posts: { include: { comments: true } } }
});

Q3: NestJS 中事务如何与 Service 结合？

注入 DataSource/PrismaService：在 Service 中管理事务
自定义装饰器：封装事务逻辑
Interceptor：请求级事务（不推荐，粒度过粗）

Q4: 如何实现软删除？

typescript

// TypeORM
@Entity()
export class User {
  @DeleteDateColumn()
  deletedAt: Date;
}

// 查询自动排除已删除
const users = await this.userRepository.find();

// 查询包含已删除
const users = await this.userRepository.find({ withDeleted: true });

// Prisma - 中间件实现
this.prisma.$use(async (params, next) => {
  if (params.action === 'delete') {
    params.action = 'update';
    params.args.data = { deletedAt: new Date() };
  }
  return next(params);
});

迁移管理

TypeORM

bash

# 生成迁移
npx typeorm migration:generate -n AddUserTable

# 运行迁移
npx typeorm migration:run

# 回滚
npx typeorm migration:revert

Prisma

bash

# 创建迁移
npx prisma migrate dev --name add_user_table

# 生产环境部署
npx prisma migrate deploy

# 重置数据库
npx prisma migrate reset

NestJS 数据库集成 ​

目录 ​

ORM 选型 ​

TypeORM 集成 ​

配置 ​

Entity 定义 ​

Repository 模式 ​

Query Builder ​

Prisma 集成 ​

配置 ​

Schema 定义 ​

Service 实现 ​

事务处理 ​

TypeORM 事务 ​

Prisma 事务 ​

Repository 模式封装 ​

通用 Repository ​

高频面试题 ​

Q1: TypeORM 和 Prisma 如何选择？ ​

Q2: 如何处理 N+1 问题？ ​

Q3: NestJS 中事务如何与 Service 结合？ ​

Q4: 如何实现软删除？ ​

迁移管理 ​

TypeORM ​

Prisma ​

NestJS 数据库集成

目录

ORM 选型

TypeORM 集成

配置

Entity 定义

Repository 模式

Query Builder

Prisma 集成

配置

Schema 定义

Service 实现

事务处理

TypeORM 事务

Prisma 事务

Repository 模式封装

通用 Repository

高频面试题

Q1: TypeORM 和 Prisma 如何选择？

Q2: 如何处理 N+1 问题？

Q3: NestJS 中事务如何与 Service 结合？

Q4: 如何实现软删除？

迁移管理

TypeORM

Prisma