How do I return an asynchronous DB query result from one module to another using Node.js?-CodePudding

I'm new to Node, and I'm trying to follow a pattern from a Udemy API course. The API is structured to utilize route, controller and service modules for flow. Database queries are to be run as services and they are supposed to be called from controllers.

I need to run a series of DB queries to generate a list (I'm showing only 2 of 6 queries in this example). I am running these using async/await in my function. The queries are working fine. My problem occurs when I try to return the 'batch result' (the result of all the queries) to the controller at the end of the process. I get Promise { <pending> }. I have tried many things, but I cannot end the promise to access the final result from my controller module--I can only access it from my service module.

Here is my code from my controller module (groups.controller.js) where I call my function:

const groupsService = require('../services/groups.service');

exports.propertyList = (req, res, next) => {

    const uid = req.body.uid;

    const batchResponse = groupsService.batchQuery(uid, res);
    console.log(batchResponse);

}

And here is my code from my service module (groups.services.js) where I run the queries:

const mysql = require('mysql2');
const dbAsync = require("../config/db.config");

async function batchQuery(uid, res) {

    var Q1;
    var Q2;

    var uid = uid * -1;

    const pool = mysql.createPool(dbAsync.dbAsync);
    const promisePool = pool.promise();

    try {
        Q1 = await promisePool.query('SELECT PropertyID FROM GroupMembership WHERE GroupID = ?', [uid]);
        Q2 = await promisePool.query('SELECT SubGroupID FROM GroupMembership WHERE GroupID = ? AND PropertyID = ?', [uid, 0]);
    }
    catch(error) {
        console.log(error);
        res.status(401).send('Server error');
        return error;
    }
    finally {
        const batchResponse = {
            Q1: Q1[0],
            Q2: Q2[0]
        }

        console.log('Q1: '  Q1[0][0].PropertyID   ', Q2: '   Q2[0][0].SubGroupID);
        res.status(200).send(batchResponse);
        return batchResponse;
    }
}

module.exports = {batchQuery};

When I send a post via postman, I get the expected query result (below). However, I can only get this to work if I put my res in my service module.

{
    "Q1": [
        {
            "PropertyID": 0
        }
    ],
    "Q2": [
        {
            "SubGroupID": 397
        }
    ]
}

Is there a way to end the promise in this pattern and return the desired batch response? Thank you.

EDIT: Adding the code updates provided by @traynor.

New controller:

const groupsService = require('../services/groups.service');

exports.propertyList = async (req, res, next) => {

    const uid = req.body.uid;

    let batchResponse;
    try {
        batchResponse = await groupsService.batchQuery(uid);
        console.log(batchResponse);
        return res.status(200).send(batchResponse);
    } catch(error) {
        console.log('Error: '   error);
        return res.status(401).send('Server error');
    }
}

New service:

const mysql = require('mysql2');
const dbAsync = require("../config/db.config");

function batchQuery(uid) {
    
    return new Promise((resolve, reject) => {

        var Q1;
        var Q2;

        var uid = uid * -1;

        const pool = mysql.createPool(dbAsync.dbAsync);
        const promisePool = pool.promise();

        try {
            Q1 = await promisePool.query('SELECT PropertyID FROM GroupMembership WHERE GroupID = ?', [uid]);
            Q2 = await promisePool.query('SELECT SubGroupID FROM GroupMembership WHERE GroupID = ? AND PropertyID = ?', [uid, 0]);
        } catch(error) {
            console.log(error);
            reject(error);
        } finally {
            const batchResponse = {
                Q1: Q1[0],
                Q2: Q2[0]
            }
            console.log('Q1: '  Q1[0][0].PropertyID   ', Q2: '   Q2[0][0].SubGroupID);
            resolve(batchResponse);
        }
    })
}

module.exports = {batchQuery};

CodePudding user response：

the service is now returning a promise, and it's also handling response instead of controller.

to return from service, you need to promisify service: return a promise which resolves when you get db data, or on error, and then you also need to await the service, which it's wrapped in try/catch for error handling.

once it's all done, handle response from the controller:

service:

function batchQuery(uid) {

    return new Promise(async (resolve, reject) => {

        var Q1;
        var Q2;
        //...

        try {
            //...
        } catch (error) {
            console.log(error);
            reject(error);
        } finally {
            const batchResponse = {
                Q1: Q1[0],
                Q2: Q2[0]
            }
            console.log('Q1: '   Q1[0][0].PropertyID   ', Q2: '   Q2[0][0].SubGroupID);

            resolve(batchResponse);
        }
    });

controller:

exports.propertyList = async (req, res, next) => {

    const uid = req.body.uid;

    let batchResponse;
    try {
        batchResponse = await groupsService.batchQuery(uid);
        console.log(batchResponse);
        res.status(200).send(batchResponse);
    } catch(error) {
        return res.status(401).send('Server error');
    }

}