Drizzle Team•13mo ago

schema unique case insensitive

Suppose I have a table Clients and I want the name to be unique. But, I want it to be case insensivive, normally I would use COLLATE NOCASE, but I couldn't find how to do that in drizzle. Would love some help please. Using sqlite.

7 Replies

MAST•13mo ago

You can use something like this for selecting:

db.select().from(ClientsTable).where(sql`${ClientsTable.name} = 'my name' COLLATE NOCASE`);

db.select().from(ClientsTable).where(sql`${ClientsTable.name} = 'my name' COLLATE NOCASE`);

I think this might work as well:

  const users = await db
    .select()
    .from(UsersTable)
    .where(eq(UsersTable.name, "my name").append(sql` COLLATE NOCASE`));

  const users = await db
    .select()
    .from(UsersTable)
    .where(eq(UsersTable.name, "my name").append(sql` COLLATE NOCASE`));

For testing and debugging you can pass logger: true to drizzle.

export const db = drizzle(queryClient, { schema, logger: true });

export const db = drizzle(queryClient, { schema, logger: true });

For creating the table you can define the tables and the generate a migration. Then you can edit the migration to add the COLLATE NOCASE so that the migration will create it correctly.

ar7casperOP•13mo ago

But I'm not selecting, I'm creating a schema and I want to use the tools sql gives me. My question is how

Angelelz•13mo ago

Drizzle doesn't support this natively. You could hack it by passing a sql argument to the default and all of that at the end On your column definition:

name: varchar('text', {...}).default(sql`'someDefaultOrNot' collate nocase`)

name: varchar('text', {...}).default(sql`'someDefaultOrNot' collate nocase`)

You might need to do some testing to see if that gives you what you want, I haven't test it

MAST•13mo ago

You can edit the generated migration sql if you want to create tables with collate nocase.

ar7casperOP•13mo ago

It worked man! Thanks!

samson•10mo ago

@Angelelz Is there a way to make every "string" column in a table behave case-insensitive on any unique check, other than appending each column with .default(sql 'collate nocase') ?

samson•10mo ago

nevermind, found the answer here: https://github.com/drizzle-team/drizzle-orm/discussions/123

GitHub

Does it support postgres extensions? · drizzle-team drizzle-orm · D...

I am most interested in CITEXT and POSTGIS. Is there a way to work with those?

Gaming

Programming

schema unique case insensitive