在Node-Express应用中基于node-mysql建立共享连接的技术疑问

Hey there! Let's break down your setup and the questions you have clearly.

First, Is This Approach Reasonable? #

Absolutely. Since your app only serves 1-4 users, sticking with a single connection instead of a pool aligns perfectly with official recommendations for low-traffic scenarios. Exporting this single connection instance is also a smart move—it lets you reuse the same connection across different parts of your app without creating redundant connections, keeping your code simple and efficient for your user scale.

Critical Notes & Best Practices (注意事项) #

Even with a small user base, there are a few key things to keep in mind to avoid headaches:

• Handle Unexpected Connection Drops
  A single connection can fail unexpectedly (network blips, database restarts, etc.). If you don't handle this, your exported connection object will become unusable. Add an error listener to trigger a reconnection:

  connection.on('error', function(err) {
    console.error('Connection lost:', err.stack);
    // Recreate and reconnect
    connection = mysql.createConnection({ /* your original config */ });
    connection.connect();
  });
  

• Be Mindful of Serialized Query Handling
  Unlike a connection pool, a single connection processes one query at a time. For 1-4 users, this is rarely a problem, but if you ever get overlapping requests (e.g., two users interacting at the exact same time), queries will queue up. It's not a showstopper now, but it's a good thing to note if you plan to scale later.

• Clean Up Resources on App Shutdown
  Don't leave dangling database connections when your app stops running. Add a shutdown handler to close the connection properly:

  process.on('SIGINT', function() {
    connection.end(function(err) {
      console.log('Connection closed on app shutdown');
      process.exit(err ? 1 : 0);
    });
  });
  

• Ensure a True Singleton Export
  Double-check that your module exports the exact same connection instance every time it's required. Don't wrap the connection creation in a function that returns a new connection each time—this would defeat the purpose of reusing a single connection. Your current setup (create once, export once) is correct here.

Your Original Implementation for Reference #

var mysql = require('mysql');
var connection = mysql.createConnection({
  host : 'example.org',
  user : 'bob',
  password : 'secret'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }
  console.log('connected as id ' + connection.threadId);
});

// Export the connection for reuse across modules
module.exports = connection;

内容的提问来源于stack exchange，提问作者Shift 'n Tab