How to await a streaming sql query in node.js

Question

I need to call out to a function that runs a sql query, with row level functionality, and await the entire process before continuing.

Function code:

const sql = require('mssql')

exports.doit = ()=>{
    const pool1 = new sql.ConnectionPool(dbConfig);
    const pool1Connect = pool1.connect();

    pool1.on('error', err => {
        console.error('error occurred on pool')
    })
    await pool1Connect
    try {
        const request = pool1.request();
        request.stream = true;
        request.query('select * from dbo.user');
        request.on('row', async orow => {
            console.log('outer row');
            const innerPool = new sql.ConnectionPool(dbConfig);
            const innerConnection = innerPool.connect();
            innerPool.on('error', err => {
                console.error('error occurred on pool')
            });
            const iConnection = await innerConnection;
            connections.push(iConnection);

            const innerRequest = innerPool.request();
            innerRequest.stream = true;
            var iquery = 'select * from dbo.order where userId='+ orow.userId
            innerRequest.query(iquery);

            innerRequest.on('row', async irow => {
                console.log(`User: ${orow.userId} Order: ${irow.orderId}`);
            });

            innerRequest.on('done', async () => {
                console.log('inner done');
                iConnection.close();
            });
        });
        request.on('done', async () => {
            console.log('outer done');
        })
    } catch (err) {
        console.error('SQL error', err);
    }
    sql.on('error', err => {
        // ... error handler
    })
}

Then call the above function like this:

var doit = require('./testmeHandler.js').doit;

 doit()
 .then(()=>{
     console.log("I AM DONE");
 });

OR

await doit();
console.log('I AM DONE');

You get the idea...

But what is really happening is, the function gets called, then 'I AM DONE' and then the results of all the sql calls.

Can someone help me get 'I AM DONE' at the bottom? Still getting used to the async/await and promises.

Thanks

Answer 1

After quite a bit of time trying to get this to work synchronously from the caller I gave up and re-wrote the method to use the regular query (not streaming) and implemented my own paging/throttling as to control memory usage. It works great now!

I am using a connection pool to allow for sub queries and other processes to occur async within a batch of results.

I will post the updated code.