Node.js 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.

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.

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.
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.

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

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

Here is output.

**UPDATE**

You can directly use pool.query() which internally will acquire connection and release it when query is executed. In my personal code review experience, majority of the developers often forget to release the acquired connection which in turns creates bottleneck and database load.

function handle_database(req,res){// connection will be acquired automatically
pool.query("select * from user",function(err,rows){if(err){return res.json({'error':true,'message':'Error occurred'+err});}//connection will be released as well.
res.json(rows);});}

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

app.listen(3000);

I have used this function in a production environment with heavy payload and it works like charm.

Final comments :

Siege is a really powerful tool for the 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 time but rest 900 will be in the queue. So the code will not break.

Conclusion :

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

Hey Very Nice article, I m new to Node and would like to know . If i have multiple function to get data ex: shopping list, recommendation, user details, cart details . How can i use this connection in the same page .
Do i have to use the function handle_database(req,res) in each action is it . Please revert.

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).

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(‘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.

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”).

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 ?

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){
…

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.

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.

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.

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.

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.

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?

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?

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.

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.

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.

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???

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.

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 ?

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%.

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.

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.

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);

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

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…

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).

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

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

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.

I was trying this sample and found the first set of code where you initially do a siege and it produces the error, may be better to just use a browser instead of siege as always the 2nd call to that node server code would error.

I took it a step further and put the createConnection and connect within the app.get function and did some stress testing using an external RDS mysql. I found that straight createConnection vs createPool connection method results in almost no transaction rate difference (390trans/sec on a siege -c200 -t30s -d1

That leads me to really question the benefit of that added pool code complexity. I get the idea of reusing a pooled cached connection but not sure where the real benefit is?

I tried but was unable to even break the straight app.get {createConnection / connect / query / end} type connection without pooled connections.

actually i was wrong. I missed the connection failed errors. under a siege -c200 -t60s -d3 non-pooled connect resulted in 16 fails out of about 8000 hits. So I did prove pooled connection is needed to queue under stress. Thanks for a great learning tool

Installed mysql however npm appears to think that it is extraneous. I have not been able to find any reason why this is so. My only guess is that mysql is somehow already installed with npm by default?

I have the following error when using the address of my server, with LOCALHOST or 127.0.0.1 it works perfectly:
Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘idsprueba’@’static-201-245-170-116.static.etb.net.co’ (using password: YES)
at Handshake.Sequence._packetToError (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolsequencesSequence.js:52:14)
at Handshake.ErrorPacket (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolsequencesHandshake.js:103:18)
at Protocol._parsePacket (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:279:23)
at Parser.write (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolParser.js:76:12)
at Protocol.write (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:39:16)
at Socket. (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibConnection.js:103:28)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
——————–
at Protocol._enqueue (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:145:48)
at Protocol.handshake (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibprotocolProtocol.js:52:23)
at Connection.connect (C:UsersLeoDocumentsElectronAppsNutSisnode_modulesmysqllibConnection.js:130:18)
at logIn