How to send multiple results of multiple db.query to client through res.render() function?

Question

I am building an website. My client code is in EJS and my server code is in Node.js. I am successful on sending the results variable of db.query through res.send();. But now I want to use two queries and retrieve and send values of those queries to the client. But if I use only one variable then its fine but when I am using both variables of 2 queries in client it's giving me error: Error: Can't set headers after they are sent.

Here is my login.js file:

var username;
var password;
var result;

module.exports = {

    inserttest : (req,res) => {

        // let query = "SELECT * FROM `login` where username"; // query database to get all the players


        db.query('SELECT * FROM login WHERE username = ? AND password = ?', [username, password], function(error, result, fields) {
            if (error) {
                res.redirect('/login');
            }

            var moisture = 11232;
            var ph = 1121;
            var ec = 543;
            var date1=new Date();

            const sqlq='insert into usertest values ?';
            var values = [
            [result[0].kitno,moisture,ph,ec,date1]
            ];
            db.query(sqlq, [values], function(error, results, fields) {
                if(error)
                {
                    console.log("error");
                    //var a
                    //res.send(500,'showAlert') 
                }
                else{
                    console.log("1 row inserted successfully");
                }
                var result2= doQuery1();

                res.render('mytest',
                {
                    url:"/mytest",
                    title:"WELCOME2",
                    results1:result,
                    results2:result2
                });
            });
        });

        function doQuery1(){
            let query = "SELECT * FROM usertest WHERE kitno IN(select kitno from login where username = ? AND password = ?)"; 
            var resource;
            db.query(query, [username, password], function(error, result4, fields){
                resource=result4;
            });
            return resource;
        };
    },

Here is my EJS i.e. login.ejs file code:

Here I am using result1[0].name as a title. and result2[i].ph in for loop.

  <main class="page-content">
        <div class="container-fluid">
          <h2>My Tests</h2>
          <hr>
          <div id="main">
            <div id="left" class="container">
            <br>
            <form method="get" action="/insertvalues">

            <input type="submit" value="Start Test" />  
            </form>
            <% for (var i = 0; i < results2.length; i++) { %>

                  <%=results2[i].ph%>

              <%}%>

            <br>

I tried several ways of sending that both variable results1 and result2 through res.render() but its running only when I am removing the result2 codes from client.

Error I am getting:

_http_outgoing.js:491
    throw new Error('Can\'t set headers after they are sent.');
    ^

Error: Can't set headers after they are sent.
    at validateHeader (_http_outgoing.js:491:11)
    at ServerResponse.setHeader (_http_outgoing.js:498:3)
    at Array.write (C:\Users\Rohit\Desktop\Project_me\soil2\node_modules\finalhandler\index.js:285:9)
    at listener (C:\Users\Rohit\Desktop\Project_me\soil2\node_modules\on-finished\index.js:169:15)
    at onFinish (C:\Users\Rohit\Desktop\Project_me\soil2\node_modules\on-finished\index.js:100:5)
    at callback (C:\Users\Rohit\Desktop\Project_me\soil2\node_modules\ee-first\index.js:55:10)
    at IncomingMessage.onevent (C:\Users\Rohit\Desktop\Project_me\soil2\node_modules\ee-first\index.js:93:5)
    at emitNone (events.js:106:13)
    at IncomingMessage.emit (events.js:208:7)
    at endReadableNT (_stream_readable.js:1064:12)

Answer 1

There is a Promise version of mysql package: mysql2. Using promise you can wait for Promise to resolve or reject akin to sync code.

You can achieve what you're trying to achieve like:

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

module.exports = {


  inserttest : async (req,res) => {

    // let query = "SELECT * FROM `login` where username"; // query database to get all the players

    const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
    try {
      const [rows, fields] = await connection.execute('SELECT * FROM login WHERE username = ? AND password = ?', [username, password]);
    } catch (e) {
      console.error(e);
      res.redirect('/login');
    }

    var moisture = 11232;
    var ph = 1121;
    var ec = 543;
    var date1=new Date();

    const sqlq='insert into usertest values ?';
    var values = [
      [result[0].kitno,moisture,ph,ec,date1]
    ];

    try {
      // will wait till execution finishes
      await connection.execute(sqlq, [values]);
      console.log("1 row inserted successfully");
    } catch (e) {
      console.error(e)
    }
    var result2= doQuery1();

    res.render('mytest',
               {
      url:"/mytest",
      title:"WELCOME2",
      results1:result,
      results2:result2

    });
  }

  async function doQuery1(){
    let query = "SELECT * FROM usertest WHERE kitno IN(select kitno from login where username = ? AND password = ?)"; 
    var resource;
    try {
      const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
      resource = await connection.execute(query, [username, password]);
      return resource;
    } catch (e) {
      console.error(e);
    }
  };
}

Using promise and async/await in this way you can make it sequential like.