javascriptnode.jssqlitenode-sqlite3

How to iterate rows sequentially in sqlite3 and nodejs?


I want to iterate through my sqlite database synchronously. Using async and sleep in db.each() does not delay the each callback since it does not use await on the callback internally:

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

(async () =>
{
    var db = await sqlite.open({
        filename: "./mydatabase.db",
        driver: sqlite3.Database
    });

    // doesn't await this async callback before calling the next
    // row result function 
    await db.each(`SELECT * FROM mytable`, async (err, row) =>
    {
        await sleep(10000);
        
        console.log(row);
    });
})();

I don't want to use .all() since it will take a long time to load hundreds of thousands of records into memory.

I also don't want to use LIMIT x OFFSET y since it will require re-running the query multiple times for each section that I would check making it slower.

How can I iterate the sqlite results synchronously so that the next row isn't fetched until I finish processing the current row?

In PHP it would be similar to this kind of loop:

// fetches one result at a time and does not continue until
// I call the next `mysql_fetch_row`
while(($row = mysql_fetch_row($queryResult))
{
    var_dump($row);
    sleep(123);
}

I want to get one result at a time rather than be flooded with hundreds of thousands of callback functions being called without pause.


Solution

  • This is currently a feature request for sqlite library.

    You can see it here: https://github.com/kriasoft/node-sqlite/issues/127

    You can see examples how to achieve that like AlttiRi's answer: https://github.com/kriasoft/node-sqlite/issues/127#issuecomment-794317769

    But my suggestion is the same as @Rick Suggs, you will be better off using better-sqlite3: https://www.npmjs.com/package/better-sqlite3

    Which is designed from the ground up to use async / await syntax.

    and it is straightforward like PHP:

    const stmt = db.prepare('SELECT * FROM cats');
    
    for (const cat of stmt.iterate()) {
      if (cat.name === 'Joey') {
        console.log('found him!');
        break;
      }
    }
    

    Here is the full documentation: https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md#iteratebindparameters---iterator

    Here is a full example how you can achieve that:

    const Database = require("better-sqlite3");
    const db = new Database("mydatabase.db", { verbose: console.log });
    
    const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
    
    (async () => {
    
        // Execute only the first time to create table with data
        // await db.exec("CREATE TABLE myTable (col TEXT)");
        // await db.exec('INSERT INTO myTable VALUES ("test")');
        // await db.exec('INSERT INTO myTable VALUES ("test2")');
    
        const stmt = db.prepare("SELECT * FROM myTable");
    
        for (const row of stmt.iterate()) {
            await sleep(3000);
            console.log(row);
        }
    })();
    

    The output is:

    // 3 seconds delay
    { col: 'test' }
    // 3 seconds delay
    { col: 'test2' }