[BUG]: relation "collections_id_seq" already exists

[TArch64]

What version of drizzle-orm are you using?

0.32.0

What version of drizzle-kit are you using?

0.32.0

Describe the Bug

Hi. I've updated to the latest version to try out sequences for id column but when I running a migration it fails with error

Error:

error: relation "collections_id_seq" already exists
    at /app/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at <anonymous> (/app/node_modules/src/pg-core/dialect.ts:89:7)
    at NodePgSession.transaction (/app/node_modules/src/node-postgres/session.ts:155:19)
    at PgDialect.migrate (/app/node_modules/src/pg-core/dialect.ts:82:3)
    at migrate (/app/node_modules/src/node-postgres/migrator.ts:10:2)
    at <anonymous> (/app/packages/api/libs/database/drizzle/migrate.ts:14:1) {
  length: 112,
  severity: 'ERROR',
  code: '42P07',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'heap.c',
  line: '1149',
  routine: 'heap_create_with_catalog'
}

Here is table example

export const collections = pgTable('collections', {
  id: integer('id').primaryKey().notNull().generatedAlwaysAsIdentity(),
  workshopId: integer('workshop_id').notNull().references(() => workshops.id, { onDelete: 'cascade' }),
  name: varchar('name', { length: 255 }).notNull(),
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'date' }).notNull().$onUpdate(() => new Date()).defaultNow(),
}, (t) => ({
  nameKey: unique('collections_name_key').on(t.workshopId, t.name),
}));

Here is generated migration

ALTER TABLE "collections" ALTER COLUMN "id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "collections" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "collections_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);--> statement-breakpoint
ALTER TABLE "collections" ALTER COLUMN "workshop_id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "email_verifications" ALTER COLUMN "id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "email_verifications" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "email_verifications_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);--> statement-breakpoint
ALTER TABLE "email_verifications" ALTER COLUMN "user_id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "sessions" ALTER COLUMN "id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "sessions" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "sessions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);--> statement-breakpoint
ALTER TABLE "sessions" ALTER COLUMN "user_id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "users" ALTER COLUMN "id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "users" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);--> statement-breakpoint
ALTER TABLE "users_workshops" ALTER COLUMN "user_id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "users_workshops" ALTER COLUMN "workshop_id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "workshops" ALTER COLUMN "id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "workshops" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "workshops_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);

Expected behavior

Migration works

Environment & setup

OS: MacOS 14.5
Docker: Docker version 27.0.3, build 7d4bcd8
Docker Compose: Docker Compose version v2.28.1-desktop.1
Database: PostgreSQL 16.3
Node.js: v20.14.0

[TArch64]

I've tried to drop a database and run migrations again however the error is still present

[MrEAlderson]

This bug happens when switching from serial to identity. Drizzle generate the following:

1. Migration file

CREATE SCHEMA "test";
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "test"."users" (
	"id" serial PRIMARY KEY NOT NULL,
	"first_name" varchar(100) NOT NULL,
	"last_name" varchar(100) NOT NULL
);

2. Migration file

ALTER TABLE "test"."users" ALTER COLUMN "id" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "test"."users" ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY (sequence name "test"."users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1);

[byalashhab]

It seems you're still facing the issue even after deleting the database because you're applying the same migrations 😅

To resolve this, try deleting all the migrations along with the database. Then, create a new migration and apply it to the new database. This worked for me ✔