Mysql: Persistent connection VS connection pool for web applications

[PeppeL-G]

I don't know how database connections works internally, but I have a question I haven't been able to find the answer to, so here we go.

It says in the README:

Rather than creating and managing connections one-by-one, this module also provides built-in connection pooling using mysql.createPool(config).

I wonder how/if a web application in Node.js would benefit from using a connection pool instead of a single persistent connection created when the web application starts. Is a connection kind of "blocking" so it can only send one query to the database at a time, so it can't send the next one before it has received back the result for the previous one? Otherwise, what would the benefits be of using a connection pool?

[sidorares]

Is a connection kind of "blocking" so it can only send one query to the database at a time, so it can't send the next one before it has received back the result for the previous one?

Yes, client is not allowed to send commands until current command is not finished, communication is always half duplex. Another benefit is much easier error handling and reconnection. If you want to manually track one persistent connection you need to listen and handle "error" event on connection itself, errors in query callback and decide yourself if connection is still in good state or you need to create new one. With pool ( even when max conn number limit is set to one ) this all is managed for you.

what would the benefits be of using a connection pool?

in short:

• Better performance if you allow more than one connection and have long running queries ( or high network latency, or both)
• less code to handle errors correctly

[PeppeL-G]

Thanks @sidorares, precisely what I was looking for.

Do you also know if this is the case for database connections for other common databases (Microsoft SQL Server, Oracle Database, PostgreSQL, etc.)?

[sidorares]

Do you also know if this is the case for database connections for other common databases (Microsoft SQL Server, Oracle Database, PostgreSQL, etc.)?

Don't know, but could be either way (if you really curious ask maintainers of network clients. Not sure one exist for Oracle, they never published protocol docs, only C API). All 3 very old protocols. Some old tend to assume half duplex as some of the hardware could not handle full duplex communication back then. At the same time X11 protocol for example ( which is 35 years old ) is extremely optimised for latency and allows you to send bunch of commands before you receive first reply even when commands depend on each other ( you generate resource id on the client and this is used as "future result" input for consuming commands )