Drizzle Team•17mo ago

Bug: drizzle-kit generate:pg didn't see the changes in the schema

On my uniqueIndex i added a where clause to not check the soft delete records. When running generate, the drizzle-kit didn't generate any migration since it thinks i haven't modified the schema.

8 Replies

ale3xdyoOP•17mo ago

After I deleted the migrations folder and rerun the generate function, the migration was not correctly generated. The where clause was missing. CREATE UNIQUE INDEX IF NOT EXISTS "sku_workspace_products_itemsuniquekey" ON "products_items" ("sku","workspace_id");--> statement-breakpoint When adding it manually in the database as: create unique index sku_workspace_products_itemsuniquekey on public.products_items using btree (sku, workspace_id) where deleted_at IS NULL; it works like a charm. So it seems that it's not seeing the where clause to generate the schema from. Am I missing something?

Asseater Peter•17mo ago

Can you confirm that it's 100% seeing this file?

ale3xdyoOP•17mo ago

yea, I just removed the migration folder again after your message and ran drizzle-kit generate:pg and it just created the same unique index without the where clause Also it's the only schema i have declared in my project

Asseater Peter•17mo ago

Could you share the whole table declaration + schema file if you have one? I haven't personally had this issue before, but something is bound to jump out at me

ale3xdyoOP•17mo ago

export const productsItems = pgTable(
  "products_items",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    variantName: text("variant_name"),
    productId: uuid("product_id")
      .notNull()
    sku: text("sku").notNull(),
    barcode: text("barcode"),
    imageId: uuid("image_id")
    deletedAt: timestamp("deleted_at", { mode: "date" }),
    workspaceId: uuid("workspace_id")
      .notNull()
  },
  (table) => ({
    skuKey: uniqueIndex("sku_workspace_products_itemsuniquekey")
      .on(table.sku, table.workspaceId)
      .where(isNull(table.deletedAt)),
  })
);

export const productsItems = pgTable(
  "products_items",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    variantName: text("variant_name"),
    productId: uuid("product_id")
      .notNull()
    sku: text("sku").notNull(),
    barcode: text("barcode"),
    imageId: uuid("image_id")
    deletedAt: timestamp("deleted_at", { mode: "date" }),
    workspaceId: uuid("workspace_id")
      .notNull()
  },
  (table) => ({
    skuKey: uniqueIndex("sku_workspace_products_itemsuniquekey")
      .on(table.sku, table.workspaceId)
      .where(isNull(table.deletedAt)),
  })
);

Also i did some research and I saw this opened issue on github for this problem https://github.com/drizzle-team/drizzle-kit-mirror/issues/47

GitHub

[BUG]: uniqueIndex not respecting WHERE clause in postgres · Issue ...

What version of drizzle-orm are you using? 0.25.4 What version of drizzle-kit are you using? 0.17.6 Describe the Bug in my schema, I have: indexWithWhere: uniqueIndex("index_with_where") ...

Asseater Peter•17mo ago

Well, seems like it's not supported right now, not sure what else I can do here tbh

Andrii Sherman•17mo ago

Not supported in drizzle-kit right now, but will work on that soon Have a lot of things to finish, so sorry for delays on some feature requests we have

ford prefecture•10mo ago

Is there a way around this for now? @Andrew Sherman

Gaming

Programming

Bug: drizzle-kit generate:pg didn't see the changes in the schema