Node.js, mysql and transaction with util(async/await)

Question

How I can use transaction with node.js, mysql, util? My connection is create by

    var mysql = require("mysql");
    var util = require("util");
    var connection = mysql.createPool({
        connectionLimit: 10000,
        acquireTimeout: 10000,
        host: process.env.HOST,
        user: process.env.AUSER,
        password: process.env.PASSWORD,
        database: process.env.DATABASE,
        dateStrings: true
    });
    connection.query = util.promisify(connection.query);
    module.exports = connection;

And here is example request:

addNewEvent: async function(req, res) {
  try{
    let strSql = "SELECT * FROM users"
    const user = await connection.query(strSql);

    //now I need insert something in other table but I have to use transaction
  }
  catch (error) {
    console.log(error);
  }
})

If I add this code const conn = await connection.beginTransaction(); after try{ I getting error:

TypeError: connection.beginTransaction is not a function

score 4 · Answer 1 · answered Oct 08 '18 at 13:18

4

Pools are not the same as connections. You have to get a connection from the connection pool.

const connection = await util.promisify(pool.getConnection)();
await util.promisify(connection.beginTransaction)();
const user = util.promisify(connection.query)(strSql);

Rather than promisify everything yourself, you could also use one of a few available libraries such as mysql-promise that are small wrappers for the mysql library that already have the methods promisified.

answered Oct 08 '18 at 13:18

Explosion Pills

188,624
52
326
405

This Not Work. Show me this error: ```TypeError [ERR_INVALID_ARG_TYPE]: The "original" argumen t must be of type Function``` . ¿ any idea ? – JulianProg Sep 03 '20 at 22:29

Ng Sek Long · Answer 2 · 2020-09-23T06:46:04.940

In the NodeJS mysql 2.18.1 version, seems like the above solution is not working.

However adding bind keyword does works to the solution will works, see this example:


const databaseConfig = {
  connectionLimit : 10,
  host     : "",
  user     : "",
  password : "",
  database : "",
}

const pool = mysql.createPool(databaseConfig)


const connection = await util.promisify(pool.getConnection).bind(pool)();


await util.promisify(connection.beginTransaction).bind(connection)();
let sql = `SELECT * FROM table;`;
const result = await util.promisify(connection.query).bind(connection)(sql);

console.log('result', result);

await util.promisify(connection.commit).bind(connection)();
const promisePoolEnd = await util.promisify(pool.end).bind(pool)();

Node.js, mysql and transaction with util(async/await)

2 Answers2