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

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 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.

Answer 2

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 3

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 4

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 5

I tackled this differently, since these calls are asynchronous you need to wait until they complete to return their data. I did it with a setInterval(), kind of like throwing pizza dough up into the air and waiting for it to come back down.

var reply = '';

db.all(query, [], function(err, rows){
    if(err != null) {
        reply = err;
    } else {
        reply = rows;
    }
});

var callbacker = setInterval(function(){
    // check that our reply has been modified yet
    if( reply !== '' ){
        // clear the interval
        clearInterval(callbacker);

        // do work

    }
}, 10); // every ten milliseconds