Database, RDS

※이 포스팅은 sopt 26기 server part seminar 자료 바탕으로 작성되었으며, 복습용입니다.

1-1. DATABASE

database

Database란,

- 체계화된 데이터의 집합체

- 중복된 데이터를 없애고, 자료를 구조화하는 효율적인 처리

 

DBMS란,

데이터베이스를 관리하는 미들웨어

 

SQL을 사용하면 RDB, SQL을 사용하지 않으면 NoSQL이다.

 

---

2-1. RDB

RDB란,

Relational Database로

- key & value 들의 간단한 관계를 테이블화 시킨 매우 간단한 원칙의 데이터베이스

- 데이터를 column과 row를 이루는 하나 이상의 테이블

예를 들면 테이블의 컬럼이 id 일 때, id는 key가 되고 id 컬럼에 속한 'a'라는 id는 value가 된다.

2-2. Primary key

기본키로, 다른 데이터와 구별할 수 있는 식별자이다.

조건은 한 테이블엔 하나, 혹은 그 이상의 primary key가 있고 데이터 무결성 조건을 만족해야한다.

기본키는 Unique, Not Null 조건을 만족시키지만 반대로 Unique, Not null 이라고 해서 기본키가 되지 않는다.

---

2-3. Index

- Index는 데이터의 색인기능

- 관계형 데이터베이스에서 검색 속도를 높이기 위한 도구

- Unique하면서 특정 데이터를 대표하는데 사용

(개인적으로 테이블마다 AutoIncreasement를 설정한 Index 컬럼을 무조건적으로 넣는 편이다.)

 

---

2-4. Relational Database

- 테이블과 테이블간의 관계가 형성되어 있는 데이터베이스

1) 1:1 관계

2) 1:N 관계

3) N:M 관계

여기서 2, 3번의 경우는 중복 데이터가 있을 수 있다. 이럴 땐, 정규화를 사용한다..!

---

2-5. 정규화

정규화란,

RDB 설계를 논리적이고 직관적으로 만드는 과정

- 불필요한 데이터를 제거해서 데이터의 중복 최소화

- 데이터를 다루면서 생기는 이상현상 방지

* 이상현상 (삽입이상, 갱신이상, 삭제이상)

1) 삽입이상 : 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제

2) 갱신이상 : 중복 튜플 중 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제

3) 삭제이상 : 튜플을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 데이터 손실의 문제

- 개발 중 데이터의 변화가 생겨도 설계를 재구성할 필요성 감소

하지만 ! ! 과도한 정규화는 복잡한 DB 모델링이 될 수 있다.

정규화에는 여러가지 단계가 있다.

- 1차 정규화 : row마다 컬럼의 값이 1개씩만 있어야 한다. = 분해될 수 없는 원자값을 갖는다.

- 2차 정규화 : 1차 정규화를 만족하고 부분 함수적 종속을 제거한다. = 기본키 중에 특정 컬럼에만 종속된 컬럼이 없어야 한다.

* 함수적 종속 : X -> Y ( X : 결정자 , Y : 종속자 )

결정자의 값을 알면 종속자의 값을 바로 식별할 수 있고 결정자의 값에 종속자 값이 달라질 때, 종속자는 결정자에 함수적 종속이라고 한다.

- 3차 정규화 : 2차 정규화를 만족하고 이행적 함수 종속을 제거한다. = 기본키 외 컬럼이 다른 컬럼을 결정할 수 없다.

- BCNF : 3차 정규화를 만족하고 결정자가 후보 키가 아니면 제거한다. = 모든 결정자가 후보키가 되도록 만든다.

* 후보키 : 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별하기 위해 사용하는 속성들의 부분집합, 즉, 기본키로 사용할 수 있는 속성들을 말한다.

 

---

2-6. 역정규화

역정규화란,

정규화된 테이블을 중복을 허용하고, 다시 통합하거나 분할하여 구조를 재조정하는 과정

정규화된 모델은 저장된 자료를 검색하는 시간을 증가시키기 때문에 성능을 저하시킨다.

따라서 물리적 설계 과정에서 성능을 향상시키기 위해 역정규화를 실행

---

3-1. MySQL

MySQL은 세계에서 가장 많이 쓰이는 오픈 소스 RDBMS로 오라클이 지원 및 관리하고 기본 포트는 3306이다.

---

3-2. MySQL Workbench

MySQL Workbench는 SQL 개발과 관리 및 데이터베이스 설계, 생성과 유지를 위한 개발 통합 환경 도구

