NodeJs: `beginTransaction` is not a function error in mysql2

Issue

In my below code, I am getting the error is not a function. This error is thrown in the line await con.promise().beginTransaction(); please check my code below.

const mysql = require('mysql2');
const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');


const con = mysql.createPool({
    connectionLimit : 10,
    host: prop.get('server.host'),
    user: prop.get("server.username"),
    password: prop.get("server.password"),
    port: prop.get("server.port"),
    database: prop.get("server.dbname")
});


exports.checkInvestorBoost = async (event, context) => {

    context.callbackWaitsForEmptyEventLoop = false;
    con.config.namedPlaceholders = true;

    const params = event.queryStringParameters;


    if (!params || params.iduser == null) {
        var response = errorCodes.not_null_parameters;
        return response;
    } else {
        if(isNaN(params.iduser))
        {
            var response = errorCodes.missing_fields;
            return response;
        }
        const iduser = Number(params.iduser);
        const toDate = new Date();

        console.log("iduser: " + iduser);

        let sql = "SELECT * FROM golden_circle_member WHERE iduser= :iduser AND is_investor_boost = true AND to_date > :to_date";


        try {
            await con.promise().beginTransaction();
            const [data, meta] = await con.promise().query(sql, {
                iduser: iduser,
                to_date: toDate 
            });

            // commit and complete
            await con.promise().commit();
            let output= false;

            if(data.length>0)
            {
                output = true;
            }

            var response = {
                "statusCode": 200,
                "headers": {
                  "Content-Type": "application/json"
                },
                "body": JSON.stringify({
                    "is_investor_boost": output
                }),
                "isBase64Encoded": false
              };

            return response;

        } catch (error) {
            console.log(error);
            var response = errorCodes.internal_server_error;
            return response;
        }
    }
};

Reading about the question, I figured out that when createPool is used, I need to get the connection first. Not sure how I can get that done. Plus, I prefer to do this with async/await

Appreciate your advice here

Solution

This should work:

const mysql = require('mysql2/promise');
…
const pool       = mysql.createPool(…);
const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  …
  const [data, meta] = await connection.query(…);
  …
  await connection.commit();
} catch(err) {
  await connection.rollback();
}

This also uses the mysql2 promise wrapper so you don’t have to use .promise() each time.

Answered By – robertklep

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published