Drizzle TeamDT
Drizzle Team•8mo ago
adiSuper94

SQL within a single migration is generated out of order

Case 1:

I have two tables
export const survey = mysqlTable("survey", {
  id: varchar("id", { length: 64 })
    .$defaultFn(() => crypto.randomUUID())
    .primaryKey(),
    // more columns
});

export const surveyResponse = mysqlTable("survey_response", {
  id: varchar("id", { length: 64 })
    .$defaultFn(() => crypto.randomUUID())
    .primaryKey(),
  surveyId: varchar("survey_id", { length: 64 }).references(() => survey.id),
  responseId: varchar("response_id", { length: 64 }).unique().notNull(),
  //morr columns
});


When I delete both of them from my schema.js
the migration that is created is 
DROP TABLE `survey`;--> statement-breakpoint
DROP TABLE `survey_response`;

They are out of order.

Case 2

 export const authors = mysqlTable(
  "authors",
  {
    publicationId: varchar("publication_id", { length: 64 }).references(() => publications.id),
    authorID: varchar("author_id", { length: 10 }).references(() => faculty.id),
    orcidId: varchar("orcid_id", { length: 64 }).references(() => t2.orcidId), //Added new column
  },
  (table) => {
    return {
      pk: primaryKey(table.publicationId, table.authorUFID), // removed line
      pk: primaryKey({ columns: [table.publicationId, table.authorID, table.orcidId], name: "authors_pk" }), // Added line
    };
  }
);


Generated sql migration
ALTER TABLE `authors` DROP PRIMARY KEY;--> statement-breakpoint
ALTER TABLE `authors` ADD PRIMARY KEY(`publication_id`,`author_ufid`,`orcid_id`);--> statement-breakpoint
ALTER TABLE `authors` ADD `orcid_id` varchar(64) NOT NULL;--> statement-breakpoint


This is soo out of order.
new column should be created before adding it as FK.
old fk need to dropped before dropping pk, and should be recreated after the new old pk is dropped.
Can anyone take a look at this??

v: 
    "drizzle-kit": "^0.31.0",
    "drizzle-orm": "^0.43.1",
    "drizzle-zod": "^0.7.1",
    "mysql2": "^3.14.0",
Was this page helpful?