[BUG]:push creates duplicate statements for unique column index

[jmyt8]

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.3

What version of drizzle-kit are you using?

0.28.1

Other packages

No response

Describe the Bug

When using drizzle-kit push to update existing table, statements for creating unique index appear twice, leading to SqliteError: index user_email_unique already exists.

CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);
CREATE UNIQUE INDEX `user_username_unique` ON `user` (`username`);
PRAGMA foreign_keys=ON;
CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);
CREATE UNIQUE INDEX `user_username_unique` ON `user` (`username`);

Table schema

export const user = sqliteTable('user', {
	id: text('id').primaryKey().notNull(),
	googleId: text('google_id'),
	email: text('email').notNull().unique(),
	username: text('username').unique(),
	...
});

SQL statement

PRAGMA foreign_keys=OFF;
CREATE TABLE `__new_user` (
        `id` text PRIMARY KEY NOT NULL,
        `google_id` text,
        `email` text NOT NULL,
        `username` text,
        ...
);

INSERT INTO `__new_user`("id", "google_id", "email", "username", ...) SELECT "id", "google_id", "email", "username", ... FROM `user`;
DROP TABLE `user`;
ALTER TABLE `__new_user` RENAME TO `user`;
CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);
CREATE UNIQUE INDEX `user_username_unique` ON `user` (`username`);
PRAGMA foreign_keys=ON;
CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);
CREATE UNIQUE INDEX `user_username_unique` ON `user` (`username`);

[faulander]

i have a very similar problem.

• drizzle orm: 0.36.3
• drizzle kit: 0.28.0

i did the following:

• deleted all the indizes in the db
• deleted .drizzle folder
• ran db:generate

it tries to add another index that doesn't exist in the current database, not in the schema or anywhere else as far as i know.

this is the generated sql file:

CREATE TABLE `setting` (
	`id` integer PRIMARY KEY NOT NULL,
	`key` text NOT NULL,
	`value` text NOT NULL,
	`user_id` text NOT NULL,
	FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `setting_key_unique` ON `setting` (`key`);--> statement-breakpoint
CREATE UNIQUE INDEX `user_setting_idx` ON `setting` (`user_id`,`key`);--> statement-breakpoint`

this is the schema for setting:

export const setting = sqliteTable(
	'setting',
	{
		id: integer().primaryKey().notNull(),
		key: text().notNull().unique(),
		value: text().notNull(),
		userId: text('user_id')
			.notNull()
			.references(() => user.id)
	},
	(table) => ({
		userSettingsIndex: uniqueIndex('user_setting_idx').on(table.userId, table.key)
	})
);```

[jmyt8]

setting_key_unique index is created because you set setting.key to be unique.

UNIQUE constraints are implemented by creating a unique index in the database

[faulander]

Thank you so much. But should drizzle-kit recognize that? jmyt8 ***@***.***> schrieb am Mo. 18. Nov. 2024 um 02:20:

…

setting_key_unique index is created because you set setting.key to be unique. UNIQUE constraints are implemented by creating a unique index in the database — Reply to this email directly, view it on GitHub <#3574 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJGM6JJWVTOASYUE4CAJIFL2BE6ELAVCNFSM6AAAAABR546QOCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIOBRG42DAOBYGQ> . You are receiving this because you commented.Message ID: ***@***.***>