#1. MySQL 연동하기

1. MySQL에 사용자 계정 추가하기

1) 계정을 추가하는 이유

  • root는 시스템의 최고 관리자 아이디 이기 때문에 프로그램이 하나의 데이터베이스에 접근할 때 root 계정으로 접근하는 것은 바람직하지 않는다.
  • 프로그램의 보안이 취약해지면 시스템의 최고 관리자 계정을 그대로 노출하기 때문이다.
  • 그렇기 때문에 서비스 하나가 구상 되면 데이터베이스도 하나가 만들어지고, 해당 데이터베이스에만 접근하는 전용 사용자를 생성해야 한다.

 

2) 계정 생성하기

create user '아이디'@'접근용호스트' identified by '비밀번호';

# 사용예시
create user 'testid'@'localhost' identified by '12341234';
# localhost와 ::1은 같은 역할을 한다.
  • 계정 생성은 mysql에 root로 로그인 한 상태에서만 가능하다.
  • 보통 아이디는 사용하고자 하는 데이터베이스의 이름과 동일하게 맞춘다.
  • 접근 허용 호스트는 mysql에 접속 가능한 node.js가 구동 중인 머신의 IP주소이다.
  • node.js와 mysql이 같은 머신에 설치되어 있는 경우 localhost라고 기입한다.
  • 서로 다른 머신에 설치되어 있는 경우 접속 출발지의 IP주소(Node.js 설치 장비)를 기입한다.
  • 접근 허용 호스트를 '%'로 지정할 경우 모든 곳에서의 접근을 허용하게 된다.
  • 외부에서 접속 할 경우 mysql이 설치된 운영체제 자체의 방화벽 설정에 따라 접근이 차단될 수 있다.

 

3) 데이터베이스에 대한 권한 부여

grant all privileges on 데이터베이스이름.* to '아이디'@'접근허용호스트';

# 사용예시
grant all privileges on myschool.* to 'testid'@'localhost';
  • 데이터베이스 이름 뒤의 ' * '은 허용하고자 하는 테이블을 의미한다. (*은 여기서는 모든 테이블)

 

2. 환경설정 하기

1) 환경설정 파일 생성

  • 주로 보안이 필요한 내용을 별도의 파일에 작성한 후 프로젝트 root 이외의 경로에 별도로 보관하는 형태로 활용한다

-config.env

# 데이터베이스 환경설정
DATABASE_HOST = localhost	  # MySQL 서버 주소 (다른 PC인 경우 IP주소)
DATABASE_PORT = 3306		  # MySQL 포트번호 (기본값이 3306)
DATABASE_USERNAME = testid	  # MySQL에 로그인 할 수 있는 계정 이름
DATABASE_PASSWORD = 12341234      # 비밀번호
DATABASE_SCHEMA = myschool	  # 사용하고자 하는 데이터베이스 이름

 

2) 환경설정 불러오기

  • dotenv 패키지 설치하기
yarn add dotenv
  • 설정 파일 내용 가져오기
dotenv.config('config.env 파일 위치');
  • 호출
process.env.설정이름

 

3. MySQL 연동하기

1) 기본설정

  • mysql2 패키지 설치
yarn add mysql2
  • 필요한 모듈 및 환경설정 불러오기
/** (1) 모듈 및 환경설정 불러오기 */
import { join, resolve } from 'path';
import dotenv from 'dotenv';
import mysql from 'mysql2';

// 설정 파일 내용 가져오기
// 현재폴더(resolve)의 상위폴더(../)에 있는 config.env
dotenv.config({path: join(resolve(), '../config.env')});

// 접속 정보 설정
const connectionInfo = {
  host: process.env.DATABASE_HOST,          // MYSQL 서버 주소 (다른 PC인 경우 IP주소)
  port: process.env.DATABASE_PORT,          // MYSQL 포트번호
  user: process.env.DATABASE_USERNAME,      // MYSQL의 로그인 할 수 있는 계정 이름
  password: process.env.DATABASE_PASSWORD,  // 비밀번호
  database: process.env.DATABASE_SCHEMA     // 사용하고자 하는 데이터베이스 이름
};

 

2) MySQL 접속 객체 생성

/** (2) mysql 접속 객체 생성 */
const dbcon = mysql.createConnection(connectionInfo);
  • mysql 패키지의 createConnection으로 접속 객체를 생성한다.

 

