[BUG]: Sqlite Error: near "ilike": syntax error

[hyunbinseo]

What version of drizzle-orm are you using?

0.34.1

What version of drizzle-kit are you using?

No response

Describe the Bug

Someone had this issue 16 months ago, and it still seems to exist.

SqliteError: near "ilike": syntax error

await db //
  .select()
  .from(userTable)
  .where(ilike(userTable.contact, '%hyunbinseo%'));

Expected behavior

No response

Environment & setup

No response

[hyunbinseo]

I feel like this is a confirmed bug because the following works without issue:

Actually, this is not a bug. SQLite does not support the ilike filter.

It is stated in the Drizzle ORM docs as well. Would be nice if a better error was thrown by Drizzle ORM itself.

SELECT * FROM table_name WHERE column_name LIKE 'pattern' COLLATE NOCASE;

COLLATE NOCASE should be used instead. Possibly related https://github.com/drizzle-team/drizzle-orm/issues/638

[hyunbinseo]

RangeError: Too many parameter values were provided

// no
sql`${userTable.contact} LIKE '%${keyword}%' COLLATE NOCASE`,

// yes
sql`${userTable.contact} LIKE ${'%' + keyword + '%'} COLLATE NOCASE`,
like(userTable.contact, `%${keyword}%`),