[BUG]: Re arrange table migration

[falentio]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

How to re arrange table migration on drizzle push command?

the migrations looks like

INSERT INTO "examination" SELECT * FROM "__old_push_examination";
DROP TABLE `__old_push_examination`;
ALTER TABLE `examination_participant` RENAME TO `__old_push_examination_participant`;
CREATE TABLE `examination_participant` (
        `examination_id` text NOT NULL,
        `user_id` text NOT NULL,
        `started_at` integer DEFAULT 0 NOT NULL,
        PRIMARY KEY(`examination_id`, `user_id`),
        FOREIGN KEY (`examination_id`) REFERENCES `examination`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade
);

....

INSERT INTO "school" SELECT * FROM "__old_push_school";
DROP TABLE `__old_push_school`;
CREATE UNIQUE INDEX `school_name_unique` ON `school` (`name`);
ALTER TABLE `school_member` RENAME TO `__old_push_school_member`;
CREATE TABLE `school_member` (
        `user_id` text NOT NULL,
        `school_id` text NOT NULL,
        `role` text NOT NULL,
        PRIMARY KEY(`school_id`, `user_id`),
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`school_id`) REFERENCES `school`(`id`) ON UPDATE no action ON DELETE cascade
);

so the table schema become like this, and it will error whenever insertion into examination table

does my drizzle schema deffinitions are wrong so the sql statement is broken, or this is a bug?

Expected behavior

the examination table migration must be after school table

Environment & setup

No response

[falentio]

schema.ts

```ts import { relations, sql } from "drizzle-orm"; import { blob, int, primaryKey, sqliteTable, text } from "drizzle-orm/sqlite-core"; export const user = sqliteTable("user", { id: text("id").primaryKey(), }); export type User = typeof user.$inferSelect; export type InsertUser = typeof user.$inferInsert; export const userRelations = relations(user, ({ many }) => ({ schools: many(school), members: many(schoolMember), })); export const session = sqliteTable("session", { id: text("id").primaryKey(), activeExpress: blob("active_express", { mode: "bigint" }).notNull(), idleExpress: blob("idle_express", { mode: "bigint" }).notNull(), userId: text("user_id").notNull().references(() => user.id), }); export type Session = typeof session.$inferSelect; export type InsertSession = typeof session.$inferInsert; export const userKey = sqliteTable("user_key", { id: text("id").primaryKey(), userId: text("user_id").notNull().references(() => user.id), hashedPassword: text("hashed_password"), }); export type UserKey = typeof userKey.$inferSelect; export type InsertUserKey = typeof userKey.$inferInsert; export const school = sqliteTable("school", { id: text("id").primaryKey(), name: text("name", { length: 64 }).unique().notNull(), displayName: text("display_name", { length: 64 }).notNull(), createdAt: int("created_at", { mode: "timestamp" }).notNull().default(sql`(UNIXEPOCH())`), ownerId: text("owner").references(() => user.id, { onDelete: "set null" }), }); export type School = typeof school.$inferSelect; export type InsertSchool = typeof school.$inferInsert & { ownerId: string }; export const schoolRelations = relations(school, ({ one, many }) => ({ owner: one(user, { fields: [school.ownerId], references: [user.id], }), members: many(schoolMember), examinations: many(examination), })); export const schoolMember = sqliteTable("school_member", { userId: text("user_id").notNull().references(() => user.id, { onDelete: "cascade" }), schoolId: text("school_id").notNull().references(() => school.id, { onDelete: "cascade" }), role: text("role").$type<"owner" | "admin" | "student">().notNull(), }, (t) => ({ pk: primaryKey(t.userId, t.schoolId), })); export type SchoolMember = typeof schoolMember.$inferSelect; export type InsertSchoolMember = typeof schoolMember.$inferInsert; export const schoolMemberRelations = relations(schoolMember, ({ one }) => ({ school: one(school, { fields: [schoolMember.schoolId], references: [school.id], }), user: one(user, { fields: [schoolMember.userId], references: [user.id], }), })); export const examination = sqliteTable("examination", { id: text("id").primaryKey().notNull(), name: text("name", { length: 64 }).notNull(), createdAt: int("created_at", { mode: "timestamp" }).notNull().default(sql`(UNIXEPOCH())`), duration: int("duration").notNull(), questionsCount: int("questions_count").notNull(), startAt: int("started_at", { mode: "timestamp" }).notNull(), schoolId: text("school_id").notNull().references(() => school.id, { onDelete: "cascade" }), }); export const examinationRelations = relations(examination, ({ many, one }) => ({ participants: many(examinationParticipant), questions: many(question), school: one(school, { fields: [examination.schoolId], references: [school.id], }), })); export type Examination = typeof examination.$inferSelect; export type InsertExamination = typeof examination.$inferInsert & { schoolId: string }; export const examinationParticipant = sqliteTable("examination_participant", { examinationId: text("examination_id").references(() => examination.id, { onDelete: "cascade" }).notNull(), userId: text("user_id").references(() => user.id, { onDelete: "cascade" }).notNull(), startedAt: int("started_at", { mode: "timestamp" }).notNull().default(sql`0`), }, (examinationParticipant) => ({ pk: primaryKey( examinationParticipant.examinationId, examinationParticipant.userId, ), })); export const examinationParticipantRelations = relations(examinationParticipant, ({ one }) => ({ examination: one(examination, { fields: [examinationParticipant.examinationId], references: [examination.id], }), })); export type ExaminationParticipant = typeof examinationParticipant.$inferSelect; export type InsertExaminationParticipant = typeof examinationParticipant.$inferInsert; export const question = sqliteTable("question", { id: text("id").primaryKey().notNull(), examinationId: text("examinationId").references(() => examination.id), createdAt: int("created_at", { mode: "timestamp" }).notNull().default(sql`(UNIXEPOCH())`), content: text("content", { length: 2096 }).notNull(), type: text("type").notNull(), data: text("data", { mode: "json" }).notNull().$type>().$defaultFn(() => ({})), }); export type Question = typeof question.$inferSelect; export type InsertQuestion = typeof question.$inferInsert; export const questionRelations = relations(question, ({ many, one }) => ({ answers: many(answer), examination: one(examination, { references: [examination.id], fields: [question.examinationId], }), })); export const answer = sqliteTable("answer", { id: text("id").primaryKey().notNull(), questionId: text("question_id").notNull().references(() => question.id, { onDelete: "cascade" }), createdAt: int("created_at", { mode: "timestamp" }).notNull().default(sql`(UNIXEPOCH())`), content: text("content", { length: 2096 }).notNull(), data: text("data", { mode: "json" }).notNull().$type>().$defaultFn(() => ({})), correct: int("correct", { mode: "boolean" }).notNull(), }); export type Answer = typeof answer.$inferSelect; export type InsertAnswer = typeof answer.$inferInsert; export const answerRelations = relations(answer, ({ one }) => ({ question: one(question, { fields: [answer.questionId], references: [question.id], }), })); ```