Zane Anis Zane Anis - 4 days ago 5
MySQL Question

perform async multiple Mysql queries on Node

I'm using node with Mysql and here's my problem.

I'm trying to add new photos on my database and return it as an array

here is my function :

function addNewPhotos(_id, files) {
var deferred = Q.defer();
var new_photos = []
_.each(files, function (one) {
var data = [
one.path,
_id,
0
]
var sql = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)';
db.connection.query(sql, data, function (err, result) {

if (err)
deferred.reject(err.name + ': ' + err.message);
var sql = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?';
if (result){
db.connection.query(sql, [result.insertId], function(err, photo) {
if (err) deferred.reject(err.name + ': ' + err.message);
if (photo) {
new_photos.push(photo[0]);
}
});
}
})
})
deferred.resolve(Array.prototype.slice.call(new_photos));
return deferred.promise}


The Insert works well but i can't retrieve the results to send them back to the client. (my array is empty)

Thanks.

Answer

Always promisify at the lowest level, in this case db.connection.query().

if(!db.connection.queryAsync) {
    db.connection.queryAsync = function(sql, data) {
        returm Q.Promise(function(resolve, reject) { // or possibly Q.promise (with lower case p), depending on version
            db.connection.query(sql, data, function(err, result) {
                if(err) {
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    };
}

Now the higher level code becomes very simple :

function addNewPhotos(_id, files) {
    var sql_1 = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)',
        sql_2 = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?';
    return Q.all(files.map(function(one) {
        return db.connection.queryAsync(sql_1, [one.path, _id, 0]).then(function(result) {
            return db.connection.queryAsync(sql_2, [result.insertId]);
        });
    }));
};

To prevent a single failure scuppering the whole thing, you might choose to catch individual errors and inject some kind of default ;

function addNewPhotos(_id, files) {
    var sql_1 = 'INSERT INTO photos(photo_link, id_user, isProfil) VALUES (?, ?, ?)',
        sql_2 = 'SELECT id_user, photo_link, isProfil FROM `photos` WHERE id = ?',
        defaultPhoto = /* whatever you want as a default string/object in case of error */;
    return Q.all(files.map(function(one) {
        return db.connection.queryAsync(sql_1, [one.path, _id, 0]).then(function(result) {
            return db.connection.queryAsync(sql_2, [result.insertId]);
        }).catch(function() {
            return defaultPhoto;
        });
    }));
};