Change the data type of some varchar and text columns to be citext

[L-Mario564]

Why?

citext is a Postgres extension that stores strings in a similar fashion to text 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 user Mario564 pops up, instead of having to search for Mario564. 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 the tournament table, which means there can't be two tournaments named osu! World Cup 2023, but if someone were to create one named osu! world cup 2023 then it would be allowed since the casing for W and C are different, but using the citext data type makes it so the latter string violates the unique constraint as citext 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:

• OsuUser: username.
• DiscordUser: username.
• Tournament: name.
• Round: name.
• StaffRole: name.

Citext Postgres docs: https://www.postgresql.org/docs/current/citext.html
Drizzle custom types docs: https://orm.drizzle.team/docs/custom-types

[ArtemOsuskyi]

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

[L-Mario564]

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.

[ArtemOsuskyi]

In that case we'll have to drop length constraints from mentioned varchar fields, because citext - just like text - doesn't accept any params