Node js (getConnection)
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 connect with nodejs mysql, I opened a new connection for every operation (message, get, put, delete) and release. Does this fit well? or is one connection better? what is the difference between all operations in one connection or one connection for each operation?
To clarify - Node.js is not single threaded. Your application code runs on a single thread, but under the hood it uses them as needed - look here (both the answer and the comments below):
There is only one thread in a Node.js Javascript program.
If you're looking for technical feasibility, Node.js can use streams to solve asynchronous I / O if the required operating system requires it.
and
Since a Node.js user (i.e. a Javascript programmer) that abstraction is that there is only one thread. In the case of a basic runtime (v8), it uses threads internally for - for example, profiling, and it can do it freely as long as it doesn't pass this information down to Javascript.
In other words, if you dive into actual runtime, you will find more than one thread helping to keep a single thread of Javascript running smoothly.
As you can see the module mysql
you are using, you need to pass a callback to the method query()
(and probably many others). So when you call it, execution of your code continues and the callback is called when results come from the database.
As for your question - you are not creating a new connection for every request. Take a look at the readme file of the module mysql
, Merge Connections :
Links are created lazily by the pool. If you configure the pool to provide up to 100 connections, but only ever use 5 at the same time, only 5 connections will be made. The connections are also cyclically circular in style, with connections taken from the top of the pool and returning to the bottom.
When a previous connection is pulled from the pool, a ping packet is sent to the server to check if the connection is good.
When you call dbPool.getConnection()
, a connection is only created if there are no more connections available in the pool - otherwise it just grabs one of its vertices. The call objConn.release()
puts the connection back into the pool - it doesn't disconnect. This call allows you to reuse other parts of your application.
Summarizing:
- Creating a new connection for each request is not a good idea as it will use more resources (CPU, RAM) on your application and database computers.
- Using the same connection for all requests is also wrong, because if any of the operations take a long time to complete the connection, it will hang waiting while all other requests are waiting for it.
- Using a connection pool is a great idea to allow multiple operations to be performed on your database at the same time, even if one of them takes a long time.
Update: To answer questions from comments:
When you use one connection for each request, the module mysql
has to open a new socket, connect to the database and authenticate before making its request - this takes time and eats some resources. This is a bad approach because of this.
On the other hand, when using only one connection (not a connection pool), executing a request that takes a long time will block any other requests on that connection until it completes - this means that any other request will have a Wait. This is also a bad approach.
Creating a new connection pool for each request is a lot like using a new connection, unless you name it pool.getConnection()
multiple times - then it's even worse (take the resources used when creating the new connection and multiply it by a number pool.getConnection()
).
To refine one connection for each operation and all operations in a single connection request:
Each operation on each connection starts after the previous one completes (this is synchronous, but not client-side), so if you have a table with several billion rows and a problem SELECT * FROM yourtable
, it will take some time to complete, blocking each operation on that connection until it completes.
If you have one connection for each operation that needs to be issued in parallel (for example, for each request), the problem goes away. But as stated earlier, it takes time and resources to open a new connection, which is why the concept of a connection pool was introduced.
So the answer is: use one connection pool for all requests (as in the example code) - the number of connections will scale according to your application's traffic.
Update # 2:
Based on the comments, I see that I should also explain the concept of connection pools. The way it works is you start your application with a connection pool empty and initialized to create a maximum of n connections (by default, it's the default for a module mysql
).
Whenever you call dbPool.getConnection()
, it checks for any available connections in the pool. If there is, it grabs one (makes it unavailable) unless it creates a new one. If the connection limit is reached and there are no available connections, an exception is thrown.
The call connection.release()
pushes the connection back to the pool so that it is available again.
Using pooling to get just one global connection for the whole application is completely wrong and against the very concept (you can do the same by simply creating a connection manually), so by using connection pooling I mean use connection pooling as it was meant to be used - to get connections from him when you need them .
source to share
It is good to open a new connection on different routes. There are two things:
1) your database can handle multiple connections at once.
2) nodejs is single threaded.
If you create one connection for all routes, it is likely that the database query that takes longer for the database will also starve for all other queries on the 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 are using different connections on different routes, and even if the database is running one blocking operation on one request, it will not affect the other request, as a shared database connection can do it.
source to share
Using one connection each time, implicitly control your transaction. Thus, the result will be recorded and so visible to other users. If you are using the same connection, you need to commit it on update, add, or remove to make it visible to others.
But, if you, for example, use a loop to add many lines; you should consider using a unique transaction; because on the db server side there is an overhead to manage context and connection transactions.
So my answer is, it depends on what you plan to manage in your application. If you may have a DML package, consider using a unique join. Alternatively, you can use multiple connections.
source to share