- 공식적인 MySQL GUI 툴

(Mysql을 사용할 때면 항상 Workbench를 사용했다. 간편해서 너무 좋다,,헤헤)

---

3-3. RDS

RDS란,

클라우드에서 RDB를 간편하게 설정, 운영 및 확장하며 여러 인스턴스 유형(메모리, 성능 등)으로 제공

---

4-1. SQL

SQL은 DB에서 테이블을 처리하는 방식

데이터 CRUD

- 데이터 생성 CREATE -> INSERT

- 데이터 조회 READ -> SELECT

- 데이터 수정 UPDATE -> UPDATE

- 데이터 삭제 DELETE -> DELETE

PK : Primary Key, (기본키) 설정  NN : Not Null, Null 허용 안함 설정  UQ : Unique, 테이블에서 중복 값 허용 안함 설정  AI : Auto Increasement, 레코드 삽입 시 자동으로 값 증가 (dataType - INT)  BIN : Binary, 이진 값으로 설정  UN : Unsigned, 부호없는 양의 값으로 설정  ZF : Zero fill, 정수 들어오면 앞에 0 채움 설정  G : Generated, 가상 컬럼으로 설정  Default : 값이 들어오지 않으면, Default 값으로 어떤 값을 넣을지 결정 RESTRICT : 변경/삭제가 취소 (거부)  CASCADE : 함께 변경/삭제  NO ACTION : 변경/삭제할 개체만 변경/삭제되고 참조하고 있는 개체는 변동이 없음  SET NULL : 참조하고 있는 값은 NULL로 세팅

[ INSERT ]
INSERT INTO table_name (col1, col2) VALUES (val1, val2); INSERT INTO table_name VALUES (val1, val2); 컴럼 이름을 생략할 시, 테이블 컬럼 개수 및 위치가 values의 개수 및 위치가 일치 해야함

[ UPDATE ]
UPDATE table_name SET col1=val1, col2=val2 WHERE 조건;

[ DELETE ]
DELETE FROM table_name WHERE 조건;

[ SELECT ]
- 조회) SELECT column_names FROM table_name WHERE 조건; 
* 전체 컬럼을 조회할 경우 조회할 컬럼 이름들에 * 표시

- 중복 제거) SELECT DISTINCT 중복_불허한_컬럼이름 FROM table_names WHERE 조건;

- 정렬) SELECT column_names FROM table_name WHERE 조건 ORDER BY 정렬_기준이_될_컬럼 [ASC/DESC];
* ASC : 오름차순 (default), DESC : 내림차순

- 테이블 합치기) SELECT * FROM a_table JOIN b_talbe ON a_table.동일_컬럼 = b_table.동일_컬럼 WHERE 조건; 

---

5-1. promise-mysql 모듈 (NodeJS 연동)

- 해당 프로젝트에$ npm install promise-mysql 설치해줘야함!

- DB와 통신할 connection 설정을 위해 설정 파일이 필요 (config/database.js로 설정 -> 깃헙에 절! 대! 공개적으로 올리지말고 .gitignore시킬것)

const mysql = require('promise-mysql');
const dbConfig = { 
host: 'abcdefg', 
port: 3306, 
user: 'admin', 
password: '1234', 
database: 'our-sopt', 
} 

module.exports = mysql.createPool(dbConfig);

createPool -> pool.getConnection ->  connection.query -> connection.release

- pool 생성

pool = createPool(config)

connection pool을 생성

- pool 사용

connection = pool.getConnection()

connection pool에서 connection을 가져옴connection.query(query, [value])

query -> String : 실행할 쿼리 입력. 변수 자리에 ?로 표시하면 런타임 시 배정 혹은 백틱 문자열로 변수를 바로 넣어줘도 됨

values -> String : 쿼리문 안에 ? 순서에 맞게 들어갈 변수들이 배열 형식으로 들어감

- pool 사용 종료

releaseConnection(connection)

반납하지 않으면 connection이 쌓여서 connection leak 현상 발생

회원가입/로그인 : routes/user.js

const express = require('express');
const router = express.Router();
const UserModel = require('../models/user');
const util = require('../modules/util');
const statusCode = require('../modules/statusCode');
const resMessage = require('../modules/responseMessage');
const encrypt = require('../modules/crypto');
const jwt = require('../modules/jwt');

