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.
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?
See the edit below for async/await syntax
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);
});
});
}
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()
.
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)
}
}