How to iterate rows sequentially in sqlite3 and nodejs?

Question

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.

You might want to try a different npm package. better-sqlite3 claims to support iterating synchronously. See: github.com/WiseLibs/better-sqlite3/blob/master/docs/… — Rick Suggs
– Rick Suggs, Commented Jan 7, 2023 at 4:15

Alex Kolarski · Accepted Answer · 2023-01-08 14:30:00Z

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' }

Collectives™ on Stack Overflow

How to iterate rows sequentially in sqlite3 and nodejs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related