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