Node-oracledb: Will this asynch / await typescript code lock node event loop?

[josecaodaglio]

In order to simplify the business logic which constantly accesses the database, I made some typescript wrappers as follows, but I am wondering whether I am incurring in some very bad collateral damages.

Wrapper example:

export async function getSequence (conn: db.IConnection, sequenceName: string): Promise<number> {

    try {
        let sql: string = 'select ' + sequenceName + '.nextval next from dual';
        let ret: TReturn = await executeSQL(conn,  sql);

        return <number>ret[0]['NEXT'];
    } catch (err) {
        console.log('GETSEQUENCE ', err, '   ', typeof err); 
        throw new CustomError(errorCodes.sequenceDoesNotExists, 'Sequência ' + sequenceName + ' inválida ');
    } 
}

export async function executeSQL (conn: db.IConnection, sql: string, binders: Object = {}): Promise<TReturn> {  

    let execReturn : db.IResultSet;
    let dbReturn   : db.IExecuteReturn;

    try {
         dbReturn = await conn.execute(sql, binders, executeOptions);
        if (! dbReturn.rows) {
            console.log('** CONNECTION - NO LINES??'); 
        }
        metaDataPrint(dbReturn.metaData);
        return dbReturn.rows;
    }
    catch (err) {
        console.log('EXECUTESQL ====> ', err, '   ', typeof err); 
        throw new PropagateError(errorCodes.statementInvalid, err, sql);  
    };

export async function getConnection(): Promise<db.IConnection> {
    let connection: db.IConnection; 
    if (! connectionPool) {    // == semaphore?
        connectionPool = await initPool();
    };

    return connectionPool.getConnection();

};

I have codified many wrappers like this. Three questions:
1- async and await will block the others calls to getConnection, execute, getSequence?
2- As I don't have typescript/node experience, is this a reasonable way to deal with database access? Am I reinventing the wheel?
3- Should I put a semaphore in the "if (! connectionPool)" sentence?

Thank you

edit: some code like metaDataPrint and console.log are there for debug purposes.

[bjouhier]

1- async and await will block the others calls to getConnection, execute, getSequence?

No. await will pass control back to the event loop, and the event loop will resume just after await when the promise is resolved.

2- As I don't have typescript/node experience, is this a reasonable way to deal with database access? Am I reinventing the wheel?

Yes, this is reasonable. You are not reinventing the wheel. This coding style existed even before async/await landed in JavaScript, with tools like fibers, streamline.js, iced coffeescript, co, ...

3- Should I put a semaphore in the "if (! connectionPool)" sentence?

Yes. await will yield to the event loop so you may end up with several pools without semaphore.

[dmcghan]

@StdInOut I might be missing something, but I don't think a semaphore will be sufficient. To delay pool creation to the getConnection call would require the calls to be queued till the pool is created. I don't recommend this approach.

Instead, create the pool in your "main" module, prior to starting your web server. That way everything is ready by the time requests start coming in.

[bjouhier]

@dmcghan By semaphore I meant a function that will queue the calls and only let them execute one by one. Something like https://github.com/Sage/streamline-runtime/blob/master/src/flows.md#funnel (doc uses streamline.js syntax - just think of the _ as an async/await marker - pure callback implementation here). This is how we create critical sections around blocks that contain async calls.

[josecaodaglio]

Thank you guys.

Good to know I am going to somewhere and not reinventing the wheel.

While I was testing multi-sessions, I did have an exception due to the lack of semaphore. I liked dmcghan suggestion to create the pool in the main module.

But sometimes it will be necessary to have more pools (error log for instance) and I should be able to get more during execution time.

By semaphore I meant something like prex, https://www.npmjs.com/package/prex
@bjouhier, I think the code with flows would be pretty the same, though it seems to me that flow is a more reliable solution.

the code could be like this

import { Semaphore } from "prex";
const criticalResource = new Semaphore(1); // this lines would be in "main"

and in the pool

import { Semaphore } from "prex";

export async function getConnection(): Promise<db.IConnection> {
    let connection: db.IConnection; 
    try {
        if (! connectionPool) {
            connectionPool = await initPool();
        };
    } catch (err) {
        throw new ErrorPropagate(errorCodes.connection.connectionError, err, '@getConnection');   
    };
    return connectionPool.getConnection();

};

async function initPool(): Promise<db.IConnectionPool> {
   try {
     await criticalResource.wait();
     if (! connectionPool) {   //  <=== double check needed here
          return await db.createPool(connectionAtributes);
   };
   } catch (err) {
     throw new ErrorPropagate(errorCodes.connection.connectionError, err, '@initPool');  
   }
};  

The second time I check if connectionPool is null is because the if in getConnection is not locked and as it will be called thousand of times it would affect the overall performance.

initPool will be called very few times so it is ok to be locked.

I think the prex approach will manage queue for me, is it correct?

Thanks

[bjouhier]

I haven't used prex but I don't see how it would implement the semaphore without a queue.

I don't see where you are releasing the semaphore and you are not assigning connectionPool. Shoudn't it be:

async function initPool(): Promise<db.IConnectionPool> {
   await criticalResource.wait();
   try {
     return connectionPool || (connectionPool = await db.createPool(connectionAtributes));
   } finally {
     criticalResource.release();  
   }
}; 

node JavaScript execution is single threaded and non-preemptive. So this is a very lightweight semaphore; just a little bit of clever queuing logic.

[josecaodaglio]

Oh, I forgot to release the lock.

return connectionPool || (connectionPool = await db.createPool(connectionAtributes));

Will the code above return a boolean?

return connectionPool? connectionPool : await db.createPool(connectionAtributes));
Thanks a lot.

[bjouhier]

return connectionPool || (connectionPool = await db.createPool(connectionAtributes));

It will return the connectionPool. This is a common idiom in JavaScript.

return connectionPool? connectionPool : await db.createPool(connectionAtributes));

Not quite because connectionPool is never assigned. It would need to be:

return connectionPool ? connectionPool : (connectionPool = await db.createPool(connectionAtributes));`

The || idiom is equivalent (and shorter).