삵
Amazon Web Services

node.js와 MariaDB 연결하기 (조회 테스트)

sstdio.h·2018년 11월 29일·조회 8,116

1. 개요

Node.js에서 mysql 모듈을 사용해 MySQL에 접속하고, 커넥션 풀을 통해 쿼리를 실행하는 간단한 예제이다. DB 접속 정보, 커넥션 생성, SQL 실행 파일을 분리해서 구성한다.

예제에서는 information_schema.tables를 조회해 현재 DB 서버에서 확인 가능한 테이블 목록을 출력한다.

2. 사전 작업

mysql 모듈을 설치한다.

$ npm install mysql
mysql@2.16.0 node_modules/mysql
├── safe-buffer@5.1.2
├── sqlstring@2.3.1
├── bignumber.js@4.1.0
└── readable-stream@2.3.6 (process-nextick-args@2.0.0, inherits@2.0.3, string_decoder@1.1.1, util-deprecate@1.0.2, core-util-is@1.0.2, isarray@1.0.0)

설치 여부를 확인한다.

$ ls -l node_modules
total 4
drwxrwxr-x 4 ec2-user ec2-user 4096 Nov 29 14:44 mysql

3. 파일 및 디렉터리 구조

테스트를 위해 다음과 같은 디렉터리 구조를 생성하였다.

  • db 디렉터리: DB 접속 정보와 커넥션 풀 관리
  • sql 디렉터리: 실행할 SQL 로직
  • execute.js 파일: 실제 실행 진입점
.
├── db
│   ├── info.js
│   └── connect.js
├── sql
│   └── select.js
└── execute.js

4. db 디렉터리 하위

4-1. info.js

DB 연결에 필요한 정보를 별도 파일로 분리한다. 실제 사용 시에는 아래 문자열을 본인의 DB 환경에 맞게 수정한다.

module.exports = (function() {
  return {
    host: "DB연결정보(URL)",
    user: "계정이름",
    password: "패스워드",
    database: "DB이름"
  }
})();

4-2. connect.js

mysql.createPool을 사용해 커넥션 풀을 생성한다. 매번 새 커넥션을 직접 만들기보다 풀에서 커넥션을 받아 사용하고, 쿼리 실행이 끝나면 다시 반환하는 방식이다.

var mysql = require('mysql');

module.exports = function () {
  var config = require('../db/info');
  var pool = mysql.createPool({
    host: config.host,
    user: config.user,
    password: config.password,
    database: config.database
  });

  return {
    getConnection: function (callback) {
      pool.getConnection(callback);
    },
    end: function(callback){
      pool.end(callback);
    }
  }
}();

5. sql 디렉터리 하위

5-1. select.js

커넥션 풀에서 커넥션을 하나 가져와 SQL을 실행한다. 커넥션을 가져오는 과정에서 에러가 발생할 수 있으므로, getConnection의 에러를 먼저 확인하는 것이 안전하다. 또한 쿼리 실행 후에는 con.release()로 커넥션을 풀에 반환한다.

var pool = require('../db/connect');

module.exports = function () {
  return {
    select: function(callback){
      pool.getConnection(function(err, con){
        if (err) return callback(err);

        var sql = 'SELECT table_name FROM information_schema.tables';
        con.query(sql, function (err, result, fields) {
          con.release();
          if (err) return callback(err);
          callback(null, result);
        });
      });
    },
    pool: pool
  }
};

6. execute.js

select.js에서 정의한 select 함수를 실행하고, 모든 작업이 끝난 뒤 커넥션 풀을 종료한다.

var sql = require('./sql/select')();

console.log('** Started');

sql.select(function(err, data){
  if (err) console.log(err);
  else console.log(data);

  sql.pool.end(function(err){
    if (err) console.log(err);
    else {
      console.log('** Finished');
    }
  });
});

7. 실행 결과

실행하면 information_schema.tables 조회 결과가 배열 형태로 출력된다. 출력되는 테이블 목록은 MySQL/MariaDB 종류, 버전, 권한, RDS 사용 여부 등에 따라 달라질 수 있다.

