sqlite3 - promise for asynchronous calls

Question

I want to select asynchronous some data of a sqlite3 database. But since db.each is a asynchron function my following select function doesn't work properly. How to add a Promise to wait for the result?

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

export default function select(database, table) {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(database);
    const queries = [];
    db.each(`SELECT rowid as key, * FROM ${table}`, (err, row) => {
      if (err) {
        reject(err);
      }
      console.log(`Push row ${row.key} from database.`);
      queries.push(row);
    });
    console.log(queries);
    console.log(JSON.stringify(queries));
  });
}

Solution

db.each() requires a slightly cumbersome, non-standard promisification due to the nature of its callbacks, through which it :

• delivers rows, one at a time, to a first callback,
• signals completion to a second callback.

Compare that with the standard pattern exhibited in db.all(), which takes a single callback with the signature (err, rows).

Sticking with db.each(), what you have written is correct as far as it goes but stops short of resolving the Promise on completion of db.each().

Fortunately the solution, despite being cumbersome, is fairly simple. resolve(queries) can be called from a second, callback.

export default function select(database, table) {
    return new Promise((resolve, reject) => {
        const db = new sqlite3.Database(database);
        const queries = [];
        db.each(`SELECT rowid as key, * FROM ${table}`, (err, row) => {
            if (err) {
                reject(err); // optional: you might choose to swallow errors.
            } else {
                queries.push(row); // accumulate the data
            }
        }, (err, n) => {
            if (err) {
                reject(err); // optional: again, you might choose to swallow this error.
            } else {
                resolve(queries); // resolve the promise
            }
        });
    });
}

If the expected number of rows is "very limited" (as it says in the SQLite documentation), then use db.all() instead.