Drizzle TeamDT
Drizzle Team•13mo ago
alex (he/him)

How to set opclass for index with custom sql column

we do have following definitions, after we used drizzle-kit pull

uniqueIndex("sap_id_upper_trim_idx").using("btree", sql`upper(TRIM(BOTH FROM sap_id))`),


Now the issue is that, drizzle-kit generate thinks it needs to drop the index and recreates it, because it doesn't understand that the opclass is optional.

This is the initial state from introspection
CREATE UNIQUE INDEX "sap_id_upper_trim_idx" ON "companies" USING btree (upper(TRIM(BOTH FROM sap_id)) text_ops);--> statement-breakpoint


This is what drizzle generates as a migration
DROP INDEX "sap_id_upper_trim_idx";--> statement-breakpoint
CREATE UNIQUE INDEX "sap_id_upper_trim_idx" ON "companies" USING btree (upper(TRIM(BOTH FROM sap_id)));--> statement-breakpoint


I know for other columns we can use .op('text_ops'), but that doesn't work for the custom index column with 
sql
?
Was this page helpful?