express + mysql2 라이브러리 사용기

개요

express로 만든 웹 서버에 mysql2 라이브러리를 설치하고 SQL Raw Query를 사용하여 API를 만들어봤다. 이 과정에서 겪은 에러와 해결 과정을 소개한다. (*MySQL2 라이브러리 공식 문서 참고)

mysql과 mysql2의 차이

mysql과 mysql2 둘 다 Nodejs에서 mysql을 사용할 수 있게 해주는 라이브러리다. 하지만 mysql2에서는 추가적으로 다음과 같은 기능들을 제공한다.

• Faster / Better Performance
• Prepared Statements
• MySQL Binary Log Protocol
• MySQL Server
• Extended support for Encoding and Collation
• Promise Wrapper
• Compression
• SSL and Authentication Switch
• Custom Streams
• Pooling

이 중 가장 중요한 점이라면 Promise를 지원한다는 것이다.

설치

npm install --save mysql2

데이터베이스 연결

mysql2 설치 라이브러를 설치했다면 이제 쿼리를 작성하기 전에 먼저 데이터베이스와 연결을 해줘야 한다.

연결하는 메소드로는 2가지가 있다.

1. createConnection

제일 기본적인 연결 방법으로 한 번 연결한 다음 쿼리를 실행하고 연결을 끊는다.

// get the client
const mysql = require('mysql2');

// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});

2. createPool

Connectin pool을 이용하는 방법으로, 말 그대로 pool 안에 connection을 만들어두고 사용 후에 다시 pool로 반환하는 방법이다.

단일 connection과 다르게 다시 연결하는 데 비용이 소모되지 않기 때문에 실행 속도가 빨라진다. pool은 연결 제한 수를 설정할 수 있다. 이를 필요량보다 적게 설정하면 대기 시간이 발생할 수 있고 너무 많이 설정하면 메모리 소모가 클 수 있기 때문에 사용자 수에 맞게 잘 조절해야 한다.

// get the client
const mysql = require('mysql2');

// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
  idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit: 0
});

그렇다면 둘 중 어떤 방법을 이용해서 연결해야 할까?

여러 자료들을 찾아본 결과 대체로 pool을 사용하는 것이 권장되고 있었다. connection pool을 사용해 쿼리를 수행해보기로 했다.

MySQL 쿼리를 사용할 파일에 바로 임포트를 해도 되지만 모듈을 따로 만들어주는 게 깔끔하기 때문에 util 폴더 안에 database.js 파일을 만들어 export를 했다.

// datatbase.js
const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
});

module.exports = pool.promise();

export할 때는 pool.promise()로 반환해야 promise를 사용할 수 있다.

쿼리 수행

이제 데이터베이스 연결 작업이 완료됐으니 Mysql 쿼리를 실행해보자.
쿼리를 수행할 때는 query, execute 모두 사용할 수 있는데, 차이점은 execute는 내부적으로 prepare와 query를 호출한다는 점이다.

(*execute와 query의 정확한 차이점을 알고 싶어서 찾다보니 sql의 동작 원리까지 이해해야 한다는 것을 깨닫고... 이 부분은 따로 공부해서 포스팅할 예정이다.)

sql injection 방어를 위한 prepared statements 사용

prepraed statements는 sql 쿼리에서 실제 데이터가 들어갈 자리는 ? 형태의 플레이스 홀더로 비워두고 데이터를 배열에 따로 전달하는 구문이다.

connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Rick C-137', 53],
  function(err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available

    // If you execute same statement again, it will be picked from a LRU cache
    // which will save query preparation time and give better performance
  }
);

prepared statements를 사용하는 이유는 sql injection 공격을 막기 위한 것이다.

stackoverflow의 How prepared statements can protect from SQL Injection attacks
를 보면 prepared statements가 sql injection을 방어하는 데 도움이 되는지 이해하기 쉽게 설명된 답변이 있다.

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

위와 같이 sql 쿼리에 데이터가 직접 삽입이 되면 다음과 같은 sql injection 공격이 가능해진다.

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

drop table users라는 쿼리가 삽입됨으로서 테이블 전체가 삭제될 수도 있다.

따라서 prepared statements를 사용하는 것이다.

에러 발생

