Node-sqlite3: Efficiently select rows by various ids, in a single query-CodePudding

I'd like to write a wrapper function function select(db: any, ids: number[]): Cat[] that returns an array of Cat rows fetched from the DB by ID. The function should return the entire array of rows.

Below is one approach I've written. Instead of calling db.each on every ID in a for-loop as I do below, is it possible to pass my entire ids: number[] array as a parameter to a db.all / db.each query function?

// dbmethods.ts

async function select(db: any, ids: number[]): Promise<Cat[]> {
  let query = "SELECT * FROM cats_table WHERE id = ?;";
  let cats_back: Cat[] = [];

  for (let i = 0; i < ids.length; i  ) {
    let cat: Promise<Cat> = new Promise(async function (resolve, reject) {
      await db.get(query, ids[i], (err: Error, row: any) => {
        if (err) {
          reject(err);
        } else {
          let cat: Cat = {
            index: row.id,
            cat_type: row.cat_type,
            health: row.health,
            num_paws: row.num_paws
          };

          resolve(cat);
        }
      });
    });

    cats_back.push(await cat);
  }

  return cats_back;
}

and

// index.ts

let ids = create_many_ids(10_000); // returns an array of unique ordered ints between 0 and 10K
let res = await select(db, ids);
console.log(res); // successfully prints my cats

Benchmarks on my select function above suggest that it takes 300ms to select 10_000 rows by ID. It seems to me that's a little long; 10K rows shouldn't take that long for sqlite's select by id functionality... How can I be more efficient?

CodePudding user response：

SELECT * FROM cats_table WHERE id IN (SELECT value FROM json_each(?));

The query parameter is a string representing a JSON array of ids, e.g., '[1, 2, 4]'

See this tutorial for further details