nodejs sqlite3 query returns empty - trouble understanding async

Question

Im sure im not writing my code with async in mind. But i dont know how to.

// order.xml
const db = require("./db");

let orders = db.getNewOrders();
console.log(orders);

and

//db.js
const sqlite3 = require("sqlite3").verbose();
let db = new sqlite3.Database(process.env.DB_PATH, err => {
  if (err) {
    console.error(err.message);
  }
  console.log("Connected to the test database.");
});

db.run(
  ....
);

function getNewOrders() {
  let orders = [];
  db.each("SELECT * FROM Orders", function(err, row) {
    orders.push(row);
    console.log(1);
  });
  return orders;
}

module.exports.getNewOrders = getNewOrders;

the console returns

[]
Connected to the test database.
1

But I'm expecting rows of data. I definitely have data, I checked with a SQL client.

So clearly getNewOrders returns an empty array because the DB has yet to complete the operation. How do I update my code so that the DB finishes and populates the orders array so I can use it in the other file I got?

Answer 1

You can return a promise from getNewOrders function.

And you can use db.all method instead of db.each.

Call that function with await.

const db = require("./db");

db.getNewOrders().then(rows => {
  console.log(rows);
})

//db.js
const sqlite3 = require("sqlite3").verbose();
let db = new sqlite3.Database(process.env.DB_PATH, err => {
  if (err) {
    console.error(err.message);
  }
  console.log("Connected to the test database.");
});

db.run(
  ....
);

function getNewOrders() {
 return new Promise((resolve, reject) => {
  let orders = [];
  db.all("SELECT * FROM Orders", function(err, rows) {
    resolve(rows);
  });
 }
});

module.exports.getNewOrders = getNewOrders;