🔎MySQL

데이터베이스

1. 데이터베이스란

지금까지는 데이터를 서버 메모리에 저장했음

• 서버를 재시작하면 데이터도 사라져버림 -> 영구적으로 저장할 공간 필요
  MySQL 관계형 데이터베이스 사용
• 데이터베이스: 관련성을 가지며 중복이 없는 데이터들의 집합
• DBMS: 데이터베이스를 관리하는 시스템
• RDBMS: 관계형 데이터베이스를 관리하는 시스템
• 서버의 하드 디스크나 SSD 등의 저장 매체에 데이터를 저장
• 서버 종료 여부와 상관 없이 데이터를 계속 사용할 수 있음
• 여러 사람이 동시에 접근할 수 있고, 권한을 따로 줄 수 있음

7. MySQL 접속해보기

콘솔(CMD)에서 MySQL이 설치된 경로로 이동

• 기본 경로는 C:\Program Files\MySQL\MySQL Server 8.0\bin
• -h는 호스트, -u는 사용자 –p는 비밀번호 의미
  
• 프롬프트가 mysql>로 바뀐다면 성공
• 프롬프트를 종료하려면 exit 입력
  

12. MySQL 서버 주소 입력

Connection Name에 localhost 적고 비밀번호 입력

13. localhost 서버 접속하기

아까 설정했던 비밀번호를 입력하여 접속

접속이 되었다면 성공

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

1. 데이터베이스 생성하기

콘솔에서 MySQL 프롬프트에 접속

• CREATE SCHEMA nodejs;로 nodejs 데이터베이스 생성
• use nodejs;로 생성한 데이터베이스 선택

2. 테이블 생성하기

MySQL 프롬프트에서 테이블 생성

• CREATE TABLE [데이터베이스명.테이블명]으로 테이블 생성
• 사용자 정보를 저장하는 테이블

3. 컬럼과 로우

나이, 결혼 여부, 성별같은 정보가 컬럼
실제로 들어가는 데이터는 로우

4. 컬럼 옵션들

id INT NOT NULL AUTO_INCREMENT

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

5. Primary Key, Unique Index

PRIMARY KEY(id)

• id가 테이블에서 로우를 특정할 수 있게 해주는 고유한 값임을 의미
• 학번, 주민등록번호같은 개념
  UNIQUE INDEX name_UNIQUE (name ASC)
• 해당 컬럼(name)이 고유해야 함을 나타내는 옵션
• name_UNIQUE는 이 옵션의 이름(아무거나 다른 걸로 지어도 됨)
• ASC는 인덱스를 오름차순으로 저장함의 의미(내림차순은 DESC)![]

6. 테이블 옵션

COMMENT: 테이블에 대한 보충 설명(필수 아님)
DEFAULT CHARSET: utf8로 설정해야 한글이 입력됨(utf8mb4 하면 이모티콘 가능)
ENGINE: InnoDB 사용(이외에 MyISAM이 있음, 엔진별로 기능 차이 존재)

7. 테이블 생성되었나 확인하기

DESC 테이블명

테이블 삭제하기: DROP TABLE 테이블명

8. 댓글 테이블 저장하기

comments 테이블 생성

9. 외래키(foreign key)

댓글 테이블은 사용자 테이블과 관계가 있음(사용자가 댓글을 달기 때문)

• 외래키를 두어 두 테이블이 관계가 있다는 것을 표시

• FOREIGN KEY (컬럼명) REFERENCES 데이터베이스.테이블명 (컬럼)

• FOREIGN KEY (commenter) REFERENCES nodejs.users (id)

• 댓글 테이블에는 commenter 컬럼이 생기고 사용자 테이블의 id값이 저장됨

• ON DELETE CASCADE, ON UPDATE CASCADE

• 사용자 테이블의 로우가 지워지고 수정될 때 댓글 테이블의 연관된 로우들도 같이 지워지고 수정됨

• 데이터를 일치시키기 위해 사용하는 옵션(CASCADE 대신 SET NULL과 NO ACTION도 있음)

10. 테이블 목록 보기

SHOW TABLES

CRUD 작업하기

1. CRUD

Create, Read, Update, Delete의 두문자어

• 데이터베이스에서 많이 하는 작업 4가지
  

2. Create

INSERT INTO 테이블 (컬럼명들) VALUES (값들)

3. Read

SELECT 컬럼 FROM 테이블명
SELECT * 은 모든 컬럼을 선택한다는 의미

• 컬럼만 따로 추리는 것도 가능
  

4. Read 옵션들

WHERE로 조건을 주어 선택 가능

• AND로 여러가지 조건을 동시에 만족하는 것을 찾음

• OR로 여러가지 조건 중 하나 이상을 만족하는 것을 찾음

5. 정렬해서 찾기

