SQL within a single migration is generated out of order
Case 1:
I have two tablesexport 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-breakpointALTER 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-breakpointThis 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",The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources
Was this page helpful?
