Nodejs using MySQL module for transaction processing

Dependent modules:

1. Mysql:https://github.com/felixge/node-mysql

NPM install MySQL--save

2. Async:https://github.com/caolan/async

NPM Install async--save

(Ps:async module can be replaced with other promise modules such as Bluebird, Q, etc.)

Because the MySQL module of node. JS is inherently too simple to encapsulate the transaction, and the direct use can be very serious callback hell, we encapsulate two methods, one for initializing SQL & params and one for executing transactions.

Initialize SQL & params:

function _getnewsqlparamentity (SQL, params, callback) {    if  (callback) {        return Callback (null, {            sql:sql,            params:params        });    }     return {        sql:sql,        params:params    };}

If you want to execute multiple SQL statements, you need to:

var sqlparamsentity = []; var sql1 = "Insert Table set a=?", b=? where 1=1 "; var param1 = {a:1, b:2};sqlparamsentity.push (_getnewsqlparamentity (SQL1, param1)); var sql2 = "Update ..."; Sqlparamsentity.push (_getnewsqlparamentity (SQL1, [])); // ... More SQL to be executed by the transaction

And then I encapsulated the Exectrans function in my own dbhelper.js to execute the transaction.

varMySQL = require (' MySQL ');varAsync = require ("Async"); Module.exports={Exectrans:exectrans,}varPool =Mysql.createpool ({host:"MySQL Host", User:"MySQL Login user", Password:"MySQL login pwd", Database:"Target DB name", Connectionlimit:10, Port:"MySQL DB port", Waitforconnections:false});functionExectrans (Sqlparamsentities, callback) {Pool.getconnection (function(err, connection) {if(err) {returnCallback (Err,NULL); } connection.begintransaction (function(err) {if(err) {returnCallback (Err,NULL); } console.log ("Start execution transaction, total execution" + sqlparamsentities.length + "bar data"); varFuncary = []; Sqlparamsentities.foreach (function(sql_param) {vartemp =function(CB) {varsql =Sql_param.sql; varparam =Sql_param.params; Connection.query (SQL, param,function(Terr, rows, fields) {if(Terr) {Connection.rollback (function() {Console.log ("Transaction failed," + Sql_param + ", ERROR:" +Terr); ThrowTerr;                        }); } Else {                            returncbNULL, ' OK ');                }                    })                };            Funcary.push (temp);            }); Async.series (Funcary,function(err, result) {Console.log ("Transaction error:" +err); if(Err) {Connection.rollback (function(Err) {Console.log ("Transaction error:" +err);                        Connection.release (); returnCallback (Err,NULL);                }); } Else{connection.commit (function(err, info) {Console.log ("Transaction info:" +json.stringify (info)); if(Err) {Console.log ("Execution transaction failed," +err); Connection.rollback (function(Err) {Console.log ("Transaction error:" +err);                                Connection.release (); returnCallback (Err,NULL);                        }); } Else{connection.release (); returnCallbackNULL, info);    }                    })                }            })        }); });}

This allows you to perform the transaction:

function (err, info) {if(err) {   console.error ("Transaction execution failed");} Else {   Console.log ("done.") );}})

The great God can also help me improve this package for the project.

Nodejs using MySQL module for transaction processing