[5주차 - DAY5] Workbench 사용 및 DB 연동

 

 

DB 커넥션 생성

아래 화면에서 플러스 버튼을 누른다.

Connection Name을 입력하고 Test Connection 클릭해서 커넥션이 생성될 수 있는지 확인한다. 경고창은 무시하고 계속 진행하면 되고, OK 버튼을 누른다.

Hostname이 기본적으로 127.0.0.1로 설정되어 있는데, 127.0.0.1로 하니까 에러가 나서 localhost로 해주었더니 정상적으로 잘 동작했다. 127.0.0.1로 해보고 안되면 localhost로 하면 된다.

 

 

스키마(데이터베이스) 생성

생성한 커넥션을 클릭해서 들어오면 아래와 같은 화면을 볼 수 있다. 왼쪽 SCHEMAS의 빈 공간을 우클릭해서 Create Schema를 클릭한다.

Name에 원하는 스키마 이름을 입력하고, Apply 버튼을 누른다.

그러면 아래와 같은 창이 뜨는데, 여기서 우리가 클릭한 버튼에 대한 코드를 보여준다. 꼭 확인해야하며 원하는 동작이 맞다면 Apply를 누르면 된다.

 

 

테이블 생성

생성한 스키마를 더블 클릭한다. USE DB이름; 의 명령어를 실행해주는 역할을 한다.

Tables를 우클릭하여 Create Table을 클릭하면 테이블을 생성할 수 있게 해주는 창이 뜬다.

생성해야 하는 테이블

 

사용자 테이블 생성

PK	기본키
NN	Null 값을 가질 수 없는가
UQ	유니크한 값인가
B	이진 문자열로 저장할 것인가
UN	정수의 유형으로 부호가 없는가
ZF	0으로 채울것인가 ex) INT(5)에 데이터가 5일 경우 00005로 저장
AI	자동 증가시킬 것인가(정수의 경우)
G	다른 열을 기반으로 수식에 의해 생성된 값인가

Apply 버튼을 누르면 아래와 같이 코드를 보여준다.

잘못된 것이 없는지 확인하고 Apply를 누른다.

정상적으로 적용되었으면 테이블이 생성된 것을 확인할 수 있다.

 

채널 테이블 생성

user_id는 외래키다. 외래키는 아래의 Foreign Keys 탭에서 설정한다.

왼쪽 상자에 있는 Foreign Key Name은 외래키의 컬럼명을 말하는 게 아니라 생성할 외래키의 별칭을 의미한다. 외래키도 제약 조건이기 때문에 제약 조건의 이름을 설정한다고 봐도 무방하다. Referenced Table은 참조당하고 있는 테이블을 의미하며 채널 테이블의 user_id 칼럼이 사용자 테이블의 id 칼럼을 참조하고 있으므로 Referenced Table은 Youtube 스키마 안에 있는 사용자 테이블인 users이다.

오른쪽 상자에서 외래키를 설정한다. 채널 테이블의 외래키는 user_id이고, 참조당하고 있는 컬럼은 사용자 테이블의 id 칼럼이다.

Apply를 누르면 아래와 같이 테이블을 생성하는 코드를 확인시켜 준다.

CONSTRAINT 옆에 쓰인 user_id는 앞서 말한 외래키의 별칭이자 제약 조건 이름이다. 칼럼명을 말하는 게 아니다.

잘못 작성된 부분이 없는 것을 확인하고 Apply를 누른다.

 

 

데이터 삽입

원하는 테이블을 우클릭해서 Select Rows - limit 1000을 클릭하거나 아래에 표시한 버튼을 클릭하면 삽입되어 있는 데이터를 볼 수 있는 창이 열린다.

 

사용자 테이블 데이터 삽입

아래와 같이 데이터를 삽입하고 Apply 버튼을 누른다.

제대로 작성된 코드가 맞는지 확인하고 Apply를 누른다.

 

id를 AUTO_INCREMENT로 설정했기 때문에 아래와 같이 id에 아무것도 적지 않고 apply를 하면 프로그램이 알아서 id 값을 채워준다.

 

