Node js (getConnection)

Question

var nodePort = 3030;
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var db = require('mysql');
var dbPool = db.createPool({
    host : 'localhost',
    user : 'root',
    password : '1234',
    database : 'test',
    port : 3306
});


app.use( bodyParser.json() );
app.get('/api/db', function(req, res){
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("SELECT * FROM person", function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success',
                        err : '',
                        err_type : '',
                        fields : Fields,
                        rows : Rows,
                        length : Rows.length
                    });
                    objConn.release();
                }//else
            });
        }//else
    });
});
/*
app.get('/api/db:id', function(req, res){
    var id = req.params.id;
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("SELECT * FROM person WHERE id = ? ",[id], function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success',
                        err : '',
                        err_type : '',
                        fields : Fields,
                        rows : Rows,
                        length : Rows.length
                    });
                    objConn.release();
                }//else
            });
        }//else
    });
});
*/
app.post('/api/db', function(req, res){
    if(!req.body.tableName){
        var data = {
            ID : req.body.id,
            Name : req.body.name
        }
        tableName = 'person';
    }else{
        var data = {
            email : req.body.email,
            regid : req.body.regid
        }
        tableName = 'users';
    }//else
    console.log(req.body.regid);
    console.log(req.body.tableName);
    console.log(req.body.email);
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("INSERT INTO "+tableName+" SET ? ", data, function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success'
                    });
                    objConn.release();
                    if(!req.body.tableName){ gcmSend(); }
                }//else
            });
        }//else
    });
});

app.put('/api/db', function(req, res){
    var id = req.body.id;
    var data = {
        Name : req.body.name
    }
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("UPDATE person SET ? WHERE ID = ? ", [data,id], function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success'
                    });
                    objConn.release();
                    gcmSend();
                }//else
            });
        }//else
    });
});

app.delete('/api/db/:id', function(req, res){

    var id = req.params.id;
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            objConn.query("DELETE FROM person WHERE ID = ? ",[id], function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    res.send({
                        results : 'success'
                    });
                    objConn.release();
                    gcmSend();
                }//else
            });
        }//else
    });
});

function gcmSend(){

    message = new gcm.Message({
        collapseKey: 'demo',
        delayWhileIdle: true,
            timeToLive: 3,
            data: {
                title: 'Node.js den mesaj gönderildi'
            }
    });
    sender.send(message, registrationIds, 4, function (err, result) {
        console.log(result);
    });
}

function sendError(res, iStatusCode, strResult, strType, objError){
    res.send({
        results : strResult,
        err : objError.type,
        err_type : strType
    });
}

app.listen(nodePort);
console.log('App listening on port' + nodePort);

Hi,
I wrote some codes to connection with nodejs mysql, i opened new connection each operation (post,get,put,delete) and release. Is this good approaching? or one connection is better? what is the difference between all operations in one connection or one connection for each operation?

Answer 1

To clarify - Node.js is not single-threaded. Your application code is executed in one thread, but under the hood it uses them when needed - take a look here (both the answer and the comments below it):

To a Javascript program on node.js, there is only one thread.

If you're looking for technicalities, node.js is free to use threads to solve asynchronous I/O if the underlying operating system requires it.

And:

As far as the user of node.js (ie the Javascript programmer) is involved, the abstraction is that there is only a single thread. In the case of the underlying runtime (v8), it uses threads internally for - for example - profiling, and it may do so freely as long as it doesn't leak that information up to the Javascript.

In other words, if you dive down inside the actual runtime, you will find more than one thread helping to keep the single Javascript thread running smoothly.

As you can see the mysql module you use requires you to pass a callback for the query() method (and probably for many more). So when you call it, the execution of your code continues and the callback is called when the results from database arrive.

As for your question - you are not creating a new connection for every request. Take a look at the readme file of the mysql module, the Pooling Connections section:

Connections are lazily created by the pool. If you configure the pool to allow up to 100 connections, but only ever use 5 simultaneously, only 5 connections will be made. Connections are also cycled round-robin style, with connections being taken from the top of the pool and returning to the bottom.

When a previous connection is retrieved from the pool, a ping packet is sent to the server to check if the connection is still good.

When you call dbPool.getConnection() the connection is created only if there are no more available connections in the pool - otherwise it just grabs one from the top of it. Calling objConn.release() releases the connection back to the pool - it's not being disconnected. This call allows it to be reused by other parts of your application.

To sum up:

• Creating new connection for every request is not a good idea as it will use more resources (CPU, RAM) on both your app's and database's machines.
• Using one connection for all requests is also wrong because if any of the operations take a long time to complete your connection will hang making all other requests wait for it.
• Using a connection pool is a great idea that allows you to perform multiple operations on your database at the same time, even if one of them takes a long time to complete.

Update: To answer the questions from comments:

When you are using one connection for every request the mysql module has to open a new socket, connect to database and authenticate before you make your query - this takes time and eats some resources. Because of that it's a bad approach.

On the other hand, when using only one connection (not connection pool), running a query that takes a long time to complete will block any other queries on that connection until it completes - which means that any other request will have to wait. It's also a bad approach.

Creating a new connection pool for every request is pretty much like using new connection, unless you call pool.getConnection() multiple times - then it's even worse (take the resources used by creating a new connection and multiply it by the number of pool.getConnection() calls).

To further clarify the one connection for each operation vs all operations in one connection question:

Each operation in every connection is started after the previous one completes (it's synchronous, but not on the client side), so if you have a table with a few billion rows and issue SELECT * FROM yourtable it will take some time to complete, blocking every operation on this connection until it finishes.

If you have one connection for each operation that needs to be issued in parallel (eg. for every request) the problem disappears. But as stated previously opening a new connection requires time and resources which is why the connection pool concept was introduced.

So the answer is: use one connection pool for all requests (like you do in your example code) - the number of connections will scale accordingly to the traffic on your app.

Update #2:

Based on the comments I see that I should also explain the concept behind connection pools. How it works is that you start an app with a connection pool empty and initialized to create a maximum of n connections (afaik it's 10 for mysql module by default).

Whenever you call dbPool.getConnection() it checks if there are any available connections in the pool. If there are it grabs one (makes it unavailable), if not it creates a new one. If the connection limit is reached and there are no available connections some kind of exception is raised.

Calling connection.release() releases the connection back to the pool so it is available again.

Using a pool to get only one global connection for a whole app is totally wrong and against the concept itself (you can do the same thing by just creating the connection manually), so by use a connection pool I mean use a connection pool as it was supposed to be used - to get connections from it when you need them.

Answer 2

Using one connection each time, implicitly manage your transaction. So the result will be commited and so visible for other users. If you use the same connection, you have to commit when you update, add or delete to make it visible to others.

But, if for example you use a loop to add many rows ; you should consider to use a unique transaction ; because, there is an overhead on db server side to manage connection context and transactions.

So my answer is : It depends on what you plan to manage in you app. If you may have batch of DML , consider to use a unique connection. Else you may use multiple connections.

Answer 3

It is good to open a new connection on different routes. There are two things,

1)your database can handle multiple connections simultaneously.

2)nodejs is single threaded.

If you create a single connection for all routes it is likely that, a database request which takes longer for database will also starve all other requests on node js server, until the existing request is processed, because there is only one connection shared by the application.

on the other hand, If you use different connections on different routes then even if there is a blocking operation being performed on the database by a single request, this will not affect other request, since it can make saperate connections to the database.