router.post('/signup', async (req, res) => {
    const {
        id,
        name,
        password,
        email
    } = req.body;
    if (!id || !name || !password || !email) {
        res.status(statusCode.BAD_REQUEST)
            .send(util.fail(statusCode.BAD_REQUEST, resMessage.NULL_VALUE));
        return;
    }
    // 사용자 중인 아이디가 있는지 확인
    if (await UserModel.checkUser(id)) {
        res.status(statusCode.BAD_REQUEST)
            .send(util.fail(statusCode.BAD_REQUEST, resMessage.ALREADY_ID));
        return;
    }
    const {
        salt,
        hashed
    } = await encrypt.encrypt(password);
    const idx = await UserModel.signup(id, name, hashed, salt, email);
    if (idx === -1) {
        return res.status(statusCode.DB_ERROR)
            .send(util.fail(statusCode.DB_ERROR, resMessage.DB_ERROR));
    }
    res.status(statusCode.OK)
        .send(util.success(statusCode.OK, resMessage.CREATED_USER, {
            userId: idx
        }));
});

router.post('/signin', async (req, res) => {
    const {
        id,
        password
    } = req.body;
    if (!id || !password) {
        res.status(statusCode.BAD_REQUEST)
            .send(util.fail(statusCode.BAD_REQUEST, resMessage.NULL_VALUE));
        return;
    }

    // User의 아이디가 있는지 확인 - 없다면 NO_USER 반납
    const user = await UserModel.getUserById(id);
    if (user[0]===undefined){
        return res.status(statusCode.BAD_REQUEST).send(util.fail(statusCode.BAD_REQUEST, resMessage.NO_USER));
    }
    // req의 Password 확인 - 틀렸다면 MISS_MATCH_PW 반납
    const hashed = await encrypt.encryptWithSalt(password, user[0].salt);
    if (hashed !== user[0].password){
        return res.status(statusCode.BAD_REQUEST).send(util.fail(statusCode.BAD_REQUEST, resMessage.MISS_MATCH_PW));
    }
    const {token, _} = await jwt.sign(user[0]);
    // 로그인이 성공적으로 마쳤다면 - LOGIN_SUCCESS 전달
    res.status(statusCode.OK)
        .send(util.success(statusCode.OK, resMessage.LOGIN_SUCCESS, { accessToken : token}));
});

module.exports = router;

 

회원가입 / 로그인 : models/user.js

const pool = require('../modules/pool');
const table = 'user';

const user = {
    signup: async (id, name, password, salt, email) => {
        const fields = 'id, name, password, salt, email';
        const questions = `?, ?, ?, ?, ?`;
        const values = [id, name, password, salt, email];
        const query = `INSERT INTO ${table}(${fields}) VALUES(${questions})`;
        try {
            const result = await pool.queryParamArr(query, values);
            const insertId = result.insertId;
            return insertId;
        } catch (err) {
            if (err.errno == 1062) {
                console.log('signup ERROR : ', err.errno, err.code);
                throw err;
            }
            console.log('signup ERROR : ', err);
            throw err;
        }
    },
    checkUser: async (id) => {
        const query = `SELECT * FROM ${table} WHERE id="${id}"`;
        try {
            const result = await pool.queryParam(query);
            if (result.length === 0) {
                return false;
            } else return true;
        } catch (err) {
            if (err.errno == 1062) {
                console.log('checkUser ERROR : ', err.errno, err.code);
                throw err;
            }
            console.log('checkUser ERROR : ', err);
            throw err;
        }
    },
    getUserById: async (id) => {
        const query = `SELECT * FROM ${table} WHERE id="${id}"`;
        //return await pool.queryParam(query);
        // const query = `SELECT * FROM ${table} WHERE id=id`;
        // return await pool.queryParam(query,[id]);
        // query문 작성
        // pool module로 전달해서 결과값 받기
        // try - catch로 ERROR 받기
        try {
            return await pool.queryParam(query);
        } catch (err) {
            console.log('checkUser ERROR : ', err);
            throw err;
        }
    },
}

module.exports = user;

5-2. Transaction

Transaction이란,

데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위

-> 여러 단계의 처리를 하나처럼 다룸

1) commit : Transaction의 실행 결과를 데이터베이스에 반영하는 것

2) roll-back : Transaction의 실행 결과를 반영하지 않고 원상태로 되돌리는 것

connection.beginTransaction(config) // Transaction 적용 시작
connection.commit() // Transaction 내 모든 쿼리 완료 후 결과 반영
connection.rollback() // 모든 쿼리가 정상적으로 마치지 못했다면 원상태로 돌려놓음