adiSuper94 Posts - Answer Overflow

adiSuper94

•Created by adiSuper94 on 4/25/2025 in #help

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",

1 replies

DTDrizzle Team

•Created by adiSuper94 on 1/31/2024 in #help

Float values get casted to int while reading from DB

When reading float value from db ,it gets casted into integer. These values were inserted into the mysql db using drizzle. drizzle orm: 0.28.6

13 replies

Gaming

Programming