开发Node.js Post WebAPI遇报错：Global connection already exists. Call sql.close() first

Hey there! That error you're hitting when your API gets busy is totally fixable—it comes from how you're managing your database connections right now. Let's break down the problem and the solution step by step.

What's Causing the Error? #

Looking at your code snippet, you're doing two critical things wrong inside the POST request handler:

• Requiring mssql every single time a request comes in (this reloads the module on each hit, which is unnecessary and causes conflicts)
• Calling sql.connect() for every request, which tries to create a new global connection instance instead of reusing existing connections

MSSQL for Node.js uses a global connection by default, so when multiple requests hit your API at once, they all try to initialize this global connection simultaneously—hence the "already exists" error.

The Fix: Use a Reusable Connection Pool #

MSSQL is built to work with connection pools, which let you initialize a set of connections once when your app starts, then reuse them across requests. This is way more efficient for high traffic and eliminates the conflict error. Here's your revised code:

// Load dependencies and initialize app ONCE, outside request handlers
var express = require('express'); 
var sql = require("mssql"); 
var app = express(); 

// Database configuration (store credentials in env vars for production!)
var config = { 
  user: '*****', 
  password: '******', 
  server: '********', 
  port: '****', 
  database: '*****',
  // Configure connection pool settings
  pool: {
    max: 10, // Adjust based on your database's connection limit
    min: 0,
    idleTimeoutMillis: 30000 // Close idle connections after 30s
  }
};

// Initialize the connection pool when the app starts
sql.connect(config)
  .then(pool => {
    console.log('Successfully connected to SQL Server');

    // Define your POST route now that the pool is ready
    app.post('/', function(req, res){ 
      // Create a request using the existing pool (no new connection needed!)
      const request = new sql.Request(pool);

      // Add your database logic here (example query)
      request.query('INSERT INTO YourTable (name, phone) VALUES (@name, @phone)', {
        name: req.body.name,
        phone: req.body.phone
      })
      .then(result => {
        res.status(200).send('Data saved successfully');
      })
      .catch(err => {
        console.error('Query error:', err);
        res.status(500).send('Failed to save data');
      });
    });

    // Start the server only after the pool is connected
    app.listen(3000, () => {
      console.log('API server running on port 3000');
    });
  })
  .catch(err => {
    console.error('Failed to connect to SQL Server:', err);
    process.exit(1); // Exit if the initial connection fails
  });

Key Improvements Explained #

• Initialize dependencies once: Moving require('mssql') and the connection setup outside the route ensures they run only when your app starts, not per request.
• Connection pool reuse: The pool manages a set of connections, so each request uses an existing connection instead of creating a new one—perfect for handling high traffic.
• No more global connection conflicts: By using the pool's request object, you avoid trying to create duplicate global connections.
• Avoid sql.close() in requests: Closing the pool would shut down all connections, which you only want to do when your app is shutting down (e.g., on server exit).

Quick Extra Tips #

• Use async/await instead of promises/callbacks if you prefer cleaner, more readable code.
• Add error listeners to the pool to handle reconnection if the database drops the connection.
• Never hardcode credentials—use environment variables (via process.env) for security.

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