node.jsdatabasesqliteselect

Using SQL `IN` operator with DatabaseSync in Node (sqlite)

Using DatabaseSync in Node, I can do the following:

const stmt = conn.prepare(`select name from people where id = ?`);
let res = stmt.get(100)

However, I want to select a number of records at once using the IN operator, so I tried the following, thinking that's quite expected, but it does not work:

const stmt = conn.prepare(`select name from people where id in (?)`);
res = stmt.get([100,200,300])

Also:

res = stmt2.get(1,2,3)

How do I actually use the WHERE x IN (y1, y2, ... yn) in this case with parameters?

Solution

  • Instead of a single parameter, try constructing a list of parameters which corresponds to number of binding values.

    For example:

    const values = [100,200,300];

// 3 values, so need 3 params: ?,?,?
const params = values.map(_=>'?').join(',');

const stmt = conn.prepare(`select name from people where id in (${params})`);
res = stmt.get(values);