NestJS Prisma集成 #

Prisma简介 #

Prisma是一个现代数据库工具包，包含Prisma Client（自动生成的类型安全查询构建器）、Prisma Migrate（数据库迁移工具）和Prisma Studio（数据库GUI）。

安装依赖 #

bash

npm install prisma --save-dev
npm install @prisma/client

初始化Prisma #

bash

npx prisma init

生成的文件结构：

text

prisma/
└── schema.prisma    # 数据库模型定义
.env                 # 环境变量

配置数据库 #

schema.prisma #

prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

// PostgreSQL
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
// }

// SQLite
// datasource db {
//   provider = "sqlite"
//   url      = "file:./dev.db"
// }

.env #

env

DATABASE_URL="mysql://user:password@localhost:3306/mydb"

定义模型 #

基本模型 #

prisma

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

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  avatar String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id])
}

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

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

字段类型 #

类型	说明
`String`	字符串
`Int`	整数
`Float`	浮点数
`Boolean`	布尔值
`DateTime`	日期时间
`Json`	JSON数据
`Bytes`	二进制数据
`Decimal`	高精度小数

属性装饰器 #

属性	说明
`@id`	主键
`@default(autoincrement())`	自增
`@default(uuid())`	UUID
`@default(now())`	当前时间
`@unique`	唯一约束
`@updatedAt`	自动更新时间
`@map("column_name")`	映射列名
`@db.VarChar(100)`	数据库类型

Prisma服务 #

创建Prisma服务 #

typescript

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模块 #

typescript

import { Global, Module } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

CRUD操作 #

创建 #

typescript

import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';
import { CreateUserDto } from './dto/create-user.dto';

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

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

  async createWithProfile(userData: any, profileData: any) {
    return this.prisma.user.create({
      data: {
        ...userData,
        profile: {
          create: profileData,
        },
      },
      include: {
        profile: true,
      },
    });
  }
}

查询 #

typescript

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

  async findAll() {
    return this.prisma.user.findMany();
  }

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

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

  async findActive() {
    return this.prisma.user.findMany({
      where: { isActive: true },
      orderBy: { createdAt: 'desc' },
    });
  }
}

更新 #

typescript

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

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

  async updateByEmail(email: string, data: any) {
    return this.prisma.user.update({
      where: { email },
      data,
    });
  }

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

删除 #

typescript

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

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

  async deleteMany(ids: number[]) {
    return this.prisma.user.deleteMany({
      where: {
        id: { in: ids },
      },
    });
  }
}

查询选项 #

select #

选择特定字段：

typescript

const user = await this.prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    email: true,
  },
});

include #

包含关联数据：

typescript

const user = await this.prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      include: {
        tags: true,
      },
    },
    profile: true,
  },
});

where #

条件查询：

typescript

const users = await this.prisma.user.findMany({
  where: {
    AND: [
      { isActive: true },
      {
        OR: [
          { name: { contains: 'John' } },
          { email: { endsWith: '@example.com' } },
        ],
      },
    ],
  },
});

orderBy #

排序：

typescript

const users = await this.prisma.user.findMany({
  orderBy: [
    { createdAt: 'desc' },
    { name: 'asc' },
  ],
});

分页 #

typescript

const users = await this.prisma.user.findMany({
  skip: (page - 1) * limit,
  take: limit,
});

// 或使用游标
const users = await this.prisma.user.findMany({
  cursor: { id: lastId },
  take: limit,
});

事务处理 #

批量操作 #

typescript

const result = await this.prisma.$transaction([
  this.prisma.user.create({ data: userData }),
  this.prisma.profile.create({ data: profileData }),
]);

交互式事务 #

typescript

const result = await this.prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: userData });
  await tx.profile.create({
    data: { ...profileData, userId: user.id },
  });
  return user;
});

原始查询 #

typescript

// 原始SQL查询
const result = await this.prisma.$queryRaw`
  SELECT * FROM users WHERE id = ${id}
`;

// 执行原始SQL
await this.prisma.$executeRaw`
  UPDATE users SET isActive = false WHERE id = ${id}
`;

数据库迁移 #

创建迁移 #

bash

npx prisma migrate dev --name init

应用迁移 #

bash

npx prisma migrate deploy

重置数据库 #

bash

npx prisma migrate reset

Prisma Studio #

启动数据库GUI：

bash

npx prisma studio

生成客户端 #

bash

npx prisma generate

最佳实践 #

1. 使用DTO #

typescript

import { IsEmail, IsString, MinLength } from 'class-validator';

export class CreateUserDto {
  @IsString()
  name: string;

  @IsEmail()
  email: string;

  @IsString()
  @MinLength(8)
  password: string;
}

2. 错误处理 #

typescript

import { Injectable } from '@nestjs/common';
import { Prisma } from '@prisma/client';

@Injectable()
export class UsersService {
  async create(data: CreateUserDto) {
    try {
      return await this.prisma.user.create({ data });
    } catch (error) {
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        if (error.code === 'P2002') {
          throw new ConflictException('Email already exists');
        }
      }
      throw error;
    }
  }
}

3. 软删除 #

prisma

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  deletedAt DateTime?
  isActive  Boolean   @default(true)

  @@filter(deletedAt == null)
}

4. 分页封装 #

typescript

export class PaginationService {
  async paginate(
    model: any,
    page: number,
    limit: number,
    where?: any,
  ) {
    const [data, total] = await Promise.all([
      model.findMany({
        where,
        skip: (page - 1) * limit,
        take: limit,
      }),
      model.count({ where }),
    ]);

    return {
      data,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    };
  }
}

总结 #

本章学习了NestJS与Prisma的集成：

Prisma配置和模型定义
Prisma服务创建
CRUD操作
查询选项
事务处理
数据库迁移

接下来，让我们学习 MongoDB集成。