javascriptnode.jsasynchronousasync-awaitnode-sqlite3

Cannot await for sqlite3.Database.get() function completion in Node.js


I'm struggling with some basic async/await problem in node.js using node-sqlite3.
My objective is to select some value from SQLite DB, check it for some condition and take some actions in case the condition is met. Here's the code:

const sqlite3 = require('sqlite3').verbose();
main();

async function main() {
    let ordersDb = await createDbConnection('./ProcessedOrders.db');
    var orderProcessed = await orderAlreadyProcessed(ordersDb, "555");
    console.log("orderProcessed = " + orderProcessed);
    if (!orderProcessed) {
        console.log("So condition is met!");
    }
}


async function orderAlreadyProcessed(ordersDb, orderNumberStr) {
    console.log('starting orderAlreadyProcessed function'); //DEBUG
    var result;
    var query = 'select count(SoldOrderNumber) as "recsCount" from ProcessedSoldOrders where SoldOrderNumber = ?;';
    await ordersDb.get(query
    ,[orderNumberStr]
    ,(err, row) => {
        console.log('Row with count = ' + row); //DEBUG
        console.log('row.recsCount = ' + row.recsCount); //DEBUG
        result = typeof row !== 'undefined' && row.recsCount > 0;
    });
    console.log('Returning ' + result); //DEBUG
    return result;
}

async function createDbConnection(dbFileName) {
    let db = new sqlite3.Database(dbFileName, (err) => {
        if (err) {
            console.log(err.message);
        }
    });
    return db;
}

But what I get is code executing further, not awaiting for Database.get() method at all! As a result, here's what I see printing in console:

starting orderAlreadyProcessed function
Returning undefined
orderProcessed = undefined
So IF condition met!
Row with count = [object Object]
row.recsCount = 1

As we can see, we return from orderAlreadyProcessed too early with return value = 'undefined'. So condition is met, actions taken, and only then Database.get() returns. But if it was properly awaited, condition would not be met.

How can I make it await for result value?


Solution

  • Since you want to use async/await, and the node-sqlite3 (sqlite3) library does not support the Promise API, you need to use the node-sqlite (sqlite) library, which is a wrapper over sqlite3 and adds support for the Promise API. Then, your code will look something like this:

    const sqlite3 = require('sqlite3');
    const { open } = require('sqlite');
    
    async function main() {
        try {
            sqlite3.verbose();
            const ordersDb = await createDbConnection('./ProcessedOrders.db');
            const orderProcessed = await orderAlreadyProcessed(ordersDb, "555");
            console.log("orderProcessed = " + orderProcessed);
            if (!orderProcessed) {
                console.log("So condition is met!");
            }
        } catch (error) {
            console.error(error);
        }
    }
    
    
    async function orderAlreadyProcessed(ordersDb, orderNumberStr) {
        try {
            console.log('Starting orderAlreadyProcessed function');
            const query = 'SELECT COUNT(SoldOrderNumber) as `recsCount` from ProcessedSoldOrders where SoldOrderNumber = ?;'
            const row = await ordersDb.get(query, [orderNumberStr]);
            console.log('Row with count =', row);
            console.log('row.recsCount =', row.recsCount);
            const result = typeof row !== 'undefined' && row.recsCount > 0;
            console.log('Returning ' + result);
            return result;
        } catch (error) {
            console.error(error);
            throw error;
        }
    }
    
    function createDbConnection(filename) {
        return open({
            filename,
            driver: sqlite3.Database
        });
    }
    
    main();
    

    I specifically did not remove your console.log and other parts of the code so as not to confuse the original logic of your program.