๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
โš’ Backend/Node.js

[Node.js] Prisma + MySQL ๋‹ค๋Œ€๋‹ค ๊ด€๊ณ„ ๋งŒ๋“ค๊ธฐ (MySQL Many to Many relationship using Prisma)

by Fomagran ๐Ÿ’ป 2022. 8. 18.
728x90
๋ฐ˜์‘ํ˜•

์•ˆ๋…•ํ•˜์„ธ์š” Foma ์ž…๋‹ˆ๋‹ค.

 

์š”์ฆ˜ ์ฑ„ํŒ…์•ฑ์„ ๋งŒ๋“ค๋ฉด์„œ ์œ ์ €์™€ ์ฑ„ํŒ…๋ฐฉ์˜ ๊ด€๊ณ„๋ฅผ ์„ค์ •ํ•ด์•ผ ํ•˜๋Š”๋ฐ ์กฐ๊ธˆ ๋ณต์žกํ•œ ๊ฒŒ ์žˆ๋”๋ผ๊ตฌ์š”.

 

๊ฐ„๋‹จํ•˜๊ฒŒ ์„ค๋ช…ํ•˜๋ฉด ์ฑ„ํŒ…์•ฑ์„ ๋งŒ๋“œ๋Š”๋ฐ User๋Š” ์—ฌ๋Ÿฌ ์ฑ„ํŒ…๋ฐฉ์„ ๊ฐ€์ ธ์•ผ ํ•˜๊ณ ,

 

์ฑ„ํŒ…๋ฐฉ์€ ์—ฌ๋Ÿฌ ์œ ์ €๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” ๋‹ค๋Œ€๋‹ค ๊ด€๊ณ„๋ฅผ ๋งŒ๋“ค๊ณ  ์‹ถ์—ˆ๋Š”๋ฐ์š”.

 

๊ณต์‹ ๋ฌธ์„œ๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ํ•ด๋‹น ๋ชจ๋ธ๊ณผ ๊ด€๊ณ„๋ฅผ ์ •๋ฆฌํ•ด ๋ณด๋„๋ก ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

 

๋ฐ”๋กœ ์‹œ์ž‘ํ• ๊ฒŒ์š”~


Model

 

User

 

์œ ์ € ๋ชจ๋ธ์€ ์•„๋ž˜์™€ ๊ฐ™์ด ๋งŒ๋“ค์–ด ์ค๋‹ˆ๋‹ค.

 

model User {
  id           String         @id @default(uuid())
  name         String         @unique
  profileImage String
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @updatedAt
}

 

ChatRoom

 

์ฑ„ํŒ…๋ฐฉ ๋ชจ๋ธ์€ ์•„๋ž˜์™€ ๊ฐ™์ด ๋งŒ๋“ค์–ด ์ค๋‹ˆ๋‹ค.

 

model ChatRoom {
  id              String         @id @default(uuid())
  title           String
  hasNewMessage   Boolean        @default(false)
  lastChatContent String         @default("")
  lastChatDate    DateTime       @default(now())
  createdAt       DateTime       @default(now())
  updatedAt       DateTime       @updatedAt
}

 

๊ทธ ๋‹ค์Œ ์œ ์ €์™€ ์ฑ„ํŒ…๋ฐฉ์„ ๊ฐ–๋Š” ์ƒˆ๋กœ์šด ๋ชจ๋ธ์„ ๋งŒ๋“ค์–ด ์ค˜์•ผ ํ•˜๋Š”๋ฐ์š”.

 

์ €๋Š” ์ฑ„ํŒ…๋ฐฉ๊ณผ ์œ ์ €๋ฅผ ๋ชจ๋‘ ์ €์žฅํ•  ๋ชจ๋ธ์ธ ChatRoomUser๋ผ๊ณ  ์ด๋ฆ„ ์ง“๊ฒ ์Šต๋‹ˆ๋‹ค.

 

ํ•ด๋‹น ๋ชจ๋ธ์€ ์ฑ„ํŒ…๋ฐฉ id์™€ ์œ ์ € id๋ฅผ ๊ด€๊ณ„๋กœ ๊ฐ–์Šต๋‹ˆ๋‹ค.

 

