How to properly connect to the database in loaded projects?

Question

MySQL

In all tutorials for Noda and Express, one global connection to the database is simply created (we will talk about MySQL). Immediately, in the application code, all the views that operate on this connection are described.

I'm concerned about the following points:

1) How best to make the connection available everywhere, incl. other modules? Should I declare it in globals or store it in my module?
2) Should I store and maintain one connection or create a new one for each request? In the first case: what if the DB server tore it apart?
3) I read about the connection pool that you need to return each connection to the pool by calling the end () method. How can I make it return itself when the request completes? Because there can be many exit points from one view, writing conn.end() in each one is tiring.
4) It follows from 3) I tried to make a pool in the project, closed all connections, the MySQL server still shows that the number of connections is growing. What can be wrong?

Thank you.

Answer 1

Once a mistake in this place (making one global connection to the database) cost me a whole weekend. I’ll make a reservation right away that it was not mysql that was used, but the node-postgres module, but the essence should be the same. With a heavy load, long requests blocked the base socket, and as a result, the operation of the entire service. Therefore, it was necessary to urgently redo the model on the Connection Pool, which at the development stage somehow went out of attention, since everything seemed to be fine on one connection with a small database of test data. :)
The pool can organize several parallel connections that will not interfere with each other. At the end of the request, the connection is released and goes to the pool. If there are no free slots in the pool, the request is queued until one of the slots becomes free. As for accessing the database, I used the simplest option - direct access through the module and its default connection parameters. In each file that needs a database, the database driver is required and the database is accessed through it. When using a pool, it is imperative to monitor the release of connections (the done function). The number of connections in the pool is set by the module settings option.
You can read about how to use the pool in pg here .

Answer 2

Thanks for the answer, I will rewrite the project under the pool.
But still, is it possible to make the connection itself go to the pool after the request is executed? Middleware or override some function?