Myujth

I wonder what is the optimal way to establish/maintain connection with MySQL/Redis (from nodejs): store in one single object (conn) or create a new connection on every request? Namely:

1, Should we use a single connection for every nodejs http request? Use connection pool? Or a single connection on every new request (so reconnection should be important because the connection could be randomly lost)? How is the performance?

2, What is the difference between MySQL and Redis in term of maintaining such connection?

I will tell you how I used to manage to do this.

1, Should we use a single connection for every nodejs http request? Use connection pool? Or a single connection on every new request (so reconnection should be important because the connection could be randomly lost)? How is the performance?

You don't want to create connections manually for every nodejs http request. Always use connection pooling if you are using nodejs mysqlijs/mysql module. I use it.

Pools take care of server reconnections automatically.

I don't have a benchmarks, but performance should be better because within pools connections can be reused once released. In other factors, believe me, creating and managing connections manually is cumbersome and error-prone.

Eg:
Declare your mysql connection pool in a Db.js file like below and export it.

var mysql = require("mysql");
var pool = mysql.createPool( 'localhost',
 user : process.env.DB_USER,
 password : process.env.DB_PASSWORD,
 database : 'mydb'
);

module.exports = db;

And use it in your inside an end-point in another file.

var pool = require('./Db.js');

app.get('/endpoint', function (req, res) 
 // ...
 pool.query('<your-query-here>', function (err, res, fields) 
 if (err) throw err;
 // do something with result (res)
 );

);

I prefer using both pool.query and pool.getConnection based on the scenario. Using query is safer because you don't need to consider releasing connection. It will be automatically handled by the library. getConnection is used only where several queries has to be run inside an end-point, so I can reuse the same connection to do that.

2, What is the difference between MySQL and Redis in term of maintaining such connection?

In short, You don't need pooling for redis. We don't think about pooling redis connections according to this.