ORDER BY로 특정 컬럼 값 순서대로 정렬 가능

• DESC는 내림차순, ASC 오름차순

6. LIMIT, OFFSET

LIMIT으로 조회할 개수 제한

OFFSET으로 앞의 로우들 스킵 가능(OFFSET 2면 세 번째 것부터 찾음)

7. Update

데이터베이스에 있는 데이터를 수정하는 작업

• UPDATE 테이블명 SET 컬럼=새값 WHERE 조건

8. Delete

데이터베이스에 있는 데이터를 삭제하는 작업

• DELETE FROM 테이블명 WHERE 조건

시퀄라이즈 사용하기

1. 시퀄라이즈 ORM

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

• ORM: Object Relational Mapping: 객체와 데이터를 매핑(1대1 짝지음)
• MySQL 외에도 다른 RDB(Maria, Postgre, SQLite, MSSQL)와도 호환됨
• 자바스크립트 문법으로 데이터베이스 조작 가능

👉참고
https://github.com/ZeroCho/nodejs-book/tree/master/ch6/6.5.2/learn-express

• 프로젝트 세팅 후, 콘솔을 통해 경로로 이동한 후 package.json 작성
  🔻packet.json

{
  "name": "npmtest",
  "version": "1.0.0",
  "description": "저를 읽어주세요.",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "seokahi",
  "license": "MIT"
}

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

시퀄라이즈 명령어 사용하기 위해 sequelize-cli 설치

• mysql2는 MySQL DB가 아닌 드라이버(Node.js와 MySQL을 이어주는 역할)
  
  npx sequelize init으로 시퀄라이즈 구조 생성
  

3. models/index.js 수정

다음과 같이 수정
require(../config/config) 설정 로딩
new Sequelize(옵션들…)로 DB와 연결 가능

🔻models/index.js

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;

User.init(sequelize);
Comment.init(sequelize);

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

module.exports = db;

4. MySQL 연결하기

app.js 작성
sequelize.sync로 연결

🔻app.js

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

const { sequelize } = require('./models');
const indexRouter = require('./routes');
const usersRouter = require('./routes/users');
const commentsRouter = require('./routes/comments');

const app = express();
app.set('port', process.env.PORT || 3001);
app.set('view engine', 'html');
nunjucks.configure('views', {
  express: app,
  watch: true,
});
sequelize.sync({ force: false })
  .then(() => {
    console.log('데이터베이스 연결 성공');
  })
  .catch((err) => {
    console.error(err);
  });

app.use(morgan('dev'));
app.use(express.static(path.join(__dirname, 'public')));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/comments', commentsRouter);

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

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

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

5. config.json 설정하기

DB 연결 정보를 넣기
🔻config/config.json

{
  "development": {
    "username": "root",
    "password": "nodejsbook",
    "database": "nodejs",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

6. 연결 테스트하기

npm start로 실행해서 SELECT 1+1 AS RESULT가 나오면 연결 성공

7. 모델 생성하기

테이블에 대응되는 시퀄라이즈 모델 생성
🔻models/user.js

const Sequelize = require('sequelize');

module.exports = class User extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      name: {
        type: Sequelize.STRING(20),
        allowNull: false,
        unique: true,
      },
      age: {
        type: Sequelize.INTEGER.UNSIGNED,
        allowNull: false,
      },
      married: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
      },
      comment: {
        type: Sequelize.TEXT,
        allowNull: true,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW,
      },
    }, {
      sequelize,
      timestamps: false,
      underscored: false,
      modelName: 'User',
      tableName: 'users',
      paranoid: false,
      charset: 'utf8',
      collate: 'utf8_general_ci',
    });
  }

  static associate(db) {
    db.User.hasMany(db.Comment, { foreignKey: 'commenter', sourceKey: 'id' });
  }
};

8. 모델 옵션들

시퀄라이즈 모델의 자료형은 MySQL의 자료형과 조금 다름
define 메서드의 세 번째 인자는 테이블 옵션

• timestamps: true면 createdAt(생성 시간), updatedAt(수정 시간) 컬럼을 자동으로 만듦
• 예제에서는 직접 created_at 컬럼을 만들었으므로 false로 함
• paranoid 옵션은 true면 deletedAt(삭제 시간) 컬럼을 만듦, 로우 복구를 위해 * 완전히 삭제하지 않고 deletedAt에 표시해둠
• underscored 옵션은 캐멀케이스로 생성되는 컬럼을 스네이크케이스로 생성
• modelName은 모델 이름, tableName 옵션은 테이블 이름을 설정
• charset과 collate는 한글 설정을 위해 필요(이모티콘 넣으려면 utf8mb4로)
  

9. 댓글 모델 생성하기

🔻models.comment.js

const Sequelize = require('sequelize');

