是否需用Promise处理Node.js路由数据库查询响应？求优化方案

我不确定当前的Express路由代码存在什么问题，希望得到指导。我猜测可能需要用Promise等待所有数据推入数组，但不确定有没有更优的处理方式。

router.get('/notifications/:user_id', (req, res) => {

    console.log('get comments by ID route received a GET request');

    var user_id = req.params.user_id;
    console.log(user_id)
    
    // 获取用户所有帖子
    var getNotificationsById = 'SELECT user_profiles.forename, user_profiles.surname, user_profiles.profile_image, user_posts.id, user_posts.title, user_posts.price, count(distinct post_comments.id) AS total_comments, sum(post_likes.likes=1) AS total_likes FROM user_posts LEFT JOIN post_comments ON user_posts.id = post_comments.post_id LEFT JOIN post_likes ON user_posts.id = post_likes.post_id LEFT JOIN post_comment_likes ON user_posts.id = post_comment_likes.post_id LEFT JOIN user_profiles ON post_likes.user_id = user_profiles.id WHERE user_posts.user_id = ? GROUP BY user_posts.id ORDER BY user_posts.created DESC'

    // 通过帖子ID获取关联内容
    var getRelationsById = 'SELECT user_profiles.forename, user_profiles.surname, user_profiles.profile_image, user_posts.id, user_posts.title, user_posts.price, count(distinct post_comments.id) AS total_comments, sum(post_likes.likes=1) AS total_likes FROM user_posts LEFT JOIN post_comments ON user_posts.id = post_comments.post_id LEFT JOIN post_likes ON user_posts.id = post_likes.post_id LEFT JOIN post_comment_likes ON user_posts.id = post_comment_likes.post_id LEFT JOIN user_profiles ON post_likes.user_id = user_profiles.id WHERE post_comment_likes.post_id = ? GROUP BY post_comments.id ORDER BY user_posts.created DESC'
    
    var userPostId = [];
    var userResult = [];

    var query = connection.query(getNotificationsById, [user_id], (error, result) => {
        if(error) {
            console.error(error);
            logging.error.error(error);
            return;
        }
        var count = 0
        for(i in result) {
            count++
            console.log('count total = ' + count + ' or more')
            var post_id = result[i].id
            userPostId.push(post_id)

            var query = connection.query(getRelationsById, [post_id], (error, result) => {
                if(error) {
                    console.error(error);
                    logging.error.error(error);
                    return;
                }
                var count = 0
                for(i in result) {
                    count++
                    console.log('data found = ' + count)
                    userResult.push(result)
                }
                console.log(result)
            })
        }
        const message = "Successful"
        res.status(200).json({message: message, status: 200, result: userResult})
    });
})

核心问题 #

当前代码的关键问题是异步操作未等待完成就返回响应：

• connection.query是异步回调函数，外层循环中发起的多次getRelationsById查询还没执行完毕，就已经执行了res.status(200).json(...)，此时userResult还没被填充完整，返回的结果是空或不完整。
• 代码中使用了未声明的全局变量i，会导致循环变量污染，出现逻辑错误。

优化方案 #

方案1：Promise化查询 + Promise.all 处理异步 #

先把回调式的connection.query包装成Promise，再用Promise.all等待所有查询完成：

// 包装connection.query为Promise函数
const queryPromise = (sql, params) => {
    return new Promise((resolve, reject) => {
        connection.query(sql, params, (error, result) => {
            if (error) {
                reject(error);
            } else {
                resolve(result);
            }
        });
    });
};

router.get('/notifications/:user_id', async (req, res) => {
    console.log('get comments by ID route received a GET request');
    const user_id = req.params.user_id;
    console.log(user_id);

    const getNotificationsById = 'SELECT user_profiles.forename, user_profiles.surname, user_profiles.profile_image, user_posts.id, user_posts.title, user_posts.price, count(distinct post_comments.id) AS total_comments, sum(post_likes.likes=1) AS total_likes FROM user_posts LEFT JOIN post_comments ON user_posts.id = post_comments.post_id LEFT JOIN post_likes ON user_posts.id = post_likes.post_id LEFT JOIN post_comment_likes ON user_posts.id = post_comment_likes.post_id LEFT JOIN user_profiles ON post_likes.user_id = user_profiles.id WHERE user_posts.user_id = ? GROUP BY user_posts.id ORDER BY user_posts.created DESC';

    const getRelationsById = 'SELECT user_profiles.forename, user_profiles.surname, user_profiles.profile_image, user_posts.id, user_posts.title, user_posts.price, count(distinct post_comments.id) AS total_comments, sum(post_likes.likes=1) AS total_likes FROM user_posts LEFT JOIN post_comments ON user_posts.id = post_comments.post_id LEFT JOIN post_likes ON user_posts.id = post_likes.post_id LEFT JOIN post_comment_likes ON user_posts.id = post_comment_likes.post_id LEFT JOIN user_profiles ON post_likes.user_id = user_profiles.id WHERE post_comment_likes.post_id = ? GROUP BY post_comments.id ORDER BY user_posts.created DESC';

    try {
        // 获取用户所有帖子
        const posts = await queryPromise(getNotificationsById, [user_id]);
        // 为每个帖子生成关联查询的Promise
        const relationPromises = posts.map(post => queryPromise(getRelationsById, [post.id]));
        // 等待所有关联查询完成
        const allRelations = await Promise.all(relationPromises);
        // 合并结果（根据需求调整格式）
        const userResult = allRelations.flat();
        
        res.status(200).json({
            message: "Successful",
            status: 200,
            result: userResult
        });
    } catch (error) {
        console.error(error);
        logging.error.error(error);
        res.status(500).json({
            message: "Error retrieving data",
            status: 500,
            error: error.message
        });
    }
});

方案2：优化SQL查询，减少数据库请求 #

多次查询会增加性能开销，更优的方式是通过一次SQL关联查询获取所有数据：

router.get('/notifications/:user_id', (req, res) => {
    console.log('get comments by ID route received a GET request');
    const user_id = req.params.user_id;
    console.log(user_id);

    // 合并后的SQL查询，一次获取所有需要的数据
    const sql = `SELECT 
        up.forename, 
        up.surname, 
        up.profile_image, 
        upost.id AS post_id, 
        upost.title, 
        upost.price, 
        COUNT(DISTINCT pc.id) AS total_comments, 
        SUM(pl.likes=1) AS total_likes
    FROM user_posts upost
    LEFT JOIN post_comments pc ON upost.id = pc.post_id
    LEFT JOIN post_likes pl ON upost.id = pl.post_id
    LEFT JOIN post_comment_likes pcl ON upost.id = pcl.post_id
    LEFT JOIN user_profiles up ON pl.user_id = up.id
    WHERE upost.user_id = ?
    GROUP BY upost.id
    ORDER BY upost.created DESC`;

    connection.query(sql, [user_id], (error, result) => {
        if(error) {
            console.error(error);
            logging.error.error(error);
            return res.status(500).json({
                message: "Error retrieving data",
                status: 500,
                error: error.message
            });
        }
        res.status(200).json({
            message: "Successful",
            status: 200,
            result: result
        });
    });
});

额外注意点 #

• 变量声明必须使用let/const，避免全局变量污染。
• 错误处理要完整，所有出错分支都要返回响应，防止请求挂起。

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