Synchronous database queries with Node.js
Solution 1:
The goal with node is not to care what order things happen in. This can complicate some scenarios. There is no shame in nesting callbacks. Once you are used to how it looks, you may find that you actually prefer that style. I do; it is very clear what order callbacks will fire. You can forgo the anonymous functions to make it less verbose if you have to.
If you are willing to restructure your code a bit, you can use the "typical" nested callback method. If you want to avoid callbacks, there are numerous async frameworks that will try and help you do this. One that you might want to check out is async.js (https://github.com/fjakobs/async.js). Example of each:
app.get('/home', function (req,res) {
var lock = 2;
var result = {};
result.user_array = [];
result.title_array = [];
var finishRequest = function(result) {
req.session.title_array = result.title_array;
req.session.user_array = result.user_array;
res.render('home.ejs', {layout: false, locals: { user_name: result.user_array, title: result.title_array }});
};
// first query
var q1 = function(fn) {
var sql = 'select user_name from users';
db.execute(sql)
.addListener('row', function(r) {
result.user_array.push( { user_name: r.user_name } );
})
.addListener('result', function(r) {
return fn && fn(null, result);
});
};
// second query
var q2 = function(fn) {
var sql = 'select title from code_samples';
db.execute(sql)
.addListener('row', function(r) {
result.title_array.push( { title: r.title } );
})
.addListener('result', function(r) {
return fn && fn(null, result);
});
}
//Standard nested callbacks
q1(function (err, result) {
if (err) { return; //do something}
q2(function (err, result) {
if (err) { return; //do something}
finishRequest(result);
});
});
//Using async.js
async.list([
q1,
q2,
]).call().end(function(err, result) {
finishRequest(result);
});
});
For a one-off, I would probably just use a reference counting type approach. Simply keep track of how many queries you want to execute and render the response when they have all finished.
app.get('/home', function (req,res) {
var lock = 2;
var user_array = [];
var title_array = [];
var finishRequest = function() {
res.render('home.ejs', {layout: false, locals: { user_name: user_array, title: title_array }});
}
// first query
var sql = 'select user_name from users';
db.execute(sql)
.addListener('row', function(r) {
user_array.push( { user_name: r.user_name } );
})
.addListener('result', function(r) {
req.session.user_array = user_array;
lock -= 1;
if (lock === 0) {
finishRequest();
}
});
// second query
var sql = 'select title from code_samples';
db.execute(sql)
.addListener('row', function(r) {
title_array.push( { title: r.title } );
})
.addListener('result', function(r) {
req.session.title_array = title_array;
lock -= 1;
if (lock === 0) {
finishRequest();
}
});
});
An even nicer approach would be to simply call finishRequest() in each 'result' callback an check for non-empty arrays before you render the response. Whether that will work in your case depends on your requirements.
Solution 2:
Here's a really easy trick to handle multiple callbacks.
var after = function _after(count, f) {
var c = 0, results = [];
return function _callback() {
switch (arguments.length) {
case 0: results.push(null); break;
case 1: results.push(arguments[0]); break;
default: results.push(Array.prototype.slice.call(arguments)); break;
}
if (++c === count) {
f.apply(this, results);
}
};
};
Example
Usage:
var handleDatabase = after(2, function (res1, res2) {
res.render('home.ejs', { locals: { r1: res1, r2: res2 }):
})
db.execute(sql1).on('result', handleDatabase);
db.execute(sql2).on('result', handleDatabase);
So basically you need reference counting. This is the standard approach in these situations. I actually use this small utility function instead of flow control.
If you want a full blown flow control solution I would recommend futuresJS
Solution 3:
I find that the async library is the best for things like this. https://github.com/caolan/async#parallel
I can't test this or anything, so forgive me if there are some typos. I refactored your query function to be reusable. So, calling queryRows will return a function that matches the format of the async module's parallel callback functions. After both queries are complete, it will call the last function and pass the result of the two queries as an argument, which you can read to pass to your template.
function queryRows(col, table) {
return function(cb) {
var rows = [];
db.execute('SELECT ' + col + ' FROM ' + table)
.on('row', function(r) {
rows.push(r)
})
.on('result', function() {
cb(rows);
});
}
}
app.get('/home', function(req, res) {
async.parallel({
users: queryRow('user_name', 'users'),
titles: queryRow('title', 'code_samples')
},
function(result) {
res.render('home.ejs', {
layout: false,
locals: {user_name: result.users, title: result.titles}
});
});
});
Solution 4:
There are some solutions here, but in my opinion the best solution is to make the code synchronously in a very easy way.
You could use the "synchonize" package.
Just
npm install synchronize
Then var sync = require(synchronize);
Put logic which should be synchronous into a fiber by using
sync.fiber(function() {
//put your logic here
}
An example for two mysql queries:
var express = require('express');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var sync = require('synchronize');
var db = mysql.createConnection({
host : 'localhost',
user : 'user',
password : 'password',
database : 'database'
});
db.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
});
function saveSomething() {
var post = {id: newId};
//no callback here; the result is in "query"
var query = sync.await(db.query('INSERT INTO mainTable SET ?', post, sync.defer()));
var newId = query.insertId;
post = {foreignKey: newId};
//this query can be async, because it doesn't matter in this case
db.query('INSERT INTO subTable SET ?', post, function(err, result) {
if (err) throw err;
});
}
When "saveSomething()" is called, it inserts a row in a main table and receives the last inserted id. After that the code below will be executed. No need for nesting promises or stuff like that.
Solution 5:
option one: if all your queries related to each other, create stored procedure, put all your data logic into it and have a single db.execute
option two: if your db uses one connection then commands a guaranteed to be executed serially and you can use this as async helper
db.execute(sql1).on('row', function(r) {
req.session.user_array.push(r.user);
});
db.execute(sql2)
.on('row', function(r) {
req.session.title_array.push(r.title);
})
.on('end'), function() {
// render data from req.session
});