[데브코스] GUI 활용과 API 연동으로 배우는 DB 활용 심화

지금까지는 Terminal 기반 CLI로 MariaDB를 다뤄왔고, 대부분의 작업에 큰 불편은 없었다. 하지만 스키마나 테이블 구조를 한눈에 확인하거나 시각적으로 데이터를 조작하는 데에는 한계가 있었다.

오늘은 GUI 기반 도구인 MySQL Workbench를 활용을 배웠는데, 예상치 못한 오류를 겪어서 다른 해결책도 찾아보게 되었다. Workbench 오류를 대체하기 위한 방안과, 새로 배운 내용들(DB 연동, 리팩토링)을 정리해본다.

---

🐬MySQL GUI

MySQL Workbench

MySQL Workbench는 SQL 개발과 관리, 데이터베이스 설계, 그리고 유지를 위한 단일 개발 통합 환경을 제공하는 비주얼 데이터베이스 설계 도구

MySQL Workbench는 처음 써보는게 아니다. DB 전공 과목을 들을 때, 실습을 위해 MySQL과 Workbench를 사용해보았고, 이게 오늘의 실습에 발목을 잡는 셈이 되었다...
 
이미 Workbench가 설치되어 있어서, 설치 과정은 건너뛰고 바로 Database Connection을 시도했다. Docker Desktop을 실행하고, Hostname(127.0.0.1), Port(3306), Username(root), Password까지 모두 제대로 설정하고 connect를 눌러보았다.
접속 자체에는 문제가 없었지만 접속 후 보이는 화면이 문제였다. 데브코스 실습을 위해 사용하는 MariaDB 상의 테이블이 아니라 MySQL 실습 시 사용했던 테이블들이 보이고 있었다. 내가 접속하려고 했던 DB는 Docker 컨테이너에서 실행되는 mariadb였지만, 어째서인지 로컬로 실습했었던 MySQL 데이터베이스가 보인 것이다.
 
---
(해결방법 #1 정리중)
---

VSCode MySQL Extension

더 간단한 해결방법이 있다. 그냥 다른 GUI를 쓰면 되는 것. 나는 개발 편의성을 위해 IDE 내에서 해결하고 싶어 VSCode의 Extension을 사용해 문제를 해결했다.
 
확장 프로그램 검색창에서 MySQL을 검색했을 때, 두 번째로 나온 Extension을 설치했다.

Extension을 설치한 후, 메뉴 목록에서 Database를 클릭하면 Connect to Server 화면이 나온다. 이 화면에서 호스트, 포트, 유저, 패스워드를 입력하고 connect를 눌렀더니 mariadb에서 생성한 테이블이 정상적으로 표시되는 것을 확인할 수 있었다.

정석적인 방법은 MySQL Workbench인 것 같긴 하지만, 나처럼 창을 여러개 띄우는 것이 부담스럽고 IDE 내에서 한 번에 해결하고 싶다면 Extension 사용도 좋은 선택일 것 같다.

---

🧩테이블 생성

Youtube 스키마를 생성하고, users와 channels 테이블을 생성했다. workbench 에서는 GUI 기반으로, 체크박스 클릭하여 간단하게 테이블 생성이 가능한데 vscode mysql에서는 직접 SQL을 작성해줘야 한다는게 조금 번거로운 것 같기도 하다. MySQL Workbench에서는 GUI에서 row 추가 후 실행하면, SQL 명령어도 나오는데 mysql Extension은 SQL로 Export하는게 유료 플랜으로 제공되고 있기도 하다. 그래도 필요한 기능은 다 제공하고 IDE에서 한번에  해결 가능하다는게 너무 편해서 충분히 만족하고 사용중

CREATE TABLE users(  
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(45) NOT NULL,
    password VARCHAR(20) NOT NULL,
    contact VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sub_num INT NOT NULL DEFAULT 0,
    video_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

---

🗂️DB 연동하여 API 리팩토링하기

우선 mysql2 패키지를 설치해준다

npm install --save mysql2

패키지 설치 후, db 연결을 수행할 스크립트 파일을 생성하고, 아래 코드를 입력해주었다.

const mysql = require("mysql2");

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "Youtube",
});

connection.query("SELECT * FROM `users` ", function (err, results, fields) {
  console.log(results);
  console.log(fields);
});

• 설정한 데이터베이스에 연결
• 데이터베이스 테이블 중 users인 테이블을 받아와, 결과값과 필드(결과에 대한 메타데이터)를 출력한다.

 

