workbench
MySQL 워크벤치(MySQL Workbench)는 SQL 개발과 관리, 데이터베이스 설계, 생성 그리고 유지를 위한 단일 개발 통합 환경을 제공하는 비주얼 데이터베이스 설계 도구이다.
하이퍼링크를 통해 OS에 맞는걸 설치하면 된다.
연결하기
해당 페이지에서 Database > connect to database(맥이면 commend+U)를 통해 다음 창을 띄워서 이전에 만든 Database와 연결한다.
!하기 전에 docker로 database를 실행해둬야한다!
여기서 connection Name을 채운 다음, port번호와 Username, passwod등을 기호에 맞게 설정하고 ok를 누르면 DB와 연결을 설정할 수 있다.
그러면 다음과 같은 페이지를 볼 수 있다.
만약 이전에 DB를 만들어 뒀다면 왼쪽에서 Schemas를 클릭하면 다음과 같이 만들어둔 DB테이블들을 볼 수 있다.
이후 쿼리를 다음과 같이 작성했지만 오류가 나는 것을 확인할 수 있는데 이는 데이터베이스를 선택하지 않아서 생기는 오류이다.
쿼리를 실행하고자 하는 데이터베이스을 클릭하면 다음과 같이 볼드체가 되면서 선택되었다는걸 확인할 수 있다.
그리고 오른쪽 클릭으로 만들어진 데이터베이스에 만들어진 테이블들을 빠르게 확인할 수 있다.
youtuber DB를 GUI로 만들기
이전에 CLI를 통해서 Board를 만들었으니, 이제 프로젝트에 쓰일 DB를 만들어 보자
유저 테이블 작성 및 데이터 삽입
이제 GUI 화면 사이드탭의 여백에서 오른클릭으로 데이터베이스를 만들고,
해당 데이터베이스에 다음과 같이 테이블을 만들 수 있다.
- id => Primary Key로 설정, int타입을 사용하며 Auto increment를 설정한다.
- email => 추가한 컬럼, varchar(100), Not Null로 설정
- name => varchar(45), Not Null로 설정
- password => varchar(20), Not Null로 설정
- phone => varchar(45)
channels 테이블 생성
- id => Primary Key로 설정, int타입을 사용하며 Auto increment를 설정한다.
- title => varchar(45), Not Null로 설정
- sub_num => INT타입, Defualt값을 0으로 설정
- video_cnt => INT타입, Default값을 0으로 설정
- user_id => Foreign Key로 설정, Users 테이블의 user_id와 연결하는걸 아래와 같이 설정할 수 있다.
두 테이블 모두 만들게 되면 다음과 같이 CLI로 어떻게 입력되는지 보여준다.
zero fill이란?
뭔가 0으로 채운다는 느낌처럼, int타입으로 컬럼을 설정할때INT(11)과 같이 옆에 숫자가 표시되는걸 볼 수 있다.
이때 zero fill기능을 설정하면, 11자리중 남는 숫자를 0으로 채워서 11자리를 맞추게 된다.
ex.) 해당 컬럼자리에1234를 넣게되면 데이터베이스에는00000001234로 표시된다.mysql> SHOW COLUMNS FROM zerofill_test; +-------+---------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------------+------+-----+---------+-------+ | a | int(11) | NO | | NULL | | | b | int(11) unsigned zerofill | NO | | NULL | | | c | int(5) | NO | | NULL | | | d | int(5) unsigned zerofill | NO | | NULL | | +-------+---------------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) --- mysql> SELECT * FROM zerofill_test; +------------+-------------+------------+------------+ | a | b | c | d | +------------+-------------+------------+------------+ | 1 | 00000000001 | 1 | 00001 | | 1234567890 | 01234567890 | 1234567890 | 1234567890 | | 123456 | 00000123456 | 123456 | 123456 | +------------+-------------+------------+------------+ 3 rows in set (0.00 sec)
데이터 삽입 - Users
이제 데이터를 삽입해볼려고 한다.
처음에는 다음과 같이 테이블이 비어있는 것을 볼 수 있다.
이상태에서 Result Grid의 null되어있는 곳을 더블클릭하거나 위에 연필로 쓰는듯한 아이콘을 눌러 데이터를 입력할 수 있다.
이후 오른쪽 아래 Apply를 누르면 다음과 같은 창이 나온다.
창을 통해 우리는 데이터가 삽입되는 것을 확인할 수 있다.
다음과 같이 데이터가 들어가는 것을 확인할 수 있다.
Auto increment
다음을 보면 2번 이후 3번이 없는 것을 볼 수 있다.
이는 작성자가 1번과 2번 후 id에 4를 넣어 만들었고, 이후에 auto increment를 통해 자동으로 만들어졌을 때, 5로 올라가는 것을 알 수 있다.
즉, 컴퓨터에게 맡길거면 믿고 계속 맡기거나 아니면 사용자가 하나하나 다 입력해야한다는 것이다.
데이터 삽입 - channels
다음과 같이 데이터를 삽입할려고 할때, users테이블에는 3번이 없는데 저장할려고 시도하게되면, 다음과 같은 에러가 나오게 된다. 이걸 조심해서 데이터를 넣는게 좋다.
컬럼에 속성 추가하기
테이블에 컬럼을 만들던거처럼 해당 버튼을 통해 나오는 창에 추가하여 만들 수 잇다.
다음과 같이 Created_at이 추가되는 것을 알 수 있다.
DB연결하기
mysql2 라이브러리를 통해 node와 DB를 연결해줄 수 있다.
npm i --save mysql2해당 라이브러리의 사이트에 들어가보면 Quickstart라는 칸이 있는데, 그중 callback 코드를 사용하여 터미널에서 Youtube.Users테이블의 정보를 불러올 수 있었다.
// Get the client
const mysql = require('mysql2');
// Create the connection to database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '설정한 비밀번호입력',
database: 'Youtube',
});
// A simple SELECT query
connection.query(
'SELECT * FROM `Users`',
function (err, results, fields) {
console.log(results); // results contains rows returned by server
console.log(fields); // fields contains extra meta data about results, if available
}
);
// Using placeholders
// connection.query(
// 'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
// ['Page', 45],
// function (err, results) {
// console.log(results);
// }
// );
데이터 가공하기
비구조화를 통해 result로 출력되는 DB값들을 가공할 수 있다.
connection.query(
'SELECT * FROM `Users`',
function (err, results, fields) {
var {id, email, name} = result[0];
console.log(id);
console.log(email);
console.log(name);
console.log(fields); // fields contains extra meta data about results, if available
}
);1
kim@mail.com
김지성
[
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`email` VARCHAR(100) NOT NULL UNIQUE_KEY,
`name` VARCHAR(45) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`phone` VARCHAR(45)
]creaeted_at을 출력했을 때, db와 터미널에서의 시간이 달라요
이는 timezone이 설정되지 않아서이다.
node.js에서timezone: "Asia/Seoul"이라고 설정해줄 수도 있지만, DB에서
SET GLOBAL time_zone = "Asia/Seoul";로 글로벌하게 timezone을 설정해줄 수 있다.
이후 workbeanch에서SET @@global time_zone;을 해보면 다음과 같이 설정되는 것을 알 수 있다.
이후 해당 세션의 timezone도 아시아 서울로 바꿔주면, db에서의 시간이 서울로 설정된다.
하지만 node에서는 수정되지 않는 것을 볼 수 있는데 이는 connection에dateStrings: ture,를 추가해주면 시간대가 맞는 것을 볼 수 있다.
이는 db에서 설정을 바꾸더라도 보여주는시간을 바꾸는것인데, node에서는 날것을 불러왔었기에 시간이 달랐다. 하지만 dateStrings를 통해 보여주는 시간을 가져오면 db에 설정한 시간과 같은 시간이 출력되는 것을 볼 수 있다.
이렇게 연결한 db를 모듈화 하여 각 routes에서 불러온다.
// Create the connection to database
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
//...
});
module.exports = connection;데이터베이스 사용하기 - users
데이터베이스를 연동하기 전에 현재 코드중 데이터베이스와 다른 부분을 수정해줘야한다.
- 수정할 부분
- map대신 mariadb모듈 사용해서 db연결하기
- 로그인과 로그아웃에서 userId 대신 email을 사용함
이후 변경된 기존에 map을 이용해 만든 db를 대체해준다.
변경중 코드를 다음과 같이 변경해주었다.
// const userInfo = db.get(email); 를
let userInfo = db.query(
`SELECT * FROM Users WHERE email = ${email};`,
function (err, results, fields) {
console.log(err);
console.log(results);
userInfo = results;
}
);-
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@mail.com' at line 1에러- 해결: SQL문이 틀렸다면서; @mail.com부분을 확인하라고 한다. 혹시나 하는 마음에
${email}을 따움표로 감싸주었더니 해결되었다.
- 해결: SQL문이 틀렸다면서; @mail.com부분을 확인하라고 한다. 혹시나 하는 마음에
-
이번에는 결과값으로 나와야할 email과 name중 email만 나오는 것을 확인할 수 있었다.
확인하기 위해 userInfo를 출력했을 때<ref *1> Query {...가 출력되었다.- 해결: db결과를 map처럼 사용해서 변수에 할당하였기에 생기는 문제였다.
그래서 이를 해결하고자, 콜백 함수에서 분기에 따라 결과가 출력되도록 수정했다.// const userInfo = db.get(email); db.query( `SELECT * FROM Users WHERE email = '${email}';`, function (err, results, fields) { let userInfo = results[0]; if (userInfo){ res.status(200).json({ email: email, name: userInfo.name }) } else { res.status(404).json({ message: "회원 정보가 업습니다."}) } } );
- 해결: db결과를 map처럼 사용해서 변수에 할당하였기에 생기는 문제였다.
-
if분기를 userInfo에 담는걸 간단히 하기위해서 results를 바로 if문에 넣어
if(results.length > 0)사용하였는데, 이때 results.length를 사용할 수 없다는 에러가 나왔다.- 해결: 정보를 받아오기 전에 results가 존재하는지를 먼저 확인한 후 results.length를 확인하도록 만들었다.
if(results && results.length > 0)...
- 해결: 정보를 받아오기 전에 results가 존재하는지를 먼저 확인한 후 results.length를 확인하도록 만들었다.
-
자꾸 데이터가 누락되는 일이 생겨서 SQL을 확인하라고 해서 보니까, SELECT를 잘못 적었더라요
- 해결: 따움표나 스펠링을 잘 수정해주었다.
이걸 조금 방지할 수 있는 방법
.query를 사용할 때, SQL문을 따로 변수로 지정해서 정리하면 좋다.
- 해결: 따움표나 스펠링을 잘 수정해주었다.
이러한 점들을 조심하여 기존 코드를 DB로 연결할 수 있다.
요즘 로그인 트렌드
아이디가 틀렸는지 비밀번호가 틀렸는지 알려주지 않는다고한다.
데이터베이스 사용하기 - channels
users파일 처럼 데이터베이스와 연결하는 작업을 해준다.
- 수정할 부분
- map대신 mariadb모듈 사용해서 db연결하기
- 로그인과 로그아웃에서 userId 대신 email을 사용함
- 채널 생성할 때 INSERT를 했을 때 result값이 안나와서 채널이 생성되지 않았다.
- 해결: 이전에는 query에 if문으로 문기를 만들었다면, 이번에는 query문 밖에서 channelTitle과 userID가 비어있는지 확인한 후 채워져 있다면, query를 실행하는 식으로 바꿔줌
리펙토링
이렇게 db와 코드를 연결하였지만 코드가 상당히 읽기 어렵고 정리가 되지 않은 것을 알 수 있다.
이걸 해결하고자 여러 개념들을 짚고 전체적인 코드를 수정해보자
유효성 검사(valid ation)
"사용자가 입력한 값" 유효성(=타당성)을 서버로 전송하기 전에 확인하는 것이다.
사용자가 데이터 타입이나 형태에 맞는 값을 입력했는지를 확인하는 것이다.
이번에는 외부모듈 "express-vaildtor"를 사용할 것이다. 해당 사이트를 통해 사용하는 방법과 적용방법 등을 알 수 있다.
이를 channels파일에 적용하였다.
const express = require("express")
const router = express.Router()
const db = require('../mariadb')
const {body, validationResult} = require('express-validator') // 라이브러리를 불러옴
router.route('/')
// 채널 생성
.post(
[body('userId').notEmpty().isInt().withMessage('숫자 입력필요!'),
body('channelTitle').notEmpty().isString().withMessage('문자 입력필요!')]
,(req,res) =>{
const err = validationResult(req);
if (!err.isEmpty()){
return res.status(400).json(err.array())
}
const {channelTitle, userId} = req.body;
// db.set(id++, req.body)
// if (channelTitle && userId){
let insertValueSQL = `INSERT INTO channels (title, user_id) VALUES ("${channelTitle}","${userId}")`;
db.query(insertValueSQL, function(err,result,fields){
console.log("채널 생성에 성공했습니다.")
res.status(201).json({
message: `${channelTitle}님 채널을 응원합니다.`
})
});
// } else{
// console.log("채널 생성에 실패했습니다.")
// res.status(400).json({
// message: `요청 값을 제대로 보내주세요.`
// })
// }
})채널 생성 api에서 기존에 사용하던 if문을 유효성 검사를 추가하면서 좀 더 깔끔하게 리팩토링 할 수 있었다.
콜백함수의 err 매개변수 사용하기
이때까지 에러가 발생해도 찾기 힘들었지만, 이것은 콜백함수의 err를 사용하지 않았기 때문이다.
이제부터 if문을 통해 err 핸들링을 해줘야한다.
let insertValueSQL = `INSERT INTO channels (title, user_id) VALUES ("${channelTitle}","${userId}")`;
db.query(insertValueSQL, function(err,result,fields){
if (err) {
console.log(err)
return res.status(400).send();
}
console.log("채널 생성에 성공했습니다.")
res.status(201).json({
message: `${channelTitle}님 채널을 응원합니다.`
})
});미들웨어 만들어주기
유효성 검사를 할때마다 validationResult를 불러오기에 이를 미들웨어로 만들어 줄려고한다.
반복되는 코드를 하나의 함수로 만들어서 코드의 가독성을 높여준다.
const validate = (res,req) => {
const err = validationResult(req);
if (!err.isEmpty()){
return res.status(400).json(err.array())
}
}이렇게 만든 모듈을 api의 콜백함수를 하기 전인 유효성이 들어있는 리스트에 넣었는데, 실행했을 때 정상적인값이 입력되었을 때 에러가 뜨지 않으면서 무한로딩이 실행되기 시작했다.
이는 다음과 같이 모듈을 만들었을때 생기는 문제로 현재 함수에서 처리하지 못하거나 걸리지 않았을 때 다음 모듈로 이동할 수 있도록 .next()를 넣어 해결해줘야한다.
const validate = (res,req) => {
const err = validationResult(req);
if (err.isEmpty()){
return next();
} else {
return res.status(400).json(err.array())
}
}이렇게 만든 모듈을 유효성 검사와 함께 넣어준다.
.post(
[
body('userId').notEmpty().isInt().withMessage('숫자 입력필요!'),
body('channelTitle').notEmpty().isString().withMessage('문자 입력필요!'),
validate
]
,(req,res) =>{
const {channelTitle, userId} = req.body;
//...이런식으로 유효성 검사와 에러확인을 channels파일과 users파일에 적용해준다.
기타
폰트 바꾸는 법
설정에 들어가서 Fonts를 통해 바꿀 수 있다.
