[노드교과서] 섹션 6. 데이터베이스

7.1 데이터베이스

 1. 데이터베이스란

  >> 메모리가 아닌 데이터를 영구적으로 저장할 수 있는 공간
  >> MySQL 관계형 데이터베이스 사용
   - 데이터베이스 : 관련성을 가지며 중복이 없는 데이터들의 집합이다.
   - DBMS : 데이터베이스를 관리하는 시스템
   - RDBMS : 관계형 데이터베이스를 관리하는 시스템
   - 서버의 하드디스크, SSD 등 저장 매체에 데이터들을 저장
   - 서버 종료 여부와 관계없이 데이터를 계속 사용 가능하다.
   - 동시접속, 권한관리가 가능하다.

7.3 데이터베이스, 테이블 생성하기

 1. 데이터베이스(스키마) 생성하기

 >> 콘솔에서 Mysql 프롬프트에 접속
  - create schema nodejs; 로 nodejs 데이터베이스(스키마) 생성
  - use node;로 생성한 데이터베이스를 선택한다.

mysql> create schema nodejs default character set utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nodejs             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use nodejs;
Database changed

 2. 테이블 생성하기

  >> MySQL 프롬프트에서 테이블 생성
   - create table[테이터베이스명.테이블명]으로 테이블 생성

mysql> create table nodejs.users (
    -> id int not null auto_increment,
    -> name varchar(20) not null,
    -> age int unsigned not null,
    -> married tinyint not null,
    -> comment text null,
    -> created_at datetime not null default now(),
    -> primary key(id),
    -> unique index name_unique (name asc))
    -> comment = '사용자정보'
    -> default character set = utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

 3. 컬럼과 로우

  - 컬럼 : 나이, 결혼여부, 성별 같은 정보를 컬럼이며 컬럼은 열이다.
  - 로우 : 로우는 나이, 결혼여부, 성별의 정보를 모두 담고있는 행이다.

 4. 컬럼 옵션들

  >> id int not null auto_increment
   - 컬럼명 옆에 것들은 컬럼에 대한 옵션들이다.
   - INT : 정수 자료형(FLOAT, DOUBLE은 실수)
   - VARCHAR : 문자열 자료형, 가변 길이(CHAR는 고정길이)
   - TEXT : 긴 문자열은 TEXT로 별도 저장
   - DATETIME : 날짜 자료형 저장
   - TINYINT : -128 ~ 127까지 저장하지만 여기서는 1 또는 0만 저장해 불(BOOLEAN)값 표현
   - NOT NULL : 빈 값은 받지 않는다는 뜻(NULL은 빈 값 허용)
   - AUTO_INCREMENT : 숫자 자료형일 경우 다음 로우가 저장될 때 자동으로 1 증가
   - UNSIGNED : 0과 양수만 허용
   - ZEROFILL : 숫자의 자리 수가 고정된 경우 빈 자리에 0을 넣음
   - DEFAULT now() : 날짜 컬럼의 기본 값을 현재 시간으로

 5. Primary Key, Unique Index

  >> primary key(id)
   - id가 테이블에서 로우를 특정할 수 있게 해주는 고유의 값임을 의미한다.
   - 학번, 주민등록번호같은 유일한 하나의 키
  >> UNIQUE INDEX nmae_UNIQUE (name ASC)
   - 해당 컬럼(name)이 고유해야 함을 나타내는 옵션이다.
   - name_UNIQUE는 옵션의 이름이며 ASC는 인덱스를 오름차순을 저장함을 의미한다.(DESC는 내림차순)

 8. 사용자 테이블과 관계를 맺는 댓글 테이블을 생성하기

mysql> create table nodejs.comments (
    -> id int not null auto_increment,
    -> commenter int not null,
    -> created_at datetime not null default now(),
    -> primary key(id),
    -> index commenter_idx ( commenter asc),
    -> constraint commenter
    -> foreign key (commenter)
    -> references nodejs.users (id)
    -> on delete cascade
    -> on update cascade)
    -> comment = '댓글'
    -> default charset=utf8mb4
    -> engine=innodb;
Query OK, 0 rows affected (0.05 sec)

 9. 외래키

  >> 댓글 테이블은 사용자 테이블과 관계가 있다.
   - 외래키를 두어 두 테이블이 관계가 있다는 것을 표시한다.
   - FOREIGN KEY(컬럼명) REFERENCES 데이터베이스.테이블명(컬럼)
    : 댓글 테이블 commenter 컬럼의 값과 사용자테이블 id 값이 매칭된다.
   - ON DELETE CASCADE, ON UPDATE CASCADE
    : 사용자 테이블 로우가 지워지고 수정될 때 댓글 테이블의 연관된 로우들도 같이 지워지고 수정된다.
   - 데이터를 일치시키기 위해 사용하는 옵션(CASCADE 대신 SET NULL과 NO ACTION도 사용한다)

