Node.js 숙련주차 3.7 나만의 게시판 사이트 설계 - DB 테이블 만들기

테이블간의 관계 및 요구사항

게시판 프로젝트 테이블 관계 및 요구사항 정리
사용자(Users)는 1개의 사용자 정보(UserInfo)를 가지고 있어요.
   - 그렇기 때문에 Users 테이블과 UserInfo 테이블은 1:1 관계를 가지고 있어요
사용자(Users)는 여러개의 게시글(Posts)을 등록할 수 있어요.
   - 그렇기 때문에 Users 테이블과 Posts 테이블을 1:N 관계를 가지고 있어요
사용자(Users)는 여러개의 댓글(Comments)을 작성할 수 있어요.
   - 그렇기 때문에 Users 테이블과 Comments 테이블은 1:N 관계를 가지고 있어요
하나의 게시글(Posts)은 여러개의 댓글(Comments)이 작성될 수 있어요.
   - 그렇기 때문에 Posts 테이블과 Comments 테이블은 1:N 관계를 가지고 있어요

 

sql

CREATE TABLE Users
(
    userId    INTEGER             NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email     VARCHAR(191) UNIQUE NOT NULL,
    password  VARCHAR(191)        NOT NULL,
    createdAt DATETIME(3)         NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updatedAt DATETIME(3)         NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);


