saM69420 Comments - Answer Overflow

saM69420

Explore posts from servers

•Created by saM69420 on 10/5/2023 in #help

Creating a Case-Insensitive Unique Index

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...

8 replies

DTDrizzle Team

•Created by Jökull Sólberg on 5/25/2023 in #help

Simulate enums with SQLite `CHECK()`

sqlite has an enum option on the text type that does everything except adding the CHECK constraint. it's in the old docs just not the new ones https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/sqlite-core/README.md#column-types but no runtime checks, but it plays nice with drizzle-zod

12 replies

Gaming

Programming