Synchronous database queries with Node.js

Question

I have a Node.js / Express application that queries MySQL db in a route and displays the result for the user. My problem is how to execute requests and block until both requests are completed before the user is redirected to the requested page?

In my example, I have 2 questions that need to be completed before I create the page. I can make the queries run synchronously if I insert query 2 inside the callback of the query “result” 1. This, however, will become very confusing when the number of queries increases.

How can I execute multiple queries in the database at the same time (in this case 2) without embedding the subsequent query in the "result" callback of the previous query?

I looked at "Flow Control / Asynchronous Properties" in Node modules and tried to use stream-js, but I can't get it to work with asynchronous requests.

Listed below are 2 queries that I am trying to execute from the "/ home" route. Can Node experts explain the “right” way to do this.

app.get('/home', function (req,res) { var user_array = []; var 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; }); // 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; }); // because the queries are async no data is returned to the user res.render('home.ejs', {layout: false, locals: { user_name: user_array, title: title_array }}); }); 

Answer 1

The goal with node is not to care about the order in which events occur. This can complicate some scenarios. There is no shame with nested callbacks. Once you get used to how it looks, you may find that you really prefer this style. I do; it’s very clear which callbacks will fire. You can opt out of anonymous functions to make it less verbose if you need to.

If you want to slightly modify the structure of the code, you can use the "typical" nested callback method. If you want to avoid callbacks, there are many asynchronous frameworks that will try to help you with this. One that you can check out is async.js (https://github.com/fjakobs/async.js). An example of each of them:

 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-time use, I would probably just use an approach like link counting. Just keep track of how many queries you want to complete, and draw an answer when they are all done.

 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 () on each result callback to check for non-empty arrays before you post the answer. Whether this will work in your case depends on your requirements.

Answer 2

Here is a very simple 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

Application:

 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 a reference count. This is the standard approach in these situations. I actually use this little utility function instead of flow control.

If you want a full blown flow control solution that is recommended by futuresJS

Answer 3

I find that an asynchronous library is best suited for such things. https://github.com/caolan/async#parallel

I cannot verify this or anything else, so forgive me if there are any typos. I reorganized your request function for reuse. Thus, calling queryRows returns a function that matches the format of the asynchronous module's callback parallel functions. After both requests are completed, it will call the last function and pass the result of the two requests as an argument, which you can read to go 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} }); }); }); 

Answer 4

There are some solutions here, but in my opinion, the best solution is to make the code synchronously in a very simple way.

You can use the synchonize package.

Simply

npm set sync

Then var sync = require(synchronize);

Put the logic that should be synchronous into the fiber using

sync.fiber(function() { //put your logic here }

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 into the main table and gets the last inserted identifier. After that the code below will be executed. No nesting promises or the like.

Answer 5

there is only one option: if all your queries are related to each other, create a stored procedure, put all your data logic in it and get one db.execute

option two: if your db uses one connection, then the commands are guaranteed to be executed sequentially, and you can use it as an auxiliary 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 }); 

Answer 6

You can use fibers to write pseudo-synchronous code using Node.JS to see these tests for DB https://github.com/alexeypetrushin/mongo-lite/blob/master/test/collection.coffee they are asynchronous, but they look synchronous, more http : //alexeypetrushin.github.com/synchronize