Node.js and MySQL tutorial

Node.js and MySQL is one of the necessary binding needed for any web application. MySQL is one of the most popular open source database in world and efficient as well. Almost every popular programming language like Java or PHP provides driver to access and perform operations with MySQL.

In this tutorial i am trying to cover code for learning and code for production. So if you know this already and looking for ready made code for production. Click here to jump there directly.

Introduction:

Node.js is rich with number of popular packages registered at package registry called NPM. Most of them are not so reliable to use for production but there are some on which we can rely upon. For MySQL there is one popular driver called node-mysql.

In this tutorial i am going to cover following points related to Node.js and MySQL.

  • Sample code to get started.
  • Code for Production.
  • Testing concurrent users.

Sample code to get started.

Project directory:

---node_modules
-----+ mysql
-----+ express
---index.js
---package.json
package.json
{
  "name": "node-mysql",
  "version": "0.0.1",
  "dependencies": {
    "express": "^4.10.6",
    "mysql": "^2.5.4"
  }
}

Install dependencies using

npm install

Here is sample code which connects to Database and perform SQL query.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : '< MySQL username >',
  password : '< MySQL password >',
  database : '<your database name>'
});

connection.connect();

connection.query('SELECT * from < table name >', function(err, rows, fields) {
  if (!err)
    console.log('The solution is: ', rows);
  else
    console.log('Error while performing Query.');
});

connection.end();

Make sure you have started MySQL on default port and changed the parameter in above code then run this code using

node file_name.js

Code for production :

Above code is just for learning purpose and not for production payload. In production scenario is different, there may be thousands of concurrent users which turns into tons of MySQL queries. Above code won’t run for concurrent users and here is a proof. Let’s modify our code little bit and add Express routes in that, here it is.

test.js ( Change database settings in code )
var express    = require("express");
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'address_book'
});
var app = express();

connection.connect(function(err){
if(!err) {
    console.log("Database is connected ... nn");    
} else {
    console.log("Error connecting database ... nn");    
}
});

app.get("/",function(req,res){
connection.query('SELECT * from user LIMIT 2', function(err, rows, fields) {
connection.end();
  if (!err)
    console.log('The solution is: ', rows);
  else
    console.log('Error while performing Query.');
  });
});

app.listen(3000);

Install siege in your system. I use this command to install it in Ubuntu.

apt-get install siege

then run our node and server and following command.

node test.js
siege -c10 -t1M http://localhost:3000

Assuming you are running Node server on Port 3000.
Here is the output.
Node and mysql
In above code, we are allowing it to run for standalone connection i.e one connection at a time but reality is bit different. You may get 100 or 1000 connection at one particular time and if your server is not powerful enough to serve those request then at least it should put them in queue.

Pool connection in MySQL :

Connection Pooling is mechanism to maintain cache of database connection so that connection can be reused after releasing it. In Node mysql, we can use pooling directly to handle multiple connection and reuse the connection. Let’s write same code with pooling and check whether it can handle multiple connection or not.

test.js
var express   =    require("express");
var mysql     =    require('mysql');
var app       =    express();

var pool      =    mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'address_book',
    debug    :  false
});

function handle_database(req,res) {
   
    pool.getConnection(function(err,connection){
        if (err) {
          res.json({"code" : 100, "status" : "Error in connection database"});
          return;
        }  

        console.log('connected as id ' + connection.threadId);
       
        connection.query("select * from user",function(err,rows){
            connection.release();
            if(!err) {
                res.json(rows);
            }          
        });

        connection.on('error', function(err) {      
              res.json({"code" : 100, "status" : "Error in connection database"});
              return;    
        });
  });
}

app.get("/",function(req,res){-
        handle_database(req,res);
});

app.listen(3000);

Run the app using

node test.js

and fire 10 concurrent users for 1 minute using siege by using this command.

siege -c10 -t1M http://localhost:3000

Here is output.
Code is stable !

Final comments :

Siege is really powerful tool for testing server under pressure. We have created 100 connection limit in code, so you might be wondering that after 100 concurrent connection code will break. Well let me answer it via code. Fire 1000 concurrent user for 1 minute and let’s see how our code reacts.

siege -c1000 -t1M http://localhost:3000

If your MySQL server is configured to handle such traffic at one socket then it will run and our code will manage the scheduling of concurrent connection. It will serve 100 connection a time but rest 900 will be in queue. So code will not break.

