How to properly close connections to mysql in node?

So, for example, we have a function that performs some kind of transaction, it gets imported in different parts of the application where it keeps being called.

// myDb.mjs
export const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'test'
});


// myFunctions.mjs
export function query() {
  db.query('SELECT * FROM table', (error, res) => {
    console.log(res)
  })
}

Now, in PHP using PDO the connection lives until the scripts ends (see related: Is it necessary to close PDO connections), How do I handle this in node using mysql ?

1. I see that there are connection.end(), connection.destroy(), When to call them, after the query executes, at the end of the script, do I have to end the connection manually ?
2. Does the server opens a connection to the database every time a fetch reqeust comes through from the client ?

Ideally you don't close them, you return them to a connection pool. Database connections are often a contentious resource so you should create them only when necessary.

In the code you have here, a singular connection is created in the module and used for all subsequent operations. If that connection fails, expires, or is otherwise incapacitated, your program will just emit errors.

I'd recommend using a light abstraction layer like Sequelize. This has connection pool support, issuing connections that are tested before issuing, and as a bonus your code becomes more database agnostic.

You'll also want to use promises, as callback-driven code gets extraordinarily messy really fast.

A better approach is to use a managed pool of connections. It cashes a set of connections and reuses them when necessary. This method enhances the performance and the response time as well. The pool takes care of opening, reusing, and closing the individual connections.

 var pool = await mysql.createPool(connectionStr);
 ...
 ...

 //When the application closes
 pool.close()