What is MySQL connection pool?
A connection pool is similar to a cache where data is stored and can be reused. Here the data is database connections that can be reused when there is a demand for data.
Why do we need a connection pool?
So why do we need a connection pool instead we can create new connection every time a request made to the database?
Opening and maintaining a new database connection for requests made to dynamic-driven websites is costly and wastes resources. Also whenever you create a new connection you will have only one connection it lasts until the connection is ended or closed by the MySQL server and if you have multiple connections you have to manually manage a number of connections.
But when using a connection pool…
When you request a connection from a pool you will receive a connection from the pool that is not being used currently. Instead of opening a new connection, it simply borrows a connection from the pool and returns it as soon as it is not needed. A connection pool also has a connection limit if the connection limit is reached during requests, it will wait until a connection is available before it continues. In pools, connections don't need to be closed manually they are opened, closed & reused as needed.
Using connection pool with NodeJS
In NodeJs you can use mysql npm package to interact with your MySQL database.
Conclusion
By Using connection pool
- Reduces connection creation time.
- Reduces time for a user to establish a connection to the database.
- Controlled resource usage reduces the load on the server.