Node.js mysql 事务

2022-01-06 00:00:00 node.js express transactions mysql node-mysql

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

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

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?

推荐答案

更新

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

Update

See the edit below for async/await syntax

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

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.

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

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

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

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

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

const mysql = require('mysql2/promise')

/** See documentation from original answer */
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)
    }
}

相关文章