module.exports = class Comment extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      comment: {
        type: Sequelize.STRING(100),
        allowNull: false,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: true,
        defaultValue: Sequelize.NOW,
      },
    }, {
      sequelize,
      timestamps: false,
      modelName: 'Comment',
      tableName: 'comments',
      paranoid: false,
      charset: 'utf8mb4',
      collate: 'utf8mb4_general_ci',
    });
  }

  static associate(db) {
    db.Comment.belongsTo(db.User, { foreignKey: 'commenter', targetKey: 'id' });
  }
};

🔻models/user.js

const Sequelize = require('sequelize');

module.exports = class User extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      name: {
        type: Sequelize.STRING(20),
        allowNull: false,
        unique: true,
      },
      age: {
        type: Sequelize.INTEGER.UNSIGNED,
        allowNull: false,
      },
      married: {
        type: Sequelize.BOOLEAN,
        allowNull: false,
      },
      comment: {
        type: Sequelize.TEXT,
        allowNull: true,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.NOW,
      },
    }, {
      sequelize,
      timestamps: false,
      underscored: false,
      modelName: 'User',
      tableName: 'users',
      paranoid: false,
      charset: 'utf8',
      collate: 'utf8_general_ci',
    });
  }

  static associate(db) {
    db.User.hasMany(db.Comment, { foreignKey: 'commenter', sourceKey: 'id' });
  }
};

10. 댓글 모델 활성화하기

index.js에 모델 연결

• init으로 sequelize와 연결
• associate로 관계 설정
  

11. 관계 정의하기

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

• 1:N 관계 (사용자 한 명이 댓글 여러 개 작성)
• 시퀄라이즈에서는 1:N 관계를 hasMany로 표현(사용자.hasMany(댓글))
• 반대의 입장에서는 belongsTo(댓글.belongsTo(사용자))
• belongsTo가 있는 테이블에 컬럼이 생김(댓글 테이블에 commenter 컬럼)
  

12. 1대1 관계

1대1 관계

• 예) 사용자 테이블과 사용자 정보 테이블
  
  

13. N대M 관계

다대다 관계

• 예) 게시글과 해시태그 테이블
• 하나의 게시글이 여러 개의 해시태그를 가질 수 있고 하나의 해시태그가 여러 개의 게시글을 가질 수 있음
• DB 특성상 다대다 관계는 중간 테이블이 생김
  

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

윗 줄이 SQL문, 아랫 줄은 시퀄라이즈 쿼리(자바스크립트)

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

윗 줄이 SQL문, 아랫 줄은 시퀄라이즈 쿼리(자바스크립트)
특수한 기능들인 경우 Sequelize.Op의 연산자 사용(gt, or 등)

1: true

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

윗 줄이 SQL문, 아랫 줄은 시퀄라이즈 쿼리(자바스크립트)

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

수정

삭제

18. 관계 쿼리

결과값이 자바스크립트 객체임

include로 JOIN 과 비슷한 기능 수행 가능(관계 있는 것 엮을 수 있음)

다대다 모델은 다음과 같이 접근 가능

19. 관계 쿼리

get+모델명으로 관계 있는 데이터 로딩 가능

as로 모델명 변경 가능

20. 관계 쿼리

include나 관계 쿼리 메서드에도 where나 attributes

👉생성 쿼리

21. 관계 쿼리

여러 개를 추가할 때는 배열로 추가 가능

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

22. raw 쿼리

직접 SQL을 쓸 수 있음

23. 쿼리 수행하기

프론트 코드보다는 서버 코드 위주로 보기

• 프론트 코드는 서버에 요청을 보내는 AJAX 요청 위주로만 파악

🔻routes/index.js

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

const router = express.Router();

router.get('/', async (req, res, next) => {
  try {
    const users = await User.findAll();
    res.render('sequelize', { users });
  } catch (err) {
    console.error(err);
    next(err);
  }
});

module.exports = router;

24. 쿼리 수행하기

users 라우터

• get, post, delete, patch 같은 요청에 대한 라우터 연결
• 데이터는 JSON 형식으로 응답
• comments 라우터도 마찬가지

🔻routes/users.js

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 (err) {
      console.error(err);
      next(err);
    }
  });

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;

25. 쿼리 수행하기

comments 라우터

🔻routes/comment.js

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;

26. 서버 접속하기

npm start로 서버 시작

• localhost:3000 으로 접속하면 시퀄라이즈가 수행하는 SQL문이 콘솔에 찍힘

27. 서버 접속하기

😃출처😃
Node.js 교과서 - 기본부터 프로젝트 실습까지
https://www.inflearn.com/course/%EB%85%B8%EB%93%9C-%EA%B5%90%EA%B3%BC%EC%84%9C/dashboard