Drizzle Team•14mo ago

How to create unique lowercase index?

I'm trying to recreate the following sql statement. I don't think what I'm writing with the where clause is correct. But writing .on(sqllower(domain)) results in a type error. Is there a way to do it in drizzle currently?

create unique index sso_domains_domain_idx on auth.sso_domains using btree (lower(domain)) tablespace pg_default;

create unique index sso_domains_domain_idx on auth.sso_domains using btree (lower(domain)) tablespace pg_default;

export const ssoDomains = authSchema.table(
  "sso_domains",
    {
      domain: text("domain").notNull(),
    },
    table => ({
      ssoDomainsDomainIdx: uniqueIndex("sso_domains_domain_idx").on(table.domain).where(sql`lower(${table.domain.name})`),
    })
);

export const ssoDomains = authSchema.table(
  "sso_domains",
    {
      domain: text("domain").notNull(),
    },
    table => ({
      ssoDomainsDomainIdx: uniqueIndex("sso_domains_domain_idx").on(table.domain).where(sql`lower(${table.domain.name})`),
    })
);

2 Replies

PaulOP•14mo ago

As a side note, how can I use sql when declaring the schema to run a check against the column (in postgres)? I note that the check function isn't implemented yet in drizzle-orm as per this. Just to confirm, this isn't possible via the sql operator either? Also, is it possible to create a trigger using the

sql``

sql``

feature when defining the schema? tldr; is it possible to use the sql`` command to create checks and triggers when defining the schema?

Indexes & Constraints - DrizzleORM

Drizzle ORM | %s

Angelelz•14mo ago

I don't think you can currently do this with drizzle. I think you'll have to create a migration and add your SQL manually there For a check in column you can make it work with customTypes

Gaming

Programming

How to create unique lowercase index?