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

[jmyt8]

Report hasn't been filed before.

• [X] 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 https://github.com/drizzle-team/drizzle-orm/issues/3574#issuecomment-2481740884, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJGM6JJWVTOASYUE4CAJIFL2BE6ELAVCNFSM6AAAAABR546QOCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIOBRG42DAOBYGQ . You are receiving this because you commented.Message ID: @.***>