Drizzle kit generates incorrect migrations for tables with indexes and foreign key constraints within a specific schema in MySQL. The migrations are generated without fully specifying the table names with their respective schema names for indexes and constraints. This leads to errors when the migrations are applied to the database.
CREATE DATABASE `custom_schema`;
--> statement-breakpoint
CREATE TABLE `custom_schema`.`posts` (
`id` serial AUTO_INCREMENT NOT NULL,
`title` text NOT NULL,
`content` text NOT NULL,
`user_id` varchar(255) NOT NULL,
CONSTRAINT `posts_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `custom_schema`.`users` (
`id` varchar(255) NOT NULL,
`name` text NOT NULL,
`email` text NOT NULL,
CONSTRAINT `users_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE INDEX `name_index` ON `users` (`name`);--> statement-breakpoint
ALTER TABLE `posts` ADD CONSTRAINT `posts_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;
The index is created on users instead of custom_schema.users, and the foreign key constraint references posts instead of custom_schema.posts. This causes the migration to fail with an error indicating that the table does not exist, as it attempts to reference tables in the default database schema instead of custom_schema.
// mysqltest is my default database
Error: Table 'mysqltest.users' doesn't exist
Expected behavior
Drizzle kit should generate migrations with schema names in references for indexes and constraints, ensuring operations are scoped to the correct schema/database.
CREATE INDEX `name_index` ON `custom_schema`.`users` (`name`); --> statement-breakpoint
ALTER TABLE `custom_schema`.`posts` ADD CONSTRAINT `posts_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `custom_schema`.`users`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
What version of
drizzle-orm
are you using?0.30.7
What version of
drizzle-kit
are you using?0.20.14
Describe the Bug
Drizzle kit generates incorrect migrations for tables with indexes and foreign key constraints within a specific schema in MySQL. The migrations are generated without fully specifying the table names with their respective schema names for indexes and constraints. This leads to errors when the migrations are applied to the database.
MySQL tables:
generates this SQL:
The index is created on
users
instead ofcustom_schema.users
, and the foreign key constraint referencesposts
instead ofcustom_schema.posts
. This causes the migration to fail with an error indicating that the table does not exist, as it attempts to reference tables in the default database schema instead ofcustom_schema
.Expected behavior
Drizzle kit should generate migrations with schema names in references for indexes and constraints, ensuring operations are scoped to the correct schema/database.
Environment & setup
No response