node.js - NodeJS SQLite - Async Awaits and Db connections

Question

I am trying to connect to SQLite database using NodeJS. I have authored the below code.

var sqlite3 = require('sqlite3').verbose();

// Connect to the SQLite database.
let db = new sqlite3.Database('D:\Development\sqlite\workflows_dev', sqlite3.OPEN_READWRITE, (err) => {
    if(err) {
    console.error(err.message);
    }

    console.log('Connected to the Workflows database.');
});

// Inserting a new row into the table.
// Ex: INSERT INTO reviews( ticket, review_requested_on, reviewer_id ) VALUES ( 'INC00001', '29-Jan-2021', 'pdittaka' );
function insertReviewRecord( ticketNumber, reviewer_id ) {

    let now = new Date().toISOString().
                        replace(/T/, ' ').      // replace T with a space
                        replace(/..+/, '')     // delete the dot and everything after

    let data = [ticketNumber, now, reviewer_id];
    let sql = `INSERT INTO reviews( ticket, review_requested_on, reviewer_id ) values ( ?, ?, ? )`

    db.run(sql, data, function(err) {
    if (err) {
        return console.error(err.message);
    }
    console.log(`Row(s) updated: ${this.changes}`);
    });
}

// Updating an existing row into the table.
// Ex: UPDATE reviews SET review_result = 'Approved', reviewed_on = '05-02-2021' WHERE ticket ='INC13409' AND reviewer_id = 'rvalden';
// Ex: '2012-11-04 14:55:45'
function updateReviewRecord( ticketNumber, reviewer_id, review_result )
{
    let now = new Date().toISOString().
                        replace(/T/, ' ').      // replace T with a space
                        replace(/..+/, '')     // delete the dot and everything after

    let data = [ review_result, now, ticketNumber, reviewer_id];
    let sql = `UPDATE reviews SET review_result = ?, reviewed_on = ? WHERE ticket = ? AND reviewer_id = ?`

    db.run(sql, data, function(err) {
    if (err) {
        return console.error(err.message);
    }
    console.log(`Row(s) updated: ${this.changes}`);
    });
}

insertReviewRecord( "REQ9978", "ychandolu" );
updateReviewRecord( 'REQ9978', 'ychandolu', 'Reject' )

// close the database connection
// Do I never need it?.
function closeConnection() {
    db.close();
}

// Exporting the two methods.
exports.insertReviewRecord = insertReviewRecord;
exports.updateReviewRecord = updateReviewRecord;

Things work fine. But I wanted to replace call backs for db.run with async-await. So I did the below conversion for the insert method.

async function insertReviewRecord( ticketNumber, reviewer_id ) {
    try {
        let now = new Date().toISOString().
                            replace(/T/, ' ').      // replace T with a space
                            replace(/..+/, '')     // delete the dot and everything after

        let data = [ticketNumber, now, reviewer_id];
        let sql = `INSERT INTO reviews( ticket, review_requested_on, reviewer_id ) values ( ?, ?, ? )`
        const result = await db.run( sql, data );
        console.log( 'Rows Updated' + result.changes );
    } catch(err) {
        return console.error(err.message);
    }
}

But I get this warning - 'await' has no effect on the type of this expression.

I am learning async await and thought this is a good opportunity for converting callbacks to async/awaits. I investigated and found that db.run is not giving a promise as response and so cannot use await. But if that is the case, does this not mean that I cannot convert this block to async await?

Also, my plan is to leave the connection part of the database as is, because it will run only once during the application lifecycle. Is this a good idea?.

Please share your ideas..

Thanks, Pavan.

question from:https://stackoverflow.com/questions/66062088/nodejs-sqlite-async-awaits-and-db-connections

Answer 1

Waitting for answers