Nest 与 Prisma 结合

Prisma 篇

注意

1. 我这里用的版本合集

• prisma6.19.0 版本
• "@prisma/client": "^6.19.0",
• "@prisma/extension-read-replicas": "^0.4.1",

2. 这里它会比老版本多了一个 prisma.config.ts 文件

3. 这里环境变量只能用 env 文件形式 yml 文件形式不行

新建一个 Nest 项目

• 具体的参考前面章节

安装 Prisma

• 安装 Prisma CLI

// 基础的使用包
pnpm install prisma
// 扩展包
pnpm install @prisma/client
// 读写分离
pnpm install @prisma/extension-read-replicas

初始化 Prisma

npx prisma init

• 这个时候无论你.env 里面写了什么它都会给你覆盖

修改.env文件

• 这里我使用的是 mysql 数据库

• 修改 .env, .env.development,.env.production 文件

 DATABASE_URL='mysql://用户名:密码@地址:端口号/数据库名'
 DATABASE_READ_URL= 'mysql://用户名:密码@读取的地址:端口号/数据库名'

修改 prisma/schema.prisma 文件

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
  moduleFormat = "cjs"
}

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

修改 prisma.config.ts 文件

import { defineConfig, env } from "prisma/config";
import "dotenv/config";
export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  engine: "classic",
  datasource: {
    url: env("DATABASE_URL"),
  },
});

拉取数据库

npx prisma db pull

• 这个时候 你的 prisma/schema.prisma 文件应该会自动生成数据库的表结构

多文件处理

• 在根目录下面找到prisma文件夹

依次按照schema.prisma文件里面的内容，新建对应的文件 类似

prisma
├── schema
│   ├── schema.prisma
│   ├── app.prisma
│   ├── shop.prisma
│   └── erp.prisma

• 每一个 prisma 文件 对应的就是一张表

分出去以后 把schema.prisma 里面表的结构都删除仅保留开始的.

• 把 schema.prisma 放到 prisma 文件夹下

再次修改 prisma.config.ts 文件

import { defineConfig, env } from "prisma/config";
import "dotenv/config";
export default defineConfig({
  schema: "prisma/schema",
  migrations: {
    path: "prisma/migrations",
  },
  engine: "classic",
  datasource: {
    url: env("DATABASE_URL"),
  },
});

生成 Prisma Client

npx prisma generate

最后的结果

prisma
├── generated
├── schema
│   ├── schema.prisma
│   ├── app.prisma
│   ├── shop.prisma
│   └── erp.prisma

Nest 篇

项目结构

prisma
├── prisma.extension.service.ts  // prisma 扩展
├── prisma.module.ts  // prisma 模块
├── prisma.service.ts  // prisma 暴露出去的服务
├── prisma.provider.ts // 核心prisma 提供

创建 一个 Module 和 Service

• 名字任意 我这里起名就是 prisma.module.ts 和 prisma.service.ts

prisma.extension.service.ts

• 给 prisma 添加一个扩展方法 exists

// query-helper.service.ts
import { Injectable } from "@nestjs/common";
import { Prisma } from "@prisma/client/extension.js";

@Injectable()
export class PrismaQueryHelperService {
  //any logic

  existsExtension = Prisma.defineExtension({
    name: "exists-extension",
    model: {
      $allModels: {
        async exists<T>(
          this: T,
          where: Prisma.Args<T, "findFirst">["where"]
        ): Promise<boolean> {
          const context = Prisma.getExtensionContext(this);
          // eslint-disable-next-line @typescript-eslint/no-explicit-any
          const result = await (context as any).findFirst({ where });
          return result !== null;
        },
      },
    },
  });
}

prisma.extension2.service.ts

• 自己写的分页方法

import { Injectable } from "@nestjs/common";
import { Prisma } from "@prisma/client/extension.js";

@Injectable()
export class PrismaQueryHelperService2 {
  existsExtension = Prisma.defineExtension({
    name: "extension2",
    model: {
      $allModels: {
        async paginate<T>(
          this: T,
          page: number,
          pageSize: number,
          options: any = {}
        ): Promise<any> {
          const context = Prisma.getExtensionContext(this);
          const skip = (page - 1) * pageSize;
          const [data, total] = await Promise.all([
            (context as any).findMany({
              skip,
              take: pageSize,
              ...options,
            }),
            (context as any).count({ where: options.where }),
          ]);
          return {
            data,
            pagination: {
              total,
              page,
              pageSize,
              totalPages: Math.ceil(total / pageSize),
              hasNextPage: page < Math.ceil(total / pageSize),
              hasPreviousPage: page > 1,
            },
          };
        },
      },
    },
  });
}

