求助：Express集成Tedious时二次请求报错‘无法在Final状态的Connection上调用.connect’

Alright, let's break down what's happening here:

Your code uses a single global connection object created once when the server starts. The first request works because this connection is fresh and active. But after processing that request, you call connection.close() — this moves the connection into a "Final" state, meaning it's no longer usable. When the second request comes in and tries to call connection.connect() again on this already-closed connection, you get that error.

Here are a few solid solutions to fix this:

Solution 1: Create a New Connection Per Request (Simple & Straightforward) #

This is the easiest fix for small-scale apps. Just move the connection creation inside your request handler so each request gets its own fresh connection:

const express = require('express') 
const router = express.Router() 
const bodyParser = require('body-parser'); 
var Connection = require('tedious').Connection; 
var Request = require('tedious').Request; 
const config = require('./config.json'); 

router.route('/') 
.post(async (req, res) => { 
  // Create a new connection for every request
  const connection = new Connection(config);
  
  connection.connect((err) => { 
    if(err) { 
      console.log('Connection Failed'); 
      console.log(err);
      return res.status(500).send('Failed to connect to database');
    } 
    executeSQL(connection, res);
  }); 

  function executeSQL(connection, res) { 
    const request = new Request('Here is a select', function(err, rowsCount, rows) { 
      if(err) {
        console.log(err);
        return res.status(500).send('Failed to execute SQL');
      } 
      res.send(rows); 
      // Close the connection after processing the request
      connection.close(); 
    }); 
    connection.execSql(request); 
  } 
}); 

module.exports = router 

Solution 2: Use a Connection Pool (Recommended for Production) #

For apps with higher traffic, creating a new connection every time can be inefficient. Tedious has a built-in ConnectionPool that manages a pool of reusable connections — this is the best approach for production:

const express = require('express') 
const router = express.Router() 
const bodyParser = require('body-parser'); 
const { ConnectionPool, Request } = require('tedious'); 
const config = require('./config.json'); 

// Initialize a connection pool
const pool = new ConnectionPool(config);

// Handle pool errors
pool.on('error', (err) => {
  console.error('Pool error:', err);
});

router.route('/') 
.post(async (req, res) => { 
  // Get a connection from the pool
  pool.acquire((err, connection) => {
    if(err) {
      console.log('Failed to acquire connection');
      console.log(err);
      return res.status(500).send('Failed to get database connection');
    }

    const request = new Request('Here is a select', function(err, rowsCount, rows) {
      if(err) {
        console.log(err);
        return res.status(500).send('Failed to execute SQL');
      } 
      res.send(rows); 
      // Release the connection back to the pool (don't close it!)
      connection.release();
    });

    connection.execSql(request);
  });
}); 

module.exports = router 

Solution 3: Reuse a Single Connection (Without Closing It) #

If you prefer to stick with a single connection, don't close it after each request. Instead, add logic to handle reconnection if the connection drops:

const express = require('express') 
const router = express.Router() 
const bodyParser = require('body-parser'); 
var Connection = require('tedious').Connection; 
var Request = require('tedious').Request; 
const config = require('./config.json'); 

let connection;

function initConnection() {
  connection = new Connection(config);
  
  connection.connect((err) => { 
    if(err) { 
      console.log('Connection Failed'); 
      console.log(err);
      // Retry connection after 5 seconds if it fails
      setTimeout(initConnection, 5000);
      return;
    }
    console.log('Successfully connected to database');
  });

  // Auto-reconnect if the connection closes
  connection.on('close', () => {
    console.log('Connection closed, attempting to reconnect...');
    initConnection();
  });

  // Handle fatal errors by reconnecting
  connection.on('error', (err) => {
    console.error('Connection error:', err);
    if (err.fatal) {
      initConnection();
    }
  });
}

// Initialize the connection when the server starts
initConnection();

router.route('/') 
.post(async (req, res) => { 
  // Check if the connection is active before using it
  if (connection.state !== 'Connected') {
    return res.status(500).send('Database connection unavailable, please try again later');
  }

  const request = new Request('Here is a select', function(err, rowsCount, rows) { 
    if(err) {
      console.log(err);
      return res.status(500).send('Failed to execute SQL');
    } 
    res.send(rows); 
    // Don't close the connection here!
  }); 

  connection.execSql(request); 
}); 

module.exports = router 

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