Better-sqlite3: SQL IN (...) operator

[aabfred]

I found no way to write parameters for such query:
SELECT * FROM mytable WHERE rowid IN (1,2,3);

Test 1:
const t1 = db.prepare('SELECT * FROM mytable WHERE rowid IN (?)');
t1.all(1,2,3);
=> RangeError: Too many parameter values were provided
t1.all([1,2,3]);
=> RangeError: Too many parameter values were provided
t1.all(new Set([1,2,3]));
=> TypeError: SQLite3 can only bind numbers, strings, Buffers, and null

Test 2:
const t2 = db.prepare('SELECT * FROM mytable WHERE rowid IN (@args)');
t2.all({args: [1,2,3]});
=> TypeError: SQLite3 can only bind numbers, strings, Buffers, and null
t2.all({args: new Set([1,2,3])});
=> TypeError: SQLite3 can only bind numbers, strings, Buffers, and null

[JoshuaWise]

This is a limitation of Sqlite3, not better-sqlite3. You have to construct the SQL string manually for each array.

function selectByIds(ids) {
  const params = '?,'.repeat(ids.length).slice(0, -1);
  const stmt = db.prepare(`SELECT * FROM mytable WHERE rowid IN (${params})`);
  return stmt.all(ids);
}
const results = selectByIds([1, 2, 3]);

If you want to reuse the prepared statements to improve performance, you can cache them by their parameter count:

const cache = new Map;
function selectByIds(ids) {
  let stmt = cache.get(ids.length);
  if (stmt === undefined) {
    const params = '?,'.repeat(ids.length).slice(0, -1);
    stmt = db.prepare(`SELECT * FROM mytable WHERE rowid IN (${params})`);
    cache.set(ids.length, stmt);
  }
  return stmt.all(ids);
}
const results1 = selectByIds([1, 2, 3]); // stmt is created and cached
const results2 = selectByIds([4, 5, 6]); // stmt is reused from cache

Depending on the use case, the caching approach could result in an unacceptably large cache.