Postgress migrations/second push fail due to trying to recreate constraints

I am using postgress on supabase, checked on docker as well same issue is replicated.
And unable to to push after pushing second time

Is there something fundamentally wrong that I am doing.
The migration files generated are correct but while pushing there is extra statements for unique id constraints.
This is a smaller schema in which I am able to replicate the issue.

package.json

1
2
3
4
"dependencies": {
  "drizzle-orm": "^0.40.0",
  "postgres": "^3.4.5"
},

drizzle.config.ts

import { config } from "./constants/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema/test.ts",
  out: "./src/db/drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: config.DATABASE_URL_SP,
  },
  strict: true,
  verbose: true,
});

test.ts [sample test schema to demonstrate issue]

import { uuid, pgTable, text, timestamp } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom().unique(),
  email: text("email").unique().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const userProfiles = pgTable("user_profiles", {
  userId: uuid("user_id")
    .primaryKey()
    .references(() => users.id, { onDelete: "cascade" }),
  firstName: text("first_name"),
  lastName: text("last_name"),
  profilePicture: text("profile_picture"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Initial generate

1
2
3
4
5
6
7
8
> drizzle-kit generate --config=./src/drizzle.config.ts

Reading config file 'D:\Ayush\Office\TalkWithIn\backend\src\drizzle.config.ts'
2 tables
user_profiles 6 columns 0 indexes 1 fks
users 4 columns 0 indexes 0 fks        

[] Your SQL migration file ➜ src\db\drizzle\0000_daffy_jackal.sql 🚀

\0000_daffy_jackal.sql

CREATE TABLE "user_profiles" (
    "user_id" uuid PRIMARY KEY NOT NULL,
    "first_name" text,
    "last_name" text,
    "profile_picture" text,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "email" text NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "updated_at" timestamp DEFAULT now() NOT NULL,
    CONSTRAINT "users_id_unique" UNIQUE("id"),
    CONSTRAINT "users_email_unique" UNIQUE("email")
);
--> statement-breakpoint
ALTER TABLE "user_profiles" ADD CONSTRAINT "user_profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

Pushing the schemas [successfully added]

> drizzle-kit push --config=./src/drizzle.config.ts

Reading config file 'D:\Ayush\Office\TalkWithIn\backend\src\drizzle.config.ts'
Using 'postgres' driver for database querying
[] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE "user_profiles" (
        "user_id" uuid PRIMARY KEY NOT NULL,
        "first_name" text,
        "last_name" text,
        "profile_picture" text,
        "created_at" timestamp DEFAULT now() NOT NULL,
        "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE "users" (
        "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
        "email" text NOT NULL,
        "created_at" timestamp DEFAULT now() NOT NULL,
        "updated_at" timestamp DEFAULT now() NOT NULL,
        CONSTRAINT "users_id_unique" UNIQUE("id"),
        CONSTRAINT "users_email_unique" UNIQUE("email")
);

ALTER TABLE "user_profiles" ADD CONSTRAINT "user_profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;

[] Changes applied

Change made in userProfile schema

export const userProfiles = pgTable("user_profiles", {
  userId: uuid("user_id")
    .primaryKey()
    .references(() => users.id, { onDelete: "cascade" }),
  firstName: text("first_name"),
  lastName: text("last_name"),
  extraProp: text("extra_prop"), // ------- extra prop
  profilePicture: text("profile_picture"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

Generate migrations

1
2
3
4
5
6
7
8
> drizzle-kit generate --config=./src/drizzle.config.ts

Reading config file 'D:\Ayush\Office\TalkWithIn\backend\src\drizzle.config.ts'
2 tables
user_profiles 7 columns 0 indexes 1 fks
users 4 columns 0 indexes 0 fks        

[] Your SQL migration file ➜ src\db\drizzle\0001_milky_shiver_man.sql 🚀

The sql file seems correct

\0001_milky_shiver_man.sql

ALTER TABLE "user_profiles" ADD COLUMN "extra_prop" text;

Final push command

> drizzle-kit push --config=./src/drizzle.config.ts

Reading config file 'D:\Ayush\Office\TalkWithIn\backend\src\drizzle.config.ts'
Using 'postgres' driver for database querying
[] Pulling schema from database...

 Warning  You are about to execute current statements:

ALTER TABLE "user_profiles" ADD COLUMN "extra_prop" text;
ALTER TABLE "users" ADD CONSTRAINT "users_id_unique" UNIQUE("id");

PostgresError: relation "users_id_unique" already exists
    at ErrorResponse (file:///D:/Ayush/Office/TalkWithIn/backend/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///D:/Ayush/Office/TalkWithIn/backend/node_modules/postgres/src/connection.js:474:6)   
    at Socket.data (file:///D:/Ayush/Office/TalkWithIn/backend/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:545:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:495:3)
    at Readable.push (node:internal/streams/readable:375:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P07',
  file: 'index.c',
  line: '873',
  routine: 'index_create'
}
Edit

Pub: 20 Mar 2025 04:19 UTC

Views: 19