Repository: https://github.com/realmankwon/nodeJsRequestHandler

1. What is the project about?

When dealing with data in a database using node.js, you are writing queries in source code. I want to manage the query and the source separately, so I store the query in the database and try to map the programid for that query. In the node.js source, when the programid is inputted and when a request is generated, a project mapped to the program_id is fetched and executed.

1 ) Common database connections

var mysql      = require('mysql');
var dbconfig   = require('./config/database.js');
var connection = mysql.createConnection(dbconfig);

  connection.query('select * from user', function(err, rows) {
    if(err) throw err;

    console.log('user : ', rows);
    res.send(rows);
  });
});

2 ) RequestHandler for node.js

var requesthandler = require('../lib/requesthandler.js');
requesthandler('USER-GETINFO', param, function(err, val){
      if(err) {
           console.log(err);
           res.render('user/info', { error : err});
      } else {
           res.render('user/info', { user_info : val});
      }
});

2. How to use requesthandler.js?

1 ) Register query

• Here is the db table that stores the current query.
  . program_id : The unique id of the query to use.
  . sql_statement : The query to use.
  . sql_typ : I (Insert), U (Update), D (Delete), S (Select)

• Now we need to enter it directly into the DB and we will create a web page for future input.

2 ) Use RequestHandler

• Enter the program_id to be executed in the requesthandler and input the parameter, and execute the mapped sql to get the result value.

var requesthandler = require('../lib/requesthandler.js'); 
…. router.route('/user/:userid') .get(function(req, res, next) { 
      var param = new Array(); 
      param.push({userid: req.params.userid}); 
      requesthandler('USER-GETINFO', param, function(err, val){ 
            if(err) { 
                   console.log(err); 
                   res.render('user/info', { error : err}); 
            } else { 
                   res.render('user/info', { userinfo : val}); 
            } 
      }); 
});

3. Source of requesthandler.js

var mysql = require('mysql'); 
var async = require('async'); 
var dbConfig = require('./config/database_new');

var debug = require('debug'); 
var errorDebug = new debug('request:error'); 
var executeDebug = new debug('request:execute'); 
var connectionDebug = new debug('request:connection');

requesthandler = function(program_id, param, cb) { var pool = mysql.createPool(dbConfig);

pool.getConnection(function(err, connection){ if(err){ connectionDebug(err); cb(err); return; }

   connection.config.queryFormat = function (query, values) {
  if (!values) return query;
     return query.replace(/\:(\w+)/g, function (txt, key) {
       if (values.hasOwnProperty(key)) {
         return this.escape(values[key]);
      }
    return txt;
     }.bind(this));
  };

    connection.beginTransaction(function(err){
        if(err) {
            endConncetion(err);
            errorDebug(err);
            return;
        }

    console.log(program_id);

    var getSqlCmd = 'select sql_statement, sql_typ from frm_prg where program_id = :program_id';

        var i = 0;

        var result;

          connection.query(getSqlCmd, {program_id : program_id}, function(err, sqlInfo, fields) {
              if(err) {
                console.log(err);
            } else {
                //connectionDebug(param[i]);
                try{
                console.log(param[i]);
                connection.query(sqlInfo[0].sql_statement, param[i], function(err, results, fields){
                    if(err) {
                        console.log(err);
                    } else {
                        if(sqlInfo[0].sql_typ == 'I'){
                            executeDebug ('insert id : ' + results.insertId);
                        }   else if(sqlInfo[0].sql_typ == 'U') {
                            executeDebug('changed ' + results.changedRows + ' rows');
                        }   else if(sqlInfo[0].sql_typ == 'D') {
                            executeDebug('deleted ' + results.affectedRows + ' rows');
                        }   else if(sqlInfo[0].sql_typ == 'S') {
                            executeDebug('selected ' + results.length + ' rows');
                            result = results;
                        }
                        i++;
                    }
                }); // end of query (excute sql)
                }
                catch(e){
                    console.log(e);
                }

              } // end of if-else
        }); // end of query (get program)

    }); // end of transaction

    function endConnection (err) {
      if(err) {
            console.log(err);
        }
        else{
        connection.release();
      //  console.log('return database connection');
        }
  } // end of endConnection

    function rollback(err){
        connection.rollback(function(){
            console.log('rollback');
            console.log(err);
        });
    } // end of rollback

    function commit(){
        connection.commit(function(err){
            if(err){
                rollback(err);
                return;
            }

    //      console.log('commit');
        });
    } // end of commit
}); // end of get connection 
}

module.exports = requesthandler; 

4. Technology Stack

1 ) languages : javascript

2 ) technical requirements : node.js, npm

5. Roadmap

1 ) Create the table to manage sql

2 ) Create Express Project(Already)

3 ) Develop requesthandler.js

4 ) Develop Web Page to register program_ids.

5 ) Enhancement.

6. Bug Fix

• I am writing a third article on Requesthandler for node.js. The first article was rejected, and the second article was not confirmed by the moderator. This is the same as the second one, so I introduce the project I made and amend the bugs found between them.
  The bug fixes a problem where exception is raised when param is null when calling requesthandler function.

  // when param is null, assign array to param. 
  if(param == null){
    param = new Array();
    param.push('');
  }