SQLITE_ERROR: near "ilike": syntax error

I've currently setup an my SQL query as follows:

export const getObservationsByUserId = async (
  db: LibSQLDatabase,
  uid: string,
  search?: string,
  page?: Pagination
): Promise<Observation[]> => {
  const { limit, offset } = page || { limit: 10, offset: 0 }

  const observationsForUserId = db.select().from(observations).where(eq(observations.owner, uid))

  if (search) {
    observationsForUserId.where(ilike(observations.name, `%${search}%`))
  }

  return await observationsForUserId.limit(limit).offset(offset).all()
}

export const getObservationsByUserId = async (
  db: LibSQLDatabase,
  uid: string,
  search?: string,
  page?: Pagination
): Promise<Observation[]> => {
  const { limit, offset } = page || { limit: 10, offset: 0 }

  const observationsForUserId = db.select().from(observations).where(eq(observations.owner, uid))

  if (search) {
    observationsForUserId.where(ilike(observations.name, `%${search}%`))
  }

  return await observationsForUserId.limit(limit).offset(offset).all()
}

However, I am seeing this error: SQLITE_ERROR: near "ilike": syntax error ... the search term is validated as a string, and I am using the %{search}% syntax, I'm wondering if this is a bug or is there something quirky I need to do to get ilike to work? (I've imported) ...

3 Replies

MichaelOP•2y ago

Yeh @Andrew Sherman I feel like this is a confirmed bug because the following works without issue:

export const getObservationsByUserId = async (
  db: LibSQLDatabase,
  uid: string,
  search?: string,
  page?: Pagination
): Promise<Observation[]> => {
  const { limit, offset } = page || { limit: 10, offset: 0 }

  const observationsForUserId = db.select().from(observations).where(eq(observations.owner, uid))

  if (typeof search === 'string' && search.length >= 1) {
    console.log(search)
    observationsForUserId.where(sql`lower(${observations.name}) like ${placeholder('name')}`)
  }

  return await observationsForUserId
    .limit(limit)
    .offset(offset)
    .all({
      name: search?.toLowerCase() || ''
    })
}

export const getObservationsByUserId = async (
  db: LibSQLDatabase,
  uid: string,
  search?: string,
  page?: Pagination
): Promise<Observation[]> => {
  const { limit, offset } = page || { limit: 10, offset: 0 }

  const observationsForUserId = db.select().from(observations).where(eq(observations.owner, uid))

  if (typeof search === 'string' && search.length >= 1) {
    console.log(search)
    observationsForUserId.where(sql`lower(${observations.name}) like ${placeholder('name')}`)
  }

  return await observationsForUserId
    .limit(limit)
    .offset(offset)
    .all({
      name: search?.toLowerCase() || ''
    })
}

Which is a similar way to do the ilike ...

Andrii Sherman•2y ago

Will check that

MichaelOP•2y ago

Thank you 🙏

Gaming

Programming

SQLITE_ERROR: near "ilike": syntax error