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:

https://github.com/tursodatabase/libsql-client-ts/blob/10cd1501233d4062cfc98b602fb170629b9c1e67/packages/libsql-core/src/api.ts#L65-L89

https://github.com/tursodatabase/libsql-client-ts/blob/10cd1501233d4062cfc98b602fb170629b9c1e67/packages/libsql-core/src/api.ts#L469-L474

---

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

https://github.com/tursodatabase/libsql-client-ts/blob/10cd1501233d4062cfc98b602fb170629b9c1e67/packages/libsql-client/src/sqlite3.ts#L125-L142

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

https://github.com/tursodatabase/libsql-client-ts/blob/10cd1501233d4062cfc98b602fb170629b9c1e67/packages/libsql-client/src/sqlite3.ts#L324-L391

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'] },
}));