Node.js mysql 事务

Node.js mysql transaction

提问by Alex Grace

Can anyone provide an example of how I could achieve MySQL transactions in Node.js. I am trying to get my head around using the node-mysql driver and node-mysql-queue.

谁能提供一个我如何在 Node.js 中实现 MySQL 事务的示例。我正在尝试使用 node-mysql 驱动程序和 node-mysql-queue。

As far are I can tell, using node-mysql-queue greatly reduces the asynchronous nature of Node.js as new queries have to wait until existing ones have completed. To get around this, has anyone attempted to combine node-mysql-queue with node-mysql's connection-pooling capabilities. i.e starting a new mysql connection for each new http request, and starting transaction queues on individual connections?

据我所知，使用 node-mysql-queue 大大减少了 Node.js 的异步特性，因为新查询必须等到现有查询完成。为了解决这个问题，是否有人尝试将 node-mysql-queue 与 node-mysql 的连接池功能结合起来。即为每个新的 http 请求启动一个新的 mysql 连接，并在单个连接上启动事务队列？

回答by SheetJS

The following transaction example was added to the documentation a month ago:

一个月前在文档中添加了以下交易示例：

https://github.com/felixge/node-mysql#transactions

https://github.com/felixge/node-mysql#transactions

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function(err, result) {
    if (err) { 
      connection.rollback(function() {
        throw err;
      });
    }

    var log = 'Post ' + result.insertId + ' added';

    connection.query('INSERT INTO log SET data=?', log, function(err, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) { 
          connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

回答by Danny Harding

Update

更新

See the edit below for async/await syntax

有关 async/await 语法，请参阅下面的编辑

---

---

I spent some time writing a generalized version of the transaction example given by node mysql, so I thought I would share it here. I am using Bluebird as my promise library, and used it to 'promisify' the connection object which simplified the asynchronous logic a lot.

我花了一些时间写了一个通用版本的node mysql给出的事务示例，所以我想在这里分享一下。我使用 Bluebird 作为我的承诺库，并用它来“承诺”连接对象，这大大简化了异步逻辑。

const Promise = ('bluebird');
const mysql = ('mysql');

/**
 * Run multiple queries on the database using a transaction. A list of SQL queries
 * should be provided, along with a list of values to inject into the queries.
 * @param  {array} queries     An array of mysql queries. These can contain `?`s
 *                              which will be replaced with values in `queryValues`.
 * @param  {array} queryValues An array of arrays that is the same length as `queries`.
 *                              Each array in `queryValues` should contain values to
 *                              replace the `?`s in the corresponding query in `queries`.
 *                              If a query has no `?`s, an empty array should be provided.
 * @return {Promise}           A Promise that is fulfilled with an array of the
 *                              results of the passed in queries. The results in the
 *                              returned array are at respective positions to the
 *                              provided queries.
 */
function transaction(queries, queryValues) {
    if (queries.length !== queryValues.length) {
        return Promise.reject(
            'Number of provided queries did not match the number of provided query values arrays'
        )
    }

    const connection = mysql.createConnection(databaseConfigs);
    Promise.promisifyAll(connection);
    return connection.connectAsync()
    .then(connection.beginTransactionAsync())
    .then(() => {
        const queryPromises = [];

        queries.forEach((query, index) => {
            queryPromises.push(connection.queryAsync(query, queryValues[index]));
        });
        return Promise.all(queryPromises);
    })
    .then(results => {
        return connection.commitAsync()
        .then(connection.endAsync())
        .then(() => {
            return results;
        });
    })
    .catch(err => {
        return connection.rollbackAsync()
        .then(connection.endAsync())
        .then(() => {
            return Promise.reject(err);
        });
    });
}

If you wanted to use pooling as you suggested in the question, you could easily switch the createConnectionline with myPool.getConnection(...), and switch the connection.endlines with connection.release().

如果您想按照问题中的建议使用池化，您可以轻松地用 切换createConnection行myPool.getConnection(...)，并用 切换connection.end行connection.release()。

---

---

Edit

编辑

I made another iteration of the code using the mysql2library (same api as mysqlbut with promise support) and the new async/await operators. Here is that

我使用mysql2库（与 api 相同，mysql但支持 promise）和新的 async/await 运算符对代码进行了另一次迭代。这是

const mysql = require('mysql2/promise')

/** See documentation from original answer */`enter code here`
async function transaction(queries, queryValues) {
    if (queries.length !== queryValues.length) {
        return Promise.reject(
            'Number of provided queries did not match the number of provided query values arrays'
        )
    }
    const connection = await mysql.createConnection(databaseConfigs)
    try {
        await connection.beginTransaction()
        const queryPromises = []

        queries.forEach((query, index) => {
            queryPromises.push(connection.query(query, queryValues[index]))
        })
        const results = await Promise.all(queryPromises)
        await connection.commit()
        await connection.end()
        return results
    } catch (err) {
        await connection.rollback()
        await connection.end()
        return Promise.reject(err)
    }
}

回答by Manish

I am using the following approach. There is an add function in my Model where I am performing database operations.

我正在使用以下方法。我的模型中有一个添加函数，我在其中执行数据库操作。

  add : function (data, callback) {

    //Begin transaction
    connection.beginTransaction(function(err) {
        if (err) {
            throw err;
        }

        var user_query = "INSERT INTO `calldata`.`users` (`username`, `password`, `enabled`, `accountNonExpired`, `accountNonLocked`, `credentialsNonExpired`) VALUES ('" + data.mobile + "', '" + sha1(data.password) + "', '1', '1', '1', '1')";
        connection.query(user_query, function(err, results) {
            if (err) {
                return connection.rollback(function() {
                    throw err;
                });
            }

            var accnt_dtls_query = "INSERT INTO `calldata`.`accnt_dtls` (`req_mob_nmbr`, `usr_nme`, `dvce_id`, `mngr_id`, `cmpny_id`, `actve_flg`, `crtd_on`, `usr`) VALUES (" + data.mobile + ", '" + data.name + "', '', " + data.managerId + ", " + data.companyId + ", 1, now(), '" + data.mobile+ "')";

            connection.query(accnt_dtls_query, function(err, results) {
                if (err) {
                    return connection.rollback(function() {
                        throw err;
                    });
                }
                var user_role_query = "INSERT INTO `calldata`.`user_roles` (`username`, `ROLE`) VALUES ('" + data.mobile + "', '" + data.role + "')";

                connection.query(user_role_query, function(err, result) {
                    if (err) {
                        return connection.rollback(function() {
                            throw err;
                        });
                    }

                    //add an entry to manager table
                    var mngr_dtls_query = "INSERT INTO `calldata`.`mngr_dtls` (`mngr_nm`, `cmpny_id`, `crtd_on`, `usr_nm`, `eml_id`) VALUES ('" + data.name + "'," + data.companyId + " , now(), '" + data.mobile + "', '" + data.mobile + "')";
                    connection.query(mngr_dtls_query, function(err, result) {
                        if (err) {
                            return connection.rollback(function () {
                                throw err;
                            });
                        }
                        console.log('Changed ' + result.changedRows + ' results');
                        connection.commit(function (err) {
                            console.log('Commiting transaction.....');
                            if (err) {
                                return connection.rollback(function () {
                                    throw err;
                                });
                            }

                            console.log('Transaction Complete.');
                            connection.end();
                            callback(null, result);
                        });
                    });
                });
            });
        });
    });
    //transaction ends here
}

and calling from controller:

并从控制器调用：

 agentAccountModel.add(data, function(err, results) {
                if(err)
                {
                    res.status(500);
                    res.json({
                        "status": 500,
                        "message": err
                    });
                }

                res.status(200);
                res.json({
                    "status": 200,
                    "message": "Saved successfully"

                });
            });

回答by Nirav Shah

I found one useful links which uses node js mysql pooling with transaction. Database Connection pooling is always useful. One can check this link

我发现了一个有用的链接，它使用节点 js mysql 池与事务。数据库连接池总是有用的。一个可以检查这个链接

https://github.com/mysqljs/mysql

https://github.com/mysqljs/mysql

回答by Shreyan Mehta

I created a wrapper ORM type thing for this specific purpose, hope it helps SQl-connecton - pool ORM type helper methods

我为此特定目的创建了一个包装器 ORM 类型的东西，希望它有助于SQl-connecton - pool ORM 类型辅助方法

回答by Jonathan Sapp

I have come up with a solution using a recursive function.

我想出了一个使用递归函数的解决方案。

var sql = 'INSERT INTO logs SET data = ?';

// array of rows to insert
var rows = [[/*first row*/], [/*additional row*/]];

connection.beginTransaction(function (err) {

    if (err) { 
        throw err; 
    }

    var insertEachRow = function () {

        var row = rows.shift();

        if (! row) {
            // Done, now commit
            return noMoreRows();
        }

        connection.query(sql, row, function (err, result) {
            if (err) { 
                connection.rollback(function () {
                    throw err;
                });
            }  

            insertEachRow();
        });
    };

    var noMoreRows = function () {
        connection.commit(function (err) {
            if (err) { 
                connection.rollback(function () {
                    throw err;
                });
            }
            console.log('success!');
        });
    };

    insertEachRow();
});