Conclusion :

MySQL is one of widely used database engine in world and with Node it really works very well. Node-mysql pooling and event based debugging is really powerful and easy to code.

Further reading:

I believe Github help page for node-mysql module is enough. Have a look and give it a star.

If you are interested in learning full stack development, you can downloa and this book.

Shahid (UnixRoot) Shaikh

Hey there, This is Shahid, an Engineer and Blogger from Bombay. I am also an Author and i wrote a programming book on Sails.js, MVC framework for Node.js.

Related Posts

104 Comments

  1. Hey, thanks for this. I’m checkin’ it out. FYI, there’s and extra comma in the first package.json file example.

    Project directory:
    —node_module
    I think “node_module” is missing an “s”.

    I assume that index.js contains the first connection to the mysql code, but it doesn’t mention it anywhere – it says to “node file_name.js”

    I think I got that first part to work now. Thanks.

  2. Hello,

    It seems to me that you are registering multiple times the same callback on each connection(when the connection is reused). A better approach could be to conditionally register if connection.listeners(‘error’) if false and EventEmitter.listenerCount(connection, error) matches the count of callbacks registered globally on a specific connection(in this scenario 1).

  3. Where do you keep that script, which connects database? Does that live on client side or server side?

      1. I was using an angular project which is started by calling ‘npm start’. It starts on port 8000 and the angular project is working. When I integrated this mySQL into it, I hoped a call to route defined in my app.js which in turn would invoke controller in controller.js, which would call
        $http.get(‘http://localhost:3000/load’).success(function(data){
        console.log(“kdfhg”+data);
        $scope.actors=data;
        });
        I don’t understand that in this flow, how and when server.js would be called. Do I need to start service defined in server.js as well by using ‘node server.js’. I tried that anyway. With same port, it is not working. With different port, it gives cross origin problem.

        1. Both proyect star call the same port Localhost:8080 do you need change the port just for one can be Localhost:8081 or other that you prefer

  4. It seems you have defined two error handeling routines for when something goes wrong with the connection.

    if (err) {
    connection.release();
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    }

    and a second time here:

    connection.on(‘error’, function(err) {
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    });

    Why is this and what is de difference between the two. When for example the database is down. Wich of the two wil be called. Or both?

    1. Hi Chris,
      The line
      if (err) {
      connection.release();
      res.json({โ€œcodeโ€ : 100, โ€œstatusโ€ : โ€œError in connection databaseโ€});
      return;
      }
      is to find out that whether there is any issue connecting with database. Once DB is connected, there are chances to get errors like PROTOCOL_CONNECTION_TIMEOUT and those kind of special database errors comes from .on(“error”).

      Hope its clear.

      Thanks,
      Shahid.

  5. Thanks, written very clearly.
    Can i have 1 question? ๐Ÿ™‚
    Im not very sure about this code:

    connection.on(‘error’, function(err) {
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    });

    This will be executed when connection is established already, but somewhy it got and error (disconnect). Am i right?

    1. Ya i should change error message actually, these kind of errors comes up after connecting to Database, like maximum queue length reached or connection time out and many more.

  6. Hey Mate NIce tutorial, Can you reccommend any books or other tutorials to learn more about nodejs and mysql? Thanks man!

  7. Hey i’m discovering node atm and i’ve read your tuto but i can’t connect to my database, probably a configuration problem but I am a bit lost, is there an option I should activate on my server configuration to access my db ?

    Thank you

    1. Actually i found it, is was an host problem, but here is my other question: how can you use a variable in your query? Like :
      var pseudo = ‘Jon’;
      connection.query(‘Select name from users where name=’ + pseudo, function(err,rows,fields){

      1. You can send it to View ( user end ) using response variable. Call API using jQuery HTTP method and whatever you return from node in response variable will be present in response variable of jQuery HTTP call.

  8. In the following code is connection.release(); necessary my app is working after removing it.

    if (err) {
    connection.release();
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    }

    1. That’s a good question. It depends upon the application actually, let’s say you have Share market app where in one second you need to do 100000+ sql query, you connectionLimit should increase else pooling will be very slow.

      1. You can also be guided by the number of CPU cores on the host, if the database is on the same device as the node.js server, the type of disks (SSD vs spindle), and any other significant applications running on the host. You want to aim for maximum utilisation of all CPUs, you need to take account of the time spent doing work that is not very CPU-intensive (e.g. waiting for the network or disk access), but you don’t want to go too much over that “sweet spot” or you will waste resources just swapping tasks between CPUs.

        In my case, my host has 72 CPU cores, the database is on the same host as the node.js server, and the storage is SSD, NVMe TLC (so very fast, efficient disk access). I have found that a connection limit of 100 works well, and gets good resource utilisation. (You can monitor that with htop.) If your disk access is a bit slower, you can probably afford to go a bit higher, relative to the number of CPUs.

  9. Hello and thanks for taking the time to write this… I am getting the following warning while running your code after ten requests. Do you know how to best solve this?

    Best – Cameron

    (node) warning: possible EventEmitter memory leak detected. 11 listeners added. Use emitter.setMaxListeners() to increase limit.
    Trace
    at PoolConnection.addListener (events.js:160:15)
    at /home/cbergoon/Documents/Projects/test/app.js:32:20
    at Ping.onPing [as _callback] (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Pool.js:94:5)
    at Ping.Sequence.end (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Ping.Sequence.OkPacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
    at Protocol._parsePacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket. (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.emit (events.js:95:17)

    1. Hello and thanks for taking the time to write this… I am getting the following warning while running your code after ten requests. Do you know how to best solve this?

      Also note this is using the ‘production’ example…

      Best – Cameron

      (node) warning: possible EventEmitter memory leak detected. 11 listeners added. Use emitter.setMaxListeners() to increase limit.
      Trace
      at PoolConnection.addListener (events.js:160:15)
      at /home/cbergoon/Documents/Projects/test/app.js:32:20
      at Ping.onPing [as _callback] (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Pool.js:94:5)
      at Ping.Sequence.end (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
      at Ping.Sequence.OkPacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
      at Protocol._parsePacket (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:271:23)
      at Parser.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Parser.js:77:12)
      at Protocol.write (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/protocol/Protocol.js:39:16)
      at Socket. (/home/cbergoon/Documents/Projects/test/node_modules/mysql/lib/Connection.js:82:28)
      at Socket.emit (events.js:95:17)

      1. Hi this is warning, you can avoid or increase the socket listener amount. You can do so by using emitter.setMaxListeners() in node-mysql package code but i don’t think so that would be good idea.

      2. increase the listeners for you ‘connection’ by doing something like connection.setMaxListeners(100);
        or
        connection.setMaxListeners(0); //no limit

  10. The tutorial is very well written. And i was searching for this kind of solution for MySql and Node. Thank you!

  11. Hi

    I am testing my node.js code as well using siege, and just wondering why does mine do not have concurrency etc in my log, mine only has response time. I only have rps and response time min,max and avg. I used npm to install siege as well.

  12. If we have nested connection.query(),will we write connection.on(‘error’,func(){}) for each of connection.query().For example–

    connection.query(qry,function(err,rows){
    connection.query(qry,function(err,rows){

    });
    connection.on(‘error’,function(err){});
    });
    connection.on(‘error’,function(err){});

  13. Above code is working fine but what about put, post,delete query?
    using
    app.post(‘/client’, function (req, res) {
    value1 = {id: 3, hostname: ‘localhost-3002’, user: ‘abc’};
    console.log(“Insert command”);
    config.connection.query(‘INSERT INTO client SET ?’, value1, function (err,result) {
    if (err) {
    console.log(“ERROR IN QUERY”);
    } else {
    console.log(“Insertion Successful.” + result);
    console.log(‘Inserted ‘ + result.affectedRows + ‘ rows’);
    res.end(result);
    }
    });
    });
    It is not working. please help in inserting, deleting and updating

  14. How did you get siege running in windows? Looks like in your screenshot you’re showing results in windows, status using siege right? So do you install it only on Ubuntu and then somehow your PC can run siege from node? I don’t get it.

  15. Also I’m using OS X, if I wanna use siege, then I install it on the Ubuntu server then still when I run node from the command-line I can use siege, how does that all wire up to running my app local on my machine?

  16. This is a great tutorial.

    I have few doubts:
    1) How could we decide the connection limit?
    2) What type of draw backs happen if we increase the connection limits?
    3) Currently I’m using nodejs for a real time social media app how could make my choice with this case?

    1. Hi,

      1 : You can use connectionLimit syntax when you create MySQL connection.

      2 : There is no such major drawback as it will help you to do pooling of connection.

      3 : You have made a good choice, Node.js + Socket is one of the best choice for real time apps like social media. Also you can look over Meteor.js which is built for real time SPA apps.

  17. Great tutorial Shahid, I did learn from this. Quick question, when would you close or terminate all connections in a pool after you’re done with it? In other words, when to call the pool.end() function? Also, pls note that I’m initiating the pool connection (pool.getConnection) in a separate route.js file, but have it created in the app.js.

    1. That’s a good question.

      Ideally whenever you do connection.query(), better to release that connection immediately so that other process can use the same connection ID. About destroying pool connection, that should happen when your program close or if you wish to close it to create another pool. I don’t find any good reason to do so in my code.

  18. I installed and ran siege on MacOSX 10.7 – No problem

    siege is weird – it runs very well as I keep increasing the number of concurrent users, until a number of concurrent users is reached and then it hangs

  19. After installing node mysql module
    What is default user and password
    how to change it
    how to create new database
    Before actually starting coding node.js code.
    How to access databases tables which tool (Like phpmyadmin for php) to be used.

    1. You have to set the user name and password which should be same as your MySQL one.

      To change the it just pass proper information to createConnection() function.

  20. Well documented Article shahid, i have a problem though..that is what brought me to this article…i am trying to run a webservice that keeps running listening for a post request…and when it see’s one i will insert data into mysql…but my problem is that the connection ends and it also kills the nodejs script.right not i am bypassing this by using forever but i don’t think thats a good solution.do u know why mysql conneciton end would be killing nodejs app???

    1. Hey,

      I am sure you have web server which runs on port and listen to request.

      And connection ends to where mySQL ? Best practice is to create pool connection when your node program starts and on each request you can either check if connection exists or create new mySQL connection.

      Hope it helps.

  21. Hey nice article. It’s very clear. But I have a question about integrating AngularJS in the front. Let’s suppose I got nodeJs in the back and communicating with MySQL. How can I deal with AngularJS in the front and how will the communication be. Also if I wanted to deploy that application, where it will be ? I mean for php for example I can find anywhere I want a free webhost. Is it the same for node ?

  22. Great write up! Can you please provide some suggestions for organizing this code in a MVC architecture?

  23. Thanks for this tutorial. I came across it while trying to learn some basics about NodeJS/MySQL. I love that you included the bit about Siege, which is fascinating stuff.

    When I upped the siege test to 1000 users, I ended up getting availability of only 66%. Not sure what MySQL setting I need to tweak on my local machine, but when I ran siege with 100 users it got 100%.

  24. Nice tutorial, but I have 1 question, does the api support parameterised queries? As the above has sql injectable written all over it.

  25. First all, thank for this great and fabulous tutorial !!!
    I am newbie working with server side. my question is when I have with many queries to my database from different routes.

    server.js
    –routes
    –index.js
    –queries1.js
    –queries2.js
    –queries3.js
    –public
    –views
    –node_modules

    server.js
    ==========================
    var express = require(“express”);
    var mysql = require(‘mysql’);
    var app = express();

    var pool = mysql.createPool({
    connectionLimit : 100, //important
    host : ‘localhost’,
    user : ‘root’,
    password : ”,
    database : ‘address_book’,
    debug : false
    });

    app.use(‘/’, routes);
    app.use(‘/users’, users);
    app.use(‘/queries1’, queries1);
    app.use(‘/queries2’, queries2);
    app.use(‘/queries2’, queries3);

    queries1.js
    ============================
    var express = require(‘express’);
    var router = express.Router();

    function handle_database(req,res) {
    pool.getConnection(function(err,connection){
    if (err) {
    connection.release();
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    }
    console.log(‘connected as id ‘ + connection.threadId);
    connection.query(“select * from user”,function(err,rows){
    connection.release();
    if(!err) {
    res.json(rows);
    }
    });
    connection.on(‘error’, function(err) {
    res.json({“code” : 100, “status” : “Error in connection database”});
    return;
    });
    });
    }

    router.get(“/”,function(req,res){-
    handle_database(req,res);
    });

    My question is when we have with many queries from many routes.
    my question is this method is the best way to work with many queries from many routes. If this the best way, how do I connect my variable “pool” from server.js to query.js.

    if there is other way, please could you give me some light about it??

    Thank you in advance for your help!!!
    Best regards,
    Frank

    1. Having different file for each query is nightmare and that too with pool.

      Best way which i feel is having different models folder where you keep every single code of database handling.

      So like this.

      folder : models
      db.js – contains function to create pool and return connection.
      queries.js – contains queries – do not require db.js here.

      in server.js require db.js and create new pool and store that pool into GLOBAL variable and access it in every query.

      Hope i helped.

  26. Your tutorial was awesome bro. I fixed an error after reading your tutorial.
    If was not releasing the connection in connection pooling.
    Thanks.

  27. I am getting following error

    C:\Users\Cres\NodeJS\DatabaseTest>node test
    { [Error: ER_BAD_DB_ERROR: Unknown database ‘nodejs’]
    code: ‘ER_BAD_DB_ERROR’,
    errno: 1049,
    sqlState: ‘42000’,
    fatal: true }
    C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\lib\protocol\Parser.js:77
    throw err; // Rethrow non-MySQL errors
    ^

    TypeError: Cannot read property ‘release’ of undefined
    at C:\Users\Cres\NodeJS\DatabaseTest\test.js:19:21
    at Handshake.onConnect (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql
    \lib\Pool.js:54:9)
    at Handshake.Sequence.end (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\my
    sql\lib\protocol\sequences\Sequence.js:96:24)
    at Handshake.ErrorPacket (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mys
    ql\lib\protocol\sequences\Handshake.js:103:8)
    at Protocol._parsePacket (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mys
    ql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\lib\pr
    otocol\Parser.js:73:12)
    at Protocol.write (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\lib\
    protocol\Protocol.js:39:16)
    at Socket. (C:\Users\Cres\NodeJS\DatabaseTest\node_modules\mysql\
    lib\Connection.js:96:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)

    Please help

      1. Yes my mysql is working
        Actually i am php guy with phpmyadmin
        I Just Started with node.
        Every thing is fine with node ,but with database node is not working properly

          1. Hey shahid
            Home Query window phpMyAdmin documentation Documentation
            appium
            auburnpublicschools (11)
            books (1)
            circleflip (28)
            codeigniter-test (1)
            crescerance (20)
            demoinvent (26)
            demotest (12)
            educollaborators (30)
            embr (12)
            information_schema (37)
            knextest (1)
            m1
            mad-teacher-new (17)
            madlearn-teachers (11)
            multi_directory (35)
            multisites (41)
            mydb (1)
            mysql (24)
            nodejs (1)
            performance_schema (17)
            piwikdemo (13)
            siouxcity (85)
            test
            test (5)
            _db (1)
            test09
            travel (28)
            truefinancial (11)

            Above are my databases

  28. Greetings! I’ve been following your blog for
    some time now and finally got the courage to go ahead
    and give you a shout out from Lubbock Texas! Just wanted to tell you keep up the fantastic job!

  29. Sir, what will happen if I will not release the connection after performing any query. I am connecting with database only once and after create or select queries I am not releasing connection. It will effect to my application or not. Thanks in advance.

      1. Hi Shahid, thanks for the reply I changes my code as
        mysql_connection.getConnection(function(err, connection) {
        if(err){
        console.log(” getConnection error: “+err);
        callback(“”,’database’);
        }
        connection.query(‘SELECT * FROM portfolio.users WHERE ‘ +
        ’email =”‘+ request.email+'”‘, function(err,res) {
        if(err){
        console.log(“database error” + err);
        callback(“”,’database’);
        }
        else if(res == ‘null’ || res == ”){
        console.log(“n data”);
        connection.release();
        callback(“”,’null’);
        }else{
        console.log(“in3”);
        console.log(‘%s: %s \n’, res[0].email, res[0].password);
        connection.release();
        callback(“”,res[0]);
        }
        });
        });

        I need to know if I am using two callbacks so I have to release cnnections in both the places.

  30. Hello Shahid i had an error with this part<

    if(err){
    connection.release();
    log.error('Connecting to database')
    context.fail(null);
    }

    TypeError: Cannot read property 'release' of undefined

    when i remove con.release (); my code works properly

      1. i see , what about if i wrote wrong the query? it never ends , stay in the part Connected as id xxxx
        how should i catch those errors?? thank you for your answer

        1. So at the end this is my code working properly :function handle_database(req,res){
          pool.getConnection(function(err,con) {
          // body…
          if(err){
          log.error(‘Connecting to database’)
          context.fail(err);
          }
          log.info(‘Connected as id ‘+ con.threadId);

          con.query(‘select * from persons’, function(err,rows){
          con.release();

          if(err){
          log.info(‘Resolving the query’)
          context.fail(err);
          }
          log.info(‘Connection succefull’)
          context.succeed(null);
          });

          con.on(‘error’,function(err){
          log.error(‘Connecting to database’)
          context.fail(err);
          });

          });
          };

          idk if your code is wrong or mine

  31. I’m sorry if this question has already been done, since there’s a lot of comments and I didn’t read them all…
    You are creating a pool of connections, but nodejs is a non-concurrent server, therefore even if you have more than one connection (of the pool), only one will be used at the same time (unless you also have a pool of nodejs servers, managed by some load-balancer like nginx)

    So my point is, there’s no sense of creating pooled mysql connections, since each server will only use once at a time, right?
    Actually I came to this conclussion after running the following test with ab:
    ab -c 1000 -t 30 localhost:7777/test

    # In a mysql 100 pool connection
    > Requests per second: 662.82 [#/sec] (mean)
    > Time per request: 1508.716 [ms] (mean)

    # In a mysql 1 pool connection
    > Requests per second: 1346.24 [#/sec] (mean)
    > Time per request: 742.811 [ms] (mean)

    # In a mysql no-pool (direct) connection
    > Requests per second: 1732.07 [#/sec] (mean)
    > Time per request: 577.344 [ms] (mean)

    Knowing this, isn’t it better to have just one direct connection for each nodejs server?
    The main problem with your code, is that you are doing connection.end();
    You can (and must) leave it open for future requests, that’s the reason your siege is failing…

    1. Hey Daniel,

      This is the awesome question so far and let me answer it with my understanding.

      First, about the non-concurrent server, I believe you meant single thread ? That’s true but let’s get to the point of threading a little bit later.

      Regarding MySQL pool, what is happening is Node does accept a lot of connection at once and generate thread inside for the various different ops, in this case, MySQL queries. So you have to say 1000 connections and all of them want to query 1000 SELECT queries.

      If I don’t use Pool and just one connection, then I have to do this.

      Repeat till 100
      Execute query.
      Get the next request.
      Done

      This will block your event loop, we want to execute 1000 queries in parallel. But, MySQL does execute them in some queuing manner which is not our concern at this moment. With pool, I can create 100 connections at a time, and a minute MySQL completes one SQL query, I am releasing it to Pool so that same can be reused (Performing connection does take time and HTTP request).

      Hope I answered this part. Regarding threading, libev (event loop of Node) performs the internal thread management by giving programmer one single thread. So for each I/O, Network call there are different threads (or maybe it reuse some, not sure).

  32. i am facing problem with node and mysql
    whenever i fetch data mysql which is having more than 30k rows and display the result as json .
    node is not performing well.
    i have to get the mysql result in array , so there is will be for loop which is blocking event

  33. Fantastic tutorial , but Its happended and strage thing:

    connection.query doesnt work when i set a sleep() function as it: (Any idea?)

    connection.query(‘UPDATE……’)
    sleepFor(2000);

    function sleepFor( sleepDuration ){
    var now = new Date().getTime();
    while(new Date().getTime() < now + sleepDuration){ /* do nothing */ }
    }

  34. Fantastic tutorial , but Its happended an strage thing:

    connection.query doesnt work when i set a sleep() function as it: (Any idea?)

    connection.query(‘UPDATE……’)
    sleepFor(2000);

    function sleepFor( sleepDuration ){
    var now = new Date().getTime();
    while(new Date().getTime() < now + sleepDuration){ /* do nothing */ }
    }

  35. Hi ,
    how can we use it in different files like for example employee.js and inventory.js
    like iam unable to find example wherein you define database name and connect in app.js and use some thing so that we can access db obj in routes file employee.js and inventory.js

    1. Hi matt,
      I do it like this.
      – Create a separate file containing code for DB handling such as Connection etc.
      – Create different model files that deals with database operation and call our DB file to handle DB stuff.
      – Include these model files in controller where our routes is and call those functions on API call.

      Hope it help.s

Leave a Reply

Your email address will not be published. Required fields are marked *