node.js - sqlite3 read all records in table and return

Question

I'm trying to read all records in a sqlite3 table and return them via callback. But it seems that despite using serialize these calls are still ASYNC. Here is my code:

var readRecordsFromMediaTable = function(callback){

    var db = new sqlite3.Database(file, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE);

    var allRecords = [];

    db.serialize(function() {

        db.each("SELECT * FROM MediaTable", function(err, row) {

            myLib.generateLog(levelDebug, util.inspect(row));
            allRecords.push(row);

        }

        callback(allRecords);
        db.close();

    });

}

When the callback gets fired the array prints '[]'.

Is there another call that I can make (instead of db.each) that will give me all rows in one shot. I have no need for iterating through each row here.

If there isn't, how do I read all records and only then call the callback with results?

Answer 1

I was able to find answer to this question. Here it is for anyone who is looking:

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

var readRecordsFromMediaTable = function(callback){

    var db = new sqlite3.Database(file, sqlite3.OPEN_READONLY);

    db.serialize(function() {

        db.all("SELECT * FROM MediaTable", function(err, allRows) {

            if(err != null){
                console.log(err);
                callback(err);
            }

            console.log(util.inspect(allRows));

            callback(allRows);
            db.close();

        });


    });

}

Answer 2

A promise based method

var readRecordsFromMediaTable = function(){
  return new Promise(function (resolve, reject) {
    var responseObj;
    db.all("SELECT * FROM MediaTable", null, function cb(err, rows) {
      if (err) {
        responseObj = {
          'error': err
        };
        reject(responseObj);
      } else {
        responseObj = {
          statement: this,
          rows: rows
        };
        resolve(responseObj);
      }
      db.close();
    });
  });
}

Answer 3

The accepted answer using db.all with a callback is correct since db.each wasn't actually needed. However, if db.each was needed, the solution is provided in the node-sqlite3 API documentation, https://github.com/mapbox/node-sqlite3/wiki/API#databaseeachsql-param--callback-complete:

Database#each(sql, [param, ...], [callback], [complete])

...

After all row callbacks were called, the completion callback will be called if present. The first argument is an error object, and the second argument is the number of retrieved rows

So, where you end the first callback, instead of just } put }, function() {...}. Something like this:

var readRecordsFromMediaTable = function(callback){

var db = new sqlite3.Database(file, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE);

var allRecords = [];

    db.serialize(function() {

        db.each("SELECT * FROM MediaTable", function(err, row) {

            myLib.generateLog(levelDebug, util.inspect(row));
            allRecords.push(row);

        }, function(err, count) {

            callback(allRecords);
            db.close();

        }
    });
}

Answer 4

I know I'm kinda late, but since you're here, please consider this:

Note that it first retrieves all result rows and stores them in memory. For queries that have potentially large result sets, use the Database#each function to retrieve all rows or Database#prepare followed by multiple Statement#get calls to retrieve a previously unknown amount of rows.

As described in the node-sqlite3 docs, you should use .each() if you're after a very large or unknown number or rows, since .all() will store all result set in memory before dumping it.

That being said, take a look at Colin Keenan's answer.