How to (properly) chain multiple sequential MSSQL queries in Node

Question

I'm writing a simple nodejs CLI tool while learning promises (to avoid callback hell), and every tutorial/stackoverflow example I've found only shows how to make a single call. My use case is as follows: 1. Connect to the Database (this I can do) 2. Perform a SQL select statement (also got this part) 3. Do some stuff with the results 4. Repeat steps 2 & 3 a few more times

I'm collecting the MSSQL user name and password (with hard-coded server 'localhost' and database name 'testdb') so when the app executes I can't just jump into the MSSQL connection.

I can get this via callbacks, but right now I have about 50 queries so you can imagine the ugliness. The full code below does get me the first query, and I strongly suspect I'm not passing the "pool" object to the next "then", but when I try

.then((pool,result) => {
   //next command
})

It still doesn't recognize pool

Here is the code (index.js):

const mssql = require('mssql');

const qry1 = "select fieldA from tblA";
const qry2 = "select fieldB from tblB";
const qry3 = "select fieldC from tblC";
var dbuser = '';
var dbpass = '';
var config = {}

function init() {
   log('Beginning Audit');
   collectDbInfo(); //The reason I don't just included it all here
}

function collectDbInfo() {
    //code is irrelevant to the problem, this is where I'm collecting the database credentials
}

function start() {
   config = {
      user: dbuser,
      password: dbpass,
      server: 'localhost',
      database: 'testdb'
   }

   mssql.connect(config)
      .then(pool => {
         //FIRST query
         return pool.request().query(qry1)
      })
      .then(result => {
         processQryA(result);
         //SECOND query
         return pool.request().query(qry2)
      })
      .then(result => {
         processQryB(result);
         //THIRD query
         return pool.request().query(qry3)
      })
      .then(result => {
         processQryC(result);
      })

   mssql.on('error',err => {
      log('SQL Error:' err)
      mssql.close();
      process.exit(0);
   }
}

processQryA(data) {
    console.log(data.recordset[0].fieldA)
}

processQryB(data) {
    console.log(data.rcordset[0].fieldB)
}

processQryC(data) {
    console.log(data.recordset[0].fieldC)
}

init();

I fully appreciate I may be approaching this all wrong, so any advice or especially examples would be greatly appreciated.

Answer 1

If the queries are absolutely sequential in nature, you can achieve that with async/await:

async function start(){
        config = {
            user: dbuser,
            password: dbpass,
            server: 'localhost',
            database: 'testdb'
         }
        try {
            pool = await mssql.connect(config);
            const res1 = await pool.request().query(qry1);
            processQryA(res1);
            const res2 = await pool.request().query(qry2);
            processQryB(res2);
            const res3 = await pool.request().query(qry3);
            processQryC(res3);
            const res4 = await pool.request().query(qry4);
            processQryD(res4);
            /*..And so on with rest of sequential queries*/
            /*Any of them resulting in error will be caught in (catch)*/

        } catch (error) {
            console.error("Error in start()::", error);
        }
    }

Also: I would probably have my pool getting method separately from query executions to handle errors/validations nicely.