시스템은 마지막으로 저장된 id값을 기준으로 값을 자동으로 1 증가시킨다. 따라서 아래와 같이 id를 4로 넣고 데이터를 생성하고, 다음 데이터의 id 값을 비워서 삽입하면 5로 채운다.

빈 id 값을 채워주는 게 아니기 때문에 id를 내가 만들어서 넣을 거면 처음부터 끝까지 내가 만들어서 넣어야 하고, auto increment를 쓴다면 가능하면 데이터베이스에 맡기는 게 좋다.

 

채널 테이블 데이터 삽입

사용자 테이블에 존재하지 않는 id에 대해 채널을 생성해 보자. 아래와 같이 작성하고 Apply를 누른다.

아래와 같이 외래키 제약 조건에 위배돼서 SQL 명령을 실행하지 못했다는 에러 메시지를 볼 수 있다.

 

이제 존재하는 사용자 id에 대해 채널을 생성해보자.

id를 비운채로 다른 컬럼들을 채우고 Apply를 누르면 아래와 같이 id가 2로 채워진다.

auto increment는 삽입에 실패해도 값이 증가하기 때문에 id가 1이 아닌 2로 설정되었다.

 

구독자 수와 비디오 수에 default 값이 제대로 들어가는지 확인해 보자. 아래와 같이 구독자 수와 비디오 수는 비운채로 Apply를 누른다.

구독자 수와 비디오 수가 0으로 설정된 것을 확인할 수 있다.

.

 

 

컬럼 추가

사용자 테이블에 계정 생성일자를 나타내는 컬럼을 추가해보자. 아래 버튼을 누르면 테이블을 생성할 때와 같은 창이 뜬다.

 

아래와 같이 컬럼을 추가하고 Apply 버튼을 누른다.

created_at 컬럼이 생성된 것을 확인할 수 있다.

 

 

Time Zone

TIMESTAMP 자료형은 문자열로 값이 고정되어 들어가 있는 게 아니라 UTC를 기준 값으로 저장하고 사용자의 time_zone에 따라 바뀐 값을 보여준다. 예를 들자면 우리나라는 UTC+9이기 때문에 데이터베이스에는 1시라고 저장되어 있어도 우리에게는 10시라고 보여준다.

워크벤치에서도 우리나라 시간으로 보이도록 설정을 바꿔보자. 첫 줄의 명령어를 실행해도 바뀌지 않는다면 그건 세션에는 아직 반영되지 않은 상태이기 때문이다. 그럴 경우에는 두 번째 줄의 명령어도 실행하면 시간이 바뀐 것을 확인할 수 있다.

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

아래 명령어로 설정 값을 볼 수 있다.

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

 

 

Node.js와 DB 연동

vscode에서 mysql을 쓸 수 있도록 해보자.

도커가 실행되고 있어야 연동할 수 있으므로 도커가 실행되고 있는지 확인한다.

아래의 명령어를 터미널에서 실행한다.

npm i mysql2

 

먼저 설치한 모듈을 불러오고, DB와 연결을 생성한다.

DB에 커넥션을 생성하는 부분에 사용할 DB와 비밀번호를 적어줘야 한다.

dateStrings는 날짜/시간 값이 현재 세팅되어 있는 time_zone 값을 반영하여 보여주도록 하는 설정이다. 설정하지 않으면 raw 값으로 나온다.

// Get the client
const mysql = require('mysql2');

// Create the connection to database
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'Youtube',
    password: 'root',
    dateStrings: true 
});

 

이제 DB에 저장되어 있는 데이터를 조회해 보자.

콜백 함수에는 쿼리를 실행한 결과에 대해 취할 행동을 적어준다.

results는 쿼리를 실행해서 받아온 결과 값들을 배열로 가지고 있다. 조회하는 쿼리를 작성했기 때문에 결과 값으로 여러 행들을 받아왔고, 그 행들을 하나씩 출력해 주는 코드이다.

// A simple SELECT query
connection.query(
    'SELECT * FROM `users`',
    function (err, results, fields) {
        results.forEach((row) => {
            const { id, email, name, created_at } = row;
            console.log(id);
            console.log(email);
            console.log(name);
            console.log(created_at);
            console.log('\n');
        })
    }
);

 

fields는 쿼리 실행 결과에 대한 메타 데이터를 보여준다.

fields 출력 결과