DT
Drizzle Team•5d 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
});
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`;
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
    };
  }
);
 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
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",
    "drizzle-kit": "^0.31.0",
    "drizzle-orm": "^0.43.1",
    "drizzle-zod": "^0.7.1",
    "mysql2": "^3.14.0",
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?