How to use Lucid ORM to find a record by case-insensitive match?

How to use Lucid ORM to find a record by case-insensitive match?

What is your DB management system ?
For example with MySQL, it is case-insensitive by default, so you have no extra work for your queries.

PostgreSQL

Well, i created my own approach using a PostgreSQL functions, then, i call them in my code with Database.raw

Like this:

await Database
  .raw('select * from users where username = ?', [username])

But, instead of the select, i call my function.

There’s a unaccent extension in PSQL too, you can use it for searches without accents:

await Database.raw(`CREATE EXTENSION IF NOT EXISTS unaccent;`)

Maybe the unaccent option is great only for me, my Db main language is pt-br, we have a lot of accents in our grammar.

For cleaning the search string, i use the following function:

await Database.raw(`
    CREATE OR REPLACE FUNCTION clean_some(text)
    RETURNS text AS $$
    SELECT replace(replace($1, ' ',''),'-','')
    $$ LANGUAGE sql;`)

You can combine them, to have a good search. I use both for different cases…

I put all these functions inside migrations/seeds folder, so, they run in every app deploy.

Useful links:


https://alvinalexander.com/sql/sql-select-case-insensitive-query-queries-upper-lower

1 Like