使用Node.js的Tedious库连接Azure SQL DB时遇连接提前关闭错误

错误信息 #

2022-10-11T10:29:27.876235712Z: [INFO]  RequestError: Connection closed before request completed.
2022-10-11T10:29:27.876281315Z: [INFO]      at Connection.cleanupConnection (/app/node_modules/tedious/lib/connection.js:1115:21)
2022-10-11T10:29:27.876288116Z: [INFO]      at Connection.enter (/app/node_modules/tedious/lib/connection.js:2893:12)
2022-10-11T10:29:27.876292416Z: [INFO]      at Connection.transitionTo (/app/node_modules/tedious/lib/connection.js:1358:24)
2022-10-11T10:29:27.876296616Z: [INFO]      at Connection.close (/app/node_modules/tedious/lib/connection.js:1058:10)
2022-10-11T10:29:27.876300717Z: [INFO]      at Connection.<anonymous> (/app/database/sql.database.js:30:14)
2022-10-11T10:29:27.876305517Z: [INFO]      at Connection.emit (node:events:513:28)
2022-10-11T10:29:27.876310817Z: [INFO]      at Connection.emit (/app/node_modules/tedious/lib/connection.js:1048:18)
2022-10-11T10:29:27.876315218Z: [INFO]      at Connection.processedInitialSql (/app/node_modules/tedious/lib/connection.js:1669:10)
2022-10-11T10:29:27.876321618Z: [INFO]      at /app/node_modules/tedious/lib/connection.js:2723:14
2022-10-11T10:29:27.876325618Z: [INFO]      at processTicksAndRejections (node:internal/process/task_queues:96:5) {
2022-10-11T10:29:27.876329719Z: [INFO]    code: 'ECLOSE',
2022-10-11T10:29:27.876339419Z: [INFO]    number: undefined,
2022-10-11T10:29:27.876344020Z: [INFO]    state: undefined,
2022-10-11T10:29:27.876348120Z: [INFO]    class: undefined,
2022-10-11T10:29:27.876352020Z: [INFO]    serverName: undefined,
2022-10-11T10:29:27.876355821Z: [INFO]    procName: undefined,
2022-10-11T10:29:27.876359821Z: [INFO]    lineNumber: undefined
2022-10-11T10:29:27.876363821Z: [INFO]  }
2022-10-11T10:29:27.885713502Z: [ERROR]  Connection closed before request completed.

连接代码 #

const { Connection, Request } = require('tedious');

// Create connection to database

const config = {
  authentication: {
    options: {
      userName: 'user1', 
      password: 'pass1' 
    },
    type: 'default'
  },
  server: 'abc.sql.net', 
  options: {
    database: 'DB_sb13', 
    encrypt: true
  }
};

const connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on("connect", err => {
  if (err) {
    console.error(err.message);
  } else {
    queryDatabase();
  }
  connection.close();
});

connection.connect();

function queryDatabase() {
  console.log("Reading rows from the Table...");

  // Read all rows from table
  const request = new Request(
    `SELECT * from dbo.dbx_test`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      } else {
        console.log(`${rowCount} row(s) returned`);
      }
    }
  );

  request.on("row", columns => {
    columns.forEach(column => {
      console.log("%s\t%s", column.metadata.colName, column.value);
    });
  });

  connection.execSql(request);
}

问题原因 #

核心问题在于connect事件回调中过早调用了connection.close()。Tedious的execSql是异步操作，调用queryDatabase()后查询并不会立即执行完成，此时直接关闭连接，会导致正在处理的SQL请求因连接中断而失败，触发"Connection closed before request completed"错误。

解决方案 #

将connection.close()移到SQL请求完成的回调函数中，确保查询执行完毕后再关闭连接。修正后的代码如下：

const { Connection, Request } = require('tedious');

const config = {
  authentication: {
    options: {
      userName: 'user1', 
      password: 'pass1' 
    },
    type: 'default'
  },
  server: 'abc.sql.net', 
  options: {
    database: 'DB_sb13', 
    encrypt: true
  }
};

const connection = new Connection(config);

connection.on("connect", err => {
  if (err) {
    console.error(err.message);
    connection.close(); // 连接失败时关闭
  } else {
    queryDatabase();
  }
});

connection.connect();

function queryDatabase() {
  console.log("Reading rows from the Table...");

  const request = new Request(
    `SELECT * from dbo.dbx_test`,
    (err, rowCount) => {
      if (err) {
        console.error(err.message);
      } else {
        console.log(`${rowCount} row(s) returned`);
      }
      connection.close(); // 执行完成后关闭连接
    }
  );

  request.on("row", columns => {
    columns.forEach(column => {
      console.log("%s\t%s", column.metadata.colName, column.value);
    });
  });

  connection.execSql(request);
}

额外优化建议：

• 添加error事件监听，捕获连接过程中的其他异常
• 若需复用连接，可避免每次查询后关闭，改用连接池管理连接生命周期

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