【AI全栈】Prisma ORM 入门——用 TypeScript 的方式管理数据库

1
// 手写 SQL 的痛苦
2
const rows = await db.query(
3
  `SELECT * FROM cards WHERE status = $1 AND difficulty = $2 ORDER BY created_at DESC LIMIT $3 OFFSET $4`,
4
  ['todo', 'basic', 10, 0]
5
)
6
// rows 是 any[]，字段名写错了？TypeScript 不知道
7
// SQL 字符串拼错了？运行时才报错
8
// 字段改名了？全局搜索字符串替换，漏掉一处就是 bug

1
// Prisma 的方式
2
const cards = await prisma.card.findMany({
3
  where: { status: 'todo', difficulty: 'basic' },
4
  orderBy: { createdAt: 'desc' },
5
  take: 10, skip: 0,
6
})
7
// cards 是 Card[]，类型完整，字段名写错编译就报错

1
model Card {
2
  id         String   @id @default(uuid())
3
  title      String
4
  content    String
5
  summary    String?
6
  createdAt  DateTime @default(now()) @map("created_at")
7
  updatedAt  DateTime @updatedAt @map("updated_at")
8

9
  @@map("cards")
10
}

1
enum CardStatus {
2
  todo
3
  doing
4
  done
5
}
6

7
enum CardDifficulty {
8
  basic
9
  medium
10
  advanced
11
}
12

13
model Card {
14
  difficulty CardDifficulty @default(basic)
15
  status     CardStatus     @default(todo)
16
}

1
model Tag {
2
  id   String @id @default(uuid())
3
  name String @unique
4
  @@map("tags")
5
}
6

7
model CardTag {
8
  cardId String
9
  tagId  String
10
  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)
11
  tag  Tag  @relation(fields: [tagId],  references: [id], onDelete: Cascade)
12
  @@id([cardId, tagId])
13
  @@map("card_tags")
14
}

1
model LearningRecord {
2
  id     String  @id @default(uuid())
3
  cardId String  @map("card_id")
4
  action String
5
  note   String?
6

7
  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)
8
  @@map("learning_records")
9
}

1
-- prisma/migrations/.../migration.sql
2
CREATE TABLE "cards" (
3
    "id" TEXT NOT NULL,
4
    "title" TEXT NOT NULL,
5
    "content" TEXT NOT NULL,
6
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
7
    CONSTRAINT "cards_pkey" PRIMARY KEY ("id")
8
);

1
import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common'
2
import { PrismaClient } from '@prisma/client'
3
import { PrismaPg } from '@prisma/adapter-pg'
4

5
@Injectable()
6
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
7
  constructor() {
8
    const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL! })
9
    super({ adapter })
10
  }
11

12
  async onModuleInit()    { await this.$connect() }
13
  async onModuleDestroy() { await this.$disconnect() }
14
}

1
// class-validator 的写法
2
import { IsString, IsEnum, MinLength, IsOptional } from 'class-validator'
3

4
export class CreateCardDto {
5
  @IsString()
6
  @MinLength(1)
7
  title: string
8

9
  @IsEnum(CardDifficulty)
10
  @IsOptional()
11
  difficulty?: CardDifficulty
12
}

1
// ❌ class-validator：字段声明是 string，但实际可能是 undefined
2
const dto = plainToInstance(CreateCardDto, body)
3
dto.title  // TypeScript 认为是 string，实际可能是 undefined

1
// Zod 的写法
2
import { z } from 'zod'
3

4
export const createCardSchema = z.object({
5
  title:      z.string().trim().min(1, '标题不能为空'),
6
  difficulty: z.enum(['basic', 'medium', 'advanced']).default('basic'),
7
  status:     z.enum(['todo', 'doing', 'done']).default('todo'),
8
  content:    z.string().optional().default(''),
9
}).strict()
10

11
export type CreateCardDto = z.infer<typeof createCardSchema>

1
const dto = createCardSchema.parse(body)
2
// dto 是完整的 CreateCardDto 类型，已经过校验和转换
3
// 如果校验失败，直接抛 ZodError，不需要额外配置

1
// 请求校验（Zod）
2
const createCardSchema = z.object({
3
  title:      z.string().trim().min(1),
4
  difficulty: z.enum(['basic', 'medium', 'advanced']).default('basic'),
5
}).strict()
6

7
// 数据库层（Prisma Schema）
8
// enum CardDifficulty { basic medium advanced }
9
// model Card { difficulty CardDifficulty @default(basic) }