[노드 2팀] #8. MySQL

🌟키워드: 데이터베이스, MySQL, CRUD, Sequelize, 모델

---

1. 데이터베이스

- 데이터베이스: 관련성을 가지며 중복이 없는 데이터들의 집합

- DBMS: 데이터베이스 관리 시스템

- RDBMS(Relational DBMS): 관계형 DBMS로 Oracle, MySQL, MSSQL 등이 있다.

2. MySQL 설치하기

※ 운영체제에 따라 책을 참고해 설치

 

- MySQL 명령 프롬프트로 접속

설치된 폴더 경로로 이동 → (콘솔) mysql -h localhost -u root -p 입력

3. 워크벤치 설치하기

https://dev.mysql.com/downloads/workbench/

4. 데이터베이스 및 테이블 생성하기

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

MySQL 프롬프트에 접속해 → ex) CREATE SCHEMA 'nodejs' DEFAULT CHARACTER SET utf8mb4 DEFA
ULT COLLATE utf8mb4_general_ci;  입력

- 한글과 이모티콘 사용을 위해 DEFAULT CHARACTER SET utf8mb4 DEFA
ULT COLLATE utf8mb4_general_ci

- SQL구문을 입력한 후 마지막에 세미콜론(;) 붙여주기

- 데이터베이스 사용: use [데이터베이스 명];

 

    2. 테이블 생성하기

//예시 - 사용자 테이블
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 = '사용자 정보'
    -> ENGINE = InnoDB;

※오타 주의

 

- 테이블 생성: CREATE TABLE [데이터베이스명.테이블명];

- 아래에 한 줄씩 콤마로 구분해 컬럼(세로줄)을 만든다.

- 자료형: INT(정수형), VARCHAR(자릿수), TINYINT, TEXT(긴 글 저장 시), DATETIME(날짜와 시간) 등

- 컬럼 설정: NOT NULL(빈칸 허용하지 않음), NULL(빈칸 허용), UNSIGNED(음수 무시), AUTO_INCREMENT(숫자를 저절로 올림), ZEROFILL(숫자의 자릿수가 고전되어 있을 때-비어있는 자리에 모두 0), DEFAULT(기본값), PRIMARY KEY(기본키-고유한 식별자), UNIQUE INDEX(해당 값이 고유해야 함) 등의 옵션

- 테이블 자체에 대한 설정: COMMENT(테이블 보충 설명), ENGINE

 

- 테이블 확인 명령: DESC [테이블명];

- 테이블 제거 명령: DROP TABLE [테이블명];

 

- 다른 테이블의 기본키를 저장하는 컬럼: 외래키(foreign key)

CONSTRAINT [제약조건명] FOREIGN KEY [컬럼명] REFERENCES [참고하는 컬럼명]

- CASCADE: 사용자 정보가 수정되거나 삭제되면 그것과 연결된 댓글 정보도 같이 수정하거나 삭제

- 테이블 확인: SHOW TABLES;

5. CRUD 작업하기

• CREATE(생성)
  - 테이블에 데이터 넣기
  INSERT INTO [테이블명] ([컬럼1], [컬럼2], ..) VALUES ([값1], [값2],.. )
  ex) INSERT INTO nodejs.users (name, age, married, comment) VALUES ('hh', 24, 0, '자기소개1'); 
• Read(조회)
  - SELECT * FROM [테이블명];
  * 대신 특정 컬럼을 넣어 조회 가능
  
  - WHERE 절을 사용해 특정 조건을 가진 데이터 조회 가능
  ex) select name, age from nodejs.users where married =1 and age>30;
  
  - 정렬: ORDER BY [컬럼명] [ASC|DESC];
  ex) SELECT id, name FROM nodejs.users ORDER BY age DESC;
  - 조회할 로우 개수 설정 'LIMIT [숫자]'를 끝에 붙여주기
  - OFFSET [건너뛸 숫자]
• Update(수정)
  - UPDATE [테이블명] SET [컬럼명=바꿀 값] WHERE [조건];
  - 조건 AND, OR 사용해서 여러 개 가능
