新手求助：Node.js+Express下实现MySQL同步查询失败

Hey there! As someone who's navigated the async waters of Node.js, Express, and MySQL, I totally get the frustration of trying to write query code that feels "synchronous" when you're just starting out. Let's break down your setup and get this working properly.

First, Why Your Current Approach Isn't Working #

Node.js is built around an asynchronous, non-blocking event loop, and the native mysql module uses callback-based APIs by default. There's no true "sync" query method (and you wouldn't want to use one anyway—it blocks the entire app!). Instead, we can use Promises + async/await to write code that reads like synchronous code while still being non-blocking.

Step 1: Update Your Connection Setup #

First, let's adjust your connection.js to support Promises. You have two solid options:

Option 1: Use mysql2/promise (Simpler)

mysql2 is a drop-in replacement for the native mysql module with built-in Promise support:

const mysql = require('mysql2/promise');

// Returns a promise that resolves to a connection
const connMySql = async () => {
  return await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '******',
    database: 'ress'
  });
};

module.exports = connMySql;

Option 2: Promisify the Native mysql Module

If you want to stick with the original mysql module, use Node's util.promisify to convert callback-based methods to Promises:

const mysql = require('mysql');
const util = require('util');

const connMySql = () => {
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '******',
    database: 'ress'
  });

  // Convert the query method to return a Promise
  connection.query = util.promisify(connection.query);
  return connection;
};

module.exports = connMySql;

Step 2: Rewrite Your DAO with Async/Await #

Now update your UserDAO to use async/await for that "sync-style" code flow you're looking for:

function UserDAO(connection) {
  this._connection = connection;
}

// Mark the method as async to use await
UserDAO.prototype.createUser = async function(user) {
  try {
    // This line waits for the query to finish before moving on
    const result = await this._connection.query('INSERT INTO users SET ?', user);
    return result; // Return useful data like the new user's ID
  } catch (err) {
    // Throw errors so the caller can handle them gracefully
    throw new Error(`Failed to create user: ${err.message}`);
  }
};

// Example usage in an Express route
const connMySql = require('./connection');
const UserDAO = require('./DAO');
const express = require('express');
const app = express();

app.use(express.json());

app.post('/users', async (req, res) => {
  const userConnection = await connMySql(); // Get the connection
  const userDAO = new UserDAO(userConnection);

  try {
    const newUser = req.body;
    const result = await userDAO.createUser(newUser);
    res.status(201).json({ userId: result[0].insertId });
  } catch (err) {
    res.status(500).json({ error: err.message });
  } finally {
    // Always close the connection when done to avoid leaks
    await userConnection.end();
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));

Key Notes to Remember #

• Use Connection Pools: For production apps, skip creating a new connection every time—use mysql2/promise's pool instead to reuse connections and save resources.
• Error Handling is Mandatory: Always wrap await calls in try/catch to catch database errors and handle them without crashing your app.
• Async Propagates: If you use await inside a function, that function must be marked async. This includes your Express routes, which fully support async handlers.

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