$ node execute.js
** Started
[ RowDataPacket { table_name: 'ALL_PLUGINS' },
  RowDataPacket { table_name: 'APPLICABLE_ROLES' },
  RowDataPacket { table_name: 'CHARACTER_SETS' },
  RowDataPacket { table_name: 'COLLATIONS' },
  RowDataPacket { table_name: 'COLLATION_CHARACTER_SET_APPLICABILITY' },
  RowDataPacket { table_name: 'COLUMNS' },
  RowDataPacket { table_name: 'COLUMN_PRIVILEGES' },
  RowDataPacket { table_name: 'ENABLED_ROLES' },
  RowDataPacket { table_name: 'ENGINES' },
  RowDataPacket { table_name: 'EVENTS' },
  RowDataPacket { table_name: 'FILES' },
  RowDataPacket { table_name: 'GLOBAL_STATUS' },
  RowDataPacket { table_name: 'GLOBAL_VARIABLES' },
  RowDataPacket { table_name: 'KEY_CACHES' },
  RowDataPacket { table_name: 'KEY_COLUMN_USAGE' },
  RowDataPacket { table_name: 'PARAMETERS' },
  RowDataPacket { table_name: 'PARTITIONS' },
  RowDataPacket { table_name: 'PLUGINS' },
  RowDataPacket { table_name: 'PROCESSLIST' },
  RowDataPacket { table_name: 'PROFILING' },
  RowDataPacket { table_name: 'REFERENTIAL_CONSTRAINTS' },
  RowDataPacket { table_name: 'ROUTINES' },
  RowDataPacket { table_name: 'SCHEMATA' },
  RowDataPacket { table_name: 'SCHEMA_PRIVILEGES' },
  RowDataPacket { table_name: 'SESSION_STATUS' },
  RowDataPacket { table_name: 'SESSION_VARIABLES' },
  RowDataPacket { table_name: 'STATISTICS' },
  RowDataPacket { table_name: 'SYSTEM_VARIABLES' },
  RowDataPacket { table_name: 'TABLES' },
  RowDataPacket { table_name: 'TABLESPACES' },
  RowDataPacket { table_name: 'TABLE_CONSTRAINTS' },
  RowDataPacket { table_name: 'TABLE_PRIVILEGES' },
  RowDataPacket { table_name: 'TRIGGERS' },
  RowDataPacket { table_name: 'USER_PRIVILEGES' },
  RowDataPacket { table_name: 'VIEWS' },
  RowDataPacket { table_name: 'GEOMETRY_COLUMNS' },
  RowDataPacket { table_name: 'SPATIAL_REF_SYS' },
  RowDataPacket { table_name: 'CLIENT_STATISTICS' },
  RowDataPacket { table_name: 'INDEX_STATISTICS' },
  RowDataPacket { table_name: 'INNODB_SYS_DATAFILES' },
  RowDataPacket { table_name: 'TABLE_STATISTICS' },
  RowDataPacket { table_name: 'INNODB_SYS_TABLESTATS' },
  RowDataPacket { table_name: 'USER_STATISTICS' },
  RowDataPacket { table_name: 'INNODB_SYS_INDEXES' },
  RowDataPacket { table_name: 'XTRADB_RSEG' },
  RowDataPacket { table_name: 'INNODB_CMP_PER_INDEX' },
  RowDataPacket { table_name: 'INNODB_TRX' },
  RowDataPacket { table_name: 'CHANGED_PAGE_BITMAPS' },
  RowDataPacket { table_name: 'INNODB_FT_BEING_DELETED' },
  RowDataPacket { table_name: 'INNODB_LOCK_WAITS' },
  RowDataPacket { table_name: 'INNODB_LOCKS' },
  RowDataPacket { table_name: 'INNODB_TABLESPACES_ENCRYPTION' },
  RowDataPacket { table_name: 'XTRADB_INTERNAL_HASH_TABLES' },
  RowDataPacket { table_name: 'INNODB_SYS_FIELDS' },
  RowDataPacket { table_name: 'INNODB_CMPMEM_RESET' },
  RowDataPacket { table_name: 'INNODB_CMP' },
  RowDataPacket { table_name: 'INNODB_FT_INDEX_TABLE' },
  RowDataPacket { table_name: 'INNODB_SYS_TABLESPACES' },
  RowDataPacket { table_name: 'INNODB_MUTEXES' },
  RowDataPacket { table_name: 'INNODB_BUFFER_PAGE_LRU' },
  RowDataPacket { table_name: 'INNODB_SYS_FOREIGN_COLS' },
  RowDataPacket { table_name: 'INNODB_CMP_RESET' },
  RowDataPacket { table_name: 'INNODB_BUFFER_POOL_STATS' },
  RowDataPacket { table_name: 'INNODB_FT_INDEX_CACHE' },
  RowDataPacket { table_name: 'INNODB_SYS_FOREIGN' },
  RowDataPacket { table_name: 'INNODB_METRICS' },
  RowDataPacket { table_name: 'INNODB_FT_DEFAULT_STOPWORD' },
  RowDataPacket { table_name: 'INNODB_CMPMEM' },
  RowDataPacket { table_name: 'INNODB_SYS_TABLES' },
  RowDataPacket { table_name: 'INNODB_SYS_COLUMNS' },
  RowDataPacket { table_name: 'INNODB_FT_CONFIG' },
  RowDataPacket { table_name: 'INNODB_BUFFER_PAGE' },
  RowDataPacket { table_name: 'INNODB_CMP_PER_INDEX_RESET' },
  RowDataPacket { table_name: 'XTRADB_READ_VIEW' },
  RowDataPacket { table_name: 'INNODB_SYS_SEMAPHORE_WAITS' },
  RowDataPacket { table_name: 'INNODB_CHANGED_PAGES' },
  RowDataPacket { table_name: 'INNODB_FT_DELETED' },
  RowDataPacket { table_name: 'INNODB_TABLESPACES_SCRUBBING' },
  RowDataPacket { table_name: 'column_stats' },
  RowDataPacket { table_name: 'columns_priv' },
  RowDataPacket { table_name: 'db' },
  RowDataPacket { table_name: 'event' },
  RowDataPacket { table_name: 'func' },
  RowDataPacket { table_name: 'general_log' },
  RowDataPacket { table_name: 'general_log_template' },
  RowDataPacket { table_name: 'gtid_slave_pos' },
  RowDataPacket { table_name: 'help_category' },
  RowDataPacket { table_name: 'help_keyword' },
  RowDataPacket { table_name: 'help_relation' },
  RowDataPacket { table_name: 'help_topic' },
  RowDataPacket { table_name: 'host' },
  RowDataPacket { table_name: 'index_stats' },
  RowDataPacket { table_name: 'innodb_index_stats' },
  RowDataPacket { table_name: 'innodb_table_stats' },
  RowDataPacket { table_name: 'plugin' },
  RowDataPacket { table_name: 'proc' },
  RowDataPacket { table_name: 'procs_priv' },
  RowDataPacket { table_name: 'proxies_priv' },
  RowDataPacket { table_name: 'rds_configuration' },
  RowDataPacket { table_name: 'rds_global_status_history' },
  RowDataPacket { table_name: 'rds_global_status_history_old' },
  RowDataPacket { table_name: 'rds_heartbeat2' },
  RowDataPacket { table_name: 'rds_history' },
  RowDataPacket { table_name: 'rds_replication_status' },
  RowDataPacket { table_name: 'rds_sysinfo' },
  RowDataPacket { table_name: 'roles_mapping' },
  RowDataPacket { table_name: 'servers' },
  RowDataPacket { table_name: 'slow_log' },
  RowDataPacket { table_name: 'slow_log_template' },
  RowDataPacket { table_name: 'table_stats' },
  RowDataPacket { table_name: 'tables_priv' },
  RowDataPacket { table_name: 'time_zone' },
  RowDataPacket { table_name: 'time_zone_leap_second' },
  RowDataPacket { table_name: 'time_zone_name' },
  RowDataPacket { table_name: 'time_zone_transition' },
  RowDataPacket { table_name: 'time_zone_transition_type' },
  RowDataPacket { table_name: 'user' },
  RowDataPacket { table_name: 'accounts' },
  RowDataPacket { table_name: 'cond_instances' },
  RowDataPacket { table_name: 'events_stages_current' },
  RowDataPacket { table_name: 'events_stages_history' },
  RowDataPacket { table_name: 'events_stages_history_long' },
  RowDataPacket {
    table_name: 'events_stages_summary_by_account_by_event_name' },
  RowDataPacket { table_name: 'events_stages_summary_by_host_by_event_name' },
  RowDataPacket { table_name: 'events_stages_summary_by_thread_by_event_name' },
  RowDataPacket { table_name: 'events_stages_summary_by_user_by_event_name' },
  RowDataPacket { table_name: 'events_stages_summary_global_by_event_name' },
  RowDataPacket { table_name: 'events_statements_current' },
  RowDataPacket { table_name: 'events_statements_history' },
  RowDataPacket { table_name: 'events_statements_history_long' },
  RowDataPacket {
    table_name: 'events_statements_summary_by_account_by_event_name' },
  RowDataPacket { table_name: 'events_statements_summary_by_digest' },
  RowDataPacket {
    table_name: 'events_statements_summary_by_host_by_event_name' },
  RowDataPacket {
    table_name: 'events_statements_summary_by_thread_by_event_name' },
  RowDataPacket {
    table_name: 'events_statements_summary_by_user_by_event_name' },
  RowDataPacket {
    table_name: 'events_statements_summary_global_by_event_name' },
  RowDataPacket { table_name: 'events_waits_current' },
  RowDataPacket { table_name: 'events_waits_history' },
  RowDataPacket { table_name: 'events_waits_history_long' },
  RowDataPacket { table_name: 'events_waits_summary_by_account_by_event_name' },
  RowDataPacket { table_name: 'events_waits_summary_by_host_by_event_name' },
  RowDataPacket { table_name: 'events_waits_summary_by_instance' },
  RowDataPacket { table_name: 'events_waits_summary_by_thread_by_event_name' },
  RowDataPacket { table_name: 'events_waits_summary_by_user_by_event_name' },
  RowDataPacket { table_name: 'events_waits_summary_global_by_event_name' },
  RowDataPacket { table_name: 'file_instances' },
  RowDataPacket { table_name: 'file_summary_by_event_name' },
  RowDataPacket { table_name: 'file_summary_by_instance' },
  RowDataPacket { table_name: 'host_cache' },
  RowDataPacket { table_name: 'hosts' },
  RowDataPacket { table_name: 'mutex_instances' },
  RowDataPacket { table_name: 'objects_summary_global_by_type' },
  RowDataPacket { table_name: 'performance_timers' },
  RowDataPacket { table_name: 'rwlock_instances' },
  RowDataPacket { table_name: 'session_account_connect_attrs' },
  RowDataPacket { table_name: 'session_connect_attrs' },
  RowDataPacket { table_name: 'setup_actors' },
  RowDataPacket { table_name: 'setup_consumers' },
  RowDataPacket { table_name: 'setup_instruments' },
  RowDataPacket { table_name: 'setup_objects' },
  RowDataPacket { table_name: 'setup_timers' },
  RowDataPacket { table_name: 'socket_instances' },
  RowDataPacket { table_name: 'socket_summary_by_event_name' },
  RowDataPacket { table_name: 'socket_summary_by_instance' },
  RowDataPacket { table_name: 'table_io_waits_summary_by_index_usage' },
  RowDataPacket { table_name: 'table_io_waits_summary_by_table' },
  RowDataPacket { table_name: 'table_lock_waits_summary_by_table' },
  RowDataPacket { table_name: 'threads' },
  RowDataPacket { table_name: 'users' } ]
** Finished

8. 확인할 점

  • DB 접속 정보가 틀리면 getConnection 단계에서 에러가 발생한다.
  • 쿼리 실행 후에는 반드시 con.release()로 커넥션을 반환한다.
  • 프로그램 종료 시점에는 pool.end()를 호출해 풀을 정리한다.
  • 실제 서비스 코드에서는 비밀번호 같은 접속 정보를 소스에 직접 넣기보다 환경 변수나 별도 설정 관리 방식을 사용하는 것이 좋다.
#Node.js

댓글 0

로그인 후 댓글을 남길 수 있습니다.

아직 댓글이 없습니다.