Building a scalable API with Node and MySQL/MariaDB

Question

What is considered best practice for handling and managing connections when building an API or web application with Node.js that depends on MySQL (or in my case, MariaDB)?

Per the documentation for node-mysql, there seem to be two methods to use:

var connection = mysql.createConnection({...});

app.get("/", function(req, res) {
   connection.query("SELECT * FROM ....", function(error, result) {
    res.json(result);
   });
});

-- or --

var pool = mysql.createPool({...});

app.get("/", function(req, res) {
   pool.getConnection(error, connection) {
     if (error) {
       console.log("Error getting new connection from pool");
     } else {
       connection.query("SELECT * FROM ....", function(error, result) {
         connection.release();
         res.json(result);
       });
     }
   });
});

To me, it makes the most sense to use the second option, as it should use as many connections as are needed, as opposed to relying on a single connection. However, I have experienced problems using a pool with multiple routes, i.e each route gets a new connection from the pool, executes a query, and releases it back into the pool. Each time I get a connection from a pool, use it, and release it, it seems there is still a process in MySQL waiting for another request. Eventually, these processes build up in MySQL (visible by running SHOW PROCESSLIST) and the application is no longer able to retrieve a connection from the pool.

I have resorted to using the first method because it works and my application doesn't crash, but it doesn't seem like a robust solution. However, node-mariasql looks promising, but I can't tell if that will be any better than what I am currently using.

My question is: what is the best way to handle/structure MySQL connections when building an API or web application that relies heavily on SQL queries on almost every request?

Answer 1

Changing connection.release() to connection.destory() solved my issue. I'm not sure what the former is supposed to do, but the latter behaves as expected and actually removes the connection. This means once a connection is done being used, it kills the MySQL process and creates another when needed. This also means that many queries can hit the API simultaneously, and slow queries will not block new ones.