[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: ***@***.***>

[mihilmy]

I am using @libsql/client and I am seeing similar behavior with verbose on:

CREATE UNIQUE INDEX data_sources_uri_unique ON data_sources (uri);
CREATE UNIQUE INDEX data_sources_uri_unique ON data_sources (uri);

[mprather]

On an update scenario (using drizzle-kit push), we've run into the same type of breaking issue. The table in question has no unique indexes (other than primary). The upgrade code effectively doubles the index creation step, which is after a creating a new table, moving data into it, then renaming it.

ALTER TABLE `__new_events` RENAME TO `events`;
CREATE INDEX `events_investment_index` ON `events` (`investmentId`);
CREATE INDEX `events_account_index` ON `events` (`accountId`,`transactionDate`);
CREATE INDEX `events_investment_index` ON `events` (`investmentId`);
CREATE INDEX `events_account_index` ON `events` (`accountId`,`transactionDate`);
PRAGMA foreign_keys=OFF;

This breaks even the simplest upgrade.

Table schema:

export const events = s.sqliteTable("events", 
  {
    id: s.integer().primaryKey(),
    investmentId: s.integer().references(() => investments.id),
    accountId: s.integer().references(() => accounts.id, {onDelete: 'cascade'}),
    transactionDate: s.integer({ mode: 'timestamp' }).notNull(),
    planning: s.integer({ mode: 'boolean' }).notNull().default(false),
    amount: s.real().notNull(),
    type: s.text({enum: ["Issue", "Maturity", "Call"]}).notNull()
  },
  (t) => [
    s.index("events_investment_index").on(t.investmentId),
    s.index("events_account_index").on(t.accountId, t.transactionDate)
  ]
);

Experimenting with the schema, I see if my array of indexes has more than 1 value, then I will see the doubling behavior. Here's another example with 3 indexes. None of the tables that have a single index experience the doubling issue.

DROP TABLE `investments`;
ALTER TABLE `__new_investments` RENAME TO `investments`;
CREATE INDEX `investments_account_index` ON `investments` (`accountId`);
CREATE INDEX `test` ON `investments` (`securityId`);
CREATE INDEX `another_test` ON `investments` (`accountId`,`securityId`);
PRAGMA foreign_keys=ON;
CREATE INDEX `investments_account_index` ON `investments` (`accountId`);
CREATE INDEX `test` ON `investments` (`securityId`);
CREATE INDEX `another_test` ON `investments` (`accountId`,`securityId`);

The difference between the first and second set is the location of the pragma step.

Version info:
drizzle-kit: v0.30.5
drizzle-orm: v0.40.0

[davyzhang]

Running into this too

PRAGMA foreign_keys=OFF;
CREATE TABLE `__new_users` (
        `id` text PRIMARY KEY NOT NULL,
        `email` text NOT NULL,
        `userName` text,
        `hashedPassword` text,
        `emailVerified` integer DEFAULT FALSE NOT NULL,
        `picture` text,
        `createdAt` text,
        `updatedAt` text,
        `deletedAt` text
);

INSERT INTO `__new_users`("id", "email", "userName", "hashedPassword", "emailVerified", "picture", "createdAt", "updatedAt", "deletedAt") SELECT "id", "email", "userName", "hashedPassword", "emailVerified", "picture", "createdAt", "updatedAt", "deletedAt" FROM `users`;
DROP TABLE `users`;
ALTER TABLE `__new_users` RENAME TO `users`;
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);
PRAGMA foreign_keys=ON;
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);
ALTER TABLE `email_verification_tokens` ADD `type` text;

Duplicate CREATE UNIQUE INDEX when pushing to db

[paperschool]

Also running into the initial issue, is there any suggested fix?

[davyzhang]

Also running into the initial issue, is there any suggested fix?

Not that I am aware of, I switched to prisma to work with d1