Node.js从MySQL查询数据问题求助：controller无法获取结果

Hey there! Let's figure out what's going wrong with your Node.js + MySQL setup. Since you confirmed your db.js works when running queries directly inside the resolve, the issue is almost certainly how you're using the connection in controller.js. Let's break this down step by step.

First, let's align on what your db.js probably looks like #

Based on your description, I'm guessing your connection module uses a Promise to return a database connection, something like this:

// db.js example
const mysql = require('mysql2/promise');

const getConnection = () => {
  return new Promise((resolve, reject) => {
    mysql.createConnection({
      host: 'localhost',
      user: 'your_username',
      password: 'your_password',
      database: 'your_database'
    })
    .then(connection => resolve(connection))
    .catch(err => reject(err));
  });
};

module.exports = getConnection;

Common mistakes in controller.js and how to fix them #

The most likely issue is that you're not properly handling the asynchronous nature of database queries, or you're not using the connection object correctly once you have it.

1. Forgetting to wait for the query to complete

If you're just running connection.query() without awaiting it or handling its Promise, you'll never get the result. Here's the fix with async/await (the cleanest approach):

// controller.js corrected (async/await style)
const getConnection = require('./db.js');

async function fetchData() {
  let connection;
  try {
    // Get the connection first
    connection = await getConnection();
    
    // Run the query and wait for results
    const [rows] = await connection.query('SELECT * FROM your_table_name');
    
    console.log('Query results:', rows);
    return rows; // Return results if you need them elsewhere
  } catch (err) {
    console.error('Error during query:', err);
    throw err; // Propagate the error if you want to handle it upstream
  } finally {
    // Always close the connection to avoid pool exhaustion
    if (connection) {
      await connection.end();
    }
  }
}

// Call the function and handle any final errors
fetchData().catch(err => console.error('Fatal error:', err));

2. Not handling the query Promise with .then() (if you prefer callback-style chaining)

If you don't want to use async/await, make sure you chain the query Promise properly and return the results:

// controller.js corrected (.then()/.catch() style)
const getConnection = require('./db.js');

function fetchData() {
  return getConnection()
    .then(connection => {
      return connection.query('SELECT * FROM your_table_name')
        .then(([rows]) => {
          console.log('Query results:', rows);
          return rows;
        })
        .finally(() => {
          // Close connection regardless of success/failure
          if (connection) connection.end();
        });
    })
    .catch(err => {
      console.error('Error:', err);
      throw err;
    });
}

fetchData();

Key things to check #

• Are you waiting for the query to finish? The query() method from mysql2/promise returns a Promise—you must use await or .then() to access the results.
• Are you closing the connection? Leaving connections open can lead to resource leaks and unexpected behavior. Use a finally block to ensure connections are always closed.
• Is your connection valid? Add a quick check in your controller to confirm the connection is working:

  async function verifyConnection() {
    try {
      const connection = await getConnection();
      console.log('Connection successful:', connection);
      await connection.end();
    } catch (err) {
      console.error('Connection failed:', err);
    }
  }
  verifyConnection();
  

  If this logs a valid connection object, the problem is definitely in how you're executing the query.

Why your test worked #

When you replaced resolve(connection) with a query directly in db.js, you were handling the query Promise inside the connection module and resolving with the results—so the Promise returned by getConnection() gave you the data directly. But when you return the connection object instead, you have to handle the query's async logic in the controller.

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