Node.js to return true or false after querying sqlite3

Question

I am trying to query sqlite3 from node function. Depending on result it should return TRUE or FALSE back to the calling code. This is what I have done, but always get 'undefined':

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


var dbOpen = function dbOpen() {
    sqlite3 = require('sqlite3').verbose();
    db = new sqlite3.Database('./internaldb/OPMSE.db');
}
module.exports.dbOpen = dbOpen;
dbOpen();
mKey = 'AIzaSyCrIYuHJ_jiZqmGYoAs7mxiloB3-OBx5WI';
async function db1() {
    function db2() {
        db.each("SELECT STATUS status FROM API_Keys WHERE KEY = ? AND STATUS = ?", [mKey, '1'], (err, row, x) => {
            var keyValid = false;
            if (err) {
                console.log('There is an error in API Validation')
            } else {
                console.log(keyValid + '<--This is from upper fun')
                keyValid = true;
                return keyValid;
            }
            db.close();
            return keyValid;
        });
    } // db2 
    d = await db2();
    console.log(d + '<--Supposed to return TRUE or FALSE')
} //db1

x = db1();
console.log(x)
/*Returns:
Promise { <pending> }
undefined<--Supposed to return TRUE or FALSE
false<--This is from upper fun
*/

see the function expression for the callback to db.each ... returning in that does not return from function db2 ... function db2 has no return, hence it returns undefined ... regardless if you `await` on it or not - you also have no return statement in db1, so it will return a resolved promise of the value `undefined` — Jaromanda X, Dec 11 '17 at 08:10
also, `db.each` suggests the callback can be called multiple times, so how do you expect a single return value from multiple invocations of the callback. — Jaromanda X, Dec 11 '17 at 08:14
You use `await db2()` but `db2` is not async. It is defined as a regular function inside `db1` (which is async). You need `async function db2()` — Jeremy Thille, Dec 11 '17 at 08:32
Function db2 is having a return. Even after adding a return in function db1, the result is the same. Making the db2, an async function did nothing. db.each will always return a single record by the query (and my test table has only 1 record for now). Value of keyValid variable is fine within function db2. But db1 function does not see it, is the issue I guess. — Alan Miley, Dec 11 '17 at 08:52

score 1 · Accepted Answer · answered Dec 11 '17 at 10:03

If you let db2 return a promise it will work, if you want to know if the key is in the database it would be better to select count it'll return one record and you don't need db.each (not sure what the method is to get one record).

Here is the code with some comments, this should work:

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


var dbOpen = function dbOpen() {
  sqlite3 = require('sqlite3').verbose();
  db = new sqlite3.Database('./internaldb/OPMSE.db');
}
module.exports.dbOpen = dbOpen;
dbOpen();
mKey = 'AIzaSyCrIYuHJ_jiZqmGYoAs7mxiloB3-OBx5WI';
async function db1() {
  function db2() {
    //return a promise
    return new Promise(
      (resolve,reject)=>
        db.serialize(
          function() {
            var keyValid = false;
            db.each(//maybe not use each but just get one record and select count(*)
              "SELECT STATUS status FROM API_Keys WHERE KEY = ? AND STATUS = ?",
              [mKey, '1'], (err, row, x) => {
                if (err) {
                  reject(err);//reject on error
                } else {
                  console.log(keyValid + '<--This is from upper fun');
                  keyValid = true;
                }
              }
            );
            db.colose();
          }
        )
      )
  } // db2 
  d = await db2();
  console.log(d + '<--Supposed to return TRUE or FALSE')
} //db1

x = db1();
console.log(x)

Thank you HMR for the help. I am still having Promise pending. It is expecting a return from db1 function, which I can do with a conditional return. However, for that I would need the "keyValid = true;" (inside "db.each" enclosure) be available back in db1 function. If that can be achieved, my issue is resolved. Thank you all for your contribution. — Alan Miley, Dec 11 '17 at 10:34
@AlanMiley first you should maybe look into what a promise is and why it is used [here](https://stackoverflow.com/a/47678417/1641941). Both db1 and db2 return a promise, If you want the value you have to use '.then` and use `.catch` to get the error so `db1.then(x=>console.log("resolved",x)).catch(e=>console.log("rejected:",e))` — HMR, Dec 11 '17 at 11:02
Thank you HMR, it was very helpful reading to know how promises work. Appreciate it. — Alan Miley, Dec 11 '17 at 11:24

Node.js to return true or false after querying sqlite3

1 Answers1