Timezone 설정

created_at 컬럼의 시간을 확인해보니, 현재 시간과 맞지 않는 이상한 값이 저장되어 있었다. 이는 MariadDB의 기본 타임존 설정이 UTC로 되어있기 때문이다. 한국 시간(KST, UTC+9)에 맞춰서 값을 저장하거나 조회하려면, time_zone 설정을 따로 해주어야 한다.

SET GLOBAL time_zone = 'Asia/Seoul';
SET time_zone = 'Asia/Seoul';

• Global은 전체 서버 설정을 바꾸는 명령어이고, SESSION은 현재 세션(쿼리 실행 중인 연결)에만 적용된다.

time_zone 설정이 잘 적용되었는지 확인하려면 아래와 같이 쿼리한다.

SELECT @@global.time_zone, @@session.time_zone;

⭐ Asia/seoul로 잘 설정되었다면, 이후부터는 created_at 같은 TIMESTAMP/DATETIME 컬럼에 현재 한국 시간이 저장된다.

 

❌이슈: 기존 저장된 데이터의 시간대는 UTC 기준으로 표시되는 문제

time_zone을 설정했음에도 불구하고, 기존에 저장된 데이터의 시간대는 여전히 이전 설정(UTC 기준)으로 표시되는 문제가 있었다.
강사님 화면에서는 기존 데이터도 정상적으로 KST로 변환되어 보였지만, 내 로컬에서는 이미 삽입된 데이터의 시간은 변하지 않았다.

 

✅원인 분석
MySQL DATETIME 타입은 timezone 정보 없이 문자열로 시간만 저장하는 타입이다. 즉,

• DATETIME 컬럼은 입력된 시각이 그 시점의 시스템 시간 기준으로 그대로 저장된다.
• timezone을 바꿔도 기존에 저장된 DATETIME 값에는 영향을 주지 않는다.
• 바뀌는 건 timezone 설정 변경 이후에 INSERT 되는 데이터 뿐이다.

🤔그런데 강사님 화면에서는 왜 바꼈지??
강사님께서는 created_at 생성 시, TIMESTAMP를 사용해서 생성하셨기에 자동으로 변환되어 보인 것이었다. DATETIME과 다르게, TIMESTAMP는 timezone 영향을 받는 타입이기에 timezone 설정을 바꾸면 이에 맞춰 자동 변환되어 보인다.

나는 DATETIME으로 created_at을 생성했고, 강사님은 TIMESTAMP를 쓰셔서 이런 차이가 발생했던 것!

 

🔨DATETIME으로 저장된 값을 KST로 변환하는 방법
CONVERT_TZ() 함수를 사용한다.

UPDATE users
SET created_at = CONVERT_TZ(created_at, '+00:00', 'Asia/Seoul');

• 쿼리를 실행하면 기존 값들이 Asia/Seoul 시간대로 잘 변환된 것을 확인할 수 있다.

값 자체를 바꾸는 쿼리이므로, 신중하게 사용해야 한다. 기존값이 UTC인지, 그리고 9시간 차이가 나는지 꼭 확인하고 사용해야 한다.

SELECT created_at,
       CONVERT_TZ(created_at, '+00:00', 'Asia/Seoul') AS kst_time
FROM users;

 
🥸실무적 적용
나는 DB에 저장된 값을 완전히 변환했는데, 실제로는 이렇게 사용하지 않는 경우가 많다고 한다.
대부분의 경우, DB에는 UTC에 저장하고, 애플리케이션 단이나 SELECT 쿼리에서만 KST로 변환해서 보여주는 방식을 많이 쓴다고 한다.

• 서버를 여러 대/국가를 운영할 때 혼란이 줄고
• 시간 기준으로 통일해두면 디버깅이 쉽고
• 타임존 정책이 바뀔 때도 유연히 대응 가능해서

타임존 변경에 유연한 대응을 위해서는 DATETIME보다는 TIMESTAMP를 사용하여 create_at 등 시간 데이터를 다루는게 좋을 것 같다.

 
⭐결론: DATETIME vs TIMESTAMP

저장 형태	문자열로 고정 시간 저장	UTC로 저장 + timezone 변환
timezone 설정 영향	없음	있음
추천 용도	고정된 시간 정보가 필요한 경우	생성일, 수정일 등 시스템 시간 저장 시

 

dateStrings 옵션

