[Node.js] MySQL과 연동하여 서버 및 DB 구축하기

코드스테이츠의 과제로 Node.js와 MySQL을 통해 쇼핑몰의 서버와 데이터베이스를 구축했다.

진행 과정은 다음과 같다.

 

1. Node.js 환경에서 MySQL을 통한 데이터베이스 연결하기

 

2. 데이터베이스를 생성하고, 배치 모드를 통해 테이블과 레코드 입력

 

3. items 모델 구현 : Items 테이블의 아이템 리스트를 모두 조회하기 위한 Get 요청

 

4. orders 모델 구현 : orders & order_items 테이블의 주문 내역을 조회(Get)하고, 새로운 주문을 추가(Post)

 

5. MVC 모델의 컨트롤러와 클라이언트쪽은 이미 구현 완료

 

6. API 문서 요약

- GET의 Request : GET /users/:userId/orders

- GET의 Response : orders 테이블의 id, created_at, total price, name, price, image, order_quantity

- POST의 Request : POST /users/:userId/orders/new

- POST의 Parameter : orders (배열 : 주문 아이템), totalPrice(숫자 : 주문 합계)

 

스키마 모식도

 

---

Node.js 환경에서 MySQL을 통해 데이터베이스 연결하기

1. MySQL 로그인 정보 입력 : 정보를 .env 파일에 담고, 그것을 조회하는 코드를 config.js 파일에 담기

 

2. 공식문서에 따라 Node.js와 MySQL을 연결하는 코드를 db/index.js에 담기

  - npm 공식문서 : https://www.npmjs.com/package/mysql#establishing-connections

  - w3schools 자료 : https://www.w3schools.com/nodejs/nodejs_mysql.asp

 

코드 작성

// .env 파일에 MySQL의 로그인 정보 담기

DATABASE_SPRINT_PASSWORD=yourpassword

 

// 보안을 위해서 .gitignore 파일에 .env 입력하기

node_modules
.env

 

// config.js 파일에 환경변수를 조회하기 위한 코드 작성

const dotenv = require('dotenv');

dotenv.config();

const config = {
  development: {
    host: 'localhost',
    user: 'root',
    password: process.env.DATABASE_SPRINT_PASSWORD,
    database: 'cmarket'
  },
  test: {
    host: 'localhost',
    user: 'root',
    password: process.env.DATABASE_SPRINT_PASSWORD,
    database: 'cmarket_test'
  }
};

module.exports = config;

 

// db 폴더에서 Node.js와 MySQL을 연결하기 위한 코드 작성

const mysql = require('mysql');
const dotenv = require('dotenv');
const config = require('../config/config');
dotenv.config();

const con = mysql.createConnection(
  config[process.env.NODE_ENV || 'development']
);

con.connect((err) => {
  if (err) throw err;
});

module.exports = con;

 

---

배치 모드를 통한 테이블 생성 및 레코드 입력

1. cmarket 이라는 데이터베이스 생성하기

2. schema.sql 파일에 테이블 정보를 입력하고, seed.sql 파일에 레코드 정보를 입력하기

3. schema와 sql 파일에 정리된 명령어를 배치모드로 MySQL 서버에 한번에 적용하기

  - 배치(batch) : 일괄적으로 작업을 처리

  - 공식 문서 : https://dev.mysql.com/doc/refman/8.0/en/batch-mode.html

 

코드 작성

# cmarket 데이터베이스를 생성하기
CREATE DATABASE cmarket;

 

// schema.sql 파일에 테이블 생성 명령어를 작성하기

CREATE TABLE users (
  id INT AUTO_INCREMENT,
  username varchar(255),
  PRIMARY KEY (id)
);