3) 데이터베이스 접속

/** (3) 데이터베이스 접속 */
dbcon.connect((err) => {
  // 이 안에서 에러, 구문, 실행 처리한다.
});
  • 생성한 접속 객체에서 connect를 호출하면 접속한다.
  • 모든 처리가 비동기 이기 때문에 접속이 성공하면 SQL 수행한다.
    • 접속이 완료 되었다는 콜백함수안에서 처리

 

4) 쿼리 생성 및 실행

dbcon.connect((err) => {
  // 에러 발생시
  if(err) {
    console.error('데이터베이스 접속에 실패했습니다.');
    console.error(err);
    return;
  };


  /** (4) INSERT, UPDATE, DELETE 쿼리 생성하기 */
  // 실행할 SQL 구문
  // Node의 변수로 치환될 부분 (주로 저장, 수정될 값)은 '?'로 지정
  // 문자열이라더라도 홑따옴표 사용 안함
  const sql = "INSERT INTO department (dname, loc) VALUES (?, ?)";
  // SQL문의 '?'를 치환할 배열 -> ? 순서대로 값을 지정한다.
  const input_data = ['테스트학과', '1관'];


  /** (5) SQL 실행하기 */
  dbcon.query(sql, input_data, (error, result) => {
    // 이 에러가 감지되는 경우는 SQL문이 잘못 구성되어 MYSQL에서 에러가 발생한 경우
    if(error) {
      console.log('SQL문 실행에 실패했습니다.');
      console.log(error);
      dbcon.end();  // 데이터베이스 접속 해제 (*중요*)
      return;
    };

    // 저장결과 확인
    console.log('반영된 데이터의 수: ' + result.affectedRows);
    // UPDATE, DELETE 쿼리의 경우 사용할 수 없는 값임
    console.log('생성된 PK값: ' + result.insertId);
    // 데이터베이스 접속 해제 (*중요*)
    dbcon.end();
  });
});
  • 4번에서 값이 들어갈 부분(값을 치환해서 들어갈 부분)은 물음표(?)를 사용한다.
    • 물음표에 배열로 값을 대입한다. (순서대로)
  • 5번에서 생성한 접속 객체에 query를 호출하여 안에 sql문과 물음표를 치환할 배열값을 넣으면 sql문이 수행된다.
    • sql문, 배열값, function(에러값, 결과값)
    • sql injection에 대한 방어도 자동으로 수행된다.
  • 만약 에러가 났다면 데이터베이스를 해제(dbcon.end();)하고 중단.
    • 모든 처리가 정상적으로 끝나도 데이터베이스 해제는 가장 중요하다.

 

MySQL에서 확인하면 테스트학과 데이터가 추가된 것을 볼 수 있다.

 

4. pool

  • 데이터베이스를 연동할 때 시스템에 부담을 가장 많이 주는 처리가 접속과 접속 해제이다.
  • 접속해제를 안하고 접속만 계속 쌓이면 시스템이 다운된다. (접속 최대 수 약 200개)
    • 그렇기 때문에 지속적으로 접속을 해제 해줘야 한다.
    • 그래서 나온 기술이 Connection pool 이다.

1) pool이란?

  • Node.js가 mysql에 가동 하자마자 접속을 미리 한다. (시스템 성능에 따라 접속 수 조절)
  • 프론트엔드가 접속하면 노드에서 미리 접속한 것중 하나를 임대해서 sql 처리하고 다시 반납하는 구조이다. (접속해제 없음)
  • 데이터베이스에 대한 성능을 향상시키는 것이 목적이다.

 

2) 환경설정 추가

- congif.env

# 데이터베이스 환경설정
DATABASE_HOST = localhost	  # MySQL 서버 주소 (다른 PC인 경우 IP주소)
DATABASE_PORT = 3306		  # MySQL 포트번호 (기본값이 3306)
DATABASE_USERNAME = testid	  # MySQL에 로그린 할 수 있는 계정 이름
DATABASE_PASSWORD = 12341234      # 비밀번호
DATABASE_SCHEMA = myschool	  # 사용하고자 하는 데이터베이스 이름

# 기존 환경설정된 부분에서 추가
DATABASE_CONNECTION_LIMIT = 10			# 최대 커넥션 수
DATABASE_CONNECT_TIMEOUT = 2000			# 커넥션 타임아웃(접속 시간 제한)
DATABASE_WAIT_FOR_CONNECTIONS = true		# 커넥션 pool이 다 찬 경우 처리 (true, 기다림)

 

