Node.js + socket.io and mysql how to organize a connection?

Question

MySQL

I need to make a chat, for this I decided to use a bunch of node.js + socket.io and mysql.
As a database, the choice fell on mysql, because it is used for the site on which the chat will hang. Both node.js and php scripts of the site will work with the tables that will be used for the chat.
This is where the question comes from.
How to organize a connection to the database in node.js, i.e. connect only once and work with the database through this connection or make a connection for each request from the client, perform tedious actions with the database and break the connection.

Answer 1

It is necessary to open a connection pool through mysql.createPool using the https://www.npmjs.org/package/mysql library so that it does not happen that one is busy for a long time with a request, and the other has come asynchronously and wants to be processed while the previous one is still did not return. On the connection break event, you need to hang its resumption. For the case with one connection like this (for a pool, this must be hung on each connection:

var mysql = require('mysql');
connectMySql();

function connectMySql() {
    var connection = mysql.createConnection(connectionString);

    connection.connect(function(err) {
        if (err) {
            setTimeout(function() {
                connectMySql()
            }, 3000);
        }
    });

    connection.on('error', function(err) {
        if (err.code === 'PROTOCOL_CONNECTION_LOST') connectMySql();
    });
}

If you have a small application, then feel free to make the connection or pool global so that it is immediately defined on the handlers, or even better, make a namespace for your application in the global context, for example myApp = {} and write myApp.db = connection into it so that then write myApp.db.query(...); See what beautiful and concise handlers you can make if you do not fall for these REST superstitions and prejudices against the state on the server and open long-term connections to the database and global namespaces:

// Обработчик для API по урлу http://127.0.0.1/example/app/examples/mysql/getCities.json
module.exports = function(client, callback) {
  aliasNameMy.query('select * from City', function(err, rows, fields) {
    callback({ rows:rows, fields:fields });
  });
}

On github, this handler is in the context of a demo application: https://github.com/tshemsedinov/impress/blob/maste...
Well, I will also recommend my utilities for the MySQL driver - https://www.npmjs.org/package/ mysql-utilities
and two articles on Habré, one on these utilities - habrahabr.ru/post/198738 , and the second on architecture in general - habrahabr.ru/post/204958

Answer 2

https://github.com/S-anasol/sancrypto/blob/master/...
Here is an example
You put node-mysql or just mysql I don't remember exactly and go ahead.
npm install node-mysql

Answer 3

You shouldn't keep the connection open for a long time. Rule: open as late as possible, close as early as possible.

Answer 4

Timur Shemsedinov, I have a question about the database connection pool.
For example, in the main application file, I create a global
app.js pool

global.appChat = {};
appChat.pool = mysql.createPool(config.get('mysql'));

Then in the user module I create methods for working with the
user.js database

module.exports.getUserById = function(userId, callback){
    $sql = 'SELECT * users WHERE id = ?';
    appChat.pool.getConnection(function(err, connection) {
        if (err) throw err;
        connection.query($sql, [userId], function(err, rows, fields) {
            if (err) throw err;
            connection.release();
            callback(null, rows.length ? rows[0] : null);
        });
    });
};

module.exports.getUsers = function(callback){
    $sql = 'SELECT * FROM users';
    appChat.pool.getConnection(function(err, connection) {
        if (err) throw err;
        connection.query($sql,function(err, rows, fields) {
            if (err) throw err;
            connection.release();
            callback(null, rows);
        });
    });
};

Now, when I call the methods of the user module somewhere, a new connection to the database will be created every time, and as I understand it, the call connection.release(); does not close the connection (since it is written in the node-mysql docs that all pool connections are closed by calling pool.end), then they will be constantly open and accumulate?