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

Aniket Chowdhury · Answer 1 · 2019-12-15T09:40:31.087

0

Somehow I believe you have jumbled it all up a bit.

Use this

exports.doit = async ()=>
{
const request = new sql.Request(conn)
let records = await request.query('select * from dbo.user')

records.forEach(async r=>{
    try{
        // do something
        const inner = new sql.Request(conn)
        let recordInner = await request.query(innerQuery)
        recordInner.forEach(async r=>{//do some stuff})
        inner.close()
    }
    catch(err){
            //do something with the error
    }
    records.close()
})
}

The execution:

async execute(){
    const result = await doit()
    return result
}

execute()

Though I have no idea why you are using two connections at all . Just try writing a more defined query using JOIN OR WHERE subquery. You can achieve all this in a single query instead of a using nested connection. SQL though a bit old, it really is quite powerful.

select * from dbo.order WHERE userId IN (SELECT userId FROM dbo.user)

Makes more sense to me. But, whatever floats your boat.

More on sub-queries: https://www.dofactory.com/sql/subquery

edited Dec 15 '19 at 09:40

answered Dec 11 '19 at 22:47

Aniket Chowdhury

332
3
13

`async/await` in a `.forEach()` is a [no-go](https://stackoverflow.com/questions/37576685/using-async-await-with-a-foreach-loop) – Klaycon Dec 11 '19 at 22:55
It is okay to use it. It just doesn't do sequential execution. It works. That is okay for data selection. So, don't read files with it because it is done sequentially so as not to overload the RAM. We can capture an entire chunk of data and print it. – Aniket Chowdhury Dec 12 '19 at 00:00
@Klaycon Though, I agree that we should probably use a wrapper around it to prevent ambiguity. Some kind of for based loop should work. – Aniket Chowdhury Dec 12 '19 at 00:02
I think we are on a tangent here: The above code works just how I need it to work. This is a truncated version of the code, of course that is streaming the results. The example you have above gets the entire result and then iterates over the rows. I need to process the rows as they come in. ALSO, I have throttling (or pause/resume) built into the original code to avoid memory overload issues. The question i am trying to answer is this: HOW do I await the "exports.doit()" method before continuing in the calling method?? – KickinMhl Dec 12 '19 at 15:31
@KickinMhl Ah! That's rather easy. You also need to declare doit as an async function for that. Read both pieces of code. – Aniket Chowdhury Dec 15 '19 at 09:41

score 0 · Accepted Answer · answered Dec 13 '19 at 22:09

0

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.

answered Dec 13 '19 at 22:09

KickinMhl

1,218
3
14
32

1

Do share the updated code. You can have awaited the exports.doit() by calling it a bit more differently. I will put that code in case you need it. – Aniket Chowdhury Dec 15 '19 at 09:38

How to await a streaming sql query in node.js

2 Answers2