Node.js에서 DB를 조회할 때, created_at 값에 .000Z가 붙어서 출력된다. 이 값을 지우고 싶다면 Mysql2 모듈 설정에서 dateStrings 옵션을 추가해주면 된다.

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "Youtube",
  dateStrings: true
});

• dateStrings 옵션을 통해 DATETIME이나 TIMESTAMP 값이 문자열 형태로 반환되기 때문에, 파싱없이 바로 사용할 수 있다.

 

DB 모듈화

DB 연결 코드를 별도 모듈로 분리하여 재사용 가능하도록 구성했다.

const mysql = require("mysql2");

const connection = mysql.createConnection({
  host: "localhost", // ECONNREFUSED 방지를 위해 IP로 변경
  user: "root",
  password: "root",
  database: "Youtube",
  dateStrings: true,
});

module.exports = connection;

이제 API 에서는 require('./mariadb')로 쉽게 DB 커넥션을 사용할 수 있다.

 

❌오류: Error socket hang up

API를 테스트 하는 도중, socket hang up 에러가 발생하였다.

conn.query(
     `SELECT * FROM users Where email = ${email}`,
    function(err, results, fields) {
        res.status(200).json(results)
    }
)

위의 SELECT 구문 때문에 발생하는 문제였다.
 

템플릿 리터럴 값을 직접 주입하면, SQL Injection 가능성이 생기고, 값이 null, string 등의 형식일 때 쿼리 파싱 오류가 발생하여 서버가 강제로 종료된다.

`SELECT * FROM users WHERE email = ?`, email,

prepared statement 방식으로 수정하여, 안전하게 처리할 수 있다.
 

❌오류: connect ECONNREFUSED 127.0.0.1:7777

이는 MySQL이 127.0.0.1 기준으로만 포트 연결을 받도록 설정되어 있는데, Node.js에서는 localhost가 내부적으로 IPv6(::1)로 해석되면서 충돌이 발생하여 생기는 오류이다. host를 명확한 IP로 지정하면 해결할 수 있다.

---

🚩리팩토링 결과

기존에는 Map 객체에 데이터를 저장했지만, 이제는 MariaDB와 연동하여 실제 DB에 사용자 정보를 저장하도록 리팩토링했다.
(코드 일부만 첨부)

//회원가입
router.post("/signup", function (req, res) {
  if (req.body !== undefined && req.body.email) {
    const { email, password, name, contact } = req.body;

    // 이미 존재하는 이메일인지 확인
    const checkQuery = "SELECT * FROM users WHERE email = ?";
    conn.query(checkQuery, [email], (err, results) => {
      if (err) {
        console.error("회원가입 중복 확인 오류:", err);
        return res.status(500).json({ message: "서버 오류가 발생했습니다." });
      }

      if (results.length > 0) {
        return res.status(409).json({ message: "이미 존재하는 이메일입니다." });
      }

      // 새로운 사용자 추가
      const insertQuery =
        "INSERT INTO users (email, password, name, contact) VALUES (?, ?, ?, ?)";
      conn.query(
        insertQuery,
        [email, password, name, contact || null],
        (err, result) => {
          if (err) {
            console.error("회원가입 오류:", err);
            return res
              .status(500)
              .json({ message: "서버 오류가 발생했습니다." });
          }

          res.status(201).json({
            message: `${name}님 회원가입을 환영합니다.`,
          });
        }
      );
    });
  } else {
    res.status(400).json({ message: "요청값을 제대로 보내주세요" });
  }
});

리팩토링 후 Postman으로 API 테스트를 진행한 결과,

• users 테이블에 정상적으로 데이터 삽입
• 중복 이메일은 409 conflict로 응답
• 에러 메시지도 명확하게 분리되어, 원인을 파악하기 편해졌다.

---

GUI를 활용해보니 생각보다 훨씬 편리했다. Workbench가 자꾸 오류가 나서 CLI를 계속 사용해야 하나 고민했었는데, 확장 프로그램을 통해 GUI를 사용해보니 훨씬 직관적이고 쿼리 작성도 수월했다. datetime과 timestamp의 차이를 알게 된 것도 큰 수확이었다. timezone 설정을 실습하던 중 마주친 오류 때문에 처음엔 당황했지만, 원인을 찾고 해결하는 과정을 통해 개념을 명확히 이해할 수 있어서 오히려 좋은 경험이었던 것 같다. 본격적으로 DB와 연동한 API를 만들어보면서 백엔드 개발에 한 걸음 더 다가가는 기분이 들어 매우 뿌듯함