Database – Is it okay to keep open the MySQL database connection open through out the Application or open and close connection query-wise

apidatabaseMySQLnode.js

I'm developing a REST API using Nodejs along with MySQL as the database.Is it okay to keep open the MySQL database connection open through out the Application or open and close connection query-wise ? Is there a best practice or a standard methodology for handling situations like this.

Best Answer

For fast response time and high throughput, it's actually best to keep database connections open and reuse them for subsequent requests. Most database frameworks offer some kind of connection pool mechanism where a request handler can get a database connection for its work and return it to the pool afterwards.

Note that the answer referenced by @RomanBoegli mentions connection pooling, too, so we're not in contradiction here. You should just mentally replace opening and closing of the database connection with getting one and returning it to the pool, and should keep it for as short as possible.

Related Solutions

MySQL and JavaScript – One Connection vs Multiple Connections

Node is a single-process, single-threaded, asynchronous, event-driven application. So only one request/event is handled concurrently.

Since node-mysql looks like its API is synchronous, you should be fine with a single connection. This assumes that your application doesn't rely on SQL session variables, temporary tables, or transactions that might span multiple Node events.

C# – an appropriate way for managing a MySQL connection through C#

I would suggest using

using(MySqlConnection conn = new MySqlConnetion(cs)
{
    conn.open();
    //setup and execute query

} //conn gets closed here

Here, once you exit the using block, the connection is closed.

Best Answer

Related Solutions

MySQL and JavaScript – One Connection vs Multiple Connections

C# – an appropriate way for managing a MySQL connection through C#

Related Topic