How do I perform more than one database query on a single request . GET on the same route on NODE.JS?

Question

I am creating an API for a personal project, and I need to make 2 queries to the database in the same GET request. My code looks like this:

module.exports = {
    async listarAnual(req, res, next){
        const ano = req.body.ano;

        const valores = {
            entradas: 0,
            saidas: 0,           
        }

        con.getConnection((error, conn) => {
            if (error) {
                return res.status(500).send({error : error})              
            }else{
                var sqlEntradas = `
                    select d.nome_descricao_entrada, sum(e.valor_entrada) from entrada  as e
                    inner join descricao as d on e.id_descricao_entrada = d.id_descricao_entrada
                    where Year(e.data_entrada) = ? 
                    group by d.id_descricao_entrada
                    order by d.nome_descricao_entrada asc;            
                `;

                var sqlSaidas = `            
                    select d.nome_descricao_saida, sum(s.valor_saida) from saida  as s
                    inner join descricaosaida as d on s.id_descricao_saida = d.id_descricao_saida
                    where Year(s.data_saida) = ? 
                    group by d.id_descricao_saida
                    order by d.nome_descricao_saida asc;            
                `;
          
                valores.entradas = conn.query(sqlEntradas,ano, function (error, results, fields) {
                    if (error) {
                        return res.status(500).send({ error: error });      
                    } else {      
                        return res.status(200).send({ entradas: results });                 
                    }      
                });

                valores.saidas = conn.query(sqlSaidas, ano, function(error, results, fields) {
                    if (error) {
                        return res.status(500).send({error: error});
                    } else {
                        return res.status(200).send({saidas: results});
                    }                
                });              
            }            
            
            return res.status(200).send(valores);
        });          
    }
}

The result of this request is being this:

"entradas": {
    "_events": {},
    "_eventsCount": 0,
    "next": null,
    "sql": "\n            \n            select d.nome_descricao_entrada, sum(e.valor_entrada) from entrada  as e\n            inner join descricao as d on e.id_descricao_entrada = d.id_descricao_entrada\n            where Year(e.data_entrada) = 2019 \n            group by d.id_descricao_entrada\n            order by d.nome_descricao_entrada asc;\n            \n            ",
    "values": 2019,
    "_queryOptions": {
        "rowsAsArray": false,
        "sql": "\n            \n            select d.nome_descricao_entrada, sum(e.valor_entrada) from entrada  as e\n            inner join descricao as d on e.id_descricao_entrada = d.id_descricao_entrada\n            where Year(e.data_entrada) = ? \n            group by d.id_descricao_entrada\n            order by d.nome_descricao_entrada asc;\n            \n            ",
        "values": 2019
    },
    "namedPlaceholders": false,
    "_fieldCount": 0,
    "_rowParser": null,
    "_fields": [],
    "_rows": [],
    "_receivedFieldsCount": 0,
    "_resultIndex": 0,
    "_localStream": null,
    "_connection": null
},
"saidas": {
    "_events": {},
    "_eventsCount": 0,
    "next": null,
    "sql": "\n            \n            select d.nome_descricao_saida, sum(s.valor_saida) from saida  as s\n            inner join descricaosaida as d on s.id_descricao_saida = d.id_descricao_saida\n            where Year(s.data_saida) = 2019 \n            group by d.id_descricao_saida\n            order by d.nome_descricao_saida asc;\n            \n            ",
    "values": 2019,
    "_queryOptions": {
        "rowsAsArray": false,
        "sql": "\n            \n            select d.nome_descricao_saida, sum(s.valor_saida) from saida  as s\n            inner join descricaosaida as d on s.id_descricao_saida = d.id_descricao_saida\n            where Year(s.data_saida) = ? \n            group by d.id_descricao_saida\n            order by d.nome_descricao_saida asc;\n            \n            ",
        "values": 2019
    },
    "namedPlaceholders": false,
    "_fieldCount": 0,
    "_rowParser": null,
    "_fields": [],
    "_rows": [],
    "_receivedFieldsCount": 0,
    "_resultIndex": 0,
    "_localStream": null,
    "_connection": null
}

I have tried to put the Return that each value gets to be just a "Return Results", but kept returning the same thing. But if I delete the return res.status(200).send(valores); la at the end, it returns the data that I expect only from the first query.

Answer 1

To achieve the desired result it is necessary to put your queries in order and in a way that they are executed after the callback of the query, so it is only necessary to rearrange your code

Here is an example of how it can be done:

module.exports = {
    async listarAnual(req, res, next) {
        const ano = req.body.ano;
    
        const valores = {
            entradas: 0,
            saidas: 0,           
        }
    
        con.getConnection((error, conn) => {
            if (error) {
                return res.status(500).send({error : error})              
            } else {
                var sqlEntradas = `select d.nome_descricao_entrada, sum(e.valor_entrada) from entrada  as e
                inner join descricao as d on e.id_descricao_entrada = d.id_descricao_entrada
                where Year(e.data_entrada) = ? 
                group by d.id_descricao_entrada
                order by d.nome_descricao_entrada asc`;
                var sqlSaidas = `select d.nome_descricao_saida, sum(s.valor_saida) from saida  as s
                inner join descricaosaida as d on s.id_descricao_saida = d.id_descricao_saida
                where Year(s.data_saida) = ? 
                group by d.id_descricao_saida
                order by d.nome_descricao_saida asc`;
            
                conn.query(sqlEntradas,ano, function (error, results, fields) {
                    if (error) {
                        return res.status(500).send({ error: error });      
                    } else {      
                        valores.entradas = results;
                        conn.query(sqlSaidas, ano, function(error1, results1, fields1) {
                            if (error1) {
                                return res.status(500).send({error: error1});
                            } else {
                                valores.saidas = results1;
                                res.status(200).send(valores);
                            }
                        });
                    }  
                });
            }
        });
    }
}