7.4 CRUD 작업하기

 1. CRUD

  >> CREATE, READ, UPDATE, DELETE의 두문자어
   - 데이터베이스에서 많이 하는 작업 4가지이다.

 2. create

  >> insert into 테이블 (컬럼명들) values (값들)

  mysql> insert into nodejs.users (name, age, married, comment) values ('zero', 24, 0, '자기소개1');
  Query OK, 1 row affected (0.01 sec)
  mysql> insert into nodejs.users (name, age, married, comment) values ('nero', 32, 0, '자기소개2');
  Query OK, 1 row affected (0.01 sec)
  mysql> insert into nodejs.comments(commenter, comment) values (1, '안녕하세요. zero의 댓글입니다.');
  Query OK, 1 row affected (0.01 sec)

 3. Read

  >> SELECT 컬럼 FROM 테이블명(SELECT *은 모든 컬럼을 의미)

  mysql> select * from users;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  1 | zero |  24 |       0 | 자기소개1 | 2024-01-03 10:00:28 |
|  2 | nero |  32 |       0 | 자기소개2 | 2024-01-03 10:00:47 |
+----+------+-----+---------+-----------+---------------------+

 4. Read옵션들

  >> where로 조건을 주어 선택 가능

mysql> select * from nodejs.users where age = 24;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  1 | zero |  24 |       0 | 자기소개1 | 2024-01-03 10:00:28 |
+----+------+-----+---------+-----------+---------------------+

 5. 정렬해서 찾기

  >> order by로 특정 컬럼 값 순서대로 정렬 가능

  mysql> select * from nodejs.users order by name asc;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  2 | nero |  32 |       0 | 자기소개2 | 2024-01-03 10:00:47 |
|  1 | zero |  24 |       0 | 자기소개1 | 2024-01-03 10:00:28 |
+----+------+-----+---------+-----------+---------------------+
2 row in set (0.00 sec)

 6. limit, offset

  >> limit으로 조회할 개수 제한

mysql> select * from nodejs.users order by name asc limit 1;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  2 | nero |  32 |       0 | 자기소개2 | 2024-01-03 10:00:47 |
+----+------+-----+---------+-----------+---------------------+
1 row in set (0.00 sec)

 

  >> offset으로 앞의 로우들 스킵 가능(offset 1이면 두번째 것부터 찾는다)

  mysql> select * from nodejs.users order by name asc limit 1 offset 1;
+----+------+-----+---------+-----------+---------------------+
| id | name | age | married | comment   | created_at          |
+----+------+-----+---------+-----------+---------------------+
|  1 | zero |  24 |       0 | 자기소개1 | 2024-01-03 10:00:28 |
+----+------+-----+---------+-----------+---------------------+
1 row in set (0.00 sec)

 7. UPDATE

  >>  테이터베이스에 있는 데이터를 수정하는 작업
   - UPDATE 테이블명 SET 컬럼=새값 WHERE 조건

mysql> UPDATE nodejs.users set married = 1 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 8. DELETE

  >> 데이터베이스에 있는 테이블의 데이터를 삭제하는 작업
   - DELETE FROM 테이블명 WHERE 조건

mysql> delete from nodejs.users where id = 2;
Query OK, 1 row affected (0.01 sec)

7.5 시퀄라이즈 사용하기

 1. 시퀄라이즈 ORM

  ⇒ SQL 작업을 쉽게 할 수 있도록 도와주는 라이브러리이다.

   - ORM(Object Relational Mappring) : 객체 와 데이터를 매핑(1:1)

   - MySQL 외에도 다른 SQL(Maria, Postage, SQLLite, MSSQL)와도 호완된다.

   - 자바스크립트 문법으로 데이터베이스를 조작할 수 있다.

 

 2. 시퀄라이즈 CLI 사용하기

  ⇒ 시퀄라이즈 명령어를 사용하기 위해 sequelize-cli를 설치해야 한다.

   - mysql2는 MySQL DB 가 아닌 드라이버이다(Node.js와 Mysql를 연결해주는 역할)

$ npm init
$ npm i express morgan nunjucks sequelize sequelize-cli mysql2

 