CREATE TABLE UserInfos
(
    userInfoId   INTEGER        NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId       INTEGER UNIQUE NOT NULL, -- 1:1 관계 이므로 UNIQUE 조건을 삽입합니다.
    name         VARCHAR(191)   NOT NULL,
    age          INTEGER        NOT NULL,
    gender       VARCHAR(191)   NOT NULL,
    profileImage VARCHAR(191)   NULL,
    createdAt    DATETIME(3)
                                NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updatedAt    DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

ALTER TABLE UserInfos
    ADD CONSTRAINT FK_UserInfos_Users
        FOREIGN KEY (userId) REFERENCES Users (userId) ON DELETE CASCADE;

CREATE TABLE Posts
(
    postId    INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId    INTEGER      NOT NULL,
    title     VARCHAR(191) NOT NULL,
    content   VARCHAR(191) NOT NULL,
    createdAt DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updatedAt DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

ALTER TABLE Posts
    ADD CONSTRAINT FK_Posts_Users
        FOREIGN KEY (userId) REFERENCES Users (userId) ON DELETE CASCADE;


CREATE TABLE Comments
(
    commentId INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId    INTEGER      NOT NULL,
    postId    INTEGER      NOT NULL,
    content   VARCHAR(191) NOT NULL,
    createdAt DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updatedAt DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

ALTER TABLE Comments
    ADD CONSTRAINT FK_Comments_Posts
        FOREIGN KEY (postId) REFERENCES Posts (postId) ON DELETE CASCADE;

ALTER TABLE Comments
    ADD CONSTRAINT FK_Comments_Users
        FOREIGN KEY (userId) REFERENCES Users (userId) ON DELETE CASCADE;

 

테이블 만들기

 Prisma 설계하기

 

폴더 만들고 라이브러리 설치하기

[게시판 프로젝트]는 src 폴더 하위에 프로젝트 파일을 위치시킬 예정입니다. 
src/app.js 파일을 생성한 이후, 아래의 코드스니펫으로 프로젝트를 초기화해주세요

bash 

# 프로젝트를 초기화합니다.
yarn init -y
#생성된 package.json 파일에서 ES6 문법을 사용하기 위해 "type":"module" 추가

# 라이브러리를 설치합니다.
yarn add express prisma @prisma/client cookie-parser jsonwebtoken

# nodemon 라이브러리를 DevDependency로 설치합니다.
yarn add -D nodemon

# 설치한 Prisma를 초기화 하여, Prisma를 사용할 수 있는 구조를 생성합니다.
npx prisma init

2. 'src' 폴더를 만들고 'app.js' 파일 생성

 

3. /prisma/ schema.prisma에 db provider을 "mysql"로 바꾸고 테이블을 만듧니다.

 

코드

// prisma/schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

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

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

model Users {
  userId    Int      @id @default(autoincrement()) @map("userId")
  email     String   @unique @map("email")
  password  String   @map("password")
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  @@map("Users")
}

//  @db.Text : string 타입이지만 더 많은 데이터를 가질 수 있음
// @map : 해당 데이터를 db에서도 동일하게 사용하기 위함
model Posts {
  postId    Int      @id @default(autoincrement()) @map("postId")
  title     String   @map("title")
  content   String   @map("content") @db.Text
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  @@map("Posts")
}

// Int? : 타입 뒤에 ? 붙이면 입력받지 않아도 된다는 뜻.
model UserInfos {
  userInfoId   Int      @id @default(autoincrement()) @map("userInfoId")
  name         String   @map("name")
  age          Int?     @map("age")
  gender       String   @map("gender")
  profileImage String?  @map("profileImage")
  createdAt    DateTime @default(now()) @map("createdAt")
  updatedAt    DateTime @updatedAt @map("updatedAt")

  @@map("UserInfos")
}

model Comments {
  commentId Int      @id @default(autoincrement()) @map("commentId")
  content   String   @map("content")
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  @@map("Comments")
}

// annotation의 map을 이용해서 DB에소도 해당 컬럼을 사용할 수 있게 합니다.

// typscript나 java나 python 등에서 @를 코드 위에 작성하는 경우가 있습니다.
// 이런식으로 특정한 것 위에다 @를 쓰는 것을 annotation이나 decorator라고 부릅니다.

 

테이블 간의 관계

 

1 : 1

 

관계를 설정하려는 모델(UserInfos)에서 어떤 모델과 관계를 맺을지(Users) 설정해야합니다.

       user Users @relation(fields: [userId], references: [userId], onDelete: Cascade)
관계를 맺게되는 모델(Users)에서 어떤 모델이 관계를 맺는지(UserInfos) 설정해야합니다. (1명에게만 종속)

     userId Int @unique @map("userId")
관계를 맺게되는 모델(Users)에서, 타입을 지정할 때, Optional Parameter(?)를 지정해줘야합니다.

    userInfos UserInfos?

사용자는 사용자 정보가 존재하지 않을 수 있기 때문입니다.(유저 생성 후 info 생성 전) 

// schema.prisma 

model Users {
  userId    Int      @id @default(autoincrement()) @map("userId")
  email     String   @unique @map("email")
  password  String   @map("password")
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  userInfos UserInfos? // 사용자(Users) 테이블과 사용자 정보(UserInfos) 테이블이 1:1 관계를 맺습니다.

  @@map("Users")
}


model UserInfos {
  userInfoId   Int      @id @default(autoincrement()) @map("userInfoId")
  userId       Int      @unique @map("userId") // 사용자(Users) 테이블을 참조하는 외래키
  name         String   @map("name")
  age          Int?     @map("age")
  gender       String   @map("gender")
  profileImage String?  @map("profileImage")
  createdAt    DateTime @default(now()) @map("createdAt")
  updatedAt    DateTime @updatedAt @map("updatedAt")

  // Users 테이블과 관계를 설정합니다.
  user Users @relation(fields: [userId], references: [userId], onDelete: Cascade)

  @@map("UserInfos")
}

Users
    일반적인 Int, String과 같은 타입이 아닌, 참조할 다른 모델을 지정합니다.
    사용자(Users) 모델을 참조하므로 Users로 작성되어있습니다.

fields
    사용자 정보(UserInfos) 모델에서 사용할 외래키(Forien Key) 컬럼을 지정합니다.
    여기선, userId 컬럼으로 외래키를 지정하였습니다.
references
    key: 참조하는 다른 모델의 Column를 지정합니다.
    여기선, 사용자(Users) 모델의 userId 컬럼을 참조합니다.
onDelete | onUpdate
   참조하는 모델이 삭제 or 수정될 경우 어떤 행위를 할 지 설정합니다.
   Cascade 옵션을 선택하여 사용자가 삭제될 경우 그에 연결된 사용자 정보도 함께 삭제되도록 설정하였습니다.

 

1: N

// schema.prisma

model Users {
  userId    Int      @id @default(autoincrement()) @map("userId")
  email     String   @unique @map("email")
  password  String   @map("password")
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  userInfos UserInfos? // 사용자(Users) 테이블과 사용자 정보(UserInfos) 테이블이 1:1 관계를 맺습니다.
  posts     Posts[] // 사용자(Users) 테이블과 게시글(Posts) 테이블이 1:N 관계를 맺습니다.

  @@map("Users")
}

model Posts {
  postId    Int      @id @default(autoincrement()) @map("postId")
  userId    Int      @map("userId") // 사용자(Users) 테이블을 참조하는 외래키
  title     String   @map("title")
  content   String   @map("content") @db.Text
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  // Users 테이블과 관계를 설정합니다.
  user     Users     @relation(fields: [userId], references: [userId], onDelete: Cascade)

  @@map("Posts")
}

 

관계를 맺게되는 모델(Users)에서, 타입을 지정할 때, 배열 연산자([])를 작성해줘야합니다.
사용자는, 여러개의 게시글을 가질 수 있습니다.

탈퇴할 경우

// Users 테이블과 관계를 설정합니다.
user     Users     @relation(fields: [userId], references: [userId], onDelete: Cascade)

현재 게시글 모델의 경우 작성한 사용자가 회원 탈퇴(onDelete)하게 될 경우 작성한 모든 게시글이 삭제되도록 구현되어 있습니다. 이런 설정은 @relation 어노테이션을 사용하여 지정합니다.​
여기서 User는 게시글(Posts)이 참조하는 다른 모델을 지정하고, fields는 게시글(Posts) 모델에서 사용할 외래키 컬럼을 지정합니다. references는 참조하는 다른 모델의 컬럼을 지정하고, onDelete는 잠조하는 모델이 삭제될 경우 어떤 행위를 할 지 설정합니다.
onDelete의 경우, Cascade 옵션으로 사용자가 삭제될 경우 연관된 게시글 또한 삭제되도록 설정하였습니다.

 

 

제공하는 테이블

// id주인 테이블
컬럼명 테이블이름 // 1:1 관계
컬럼명 테이블이름[] // 1:n관계

 

외래키로 받아오는 테이블 ( 관계를 주구절절 적기)

// 외래키로 다른 테이블의 Id 받아오는 테이블
컬럼명 Id주인테이블명a @relation(fields: [a의 Id컬럼명], references: [a의 Id컬럼명], onDelete: Cascade)

onDelete: ___     :    연결된 데이터가 삭제된 경우 처리

 

코드

// schema.prisma
//# 해당 프로젝트에 schema.prisma에 정의된 테이블을 MySQL에 생성합니다.
// npx prisma db push

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

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

model Users {
  userId    Int      @id @default(autoincrement()) @map("userId")
  email     String   @unique @map("email")
  password  String   @map("password")
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  // 데이터타입?  :  기록하지 않을 수 있는 데이터. 
  // 여기서 데이터를 추가한 이후에 생기는 데이터의 경우 '?'를 붙여 비어있어도 에러가 발생하지 않게 합니다.
  userInfos UserInfos? // 사용자(Users) 테이블과 사용자 정보(UserInfos) 테이블이 1:1 관계를 맺습니다.
  posts     Posts[] // 사용자(Users) 테이블과 게시글(Posts) 테이블이 1:N 관계를 맺습니다.
  comments  Comments[] // 사용자(Users) 테이블과 댓글(Comments) 테이블이 1:N 관계를 맺습니다.

  @@map("Users")
}

model Posts {
  postId    Int      @id @default(autoincrement()) @map("postId")
  userId    Int      @map("userId") // 사용자(Users) 테이블을 참조하는 외래키
  title     String   @map("title")
  content   String   @map("content") @db.Text
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  // Users 테이블과 관계를 설정합니다.
  user     Users      @relation(fields: [userId], references: [userId], onDelete: Cascade)
  comments Comments[] // 게시글(Posts) 테이블과 댓글(Comments) 테이블이 1:N 관계를 맺습니다.

  @@map("Posts")
}

model UserInfos {
  userInfoId   Int      @id @default(autoincrement()) @map("userInfoId")
  userId       Int      @unique @map("userId") // 사용자(Users) 테이블을 참조하는 외래키
  name         String   @map("name")
  age          Int?     @map("age")
  gender       String   @map("gender")
  profileImage String?  @map("profileImage")
  createdAt    DateTime @default(now()) @map("createdAt")
  updatedAt    DateTime @updatedAt @map("updatedAt")

  // Users 테이블과 관계를 설정합니다.
  user Users @relation(fields: [userId], references: [userId], onDelete: Cascade)

  @@map("UserInfos")
}

model Comments {
  commentId Int      @id @default(autoincrement()) @map("commentId")
  postId    Int      @map("postId") // 게시글(Posts) 테이블을 참조하는 외래키
  userId    Int      @map("userId") // 사용자(Users) 테이블을 참조하는 외래키
  content   String   @map("content")
  createdAt DateTime @default(now()) @map("createdAt")
  updatedAt DateTime @updatedAt @map("updatedAt")

  // Posts 테이블과 관계를 설정합니다.
  post Posts @relation(fields: [postId], references: [postId], onDelete: Cascade)
  // Users 테이블과 관계를 설정합니다.
  user Users @relation(fields: [userId], references: [userId], onDelete: Cascade)

  @@map("Comments")
}

 

4. Prisma DB, Table 생성하기

 

.env파일을 수정하세요

"DB프로그램://내 DB사용자명:비번@엔드포인트:포트/새로만들 테이블이들어갈 DB이름"

# .env
# DATABASE_URL="mysql://root:aaaa4321@엔드포인트:3306/community_hub"
DATABASE_URL="mysql://root:aaaa4321@express-database.cvyiaeceu603.ap-northeast-2.rds.amazonaws.com:3306/community_hub"

 

터미널에서

# 해당 프로젝트에 schema.prisma에 정의된 테이블을 MySQL에 생성합니다.
npx prisma db push

생성됨

 

여러 제약조건이 있을 때 Uniqe가 우선해서 보입니다.

 

Comments

Posts

 

UserInfos

 

명세서 : https://teamsparta.notion.site/f0d79caca13145abb20b68f96c17d146?v=eb65c164a9b743bc872f3290d1860e37&pvs=4