Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Approach to multiple MySQL queries with Node.js

I'm a newbie both on event/callback style programming and NodeJS. I'm trying to implement a little http server which serves ddbb data using node-mysql module.

My problems comes from queries structuration. Since there are often queries that require results from previous queries to run, I am not able to run all them simultaneously (asynchronously) and I am forced to wait some results.

My first approach was to run all the non-dependant queries at the same time and then loop until all of them have set a flag up saying I'm done so I can continue with the dependant (synchronized) ones, but I don't know if this is the correct approach.

Something like this:

function x(){     var result_for_asynch_query_1 = null     var result_for_asynch_query_2 = null      mainLoop(){         // call non-dependant query 1         // call non-dependant query 2          // loop until vars are != null          // continue with queries that require data from the first ones     } }  //for each browser request httpServer{      call_to_x(); }.listen(); 

This way I can save some time in the final result since I don't wait all responses in a serial way but just to the longest one.

Is there a common way to do this? Any design pattern I'm not following?

like image 824
luso Avatar asked Jul 08 '11 09:07

luso


People also ask

How do I run multiple queries in MySQL?

MySQL also supports the execution of a string containing multiple statements separated by semicolon ( ; ) characters. This capability is enabled by special options that are specified either when you connect to the server with mysql_real_connect() or after connecting by calling mysql_set_server_option() .

Is Node JS good with MySQL?

Using programs written in C or for Node won't help speed things up, as you'll have to scale your database system - which is a whole different story (plus you'll need to scale your HTTP server setup). Basically - stick with PHP/MySQL, forget about Node, at least for now.

Can Nodejs communicate with MySQL?

Once you have MySQL up and running on your computer, you can access it by using Node. js. To access a MySQL database with Node. js, you need a MySQL driver.


1 Answers

One should avoid the pyramid of doom:

var express = require('express'); var Q = require('Q'); var app = express();  app.get('/',function(req,res){     var mysql      = require('mysql');      var connection = mysql.createConnection({         host     : 'localhost',         user     : 'root',         password : ''     });      connection.connect();      function doQuery1(){         var defered = Q.defer();         connection.query('SELECT 1 AS solution',defered.makeNodeResolver());         return defered.promise;     }      function doQuery2(){         var defered = Q.defer();         connection.query('SELECT 2 AS solution',defered.makeNodeResolver());         return defered.promise;     }      Q.all([doQuery1(),doQuery2()]).then(function(results){         res.send(JSON.stringify(results[0][0][0].solution+results[1][0][0].solution));         // Hint : your third query would go here     });      connection.end();  });  app.listen(80); console.log('Listening on port 80'); 

This sample show a result which depend of 2 independent computed values. Each of these values a queried in doQuery1 and doQuery2. They are executed in sequence, but asynchronously.

Next you can see Q.all(... which basically call the "then" callback on success. Within that callback, the calculation is done.

Using promises (details : Github Q: promise for Javascript and wikipedia ) permit to make your code cleaner, separate computation and handling of results and move things arround.

Look at how easy it would be to add "doQuery3" as prerequisit for your calculation !

And bellow the "package.json" bellonging to the sample code:

{     "name": "hello-world",     "description": "hello world test app",     "version": "0.0.1",     "private": true,     "dependencies": {         "express": "3.2.0",         "q": "0.9.3",         "mysql":"2.0.0-alpha7"     } } 
like image 107
programaths Avatar answered Sep 24 '22 00:09

programaths