nodejs에서 mysql 사용하기(mysql, mysql2 모듈)

1. mysql 모듈 (현재 권장되지 않음)

생활코딩 강의 참조: https://www.youtube.com/watch?v=udJjxv3IA4k

 

nodejs에서 mysql을 연동하고 싶다면 어떻게 해야 할까?

기본 모듈중에는 mysql을 연동할 수 있는 모듈이 없다.

하지만 누군가가 만들어 놓은 좋은 모듈이 있다!

 

사이트: https://www.npmjs.com/package/mysql

npm install mysql

위 명령어를 이용해 설치하자.

let mysql      = require('mysql');
let connection = mysql.createConnection({
  host     : 'localhost',
  port     : 3306,
  user     : 'root',
  password : 'password',
  database : 'sys'
});
 
connection.connect();
 
connection.query('SELECT * from sys_config', function (error, results, fields) {
  if (error) throw error;
  console.log('The result is: ', results);
});
 
connection.end();

간단한 예제이다.

---

mysql2 모듈이 나오며 mysql 모듈은 사용하지 않게 되었다. mysql2 모듈을 사용하자.

2. mysql2 모듈

mysql2는 mysql에서 발전된 모듈이다. 

- 빠르고 / 더 나은 성능

- Prepared Statements

- Promise Wrapper

- Pooling

- Array results

npm install mysql2

간단한 query

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

// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'dbname',
  // port: 3306,
  // password: '',
});
connection.addListener('error', (err)=>console.log(err));

// simple query
connection.query(
  'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
  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
  }
);

// with placeholder
connection.query(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Page', 45],
  function(err, results) {
    console.log(results);
  }
);

prepared statements

sql2에서는 prepared statements가 가능하다. 이 기능은 SQL injection공격을 막을 수 있는 장점이 있다. query메소드 뿐만 아니라 execute메소드를 지원하는데, execute메소드가 내부적으로 prepated statements를 사용하여 쿼리를 실행한다. SQL injection을 맏으려면 execute메소드를 사용하자.

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

// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'dbname',
  // port: 3306,
  // password: '',
});
connection.addListener('error', (err)=>console.log(err));

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

connection pools

mysql2에서는 커넥션 풀을 만들어 다중 클라이언트 요청에 대해 더 빠른 응답과 성능 향상을 제공한다. 연결을 반복적으로 생성하고 종료하는 오버헤드를 줄이고, 효율적인 리소스를 사용할 수 있다.

// 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: 'dbname',
  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,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

pool.query()또는 pool.execute()로 connection처럼 사용할 수 있다.

// For pool initialization, see above
pool.query("SELECT `field` FROM `table`", function(err, rows, fields) {
  // Connection is automatically released when query resolves
});

Promise Wrapper

mysql2는 promise api를 지원한다. ES7 async await 와 매우 잘 동작한다.

async function main() {
  // get the client
  const mysql = require('mysql2/promise');
  // create the connection
  const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'dbname',
  // port: 3306,
  // password: '',
  });
  // query database
  const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
}

connections에 .promise()함수를 사용하면 promise가 아닌 connection을 promise로 업그레이드해준다.

// get the client
const mysql = require('mysql2');
// create the connection
const con = mysql.createConnection(
  {host:'localhost', user: 'root', database: 'test'}
);
con.promise().query("SELECT 1")
  .then( ([rows,fields]) => {
    console.log(rows);
  })
  .catch(console.log)
  .then( () => con.end());

pool에 .promise()함수를 사용하면 pool의 connection을 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");
}

array results

query결과를 object의 배열로 받지 않고, 배열의 배열로 받을 수 있다. 만약 두 칼럼의 이름이 똑같다면 충돌을 피하기 위해 사용할 수 있다. connection레벨과 query레벨에서 둘다 설정 가능하다.

connection 레벨

const con = mysql.createConnection(
  { host: 'localhost', database: 'dbname', user: 'root', rowsAsArray: true }
);

query 레벨

con.query({ sql: 'select 1 as foo, 2 as foo', rowsAsArray: true }, function(err, results, fields) {
  console.log(results); // in this query, results will be an array of arrays rather than an array of objects
  console.log(fields); // fields are unchanged
});

rowAsArray 전 후. object의 key가 사라졌다.

 

참고: https://www.npmjs.com/package/mysql2