CODEWITHSUNDEEP
node.jssqlite
Curious101
Curious101

Reputation: 1738

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

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?

Upvotes: 13

Views: 23891

Answers (6)

Zedwa92
Zedwa92

Reputation: 137

Old question, but I came across the issue, with a different approach as to solve the problem. The Promise option works, though being a little too verbose to my taste, in the case of a db.all(...) call.

I am using instead the event concept of Node:

var eventHandler = require('events')

In your Sqlite function:

function queryWhatever(eventHandler) {

    db.serialize(() => {
       db.all('SELECT * FROM myTable', (err, row) => {

            // At this point, the query is completed
            // You can emit a signal
            eventHandler.emit('done', 'The query is completed')

        })
     })

}

Then, give your callback function to the eventHandler, that "reacts" to the 'done' event:

eventHandler.on('done', () => {
    // Do something
})

Upvotes: -1

Daniel Madureira
Daniel Madureira

Reputation: 35

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.

Upvotes: 2

Colin Keenan
Colin Keenan

Reputation: 1149

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();

        }
    });
}

Upvotes: 5

Abraham Brookes
Abraham Brookes

Reputation: 1998

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

Upvotes: 0

freebug
freebug

Reputation: 129

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();
    });
  });
}

Upvotes: 12

Curious101
Curious101

Reputation: 1738

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();

        });


    });

}

Upvotes: 20

Related Questions