React+Node/Express中MySQL数据库连接管理及连接数异常问询

Hey there! Let's break down your database connection issues and walk through the best practices to fix them.

First: Understanding the Initial 60 Connections #

The Connections status variable you're seeing is a cumulative count of all connections that have ever been established to your MariaDB server, not the number of currently active connections. That initial 60 comes from past activity—like server startup checks, previous test runs, or old application sessions that connected and disconnected before you started monitoring.

To see the number of currently active connections, run this query instead:

SHOW STATUS LIKE 'Threads_connected';

This will give you a real-time count of open connections being used right now.

Why Your Connection Count Keeps Growing #

Looking at your code, the main issue is that you're creating a new database connection for every incoming request in your lessonOpen handler. Even though you call connection.end(), there are a few critical problems here:

• connection.end() is asynchronous, and you're calling it immediately after starting the openLesson Promise. If the query takes longer to execute, the connection might not close properly if an error occurs before the end call completes.
• If there's an unhandled error in the connection or query, the connection could be left open indefinitely, leading to stale connections piling up over time.
• Creating a new connection for every request is inefficient—establishing database connections is resource-heavy, and reusing connections is much better for performance.

The Best Fix: Use Connection Pooling #

Instead of creating a new connection each time, use MySQL's built-in connection pool. Connection pools maintain a set of reusable connections, so you don't have to create/destroy connections on every request. Here's how to refactor your code:

Step 1: Update Your Database Connection File #

Replace your Connection class with a connection pool setup:

const mysql = require('mysql');

// Create a connection pool with sensible defaults
const pool = mysql.createPool({
  host: 'localhost',
  database: 'your_database_name', // Fill in your actual DB name
  user: 'your_username', // Fill in your DB user
  password: 'your_password', // Fill in your DB password
  connectionLimit: 10, // Max number of concurrent connections (adjust based on your server's capacity)
  waitForConnections: true, // Wait for a connection if all are in use
  queueLimit: 0 // Unlimited queue for connection requests
});

module.exports = { pool };

Step 2: Refactor Your Query Handler #

Use the pool to get/reuse connections, and make sure to handle cleanup properly. Also, fix the SQL injection risk in your original code—never concatenate user input directly into SQL queries!

Option 1: Using getConnection for explicit control (great for multiple queries in one request):

const { pool } = require('../database.js');

function openLessonSections(lessonID, connection) {
  return new Promise((resolve, reject) => {
    // Use parameterized queries to prevent SQL injection
    const sql = "SELECT * FROM sections WHERE lesson_id = ?";
    connection.query(sql, [lessonID], (error, result) => {
      console.log('Loading lesson "' + lessonID + '".');
      if (error) reject(error);
      resolve(result);
    });
  });
}

async function openLesson(lessonID, connection) {
  return await openLessonSections(lessonID, connection);
}

exports.lessonOpen = function(req, res, next) {
  console.log('request received');
  const lessonID = JSON.parse(req.body.lessonID);
  console.log('Opening lesson: ' + lessonID);

  // Get a connection from the pool
  pool.getConnection((err, connection) => {
    if (err) {
      console.error('Failed to get database connection:', err);
      return res.status(500).json({ error: 'Database connection failed' });
    }

    openLesson(lessonID, connection)
      .then(result => {
        console.log('The lesson was opened successfully.');
        res.status(200).json({ sections: result });
      })
      .catch(error => {
        console.log('Error opening lesson: ' + error);
        res.status(500).json(error);
      })
      .finally(() => {
        // Always release the connection back to the pool, regardless of success/failure
        connection.release();
      });
  });
}

Option 2: Simplified version using pool.query (automatically handles connection pooling):

const { pool } = require('../database.js');

exports.lessonOpen = function(req, res, next) {
  console.log('request received');
  const lessonID = JSON.parse(req.body.lessonID);
  console.log('Opening lesson: ' + lessonID);

  const sql = "SELECT * FROM sections WHERE lesson_id = ?";
  // pool.query automatically gets a connection, runs the query, and releases the connection
  pool.query(sql, [lessonID], (error, result) => {
    if (error) {
      console.log('Error opening lesson: ' + error);
      return res.status(500).json(error);
    }
    console.log('The lesson was opened successfully.');
    res.status(200).json({ sections: result });
  });
}

Key Takeaways #

• Use Threads_connected instead of Connections to monitor active database connections.
• Connection pooling is the standard for Node.js/Express database apps—it improves performance and prevents connection leaks.
• Never concatenate user input into SQL queries—use parameterized queries to avoid SQL injection attacks.
• Adjust the connectionLimit in your pool based on your server's resources (start with 10-20 and tweak as needed).

内容的提问来源于stack exchange，提问作者devamat