基于Prisma的客房管理系统数据库关系错误排查请求

我为客房管理系统创建数据库时，用Prisma定义数据模型对关系配置存在困惑，网上没找到有效参考资源。编写完以下Prisma脚本后，在Express中实现各实体CRUD操作时出现外键等关系错误，麻烦排查脚本中的问题以支撑后续应用开发。

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

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

model Guest {
  guestId     String        @id @default(uuid())
  name        String
  phone       String        @unique()
  address     String?
  nationality String
  Reservation Reservation[] @relation("GuestReservation")
}

model Reservation {
  reservationId Int             @id @default(autoincrement())
  checkIn       DateTime
  checkOut      DateTime
  Guest         Guest           @relation("GuestReservation", fields: [guestId], references: [guestId], onDelete: Cascade)
  guestId       String
  visitors      Int
  Room          Room            @relation("RoomReservation", fields: [roomId], references: [roomId], onDelete: Cascade)
  type          ReservationType
  roomId        Int
  Bill          Bill?           @relation("BillReservation")
}

enum ReservationType {
  Booking
  Contract
  Booked
  Canceled
}

model Room {
  roomId      Int           @id @default(autoincrement())
  price       Float
  type        Type
  Reservation Reservation[] @relation("RoomReservation")
}

enum Type {
  Single
  Double
  Triple
}

model Bill {
  invoiceNo     String      @id @default(uuid())
  Reservation   Reservation @relation("BillReservation", fields: [reservationId], references: [reservationId], onDelete: Cascade)
  reservationId Int         @unique()
  roomService   Float       @default(0)
  paymentMode   Payment
  Service       Service[]
}

enum Payment {
  Cash
  Visa
}

model Service {
  serviceId     String      @id @default(uuid())
  type          ServiceType
  name          String
  price         Float
  Bill          Bill        @relation(fields: [billInvoiceNo], references: [invoiceNo], onDelete: Cascade)
  billInvoiceNo String
}

enum ServiceType {
  Bar
  Laundry
}

问题排查与修正方案 #

1. Bill与Reservation双向关系定义不完整

Reservation模型中的Bill字段仅指定了关系名，但未关联对应的外键字段和参考字段，导致Prisma无法正确映射双向关系，触发外键错误。

修正：在Reservation的Bill字段补充fields和references配置：

bill          Bill?           @relation("BillReservation", fields: [reservationId], references: [reservationId])

2. Service与Bill关系缺少统一命名

Bill模型中的Service数组未指定关系名，而Service模型中的Bill字段虽配置了关联，但双向关系名不统一，导致Prisma无法识别关联逻辑。

修正：给双向关系添加统一的关系名"BillService"：

• Bill模型：

services      Service[]   @relation("BillService")

• Service模型：

bill          Bill        @relation("BillService", fields: [billInvoiceNo], references: [invoiceNo], onDelete: Cascade)

3. 命名规范优化（可选但推荐）

• 将模型中的关联字段改为复数形式（如Reservation改为reservations），符合数据库关联字段的命名习惯，提升可读性。
• 枚举Type重命名为RoomType，避免与编程语言内置类型冲突，减少歧义。

修正后的完整Prisma脚本 #

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

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

model Guest {
  guestId     String        @id @default(uuid())
  name        String
  phone       String        @unique()
  address     String?
  nationality String
  reservations Reservation[] @relation("GuestReservation")
}

model Reservation {
  reservationId Int             @id @default(autoincrement())
  checkIn       DateTime
  checkOut      DateTime
  guest         Guest           @relation("GuestReservation", fields: [guestId], references: [guestId], onDelete: Cascade)
  guestId       String
  visitors      Int
  room          Room            @relation("RoomReservation", fields: [roomId], references: [roomId], onDelete: Cascade)
  type          ReservationType
  roomId        Int
  bill          Bill?           @relation("BillReservation", fields: [reservationId], references: [reservationId])
}

enum ReservationType {
  Booking
  Contract
  Booked
  Canceled
}

model Room {
  roomId        Int           @id @default(autoincrement())
  price         Float
  type          RoomType
  reservations  Reservation[] @relation("RoomReservation")
}

enum RoomType {
  Single
  Double
  Triple
}

model Bill {
  invoiceNo     String      @id @default(uuid())
  reservation   Reservation @relation("BillReservation", fields: [reservationId], references: [reservationId], onDelete: Cascade)
  reservationId Int         @unique()
  roomService   Float       @default(0)
  paymentMode   Payment
  services      Service[]   @relation("BillService")
}

enum Payment {
  Cash
  Visa
}

model Service {
  serviceId     String      @id @default(uuid())
  type          ServiceType
  name          String
  price         Float
  bill          Bill        @relation("BillService", fields: [billInvoiceNo], references: [invoiceNo], onDelete: Cascade)
  billInvoiceNo String
}

enum ServiceType {
  Bar
  Laundry
}

内容的提问来源于stack exchange，提问作者MoLeo