3) 기본 설정

/** (1) 모듈 및 환경설정 불러오기 */
import { join, resolve } from 'path';
import dotenv from 'dotenv';
import mysql2 from 'mysql2/promise';

// 설정 파일 내용 가져오기
dotenv.config({path: join(resolve(), '../config.env')});

// 접속 정보 설정
const connectionInfo = {
  host: process.env.DATABASE_HOST,          // MYSQL 서버 주소 (다른 PC인 경우 IP주소)
  port: process.env.DATABASE_PORT,          // MYSQL 포트번호
  user: process.env.DATABASE_USERNAME,      // MYSQL의 로그인 할 수 있는 계정 이름
  password: process.env.DATABASE_PASSWORD,  // 비밀번호
  database: process.env.DATABASE_SCHEMA,    // 사용하고자 하는 데이터베이스 이름
  connectionLimit: process.env.DATABASE_CONNECTION_LIMIT,         // 최대 커넥션 수
  connectTimeout: process.env.DATABASE_CONNECT_TIMEOUT,           // 커넥션 타임아웃
  waitForConnections: process.env.DATABASE_WAIT_FOR_CONNECTIONS,  // 커넥션 풀이 다 찬 경우 처리
};

 

4) 객체 생성

/** (2) mysql 모듈 객체 생성 및 접속 정보 설정 */
const pool = mysql2.createPool(connectionInfo);
  • mysql 패키지의 createPool로 접속 객체를 생성한다.

 

※ createPool 객체가 지원하는 이벤트

// 접속
pool.on('connection', (connection) => {
  console.debug('>> Database 접속됨 [threaId] = %d', connection.threadId);
});

// 몇 번째 항목이 임대가 됐는지
pool.on('acquire', (connection) => {
  console.debug('>> Connection 임대됨 [threaId] = %d', connection.threadId);
});

// 진행 상태
pool.on('enqueue', () => {
  console.debug('>> 접속이 진행중이거나 모두 임대되어 반납을 기다리는 중..');
});

// 반납
pool.on('release', (connection) => {
  console.debug('>> Connection 반납됨 [threaId] = %d', connection.threadId);
});

 

5) 임대하고 반납하기

(async () => {
  let dbcon = null;

  /** (4) 커넥션 풀에서 접속객체 하나를 임대함 */
  // 에러가 발생하거나 사용이 종료된 경우 반드시 임대한 접속객체를 반납해야 한다.
  try {
    dbcon = await pool.getConnection();
  } catch(e) {
    console.error('접속객체 임대에 실패했습니다.');
    console.error(e);

    // 임대한 자원이 있다면 반드시 반납해야 함.
    if(dbcon) dbcon.release();

    // Connection Pool 접속 해제 (실 시스템에서는 사용할 일 없음)
    pool.end();
    return;
  }


  /** (5) 정상 접속이 됐다면 SQL문 실행하기 */
  const sql = 'SELECT * FROM professor WHERE deptno=?';
  const input_data = ['101'];

  try {
    // sql문 처리
    const [result] = await dbcon.query(sql, input_data);

    result.map((v,i) => {
      console.log(`${v.name}, ${v.position}\n 급여: ${v.sal}\n 입사일: ${v.hiredate}\n`);
    });
  } catch(e) {
    console.error('SQL문 수행에 실패했습니다.');
    console.error(e);
    return;
  } finally {
    // 임대한 접속 객체 반납
    if(dbcon) dbcon.release();
    // Connection Pool 접속 해제 (실 시스템에서는 사용할 일 없음)
    pool.end();
  }
})();
  • 4번에서 getConnection 객체를 호출하면 생성한 커넥션 중 한개를 빌린다.
    • getConnection( ) 으로 빌리고 release( )로 반납.
저작자표시 비영리 변경금지

'국비수업 > Node.js' 카테고리의 다른 글

[Node.js] CORS / MVC 패턴  (0) 2022.07.10
[Node.js] 객체지향 데이터베이스 프로그래밍 (Mybatis)  (0) 2022.07.07
[Node.js] SingleTon 패턴  (0) 2022.07.06
[Node.js/express] File Upload  (0) 2022.07.04
[Node.js/express] 메일보내기  (0) 2022.07.01

+ Recent posts

티스토리툴바