Creating a Case-Insensitive Unique Index

I want to be able to select a user by their username but have usernames be case insensitive:

const user = await db.select()
  .from(users)
  .where(eq(sql`lower(${usersTable.username})`, sql`lower(${params.username})`))
  .then((result) => result[0]);

const user = await db.select()
  .from(users)
  .where(eq(sql`lower(${usersTable.username})`, sql`lower(${params.username})`))
  .then((result) => result[0]);

If I setup a users table like this:

export const users = pgTable("users", {
  username: varchar("username", { length: 30 })
    .notNull()
    .unique("users_username_unique", { nulls: "not distinct" }),
  //...
});

export const users = pgTable("users", {
  username: varchar("username", { length: 30 })
    .notNull()
    .unique("users_username_unique", { nulls: "not distinct" }),
  //...
});

It will create in an index definition:

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (username) NULLS NOT DISTINCT;

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (username) NULLS NOT DISTINCT;

Is there a way to create a lowercase index:

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (LOWER(username)) NULLS NOT DISTINCT

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (LOWER(username)) NULLS NOT DISTINCT

Or is there another way to achieve a lowercase equality check on a unique index?

6 Replies

kratious•14mo ago

You're adding a unique constraint which doesn't have the flexibility of lowercasing Could try it with this https://orm.drizzle.team/docs/indexes-constraints#indexes

Indexes & Constraints - DrizzleORM

Drizzle ORM | %s

saM69420OP•14mo ago

I can create a blank migration and add the constraint myself

drizzle-kit generate:pg --custom

drizzle-kit generate:pg --custom

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (LOWER(username)) NULLS NOT DISTINCT;

CREATE UNIQUE INDEX users_username_unique ON public.users USING btree (LOWER(username)) NULLS NOT DISTINCT;

It works exactly as I expect: "sam", "Sam", and "saM" all have the same index. I can add this constraint in drizzle, but drizzle-kit ignores where and using:

export const users = pgTable("users", {
  username: varchar("username", { length: 30 }).notNull(),
  //...
}, (table) => {
  return {
    usernameIdx: uniqueIndex("username_unique_idx").on(table.username).using(sql`btree (LOWER(username))`).where(sql` NULLS NOT DISTINCT`),
  };
});

export const users = pgTable("users", {
  username: varchar("username", { length: 30 }).notNull(),
  //...
}, (table) => {
  return {
    usernameIdx: uniqueIndex("username_unique_idx").on(table.username).using(sql`btree (LOWER(username))`).where(sql` NULLS NOT DISTINCT`),
  };
});

So that only makes a normal unique constraint. I'm curious to know if my approach of creating a case-insensitive unique Index is a good one. And if this is, or will be, possible using drizzle. I honestly don't mind making custom migrations for cases like this and adding check constraints -- but when my dev flow involves prototyping and pushing...

kratious•14mo ago

Ah OK, then that's a bug with drizzle-kit I think the indexing approach is perfectly reasonable as long as you follow this

The database can use a function-based index if the exact expression of the index definition appears in an SQL statement

See: https://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search

Case-Insensitive Search in SQL

Using UPPER or LOWER for case-insensitive search is not a good practice. Prefer using a _CI collation if possible or at least add the right indexes to make your query fast.

WakaTime•12mo ago

also check out the citext extension for postgres (install it on your database) then use this answer to create a custom citext column type: https://www.answeroverflow.com/m/1101376236267253841

WakaTime•12mo ago

https://www.postgresql.org/docs/current/citext.html

PostgreSQL Documentation

F.10. citext — a case-insensitive character string type

F.10. citext — a case-insensitive character string type # F.10.1. Rationale F.10.2. How to Use It F.10.3. String Comparison Behavior F.10.4. …

Angelelz•12mo ago

I would just collate nocase That column

Gaming

Programming

Creating a Case-Insensitive Unique Index