model ChatRoomUser {
  id         String    @id @default(uuid())
  chatRoom   ChatRoom? @relation(fields: [chatRoomId], references: [id])
  user       User?     @relation(fields: [userId], references: [id])
  userId     String?
  chatRoomId String?
}

 

์œ„ ๋ชจ๋ธ์„ ๋งŒ๋“ค๋ฉด ์•„๋ž˜์™€ ๊ฐ™์€ ์—๋Ÿฌ๊ฐ€ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค.

 

์—๋Ÿฌ ๋‚ด์šฉ์€ ๊ด€๊ณ„๊ฐ€ ์žˆ๋Š”๋ฐ ํ˜„์žฌ ๊ทธ ๋ชจ๋ธ์—” ์ด ๋ชจ๋ธ์ด ์—†์–ด๋ผ๋Š” ๋œป์ž…๋‹ˆ๋‹ค.

 

 

ctrl+s๋ฅผ ํ•˜๋ฉด ์ž๋™์œผ๋กœ ์—๋Ÿฌ๊ฐ€ ๊ณ ์ณ์งˆ ๊ฑฐ์—์š”.

 

์•„๋ž˜์™€ ๊ฐ™์ด ๋‘ ๋ชจ๋ธ์— ChatRoomUser[]๊ฐ€ ์ƒ๊ธฐ๊ฒŒ ๋ฉ๋‹ˆ๋‹ค.

 

model User {
   ...
  ChatRoomUser ChatRoomUser[]
}

model ChatRoom {
	...
  ChatRoomUser    ChatRoomUser[]
}

 

์ €๋Š” ์ดํ•ดํ•˜๊ธฐ ์‰ฝ๊ฒŒ chatRooms์™€ users๋กœ ์ด๋ฆ„์„ ๋ฐ”๊ฟ” ์ฃผ๊ฒ ์Šต๋‹ˆ๋‹ค.

 

model User {
	...
  chatRooms    ChatRoomUser[]
}

model ChatRoom {
	...
  users           ChatRoomUser[]
}

Test

 

์ฑ„ํŒ…๋ฐฉ์— ์ƒˆ๋กœ์šด ์œ ์ €๋ฅผ ์ถ”๊ฐ€ํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

์•„๋ž˜์™€ ๊ฐ™์ด ChatRoom ๋ชจ๋ธ์˜ users๋ฅผ ์ ‘๊ทผํ•ด create๋กœ userId๋ฅผ ๋„ฃ์–ด ์ค๋‹ˆ๋‹ค.

 

      await prisma.chatRoom
        .create({
          data: {
            title: req.body.title,
            hasNewMessage: req.body.hasNewMessage,
            users: { create: { userId: req.body.userId } },
          },
        })
        .then((data) => {
          res.json(data);
          console.log("Insert a chatRoom: ", data);
        });

 

Prisma Studio๋ฅผ ํ†ตํ•ด์„œ ๋ฐ์ดํ„ฐ๊ฐ€ ์ •์ƒ์ ์œผ๋กœ ๋“ค์–ด ๊ฐ”๋Š”์ง€ ํ…Œ์ŠคํŠธ ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

์œ ์ € ๋ฐ์ดํ„ฐ์— chatRooms์—” ์ฑ„ํŒ…๋ฐฉ ๋ฐ์ดํ„ฐ๊ฐ€ ์ถ”๊ฐ€๋˜๊ณ ,

 

 

์ฑ„ํŒ…๋ฐฉ ๋ฐ์ดํ„ฐ์— ์œ ์ € ๋ฐ์ดํ„ฐ๊ฐ€ ์ถ”๊ฐ€๋œ ๊ฒƒ์„ ๋ณผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 


Reference

 

 

Modeling and querying many-to-many relations

Learn how you can model and query implicit and explicit many-to-many relations with Prisma

www.prisma.io

 

 

728x90
๋ฐ˜์‘ํ˜•

๋Œ“๊ธ€