Support for array parameter in "WHERE IN" clause

[Shogobg]

Hello.
I have the following code:

import { Client as LibsqlClient, createClient } from '@libsql/client/web';
...
const res = await client.execute({
	sql: 'SELECT * FROM watched_videos WHERE videoId IN (:videos) LIMIT 10;',
	args: { videos: ['--0zLNUmhRI', '--1AEHcLmuk'] },
});

Expected behavior:
Given the table has more than 2 records and the records with videoId '--0zLNUmhRI' and '--1AEHcLmuk' exist, the query should return two rows containing the corresponding videoId.

Actual result - no records are returned:

{
    "columns": [
        "videoId",
        "status"
    ],
    "columnTypes": [
        "varchar(255)",
        "INT",
    ],
    "rows": [],
    "rowsAffected": 0,
    "lastInsertRowid": null
}

I've looked at the code libsql, and it seems that currently there is no support for an array of values as argument, like the above.
https://github.com/tursodatabase/libsql-client-ts/blob/main/packages/libsql-core/src/api.ts#L422

[michaelhaar]

The expected args type for the .execute() function seems to be defined here:

libsql-client-ts/packages/libsql-core/src/api.ts

Lines 65 to 89 in 10cd150

	export interface Client {
	/** Execute a single SQL statement.
	*
	* Every statement executed with this method is executed in its own logical database connection. If you
	* want to execute a group of statements in a transaction, use the {@link batch} or the {@link
	* transaction} methods.
	*
	* ```javascript
	* // execute a statement without arguments
	* const rs = await client.execute("SELECT * FROM books");
	*
	* // execute a statement with positional arguments
	* const rs = await client.execute({
	* sql: "SELECT * FROM books WHERE author = ?",
	* args: ["Jane Austen"],
	* });
	*
	* // execute a statement with named arguments
	* const rs = await client.execute({
	* sql: "SELECT * FROM books WHERE published_at > $year",
	* args: {year: 1719},
	* });
	* ```
	*/
	execute(stmt: InStatement): Promise<ResultSet>;

libsql-client-ts/packages/libsql-core/src/api.ts

Lines 469 to 474 in 10cd150

	export type Value = null | string | number | bigint | ArrayBuffer;
	export type InValue = Value | boolean | Uint8Array | Date;
	export type InStatement = { sql: string; args: InArgs } | string;
	export type InArgs = Array<InValue> | Record<string, InValue>;

---

From what I'm understanding, the args interface is coming from the libsql package, because .execute() is internally calling executeStmt()

libsql-client-ts/packages/libsql-client/src/sqlite3.ts

Lines 125 to 142 in 10cd150

	async execute(
	stmtOrSql: InStatement | string,
	args?: InArgs,
	): Promise<ResultSet> {
	let stmt: InStatement;
	if (typeof stmtOrSql === "string") {
	stmt = {
	sql: stmtOrSql,
	args: args || [],
	};
	} else {
	stmt = stmtOrSql;
	}
	this.#checkNotClosed();
	return executeStmt(this.#getDb(), stmt, this.#intMode);
	}

and executeStmt() is passing the args down to the libsql db object via the .all() and/or the .run() functions:

libsql-client-ts/packages/libsql-client/src/sqlite3.ts

Lines 324 to 391 in 10cd150

	function executeStmt(
	db: Database.Database,
	stmt: InStatement,
	intMode: IntMode,
	): ResultSet {
	let sql: string;
	let args: Array<unknown> | Record<string, unknown>;
	if (typeof stmt === "string") {
	sql = stmt;
	args = [];
	} else {
	sql = stmt.sql;
	if (Array.isArray(stmt.args)) {
	args = stmt.args.map((value) => valueToSql(value, intMode));
	} else {
	args = {};
	for (const name in stmt.args) {
	const argName =
	name[0] === "@" || name[0] === "$" || name[0] === ":"
	? name.substring(1)
	: name;
	args[argName] = valueToSql(stmt.args[name], intMode);
	}
	}
	}
	try {
	const sqlStmt = db.prepare(sql);
	sqlStmt.safeIntegers(true);
	let returnsData = true;
	try {
	sqlStmt.raw(true);
	} catch {
	// raw() throws an exception if the statement does not return data
	returnsData = false;
	}
	if (returnsData) {
	const columns = Array.from(
	sqlStmt.columns().map((col) => col.name),
	);
	const columnTypes = Array.from(
	sqlStmt.columns().map((col) => col.type ?? ""),
	);
	const rows = sqlStmt.all(args).map((sqlRow) => {
	return rowFromSql(sqlRow as Array<unknown>, columns, intMode);
	});
	// TODO: can we get this info from better-sqlite3?
	const rowsAffected = 0;
	const lastInsertRowid = undefined;
	return new ResultSetImpl(
	columns,
	columnTypes,
	rows,
	rowsAffected,
	lastInsertRowid,
	);
	} else {
	const info = sqlStmt.run(args);
	const rowsAffected = info.changes;
	const lastInsertRowid = BigInt(info.lastInsertRowid);
	return new ResultSetImpl([], [], [], rowsAffected, lastInsertRowid);
	}
	} catch (e) {
	throw mapSqliteError(e);
	}
	}

I'm not smart enough to understand whats happening after, but it seems to be related to these files:

https://github.com/tursodatabase/libsql/blob/a636c9c3b46927833fe17776331bcbd655d7d9dc/libsql/src/statement.rs#L57-L61

https://github.com/tursodatabase/libsql/blob/a636c9c3b46927833fe17776331bcbd655d7d9dc/libsql/src/params.rs#L70-L94

Interestingly, this file contains an example using the 'WHERE IN' clause, but I'm not sure if we can use this somehow.

---

I ended up writing a custom util/helper function to transform the named placeholders:

export function transformNamedArgsToPositionalArgs({
  sql,
  args,
}: {
  sql: string;
  args: Record<string, InValue | string[]>;
}): { sql: string; args: InValue[] } {
  const positionalArgs: InValue[] = [];
  const transformedSql = sql.replace(/(:\w+)/g, (match) => {
    const argName = match.substring(1);
    const argValue = args[argName];

    if (argValue === undefined) {
      throw new Error(`Missing argument for ${argName}`);
    }

    if (Array.isArray(argValue)) {
      positionalArgs.push(...argValue);
      return argValue.map(() => '?').join(', ');
    }

    positionalArgs.push(argValue);
    return '?';
  });

  return { sql: transformedSql, args: positionalArgs };
}

Usage:

const res = await client.execute(transformNamedArgsToPositionalArgs({
	sql: 'SELECT * FROM watched_videos WHERE videoId IN (:videos) LIMIT 10;',
	args: { videos: ['--0zLNUmhRI', '--1AEHcLmuk'] },
}));