CODEWITHSUNDEEP
javascriptnode.jsasynchronous
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

Managing asynchronicity and performing multiple MySQL queries in node

I am running into some issues with handling the asynchronous nature of nodejs. I am using the nodejs-mysql connector to fetch data. In essence, the code below is performing a query to fetch file_url and sending the information to myApi. A second query should be run right after to update database with result returned from the api(inputId). However, the previous is not working do to it's asynchronous manner(connection closes before processing everything). What would be the best way to do the below using the async module?

var mysql = require("mysql");
var async = require("async");
var Q = require('q'),
    myApi = require('myApi')('xxxxx');

//DB
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "test"
});


//1ST Query - Fetch Urls, then upload file to api
con.query('SELECT file_url, file_id FROM myfiles', function(err, rows) {
    if (err) throw err;
    for (var i = 0; i < rows.length; i++) {

        // File URL for upload
        var fileUrl = rows[i].file_url,
            createInputPromise, createEncodingProfilePromise;
        var fileId = rows[i].file_id;

        // Create myApi Input
        createInputPromise = myApi.input.create(fileUrl);

        //WHERE THE ACTION OCCURS!
        Q.all([createInputPromise, createEncodingProfilePromise]).then(
            function(result) {
                console.log('Successfully uploaded file: ' + result[0].url + ' inputId: ' + result[0].inputId);

                //2ND Query - Save the inputId return by api for each file
                con.query(
                    'UPDATE myfiles SET myApi_input_id = ?  WHERE file_id = ?', [result[0].inputId, fileId],
                    function(err, result) {
                        if (err) throw err;
                        console.log('Changed ' + result.changedRows + ' rows');
                    }
                );

            },
            function(error) {
                console.log('Error while uploading file to api:', error);
            }
        );
    };

});

con.end(function(err) {});

Upvotes: 1

Views: 73

Answers (3)

Code_Ed_Student
Code_Ed_Student

Reputation: 1190

The answers posted are great options but I ultimately wanted to get it accomplished with the use of async. I came across the waterfall pattern:

Runs an array of functions in series, each passing their results to the next in the array. However, if any of the functions pass an error to the callback, the next function is not executed and the main callback is immediately called with the error.

I ended implementing the following:

var pool = mysql.createPool({...});

function fn(callback) {
    var getConnection = function(callback) {...};

    async.waterfall([
        doQuery.bind(null, getConnection),
        doSomethingAsyncWithResult,
        doUpdate(null, getConnection)
    ], function(err, result) {
        getConnection.end();
        callback(err, result);
    });

}

function doQuery(connection, callback) {
    connection.query(sql, callback);
}

function doSomethingAsyncWithResult(result, callback) {
    ...something...
    callback(null, anotherResult);
}

function doUpdate(connection, result, callback) {
    connection.update(sql, [result], callback);
}

Upvotes: 1

Sergey Yarotskiy
Sergey Yarotskiy

Reputation: 4834

Instead of async module, I would look on it in a bit different manner. Lets go with generators.

let co = require('co');
let parallel = require('co-parallel');
let sequelize = require('sequelize'); // also read documentation to provide connection details (this is just pseudocode)


let gen = function*() {
  let inputId = yield fetch_file_url(); // this function should be generator or should return promise
  let res = yield sequelize.query(`INSERT INTO... ${inputId} `);
}

co(function*() {
  let files = ['f1', 'f2', 'f3'];
  let gens = files.map(file => gen(file));
  let arrayOrResults = yield parallel(gens, 5);  // 5 threads
    // done
}).catch(function(err) {
  // catch errors
});

Upvotes: 0

devtanc
devtanc

Reputation: 136

A lot of this depends on how you are handling these: createInputPromise, createEncodingProfilePromise. More code in relation to those two would be helpful towards a good answer. And where exactly in the code do you get the issue? Does console.log('Successfully uploaded file: ' + result[0].url + ' inputId: ' + result[0].inputId); output to the console, or is there an error before that?

Right now, are you certain that myApi.input.create(fileUrl) is returning a promise? You will need to look over the docs for that API to make sure that it is. If not, then you can use the Q library to create one:

var inputPromise = Q.defer();
var encodingProfilePromise = Q.defer();

inputPromise.resolve(myApi.input.create(fileUrl));
encodingProfilePromise.resolve(/*encoding profile code*/);

Q.all([inputPromise.promise, encodingProfilePromise.promise]).then(function(result) {
  /*blah blah blah*/
});

With more info I could give a more precise answer.

Upvotes: 0

Related Questions