Room management system in Prisma

Question

I tried to create a database for a room management sytem and I'm confused about the relations and couldn't find helpful resources on the internet can you tell me if there is anything wrong with this prisma script? as I want to control it in expressJs and make an application based on it

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
}

I tried to make a crud for each entity but I end up with relational erros such as foregin key and stuff like that which means there is something wrong with my relations.

Answer 1

Your schema is valid, though I would recommend keeping consistency while defining id for your tables. Some of the tables have id of string type while some are of numeric type.

Here's an example of queries to create entites for your models.

import {
  PrismaClient,
  Type,
  ServiceType,
  Payment,
  ReservationType,
} from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query'],
});

async function main() {
  // Creating a room
  await prisma.room.create({
    data: {
      price: 100,
      type: Type.Single,
      roomId: 1,
    },
  });

  // Creating a guest
  await prisma.guest.create({
    data: {
      name: 'Test',
      nationality: 'Indian',
      phone: '1234567890',
      address: 'Test Address',
      guestId: '1',
    },
  });

  // Creating a service with a bill and a reservation
  await prisma.service.create({
    data: {
      name: 'test',
      price: 100,
      type: ServiceType.Bar,
      serviceId: '1',
      Bill: {
        create: {
          paymentMode: Payment.Cash,
          invoiceNo: '1',
          Reservation: {
            create: {
              checkIn: new Date(),
              checkOut: new Date(),
              type: ReservationType.Booked,
              visitors: 1,
              roomId: 1,
              guestId: '1',
            },
          },
        },
      },
    },
  });
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Here's the query response:

> ts-node index.ts
prisma:query BEGIN
prisma:query INSERT INTO `white_egret`.`Room` (`roomId`,`price`,`type`) VALUES (?,?,?)
prisma:query SELECT `white_egret`.`Room`.`roomId`, `white_egret`.`Room`.`price`, `white_egret`.`Room`.`type` FROM `white_egret`.`Room` WHERE `white_egret`.`Room`.`roomId` = ? LIMIT ? OFFSET ?
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO `white_egret`.`Guest` (`guestId`,`name`,`phone`,`address`,`nationality`) VALUES (?,?,?,?,?)
prisma:query SELECT `white_egret`.`Guest`.`guestId`, `white_egret`.`Guest`.`name`, `white_egret`.`Guest`.`phone`, `white_egret`.`Guest`.`address`, `white_egret`.`Guest`.`nationality` FROM `white_egret`.`Guest` WHERE `white_egret`.`Guest`.`guestId` = ? LIMIT ? OFFSET ?
prisma:query COMMIT
prisma:query BEGIN
prisma:query INSERT INTO `white_egret`.`Reservation` (`reservationId`,`checkIn`,`checkOut`,`guestId`,`visitors`,`type`,`roomId`) VALUES (?,?,?,?,?,?,?)
prisma:query INSERT INTO `white_egret`.`Bill` (`invoiceNo`,`reservationId`,`roomService`,`paymentMode`) VALUES (?,?,?,?)
prisma:query INSERT INTO `white_egret`.`Service` (`serviceId`,`type`,`name`,`price`,`billInvoiceNo`) VALUES (?,?,?,?,?)
prisma:query SELECT `white_egret`.`Service`.`serviceId`, `white_egret`.`Service`.`type`, `white_egret`.`Service`.`name`, `white_egret`.`Service`.`price`, `white_egret`.`Service`.`billInvoiceNo` FROM `white_egret`.`Service` WHERE `white_egret`.`Service`.`serviceId` = ? LIMIT ? OFFSET ?
prisma:query COMMIT