[BUG]: Migrations do not take into account sessions to other tables.

[kleinpetr]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.36.4

What version of drizzle-kit are you using?

0.28.1

Other packages

No response

Describe the Bug

Generated migrations always creates a _new_ tables and trying to migrate data and drop tables after that. In my case I have very simple structure of users and accounts, account has userId which is relation to users. But when there are data and I am trying to add a new column, which doesn't have an impact to data integrity, so could someone explain how is the migration resolved?

I've just moved 'hashed_password' from users to accounts which is nullable, but this is how the migration looks like

ALTER TABLE `__new_sessions` RENAME TO `sessions`;--> statement-breakpoint
CREATE TABLE `__new_users` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text NOT NULL,
	`created_at` integer DEFAULT '"2024-12-08T19:30:40.723Z"' NOT NULL,
	`updated_at` integer DEFAULT '"2024-12-08T19:30:40.723Z"' NOT NULL,
	`is_active` integer DEFAULT true NOT NULL,
	`avatar` text,
	`name` text
);
--> statement-breakpoint
INSERT INTO `__new_users`("id", "email", "created_at", "updated_at", "is_active", "avatar", "name") SELECT "id", "email", "created_at", "updated_at", "is_active", "avatar", "name" FROM `users`;--> statement-breakpoint
DROP TABLE `users`;--> statement-breakpoint
ALTER TABLE `__new_users` RENAME TO `users`;--> statement-breakpoint
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);--> statement-breakpoint
ALTER TABLE `accounts` ADD `hashed_password` text;

So because there is DROP of users table I am getting

D1_ERROR: FOREIGN KEY constraint failed: SQLITE_CONSTRAINT

It seems that the PRAGMA is enabled to early, but when I move it to the end it also doesn't work and raise error with FOREIGN KEY..

Thanks for clarification