Yinnv Posts - Answer Overflow

Yinnv

•Created by Yinnv on 3/15/2024 in #help

"drizzle-kit push" repeatedly drops and then recreates the same index

I am using drizzle-kit push for modifying db schema to Supabase. One table, product_tag, is a join table for the many-to-many relationship between table product and table tag. The product_tag table has two indices to support corresponding searching scenarios. Below is the table definition for product_tag table:

export const ProductTag = pgTable(
  "product_tag",
  {
    id: bigserial("id", { mode: "number" }),
    product_id: integer("product_id").notNull(),
    tag_id: integer("tag_id").notNull()
  },
  (table) => ({
    product_tag_tag_id_product_id_idx: index(
      "product_tag_tag_id_product_id_idx"
    ).on(table.tag_id, table.product_id),
    product_tag_product_id_tag_id_idx: index(
      "product_tag_product_id_tag_id_idx"
    ).on(table.product_id, table.tag_id),
  })
);

export const ProductTagRelations = relations(ProductTag, ({ many, one }) => ({
  product: one(Product, {
    fields: [ProductTag.product_id],
    references: [Product.id],
  }),
  tag: one(Tag, {
    fields: [ProductTag.tag_id],
    references: [Tag.slug],
  }),
}));

export const ProductTag = pgTable(
  "product_tag",
  {
    id: bigserial("id", { mode: "number" }),
    product_id: integer("product_id").notNull(),
    tag_id: integer("tag_id").notNull()
  },
  (table) => ({
    product_tag_tag_id_product_id_idx: index(
      "product_tag_tag_id_product_id_idx"
    ).on(table.tag_id, table.product_id),
    product_tag_product_id_tag_id_idx: index(
      "product_tag_product_id_tag_id_idx"
    ).on(table.product_id, table.tag_id),
  })
);

export const ProductTagRelations = relations(ProductTag, ({ many, one }) => ({
  product: one(Product, {
    fields: [ProductTag.product_id],
    references: [Product.id],
  }),
  tag: one(Tag, {
    fields: [ProductTag.tag_id],
    references: [Tag.slug],
  }),
}));

Running npx drizzle-kit push:pg would successfully create the product_tag table with its two indices. However, whenever I run the push command from now on, drizzle-kit would always attempt dropping one of the index and recreate it again.

> npx drizzle-kit push:pg
drizzle-kit: v0.20.14
drizzle-orm: v0.29.5

No config path provided, using default path
Reading config file 'C:\Users\ASUS\Documents\...\drizzle.config.ts'

 Warning  You are about to execute current statements:

DROP INDEX IF EXISTS "product_tag_tag_id_product_id_idx";
CREATE INDEX IF NOT EXISTS "product_tag_tag_id_product_id_idx" ON "product_tag" ("tag_id","product_id");

> npx drizzle-kit push:pg
drizzle-kit: v0.20.14
drizzle-orm: v0.29.5

No config path provided, using default path
Reading config file 'C:\Users\ASUS\Documents\...\drizzle.config.ts'

 Warning  You are about to execute current statements:

DROP INDEX IF EXISTS "product_tag_tag_id_product_id_idx";
CREATE INDEX IF NOT EXISTS "product_tag_tag_id_product_id_idx" ON "product_tag" ("tag_id","product_id");

BTW, similar issues could also happen if I create a composite primary key while using drizzle kit with PlanetScale. push would attempt to drop the composite primary key and recreate it every time. Is there a workaround for this behavior?

5 replies

Gaming

Programming