CREATE TABLE items (
  id INT AUTO_INCREMENT,
  name varchar(255),
  price INT,
  image varchar(255),
  PRIMARY KEY (id)
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT,
  user_id INT,
  total_price INT,
  created_at datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE order_items (
  id INT AUTO_INCREMENT,
  order_id INT,
  item_id INT,
  order_quantity INT,
  PRIMARY KEY (id)
);

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users (id);

ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders (id);

ALTER TABLE order_items ADD FOREIGN KEY (item_id) REFERENCES items (id);

/*  Execute this file from the command line by typing:
 *    mysql -u root < server/schema.sql -p -Dcmarket
 *  to create the database and the tables.*/

 

// seed.sql 파일에 레코드를 입력하기 위한 명령어를 작성

INSERT INTO items (name, price, image) VALUES 
("노른자 분리기", 9900, "../images/egg.png"), 
("2020년 달력", 12000, "../images/2020.jpg"), 
("개구리 안대", 2900, "../images/frog.jpg"), 
("뜯어온 보도블럭", 4900, "../images/block.jpg"), 
("칼라 립스틱", 2900, "../images/lip.jpg"), 
("잉어 슈즈", 3900, "../images/fish.jpg"), 
("웰컴 매트", 6900, "../images/welcome.jpg"), 
("강시 모자", 9900, "../images/hat.jpg");

INSERT INTO users (username) VALUES ("김코딩")

/*  Execute this file from the command line by typing:
 *    mysql -u root < server/seed.sql -p -Dcmarket
 *  to create the database and the tables.*/

 

# 레포지토리에서 아래 명령어 입력하기

# schema.sql 의 명령어로 테이블을 생성하기
mysql -u root -p < server/schema.sql -Dcmarket

# seed.sql 의 명령어로 레코드 입력하기
mysql -u root -p < server/seed.sql -Dcmarket

 

---

아이템 리스트를 모두 조회하는 모델 구현

1. 공식 문서 활용 : https://www.npmjs.com/package/mysql#establishing-connections

2. db 폴더에서 만든 함수를 불러와 사용하기

3. items 테이블의 정보를 모두 조회해야 함 (GET 요청) 

 

코드 작성

* callback 함수를 익명함수 안에서 실행하는 이유는?

  - 이미 구현된 controller 함수와 형식을 맞추기 위해서

  - sql 쿼리문을 전달한 이후에 콜백함수를 실행하기 위해서 비동기 처리

 

// items를 조회하는 모델 구현 
  
  items: {
    get: (callback) => {
      const queryString = `SELECT * FROM items`;

      db.query(queryString, (error, result) => {
        callback(error, result);
      });
    },
  },

 

---

 주문 정보 조회(Get)를 위한 모델 구현

1. GET Response(API) : orders 테이블의 id, created_at, total price, name, price, image, order_quantity

 

2. orders 테이블의 id, created_at, total_price 조회하기

3. order_items 테이블의 order_quantity 조회하기

4. items 테이블의 name, price, image 조회하기

 

5. 조건 : user의 id가 전달인자(userId)인 경우

  - 조건에 맞는 레코드를 찾으려면 user 테이블의 id를 조회해야 함  

  - 다행히 orders 테이블의 user_id를 참조가능 (외래키)

 

6. JOIN으로 연결하기 : orders, order_items, items

 

코드 작성

- Templete literal 사용

get: (userId, callback) => {

      let sql = `SELECT orders.id, orders.created_at, orders.total_price, 
      items.name, items.price, items.image, order_items.order_quantity 
      FROM orders 
      INNER JOIN order_items ON orders.id=order_items.order_id
      INNER JOIN items ON order_items.item_id=items.id
      WHERE orders.user_id=${userId}`

      db.query(sql, (error, result) => {
        callback(error, result);
      });
    },

 

- params 전달인자 사용 (보안 이유 등으로 권장됨)

get: (userId, callback) => {
      const queryString = `SELECT orders.id, orders.created_at, 
      orders.total_price, items.name, items.price, items.image, 
      order_items.order_quantity FROM items
      INNER JOIN order_items ON (order_items.item_id = items.id)
      INNER JOIN orders ON (orders.id = order_items.order_id)
      WHERE (orders.user_id = ?)`;  // params 배열의 엘리먼트 개수만큼 ? 입력

      const params = [userId];

      db.query(queryString, params, (error, result) => {
        callback(error, result);
      });
    },

 

---

주문 정보 입력(Post)을 위한 모델 구현

1. 먼저 orders 테이블에 user_id, total_price, created_at 레코드 입력을 위한 쿼리문 입력

2. 이후 order_items 테이블에 order_id, item_id, order_quantity 레코드 입력을 위한 쿼리문 입력

3. orders 테이블의 레코드를 먼저 채우는 이유

  - orders 테이블의 id값을 먼저 채워야지 order_items 테이블에서 order_id를 외래키로 가져올 수 있음

  - 첫번째 쿼리문의 콜백함수 안에 두번째 쿼리문을 넣어주기 때문에, order_id는 result.insertId로 가져오기

  - 참고 링크 : https://www.w3schools.com/nodejs/nodejs_mysql_insert.asp

 

코드 작성

- 순차적으로 콜백함수 안에 콜백함수 넣어주기 

 

post: (userId, orders, totalPrice, callback) => {
     
      // orders 테이블 user_id와 total_price를 입력하는 쿼리문
      const queryString = `INSERT INTO orders (user_id, total_price) 
                          VALUES (?, ?)`; 
                          // params의 엘리먼트가 2개이므로 '?'를 2개 입력
                          
      const params = [userId, totalPrice];

      
      db.query(queryString, params, (error, result) => {
        if (result) {
        
          // order_items 테이블에 order_id, item_id, order_quantity 입력 쿼리문
          const queryString = `INSERT INTO order_items 
                              (order_id, item_id, order_quantity) 
                              VALUES ?`;
          
          // order_items테이블에 넣을 값을 params에 이중배열로 할당
          const params = orders.map((order) => [
            result.insertId,
            order.itemId,
            order.quantity,
          ]);

          // 콜백함수 내에 콜백함수를 전달했으며 return은 생략가능
          // 쿼리문의 '?'가 1개이므로 배열의 엘리먼트를 1개로 만들기 위해 [params] 전달
          return db.query(queryString, [params], (error, result) => {
            callback(error, result);
          });
        }
        callback(error, null);
      });
    },
  },