🚀 Feature Request: D1 allow binding JS array as parameter

[GeorgeTailor]

Describe the solution

Hello.

I am not sure if this is possible or not but I would like to bind a JS array to a DB query for in clause.

For example:

await context.env.DB_local.prepare('update notification set read = 1 where id in ?').bind(['1','2','3']).run();

Documentation only mentiones a limited set of parameter types that can be used, such as null, number, string and ArrayBuffer

An alternative would be to execute a batch of queries but that seems to be an overkill.

[GeorgeTailor]

I have also tried just passing string

const info = await context.env.DB_local
.prepare(`update notification set read = true where id in (?) `)
.bind('1,2,3').run();

which returns success but the records do not change

[eloff]

For now you can workaround this by constructing and replacing the IN clause yourself:

const inClause = userIds
	.map((userId) => {
		if (!Number.isInteger(userId)) { throw Error("invalid id, expected an integer"); }
		return userId.toString();
	})
	join(',');
	
let query = 'SELECT * FROM users WHERE id IN ($userIds)';
query = query.replace('$userIds', inClause);

This is safe with integers, but very unsafe with user provided strings. Be careful not to create a sql injection vulnerability.

[hdformat]

i tried like this and it wokred for me

const guest_ids = [1,2,3,4,5,6,7,8,9,10]
const question_marks = Array.from(Array(guest_ids.length)).map(id=>"?")
DB.prepare(`SELECT * FROM guests WHERE id IN (${question_marks})`).bind(...guest_ids).run()