prisma.provider.ts

import { Injectable, OnModuleDestroy, OnModuleInit } from "@nestjs/common";
import { PrismaClient } from "../../../prisma/generated/prisma/client"; // 修改为你自己的
import { PrismaQueryHelperService } from "./prisma.extension.service"; // 扩展
import { PrismaQueryHelperService2 } from "./prisma.extension2.service"; // 分页
// 读写分离 这样读是一个数据库 写又是另外一个数据库
import { readReplicas } from "@prisma/extension-read-replicas";
import { ConfigService } from "@nestjs/config";
@Injectable()
export class PrismaProvider
  extends PrismaClient
  implements OnModuleInit, OnModuleDestroy
{
  private static initialized = false;

  constructor(
    private readonly configService: ConfigService,
    private readonly PrismaQueryHelperService: PrismaQueryHelperService,
    private readonly PrismaQueryHelperService2: PrismaQueryHelperService2
  ) {
    super({
      datasourceUrl: configService.get("DATABASE_URL"),
      log: ["info", "query"],
    });
  }

  async onModuleInit() {
    if (!PrismaProvider.initialized) {
      PrismaProvider.initialized = true;
      await this.$connect();
    }
  }

  async onModuleDestroy() {
    if (PrismaProvider.initialized) {
      PrismaProvider.initialized = false;
      await this.$disconnect();
    }
  }
  // 增加扩展 // 读写分离 绑定两个扩展
  withExtensions() {
    // 读的客户端
    const readReplicasClient = new PrismaClient({
      datasourceUrl: this.configService.get("DATABASE_READ_URL"),
      log: ["info", "query"],
    });
    return this.$extends(this.PrismaQueryHelperService.existsExtension)
      .$extends(
        readReplicas({
          replicas: [readReplicasClient],
        })
      )
      .$extends(this.PrismaQueryHelperService2.existsExtension);
  }
}

prisma.service.ts

import { Inject, Injectable, OnModuleInit, Type } from "@nestjs/common";
import { PrismaProvider } from "./prisma.provider";

const ExtendedPrismaClient = class {
  constructor(provider: PrismaProvider) {
    return provider.withExtensions();
  }
} as Type<ReturnType<PrismaProvider["withExtensions"]>>;

@Injectable()
export class PrismadbService extends ExtendedPrismaClient {
  constructor(provider: PrismaProvider) {
    super(provider);
  }
}

prisma.module.ts

import { PrismadbService } from "./prisma.service"; // 服务

import { Module, Global } from "@nestjs/common";

import { PrismaProvider } from "./prisma.provider"; // 提供

import { PrismaQueryHelperService } from "./prisma.extension.service"; // 扩展

import { PrismaQueryHelperService2 } from "./prisma.extension2.service"; // 扩展
@Global() // Global decorator to make this module available globally
@Module({
  imports: [],
  controllers: [],
  providers: [
    PrismadbService,
    PrismaProvider,
    PrismaQueryHelperService,
    PrismaQueryHelperService2,
  ],
  exports: [
    PrismadbService,
    PrismaProvider,
    PrismaQueryHelperService,
    PrismaQueryHelperService2,
  ],
})
export class PrismadbModule {}

使用

控制器

  @Post('testmysql')
  async testmysql() {
    return await this.testprojectService.testmysql();
  }

服务

import { Inject, Injectable } from "@nestjs/common";
import { ConfigService } from "@nestjs/config";
import { PrismadbService } from "../prisma/prisma.service";
@Injectable()
export class TestprojectService {
  @Inject()
  private configService: ConfigService;

  @Inject()
  private prisma: PrismadbService;

  async testmysql() {
    const result = await this.prisma.users.exists({ id: { gte: 100 } });
    console.log(result);
    return result;
  }

  // 测试分页
  async testextension() {
    let page = 1; // 当前页
    let pagesize = 2; // 每页条数
    const result = await this.prisma.users.paginate(page, pagesize, {
      where: { id: { gte: 2 } },
      orderBy: { id: "desc" },
      select: {
        username: true,
        user_roles: true,
      },
    });
    return result;
  }
}

调用

### 测试项目
POST http://localhost:5000/testproject/testmysql
Content-Type: application/json

### 测试项目
POST http://localhost:5000/testproject/testextension
Content-Type: application/json