{
  "name": "learn-sequelize",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "nodemon app"
  },
  "author": "JaeikKim",
  "license": "MIT",
  "devDependencies": {
    "nodeman": "^1.1.2"
  },
  "dependencies": {
    "express": "^4.18.2",
    "morgan": "^1.10.0",
    "mysql2": "^3.6.5",
    "nunjucks": "^3.2.4",
    "sequelize": "^6.35.2",
    "sequelize-cli": "^6.6.2"
  }
}

 

  ⇒ npx sequelize init으로 시퀄라이즈 구조를 생성해야 한다.

$ npx sequelize init        

Sequelize CLI [Node: 20.10.0, CLI: 6.6.2, ORM: 6.35.2]
Created "config\config.json"
Successfully created models folder at "C:\work\005_study\learn-sequelize\models".
Successfully created migrations folder at "C:\work\005_study\learn-sequelize\migrations".
Successfully created seeders folder at "C:\work\005_study\learn-sequelize\seeders".

 3. models/index.js 수정

const Sequelize = require('sequelize');

const env = process.env.NODE_ENV || 'development';
const config = require('../config/config')[env];
const db = {};

// 연결객체
const sequelize = new Sequelize(config.database, config.username, config.password, config);
db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

 

// config/config.json

{
  "development": {
    "username": "root",
    "password": "0000",
    "database": "nodejs",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  ...
}

 

 

// app.js

const express = require('express');
const path = require('path');
const morgan = require('morgan');
const nunjucks = require('nunjucks');

const { sequelize } = require('./models');
const { addAbortSignal } = require('stream');

const app = express();
app.set('port', process_env_PORT || 3001);
app.set('view engine', 'html');
nunjucks.configure('views', {
	express : app,
    watch : true,
});

// sequelize sync를 통해 데이터베이스에 연결한다.
sequelize.sync({force : false})
	.then(() => {
    	console.log("데이터베이스 정상 연결");
    })
    .catch((err) => {
    	console.error(err);
    });

app.use(morgan('dev'));
app.use(express.static(path.json(__dirname,'public'));  // 정적인 파일들을 제공하는 미들웨어

// body-parser
app.use(express.json());
app.use(express.urlencoded({extended : false}));

// 404 error
app.use((req, res, next) => {
	const error = new Error(`${req.method} ${req.url} 라우터가 없습니다`);
});

app.use((err,req,res,next) => {
	res.locals.message = err.message;
    res.locals.error = process.nev.NODE_ENV !== 'production' ? err : {};
    res.status
});

app.listen(app.get('port'), () => {
	console.log(app.get('port'), '번 포트에서 대기중입니다');
});

 7. 사용자모델 생성하기

// user.js
const Sequelize = require('sequelize');

class User extends Sequelize.Model {
	static initiate(sequlelize) {
    	User.init({
        	name : {
            	type : Sequelize.STRING(20),
                allowNull : false,
                unique : true,
            },
            age : {
            	type : Sequelize.INTEGER_UNSIGNED,
            },
            married: {
            	type: Sequelize.BOOLEAN,
                allowNull : false,
            },
            comment : {
            	type : Sequelize.TEXT,
                alloswNull : true,
            },
            created_at : {
            	type : Sequelize.DATE,
                allowNull : false,
                defaultValue : Sequelize.NOW,
            },
        }, {
        	sequelize,
            timestamps : false,  // createdAt, updatedAt 컬럼 자동생성
            underscored : false, // true(스네이크케이스 : created_at, false(카멜케이스 : createdAt)
            paranoid : false, // true로 변경 시 deleteAt을 통한 논리삭제(soft delete)를 할 수 있다.
            modelName : 'User',
            tableName : 'users',
            charset : 'utf8',            // utf8mb4는 이모티콘까지 사용이 가능하다.
            collate : 'utf8_general_ci',
        },);
    }
    
    // 테이블간의 관계 설정
    static associate(db) {
    	// hasMany는 sourceKey
        db.User.hasMany(db.Comment, { foreignKey : 'commenter', courceKey : 'id'});
    }
}

module.exports = User;

 8. 모델 옵션들

  ⇒ 시퀄라이즈 모델의 자료형은 MySQL의 자료형과 일치하지 않는다.

   - VARCHAR = STRING(100)

   - INT = INTEGER

   - TINYINT = BOOLEAN

   - DATETIME(DATE) = DATE(DATEONLY)

   - INT UNSIGNED = INTEGER.UNSIGNED

   - NOT NULL = allowNull :false

   - UNIQUE = unique : true

   - DEFAULT now() : defaultValue : Sequelize.NOW

 

  ⇒ define 메서드의 세 번째 인자는 테이블 옵션이다.

   - timestamps : true이면 createAt, updatedAt 컬럼을 자동으로 생성해 준다.

   - paranoid : true이면 deletedAt 컬럼을 만든다.  soft delete(논리삭제)

   - underscored : true(스네이크케이스 : create_at), false(카멜케이스: createAt)

   - modelName은 모델 이름, tableName은 테이블 이름을 설정한다.

   - charset과 collate는 한글 설정을 위해 필요하다.

 9. 댓글 모델 생성하기

const Sequelize = require('sequelize');

class Comment extends Sequelize.Model {
	static initiate(sequelize) {
    	Comment.init({
        	comment : {
            	type : Sequelize.STRING(200),
                allowNull : false,
            },
            created_at : {
            	type : Sequelize.DATE,
                allowNull : true,
                defaultValue : Sequelize.NOW,
            },
        }, {
        	sequelize,
            timestmps : false,
            modelName : 'Comment',
            tableName : 'comments',
            paranoid : false,
            charset : 'utf8mb4',
            collate : 'utf8mb4_general_ci',
        },);
    }
    
    static associate(db) {
    	// belongTo는 targetKey, foreignKey는 beLongTo에 들어간다.
        db.Comment.belongsTo(db.User, { foreignKey : 'commenter', targetKey : 'id'});
    }
}

module.exports = Comment;

 

 10. 댓글, 사용자 모델 활성화 하기

const Sequelize = require('sequelize');
const User = require('./user');
const Comment = require('./comment');

const env = process.env.NODE_ENV || 'development';
const config = require('../config/config')[env];
const db = {};

// 연결객체
const sequelize = new Sequelize(config.database, config.username, config.password, config);

db.sequelize = sequelize;
db.Sequelize = Sequelize;

db.User = User;
db.Comment = Comment;

// 모델이랑 시퀄라이즈를 initiate 통해 연결해준다.
User.initiate(sequelize);
Comment.initiate(sequelize);

User.associate(db);
Comment.associate(db);

module.exports = db;

 11. 관계 정의하기

  ⇒ users모델과 comments 모델 간의 관계를 정의

   - 1:N관계 (사용자 한명이 댓글 여러개를 작성한다)

   - 시퀄라이즈에서는 1:N관계를 hasMany로 표현한다.(User.hasMany(Comment))

   - 반대의 입장에서는 belongsTo(Comment.belongsTo(User))

   - belongsTo가 있는 테이블에 외래키가 생긴다(댓글 테이블에 commenter컬럼)

 12. 1:1 관계

  ⇒ 사용자 테이블과 사용자 정보 테이블(foreignKey는 belongsTo에 들어간다)

   - db.User.hasOne(db.Info, { foreignKey : 'UserId', sourceKey : 'id'});

   - db.Info.belongsTo(db.User, { foreignKey : 'UserId', targetKey : 'id'});

 13. N:M 관계

  ⇒ 다대다 관계

   - 예를 들어 게시글과 해시태그와의 관계를 다대다 관계라고 할 수 있다.

   - 하나의 게시글의 여러 개의 해시태그를 가질 수 있고,  하나의 해시태그가 여러 개의 게시글을 가질 수 있기 때문이다.

   - db 특성 상 다대다 관계는 중간 테이블(PostHashtag)이 생기며 두 테이블의 id들이 컬럼으로 생긴다(PostId, HashtagId)

   - db.Post.belongsToMany(db.HashTag, { through : 'PostHashTag'});

   - db.HashTag.belongsToMany(db.Post, { through : 'PostHashTag'});

14. 시퀄라이즈 쿼리 알아보기

  ⇒ sequelize operator api를 확인하면 더 상세하게 알 수 있다.

https://sequelize.org/docs/v6/core-concepts/model-querying-basics/

 

  ⇒ 조회

const { user } = require('../models');

// insert
User.create({
	name : 'zero',
    age : 24,
    married : false,
    comment : '자기소개1',
});

// select
User.findAll({}); // select * from users;

// select name, married from User;
User.findAll({ 
	attributes : ['name', 'married'],
});

 

   - 특수한 기능들인 경우 Sequelize.Op의 연산자를 사용한다(gt, or, ..등)

const { Op } = require('sequelize');
const { User } = require('../models');
User.findAll({
	attributes : ['name', 'age'],
    where : {
    	married : true,
        // gt(>), lt(<), gte(>=), lte(<=)
        age : { [Op.gt] : 30 },
    },
});

User.findAll({
	attributes : ['id', 'name'],
    where : {
    	[Op.or] : [
        	{married : false}, 
            {age : {[Op.gt]: 30}},
        ],
        limit : 1,
        offset : 1,
    }, 
});

 

  ⇒ 수정 및 삭제

// update
Const { User } = require('../models');
User.update({
	comment : '바꿀 내용',
}, {
	where : { id : 2 },
});

// delete
User.destory({
	where : { id : 2 },
});

 18. 관계 쿼리

  ⇒ 결과 값이 자바스크립트 객체이다.

const user = await User.findOne({});  // 단건
console.log(user.nuck);

 

  ⇒ include로 JSON과 비슷한 기능 수행이 가능하다.

// 미리 sequelize를 통한 관계 설정이 되어있기 때문이다.
const user = await.findOne({
	include : [{
    	model : Comment,
    }],
});
console.log(user.Comments);

 

  ⇒ M:N(다대다) 모델은 다음과 같이 접근이 가능하다.

db.sequelize.models.PostHashtag

 

  ⇒ include가 아닌 get + 모델명으로 관계 있는 데이터 설정이 가능하다.

const user = await User.findOne({});
const comment = await user.getComments();
console.log(comment);

 

  ⇒ 모델명을 as 를 통해 사용자 정의 명칭으로 변경할 수 있다.

// 관계를 설정할 시 as 속성을 사용해 사용자 정의 명칭을 설정한다.
db.User.hasMany(db.Comment, { foreignKey : 'commenter', sourceKey : 'id', as : 'Answers'});

// 관계 쿼리 작성 시
const user = await User.findOne({});
const answers = await user.getAnswers();  // Comment의 사용자 정의 명칭
console.log(answers);

 

  ⇒ include나 관계 쿼리 메서드에도 where이나 attributes를 사용할 수 있다.

const user = await User.findOne({
	include : [{
    	model : Comment,
        where : {
        	id : 1,
        },
        attributes : ['id'],
    }],
});
// 또는
const comments = await user.getComments({
	where : {
    	id : 1,
    },
    attributes : ['id'],
});

 

  ⇒ 생성 쿼리

const user = await User.findOne({});
const comment = await Comment.create();
await user.addComment(comment);  // 또는 await user.addComment(comment.id);

// 여러 건을 추가할 경우엔느 배열로 추가가 가능하다.
const comment1 = await Comment.create();
const comment2 = await Comment.create();
await user.addComment([comment1, comment2]);

 

  ⇒ 수정은 set + 모델명, 삭제는 remove + 모델명

  22. raw 쿼리

  ⇒ sequelize에서 직접 쿼리를 작성하여 수행할 수 있다.

const [result, metadata] = await sequelize.query('SELECT * FROM COMMENTS');
console.log(result);

 24. 쿼리 수행하기

  ⇒ users 라우터

   - get, post, delete, patch 같은 요청에 대한 라우터를 연결한다.

   - 데이터는 JSON형식으로 응답하며 comments라우터도 마찬가지이다.

const express = require('express');
const User = require('../models/user');
const Comment = require('../models/comment');

const router = express.Router();

router.route('/')
    .get(async (req, res, next) => {
        try {
            const users = await User.findAll();
            res.json(users);
        } catch(err) {
            console.error(err);
            next(err);
        }
    })
    .post(async (req, res, next) => {
        try {
            const user = await User.create({
                name : req.body.name,
                age : req.body.age,
                married : req.body.married,
            });
            console.log(user);
            res.status(201).json(user);
        } catch (error) {
            console.error(error);
            next(error);
        }
    });

    router.get('/:id/comments', async (req, res, next) => {
        try {
            const comments = await Comment.findAll({
                include: {
                    model : User,
                    where : { id: req.params.id},
                }
            });
            console.log(comments);
            res.json(comments);

        } catch (err) {
            console.error(err);
            next(err);
        }
    });

module.exports = router;

 

  ⇒ comments 라우터   

const express = require('express');
const { Comment } = require('../models');

const router = express.Router();

router.post('/', async (req, res, next) => {
    try {
        const comment = await Comment.create({
            commenter : req.body.id,
            comment : req.body.comment,
        });
        console.log(comment);
        res.status(201).json(comment);
    } catch(err) {
        console.error(err);
        next(err);
    }
});

router.route('/:id')
    .patch(async (req, res, next) => {
        try {
            const result = await Comment.update({
                comment : req.body.comment,
            }, {
                where : { id : req.params.id },
            });
            res.json(result);

        } catch(err) {
            console.error(err);
            next(err);
        }
    })
    .delete(async (req, res, next) => {
        try {
            const result = await Comment.destroy({where : { id : req.params.id }});
            res.json(result);
        } catch (err) {
            console.error(err);
            next(err);
        }
    });

module.exports = router;