[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

[magbicaleman]

I've encountered significant challenges with SQLITE_CONSTRAINT errors when performing migrations that involve tables with foreign key relationships. After much debugging, I've discovered that Drizzle's current approach to handling SQLite migrations with foreign key constraints requires a specific multi-step process to avoid these errors.

When modifying tables with foreign key relationships in SQLite using Drizzle, you may encounter SQLITE_CONSTRAINT errors. This happens because Drizzle's table recreation process (create new → copy data → drop old) doesn't handle foreign key dependencies automatically.

Solution: Use this multi step process:

Remove all .references() calls from schemas with foreign key relationships to the target table
Generate a migration to recreate tables without foreign keys
Update the table you're making changes to
Generate migration
Re-add .references() to schemas and generate a migration to restore foreign keys

Example:

// Step 1: Remove .references()
const posts = sqliteTable('posts', {
  userId: integer('user_id'),
  //.references(() => users.id), // removed .references(() => users.id)
});

// Step 2: Run drizzle kit migration 

// Step 3: Make updates to your intended table, for this example the users table

// Step 4: Run drizzle kit migration 

// Step 5: Re-add FK
const posts = sqliteTable('posts', {
  userId: integer('user_id').references(() => users.id)
});

You'll have to do this to all the schema files that are referencing the table you are trying to change that causes a recreation table in the migration.

I'm not sure if this issue would help or if it's even related at all to the above:

• [FEATURE]: sqlite: deferrable foreign key relations #2252

[kleinpetr]

Thanks, that's exactly what I mean, it would be nice if anyone from the Drizzle team could take a look on it, if it's just improvement or there are any blocker to keep foreign keys while updating tables with references.

[AndriiSherman]

every migration should enable and disable PRAGMA for such queries, looking into your code snippet, I can't find PRAGMA statements. Was it generated and you just didn't copy it or drizzle-kit didn't generate pragma statements?

[AndriiSherman]

do you use d1-http driver for that?

[AndriiSherman]

Now I see the issue. I guess there's nothing Drizzle can do for now, but I'll contact the D1 team so they can expose a batch function—similar to what Turso did for us—so we can enable and disable PRAGMA through HTTP