CODEWITHSUNDEEP
javascriptmysqlnode.js
PeakGen
PeakGen

Reputation: 23035

Node.js: How to better write a MySQL batch operation code without too many callbacks?

I am not coming from a javascript background, however involved in writing a Node.js backend. There are situations where I have to do multiple database entry in a row and rollback if they failed. Below is such an example. (Node.js v14.17.5)

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

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

const con = mysql.createConnection({
    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.createExtraGig = (event, context, callback) => {

    context.callbackWaitsForEmptyEventLoop = false;

    if (event.body == null && event.body == undefined) {
        var response = errorCodes.missing_parameters;
        callback(null, response)
    } else {
        let body = JSON.parse(event.body)
        console.log("body", body);

        // Check for validations
        if (body.idjob_room == null ||
            body.idgig_fund_status == null ||
            body.title == null ||
            body.cost == null ||
            body.idjob == null ||
            body.end_date == null
        ) {
            console.log("fire 1");
            var response = errorCodes.not_null_parameters;
            callback(null, response)
        } else {
            if (
                isNaN(body.idjob_room) ||
                isNaN(body.idgig_fund_status) ||
                isNaN(body.cost) || 
                isNaN(body.idjob) || 
                isNaN(body.end_date)) {
                var response = errorCodes.missing_fields;
                callback(null, response)
            }
        }

        // Validations passed. Proceed
        let idjob_room = Number(body.idjob_room);
        let idgig_fund_status = Number(body.idgig_fund_status);
        let title = body.title;
        let cost = Number(body.cost);
        let end_date = new Date(body.end_date);
        let idjob = Number(body.idjob);

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

        con.beginTransaction(function(err){
            if (err) {
                var response = errorCodes.save_failed;
                callback(null, response);
            }

            const sql = "INSERT INTO gig_service (idjob_room, idgig_fund_status, title, cost, end_date, is_deleted, is_main_offer) VALUES(?,?,?,?,?,?,?)";
            con.query(sql, [idjob_room, idgig_fund_status, title, cost, end_date, false, false], function(err, gigResult) {
                if (err) {
                    con.rollback(function() {
                        var response = errorCodes.save_failed;
                        callback(null, response);
                    });

                }

                const jobUpdateSql = "UPDATE job SET end_date=? WHERE idjob=?";
                con.execute(jobUpdateSql, [end_date, idjob], function(err, jobResult) {

                    con.commit(function(err) {
                        if (err) {
                            return con.rollback(function() {
                                var response = errorCodes.save_failed;
                                callback(null, response);
                            });
                        }
                        console.log('success!');

                        var response = {
                            "statusCode": 200,
                            "headers": {
                                "Content-Type": "application/json"
                            },
                            "body": JSON.stringify({
                                "idgig_service": gigResult.insertId
                            }),
                            "isBase64Encoded": false
                        };
                        callback(null, response)
                    });
                    
                });
            });
        }


        
        )}
};

However this is a maintenance hell, because the more transactions I have, more if-else conditions and more callbacks i have to make.

Looking at posts such as this tells me there are better ways, but i am really not sure.

How can I better write my Node.js code without having too many rollback callbacks? Like one rollback callback is "any" fails?

(Note: exports.createExtraGig = (event, context, callback) => line is important because this is an aws lambda function)

UPDATE

Below is the updated code

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

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

const con =  mysql.createConnection({
    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.createExtraGig = async (event, context)=>{
    context.callbackWaitsForEmptyEventLoop = false;

    if (event.body == null && event.body == undefined) {
        var response = errorCodes.missing_parameters;
        return response;
    } else {
        let body = JSON.parse(event.body)
        console.log("body", body);

        // Check for validations
        if (body.idjob_room == null ||
            body.idgig_fund_status == null ||
            body.title == null ||
            body.cost == null ||
            body.idjob == null ||
            body.end_date == null
        ) {
            console.log("fire 1");
            var response = errorCodes.not_null_parameters;
            return response;
        } else {
            if (
                isNaN(body.idjob_room) ||
                isNaN(body.idgig_fund_status) ||
                isNaN(body.cost) || 
                isNaN(body.idjob) || 
                isNaN(body.end_date)) {
                var response = errorCodes.missing_fields;
                return response;
            }
        }

        // Validations passed. Proceed
        let idjob_room = Number(body.idjob_room);
        let idgig_fund_status = Number(body.idgig_fund_status);
        let title = body.title;
        let cost = Number(body.cost);
        let end_date = new Date(body.end_date);
        let idjob = Number(body.idjob);

        console.log("title: " + title);
        
        try {
            await con.promise().beginTransaction();

            const sql = "INSERT INTO gig_service (idjob_room, idgig_fund_status, title, cost, end_date, is_deleted, is_main_offer) VALUES(?,?,?,?,?,?,?)";
            const gigResult = await con.promise().query(sql,[idjob_room, idgig_fund_status, title, cost, end_date, false, false]);
            console.log(gigResult);

            const jobUpdateSql = "UPDATE job SET end_date=? WHERE idjob=?";
            await con.promise().query(jobUpdateSql,[end_date, idjob]);
            await con.promise().commit();

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

          } catch (error) {
              console.log(error);
              await con.promise().rollback();
              return  errorCodes.save_failed;
          } 
        }
};

Upvotes: 0

Views: 284

Answers (1)

Someone Special
Someone Special

Reputation: 13598

You can use promise with mysql.

const mysql = require('mysql2/promise');

// get the promise implementation, we will use bluebird
const bluebird = require('bluebird');

// create the connection, specify bluebird as Promise
const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test', Promise: bluebird});

Then... (Ref: Async/Await )

try {

  const resultsFromQuery1 = await con.query(sql1) 
  const resultsFromQuery2 = await con.query(sql2)



} catch (err) {
 con.rollback(function() {
      var response = errorCodes.save_failed;
      callback(null, response);
                    });
}

You can also make your codes cleaner by using helpers like .some or .map

   if (body.idjob_room == null ||
            body.idgig_fund_status == null ||
            body.title == null ||
            body.cost == null ||
            body.idjob == null ||
            body.end_date == null)

   //and the following chunk

      isNaN(body.idjob_room) ||
            isNaN(body.idgig_fund_status) ||
            isNaN(body.cost) || 
            isNaN(body.idjob) || 
            isNaN(body.end_date))

can be written like

   let { idgig_fund_status, title, cost, idjob, end_date } = body;
   if ( [idgig_fund_status, title, cost, idjob, end_date].some(x => x === null )) {
        var response = errorCodes.not_null_parameters;
        callback(null, response)
   } //can write similar for isNan checks

The following can also be written

    let idjob_room = Number(body.idjob_room);
    let idgig_fund_status = Number(body.idgig_fund_status);
    let title = body.title;
    let cost = Number(body.cost);
    let end_date = new Date(body.end_date);
    let idjob = Number(body.idjob);

 //becomes...
 
 [idjob_room, idgig_fund_status, cost, idjob] = [idgig_fund_status, cost, end_date, idjob].map(x => x+);
 end_date = new Date(end)date;
 //title is already deconstructed above.

Upvotes: 1

Related Questions