Closed L-Mario564 closed 3 months ago
According to some Drizzle docs I've found, we can't currently apply extension types for Drizzle, so sticking to the good old ILIKE seems to be the best approach for now
The extension can be applied to the DB by inserting create extension if not exists citext;
in a migration file, and then creating the custom type as listed on their website (https://orm.drizzle.team/docs/custom-types). I believe the docs you listed are one's that are outdated and they website are the (mostly) up to date docs.
In that case we'll have to drop length constraints from mentioned varchar
fields, because citext
- just like text
- doesn't accept any params
Why?
citext
is a Postgres extension that stores strings in a similar fashion totext
but when comparing values, it's case insensitive. This could be useful on some fields for either searching capabilities or to avoid strings that only have one letter changed.For searching capabilities, this would make it so for example, searching for
mario564
will make it so the userMario564
pops up, instead of having to search forMario564
. This is already possible using Postgres' ilike operator but I think it would simplify things a little bit more.The more important case is the second one. Putting another example, right now, there's a unique constraint on the
name
field on thetournament
table, which means there can't be two tournaments namedosu! World Cup 2023
, but if someone were to create one namedosu! world cup 2023
then it would be allowed since the casing for W and C are different, but using thecitext
data type makes it so the latter string violates the unique constraint ascitext
checks a string's uniqueness case insensitively.How?
We'd need to create a custom migration to add the extension to the database and then create a custom data type with Drizzle to maintain typesafety.
The affected tables and columns would be:
Citext Postgres docs: https://www.postgresql.org/docs/current/citext.html Drizzle custom types docs: https://orm.drizzle.team/docs/custom-types