[BUG]: the order of columns is causing redeclaration of UNIQUE constraint when using "drizzle-kit push"

[Chenalejandro]

What version of drizzle-orm are you using?

0.33.0

What version of drizzle-kit are you using?

0.24.2

Describe the Bug

EDIT: I'm using postgresql

This code will cause drizzle-kit push to drop and redeclare the unique constraint (assuming that whe already run the first drizzle-kit push to add the table to the db):

export const bug = createTable(
  "bug",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    uniqueValue2: integer("unique_value_2").notNull(),
    uniqueValue1: integer("unique_value_1").notNull(),
  },
  (table) => ({
    uniqueValues: unique("unique_values").on(
      table.uniqueValue1,
      table.uniqueValue2,
    ),
  }),
);

This is the output of "drizzle-kit push --verbose"

[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

ALTER TABLE "bug" DROP CONSTRAINT "unique_values";
ALTER TABLE "bug" ADD CONSTRAINT "unique_values" UNIQUE("unique_value_1","unique_value_2");

[✓] Changes applied

Expected behavior

As soon as the order is fixed (declaring uniqueValue2 after uniqueValue1), the error is gone:

export const bug = createTable(
  "bug",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    uniqueValue1: integer("unique_value_1").notNull(),
    uniqueValue2: integer("unique_value_2").notNull(),
  },
  (table) => ({
    uniqueValues: unique("unique_values").on(
      table.uniqueValue1,
      table.uniqueValue2,
    ),
  }),
);

I have been dealing with this issue a long time ago. I couldn't find the root cause until today hahah.

From my testing using migration files (drizzle-kit generate && drizzle-kit migrate) will not cause this bug.

Environment & setup

No response

[madsh93]

Seems to be the same issue as this: https://github.com/drizzle-team/drizzle-orm/issues/2599

[JavanFriedel]

I also faced this issue with a Postgres DB but couldn't figure out your solution on my own, so thank you for this!

What made this bug worse was that in some instances, I naturally declared my unique keys in order, and I couldn't discern why some tables were working and not others.

I do not believe this is the same issue as #2599 , as they are declaring a unique key on a single column instead of a composite unique key as in this case. Ultimately, it is the failure to discern the ordering of keys that does not match the order of declaration.