NodeJS跨文件获取SQL查询返回数据失败求助

Hey there! Let's work through this problem together—async operations like Azure SQL queries across files can be tricky, and it sounds like you're hitting classic timing and scoping issues. Let's break down what's going wrong and fix it step by step.

First, Let's Diagnose the Core Issues #

From your description, here's why you're seeing empty results and broken exports:

1. Async Timing: SQL queries run in the background. When you try to access your res array outside the query callback, the data hasn't loaded yet.
2. Stale Variable References: The empty res array you export is a fixed reference. When the query finishes and updates res, the imported version in file2.js doesn't automatically sync to the new value.
3. Non-functional Export: If your getProducts method doesn't return a promise or accept a callback, file2.js has no way to know when the query completes.

Solution 1: Use Promises + Async/Await (Modern Approach) #

This is the cleanest way to handle async data across files. Let's rewrite file1.js to return a promise that resolves with your SQL data:

// file1.js
const sql = require('mssql');

// Replace with your Azure SQL config
const sqlConfig = {
  user: 'your-username',
  password: 'your-password',
  server: 'your-server.database.windows.net',
  database: 'your-db',
  options: {
    encrypt: true
  }
};

async function getProducts() {
  try {
    // Connect to the database
    await sql.connect(sqlConfig);
    // Run the query and get results
    const result = await sql.query('SELECT * FROM your-table-name');
    // Return the actual data (recordset is where mssql stores rows)
    return result.recordset;
  } catch (err) {
    // Log and rethrow errors so the caller can handle them
    console.error('SQL Query Failed:', err);
    throw err;
  } finally {
    // Always close the connection to avoid leaks
    await sql.close();
  }
}

module.exports = { getProducts };

Now in file2.js, you can wait for the promise to resolve and use the data:

// file2.js
const { getProducts } = require('./file1');

// Use async/await in an async function
async function displayProducts() {
  try {
    const products = await getProducts();
    console.log('Loaded Products:', products);
    // Add your display logic here (e.g., render to console, send to frontend)
  } catch (err) {
    console.error('Failed to Load Products:', err);
  }
}

// Call the function to trigger the query
displayProducts();

// Alternatively, use .then() if you prefer callback-style promises
// getProducts()
//   .then(products => console.log('Loaded Products:', products))
//   .catch(err => console.error('Failed to Load Products:', err));

Solution 2: Use Callback Functions (If You Prefer This Style) #

If you're more comfortable with callbacks instead of promises, modify file1.js to accept a callback parameter:

// file1.js
const sql = require('mssql');
const sqlConfig = { /* Your Azure SQL config */ };

function getProducts(callback) {
  sql.connect(sqlConfig)
    .then(pool => pool.request().query('SELECT * FROM your-table-name'))
    .then(result => {
      // Pass null for error, and the data to the callback
      callback(null, result.recordset);
    })
    .catch(err => {
      // Pass the error to the callback
      callback(err, null);
    })
    .finally(() => sql.close());
}

module.exports = { getProducts };

Then in file2.js, use the callback to access the data:

// file2.js
const { getProducts } = require('./file1');

getProducts((err, products) => {
  if (err) {
    console.error('Failed to Load Products:', err);
    return;
  }
  console.log('Loaded Products:', products);
  // Add your display logic here
});

Key Takeaways to Avoid This Issue in the Future #

• Never try to access async data immediately after calling an async function—always wait for it to resolve via promises or callbacks.
• Don't export a static variable like your original res array; instead, export a function that returns the data when it's ready.
• Always handle errors—ignoring catch blocks will make debugging impossible when things go wrong.

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