如何在Node.js中通过mssql连接Azure SQL数据库

Hey there, let's break down the issues in your current code and build a more robust, efficient connection logic for your Azure SQL + Node.js setup on App Service.

Key Issues in Your Current Code #

• Unscoped Configuration: Your azureConnection variable is missing a const/let declaration, making it a global variable which risks accidental overwrites.
• Wasteful Connection Cycles: Calling mssql.close() before every new connection defeats the purpose of connection pooling (built into the mssql package). This will hurt performance and can lead to connection leaks.
• Incomplete Error Handling: The error handling doesn't properly account for cases where the connection might not have been established yet, and you're not cleaning up resources consistently.
• Unfinished Request Flow: Your request setup cuts off mid-way, leaving the query and response handling incomplete.

Refined, Production-Ready Code #

Here's an improved version using async/await (for cleaner readability) and proper connection pool management:

const mssql = require("mssql");
const express = require("express");
const router = express.Router();

// Use environment variables for sensitive data (Azure App Service App Settings)
const azureConnection = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  server: process.env.DB_SERVER,
  database: process.env.DB_DATABASE,
  options: {
    encrypt: true, // Required for Azure SQL
    trustServerCertificate: false // Keep this false for production
  },
  pool: {
    max: 10, // Adjust based on your expected traffic
    min: 0,
    idleTimeoutMillis: 30000
  }
};

// Initialize connection pool once on app start (not per request)
async function initDbPool() {
  try {
    await mssql.connect(azureConnection);
    console.log("Successfully connected to Azure SQL Database");
  } catch (err) {
    console.error("Database connection failed:", err);
    // Exit app if we can't connect to the database
    process.exit(1);
  }
}

// Call init on app startup
initDbPool();

router.get('/BU', async (req, res, next) => {
  let request;
  try {
    // Get a connection from the pool (no need to connect/close per request)
    request = new mssql.Request();
    
    // Example query - replace with your actual SQL
    const result = await request.query("SELECT * FROM YourBU_Table");
    
    // Send successful response
    res.status(200).json(result.recordset);
  } catch (err) {
    console.error("Error fetching BU data:", err);
    // Pass error to Express error handler
    next(err);
  } finally {
    // Clean up request object if needed
    if (request) {
      request.close();
    }
  }
});

module.exports = router;

Critical Best Practices #

• Use Environment Variables: Never hardcode credentials! In Azure App Service, go to Configuration > Application Settings to add your DB_USER, DB_PASSWORD, etc. The process.env will automatically pick these up.
• Connection Pooling: The mssql package manages pooling by default - initializing once on app start avoids the overhead of creating new connections for every request.
• Async/Await Over Callbacks: Makes error handling and code flow much easier to follow compared to nested callbacks.
• Proper Resource Cleanup: The finally block ensures we clean up the request object even if an error occurs.
• Error Logging: Detailed logs help diagnose issues in Azure App Service (check Logs > Application Logs in the Azure Portal).

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