When to open the connection using node-mysql module?

The open-query-close pattern generally relies on connection pooling to perform well. Node-mysql doesn't have any built in connection pooling, so if you use this pattern you'll be paying the cost of establishing a new connection each time you run a query (which may or may not be fine in your case).

Because node is single threaded, you can get away with a single persistent connection (especially since node-mysql will attempt to reconnect if the connection dies), but there are possible problems with that approach if you intend to use transactions (since all users of the node client are sharing the same connection and so same transaction state). Also, a single connection can be a limit in throughput since only one sql command can be executed at a time.

So, for transactional safety and for performance, the best case is really to use some sort of pooling. You could build a simple pool yourself in your app or investigate what other packages are out there to provide that capability. But either open-query-close, or persistent connection approaches may work in your case also.

felixge/node-mysql now has connection pooling (at the time of this writing.)

https://github.com/felixge/node-mysql#pooling-connections

Here's a sample code from the above link:

var mysql = require('mysql');
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query( 'SELECT something FROM sometable', function(err, rows) {
    // And done with the connection.
    connection.end();

    // Don't use the connection here, it has been returned to the pool.
  });
});

So to answer your question (and same as @Geoff Chappell's answer): best case would be to utilize pooling to manage connections.