회원가입 로직에서 SELECT 문으로 동일한 이메일이 있는지 확인하려는데 에러가 발생했다. 문제는 에러 메시지가 뜨지 않은 채 브라우저는 계속 동작 중인 상태였던 것.

console.log로 req.body를 확인했을 때는 입력한 데이터가 제대로 전달되는 것 같았는데, sql 쿼리문부터 문제가 발생해서 그 다음 코드로 넘어가지 못하는 것으로 예상됐다.

const db = require("../util/database");
const bcrypt = require("bcryptjs");

exports.register = (req, res) => {
  const { name, email, password, passwordConfirm } = req.body;

  // DB에 같은 이메일이 있는지 찾는다
  db.execute(
    "SELECT email from users WHERE email = ?",
    [email],
    async (error, result) => {
      if (error) {
        console.log(error);
      }

      if (result.length > 0) {
        return res.render("register", {
          message: "That email is already in use",
        });
      } else if (password !== passwordConfirm) {
        return res.render("register", {
          message: "Passwords do not match",
        });
      }

      let hashedPassword = await bcrypt.hash(password, 8);
      console.log(hashedPassword);

      res.send("testing");
    }
  );
};

원인 분석

mysql2 공식 문서를 자세히 살펴본 결과, 데이터베이스를 연결하는 모듈에서 문제를 발견했다.

데이터베이스를 연결할 때 어떤 방식으로 연결하는지에 따라 쿼리를 수행하는 부분에서 비동기를 처리하는 방식도 달라져야 한다.

createConnection - callback 방식

// execute will internally call prepare and query
connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Rick C-137', 53],
  function(err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available

    // If you execute same statement again, it will be picked from a LRU cache
    // which will save query preparation time and give better performance
  }
);

createPool - promise 방식

async function main() {
  // get the client
  const mysql = require('mysql2');
  // create the pool
  const pool = mysql.createPool({host:'localhost', user: 'root', database: 'test'});
  // now get a Promise wrapped instance of that pool
  const promisePool = pool.promise();
  // query database using promises
  const [rows,fields] = await promisePool.query("SELECT 1");

const mysql = require("mysql2");

const db = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "12345",
  database: "nodejs-mysql-login",
});

module.exports = db.promise();

앞서 설명한 것처럼 connection pool을 만들어서 promise를 반환하는 형태로 데이터베이스를 연결한 상태였다.

그런데 실제로 쿼리를 수행하는 회원가입 로직에서 보면 promise가 아닌 callback 형태로 비동기를 처리하여 문제가 발생한 것이었다!

해결 방법

데이터베이스를 다음과 같이 단일한 연결 방식으로 변경해줬더니 코드가 정상적으로 실행됐다.

const mysql = require("mysql2");

const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "12345",
  database: "nodejs-mysql-login",
});

module.exports = db;

또는 데이터베이스 연결을 connection pool과 promise를 반환하도록 두고, 다음과 같이 async/await 문법으로 변경하니 정상적으로 작동했다.

const db = require("../util/database");
const bcrypt = require("bcryptjs");

exports.register = async (req, res) => {
  const { name, email, password, passwordConfirm } = req.body;

  // DB에 같은 이메일이 있는지 찾는다
  const [userData, fields] = await db.execute(
    "SELECT email from users WHERE email = ?",
    [email]
  );

  // DB에 같은 이메일이 있으면 return "동일한 이메일이 있습니다."
  if (userData.length > 0) {
    console.log(userData);
    return res.render("register", { message: "동일한 이메일이 있습니다." });
  } else if (password !== passwordConfirm) {
    return res.render("register", { message: "비밀번호가 일치하지 않습니다." });
  }

  // 비밀번호 암호화
  const hashedPassword = await bcrypt.hash(password, 8);

  // db에 회원 정보 저장
  await db.execute(
    "INSERT INTO users (name, email, password) VALUES (?, ?, ?)",
    [name, email, hashedPassword]
  );
  return res.render("register", { message: "회원 가입 완료" });
};

배운 점

• promise와 callback의 차이를 정확히 알지 못해 생긴 에러였다.
• sql의 동작 과정을 공부해서 query와 execute의 차이를 정확하게 이해해보자.