• Delete(삭제)
  - DELETE FROM [테이블명] WHERE [조건];
  - 조건 AND, OR 사용해서 여러 개 가능

※ 워크벤치 사용 방법은 책 참고

6. 시퀄라이즈 사용하기

시퀄라이즈(Sequelize)

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

- ORM(자바스크립트 객체와 데이터베이스의 릴레이션을 매핑해 주는 도구)으로 분류

- 자바스크립트 구문을 알아서 SQL로 바꿔준다.

//시퀄라이즈에 필요한 패키지 설치
npm i express morgan numjucks sequelize sequelize-cli mysql2
npm i -D nodemon
//설치 완료 후 
npx sequelize init

→ 위의 명령어 실행하면 config, models, migrations, seeders 폴더 생성

 

① MySQL 연결하기

- app.js 생성하고 → 익스프레스와 시퀄라이즈 연결 코드 작성

//apps.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'), '번 포트에서 대기 중');
});

//config/config.json-> 개인의 설정에 맞게 수정
{
  "development": {
    "username": "root",
    "password": "[password]",
    "database": "nodejs",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },

→ 위의 두 코드 수정 후 (프롬프트)npm start로 서버 실행하면 3001 포트에서 서버가 돌아간다.

 

② 모델 정의하기

MySQL에서 정의한 테이블을 시퀄라이즈에서도 정의해야 한다.

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

class User extends Sequelize.Model {
  static initiate(sequelize) {
    User.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' });
  }
};

module.exports = User;

→ 자료형, 옵션 등의 형태가 다르다. 

- 모델.init 메서드의 두 번째 인수는 테이블 옵션이다.

- sequelize: db.sequelize 객체를 넣어야 한다, static initiate 메서드의 매개변수와 연결

- timestamps, underscored, modelName, tableName, paranoid, charset과 collate(한글로 설정) 등

+ Comment 모델도 만들고 models/index.js와 연결하기

//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.User = User;
db.Comment = Comment;

User.initiate(sequelize);	//각각의 모델의 static initiate 메서드를 호출하는 것
Comment.initiate(sequelize);

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

module.exports = db;

→  db 객체에 User와 Comment 모델이 담겨있다. (즉, db 객체를 require 해서 User와 Comment 모델에 접근할 수 있다.)

 

③ 관계 정의하기

- 일대다, 일대일, 다대다 관계가 있다.

- 1:N

→ require 방식(순환 참조)은 문제가 발생할 수 있어 index.js에서 각 모델을 불러와 db 매개변수로 넘기는 방식을 취한다.

→ 다른 모델의 정보가 들어가는 테이블에는 belongsTo 사용

→ hasMany 메서드에서는 source 속성에 id, belongsTo 메서드에서는 targetKey 속성에 id를 넣는다.

- foreignKey를 따로 지정하지 않으면 '모델명+기본 키'인 컬럼이 모델에 생성된다.

- npm start 명령어로 서버를 실행하면 스스로 SQL문을 실행한다.

 

- 1:1

- hasOne 메서드 사용

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

 

- N:M

- belongsToMany 메서드 사용한다.(양쪽 모델 모두에)

- 새로운 모델이 생성된다.

→  자동으로 만들어진 모델에 접근하기 위해 db.sequelize.models.PostHashtag

 

④ 쿼리 알아보기

- 쿼리는 프로미스를 반환하므로 then을 붙여 결괏값을 받을 수 있다. 

- models 모듈에서 User 모델을 불러와 create 메서드를 사용하면 된다.

※ MySQL 자료형이 아닌 시퀄라이즈 모델에 정의한 자료형대로 넣어야 한다. (부합하지 않는 데이터를 넣었을 때 에러 발생)

- 모든 데이터 조회는 findAll 메서드 사용

- attributes 옵션(원하는 칼럼만) , where 옵션(조건), order 옵션(정렬), limit 옵션(개수 설정), offset 속성, update 메서드, destroy 메서드(로우 삭제)

- 0p 객체: 0p . gt(초과), 0p . or(또는) 등

 

• 관계 쿼리

- (현재 User 모델)

- 특정 사용자를 가져오면서 그 사람의 댓글까지 가져오고 싶다면 include 속성(include 배열에 넣어준다)을 사용한다.

- 관계 설정 후 getComments(조회), setComments(수정), addComment(하나 생성), addComments(여러 개 생성), removeComments(삭제) 메서드를 이용가능하다.

- 모델의 이름 바꾸려면 as 사용하면 된다.

- where, attributes 옵션 사용 가능하다.

→  쿼리 조회

- 추가는 아이디를 이용해 배열로 가능하다. 수정, 삭제도 마찬가지

• SQL 쿼리하기

- SQL문을 통해 쿼리가 가능하다.

 

⑤ 쿼리 알아보기

- (모델에서 데이터를 받아 페이지를 렌더링 한다, JSON 형식으로 데이터를 가져온다.) 두 가지 방식이 있다.

- views/sequelize.html, error.html

- public/sequelize.js  --- script 태그에 버튼이 눌렸을 때 서버의 라우터로 AJAX 요청을 보내는 코드 들어있다.

- app.js 

파일들을 만든다.

//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;

→ (모델에서 데이터를 받아 페이지를 렌더링) User.findAll 메서드로 모든 사용자를 찾고, 결괏값인 users를 넣는다.

- 시퀄라이즈는 프로미스를 지원하기 때문에 async/await 와 try/catch문을 사용해서 성공과 실패 시의 정보를 얻을 수 있다.

//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;

→ (JSON 형식으로 데이터를 가져온다) 사용자를 조회하는 요청과 (GET /users) 등록하는 요청 (POST /users)를 처리한다.

- where, include 옵션 사용 가능하다.

 

- routes/comments.js 파일을 추가해 댓글과 관련된 CRUD 작업을 한다.

- 파일을 모두 생성 후 npm start로 서버를 실행한다. →  http://localhost:3001 로 접속해 확인한다.

httP://localhost:3001 실행시 화면

---

Quiz

1. 관련성을 가지며 중복이 없는 데이터들의 집합을 (      )라 한다.

2. 만들어진 users 테이블을 확인하기 위한 명령어는 (          )이다.

3. 데이터베이스에서 수행하는 생성, 조회, 수정, 삭제 4가지 작업을 (    )라고 한다. 

4. 서버 실행 명령어는 (         )이다.

5. MySQL 작업을 쉽게 할 수 있도록 도와주는 라이브러리로 자바스크립트 구문을 스스로 SQL로 바꿔주는 것을 (     )라고 한다. 

6. 시퀄라이즈에서 hasMany메서드로 표현하는 관계는 (   )이다.

7. 쿼리를 수행할 때 모델에서 데이터를 받아 페이지를 렌더링 하는 방법과 (    ) 형식으로 데이터를 가져오는 방법 두 가지가 있다.

Programming Quiz

8. 아래의 테이블(users)에 2개의 데이터 생성을 위한 명령어를 작성하시오.

 

9. 라우터를 만드는 파일이다. 빈칸을 채우시오.

//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.(1.     )();	//메서드를 이용해 모든 사용자 찾기
    res.render('sequelize', { (2.    ) });	//sequelize.html을 렌더링할 때 결과 값
  } catch (err) {
    console.error(err);
    next(err);
  }
});

module.exports = (3     );	//다른 파일에서 사용할 수 있도록 내보냄

---

Answer

1. 데이터베이스

2. DESC users;

3. CRUD

4. npm start

5. 시퀄라이즈

6. 1:N

7. JSON

8.

mysql(프롬프트)> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('hh', 21, 0, '자기소개1');

mysql(프롬프트)> INSERT INTO nodejs.users (name, age, married, comment) VALUES ('dd', 25, 0, '자기소개12');

 

9.

//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.(1. findAll )();	
    res.render('sequelize', { (2. users ) });	
  } catch (err) {
    console.error(err);
    next(err);
  }
});

module.exports = (3. router );

 

출처) 조현정, 「Node.js 교과서 개정 3판」, 길벗(2022), 6장

 

Corner node.js 2팀

Editor : Igumi