[7주차] - [9기] 타입스크립트로 함께하는 웹 풀 사이클 개발(React, Node.js) 10회차 - 32

장바구니 API 구현

import conn from "../db/mysql_connect.js";
import { StatusCodes } from "http-status-codes";

export const addCart = (req, res) => {
  const { book_id, quantity, user_id } = req.body;

  const sql = "INSERT INTO cart (book_id, quantity, user_id) VALUES (?, ?, ?)";
  const values = [book_id, quantity, user_id];

  conn.query(sql, values, (err, results) => {
    if (err) {
      console.error("장바구니 담기 DB 에러:", err);
      return res.status(StatusCodes.INTERNAL_SERVER_ERROR).json(err);
    }
    return res.status(StatusCodes.CREATED).json(results);
  });
};

export const getCartItems = (req, res) => {
  const { user_id, selected } = req.body;

  let sql = `
    SELECT 
        cart.cart_id, 
        cart.book_id, 
        books.title, 
        books.summary, 
        cart.quantity, 
        books.price 
    FROM cart 
    LEFT JOIN books ON cart.book_id = books.book_id
    WHERE cart.user_id = ?`;

  let values = [user_id];
  if (selected && selected.length > 0) {
    sql += ` AND cart.cart_id IN (?)`;
    values.push(selected);
  }

  conn.query(sql, values, (err, results) => {
    if (err) {
      console.error("장바구니 조회 DB 에러:", err);
      return res.status(StatusCodes.INTERNAL_SERVER_ERROR).json(err);
    }
    return res.status(StatusCodes.OK).json(results);
  });
};

export const deleteCartItem = (req, res) => {
  const { cart_id } = req.params;

  const sql = "DELETE FROM cart WHERE cart_id = ?";
  const values = [cart_id];

  conn.query(sql, values, (err, results) => {
    if (err) {
      console.error("장바구니 삭제 DB 에러:", err);
      return res.status(StatusCodes.INTERNAL_SERVER_ERROR).end();
    }
    return res.status(StatusCodes.OK).json({
      message: "장바구니 삭제 성공",
      results,
    });
  });
};

 

Cart 테이블 생성 및 데이터 추가

-- 유저 테이블 생성 --
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(100) NOT NULL,
  name VARCHAR(45) NOT NULL,
  password VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
);

-- 도서 테이블 생성 --
CREATE TABLE `books` (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(45) NOT NULL,
  `format` VARCHAR(45) NOT NULL,
  isbn VARCHAR(45) NOT NULL,
  summary VARCHAR(500) NULL,
  `description` LONGTEXT NULL,
  author VARCHAR(45) NOT NULL,
  pages INT NOT NULL,
  `index` LONGTEXT NULL,
  price INT NOT NULL,
  pub_date DATE NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `isbn_UNIQUE` (`isbn` ASC)
);

-- 카테고리 테이블 생성 --
CREATE TABLE category (
  id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY(id)
);

-- 좋아요 테이블 생성 --
CREATE TABLE likes (
  user_id INT NOT NULL,
  liked_book_id INT NOT NULL,

  INDEX user_id_idx (`user_id` ASC),
  INDEX liked_book_id_idx (`liked_book_id` ASC),

  CONSTRAINT fk_likes_user
    FOREIGN KEY (`user_id`)
    REFERENCES users (`user_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,

  CONSTRAINT `fk_likes_book`
    FOREIGN KEY (`liked_book_id`)
    REFERENCES books (`book_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
)

-- 장바구니 테이블 생성 --
CREATE TABLE cart (
    cart_id INT NOT NULL AUTO_INCREMENT,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (cart_id),
    
    INDEX book_id_idx (book_id ASC),
    INDEX user_id_idx (user_id ASC),

    CONSTRAINT fk_cart_book 
      FOREIGN KEY (book_id) 
      REFERENCES books (book_id) 
      ON DELETE CASCADE,
      
    CONSTRAINT fk_cart_user 
      FOREIGN KEY (user_id) 
      REFERENCES users (user_id) 
      ON DELETE CASCADE
);