Push is recreating constraints every time

[stefanosandes]

Hello, everyone,

Every time I run drizzle-kit push:pg it tries to recreate all constraints, even if nothing has changed in the database.
Is this behavior expected?

In my schema, I'm using .references and 'relations' together for relations.

Config file:

export default {
	schema: './db/schema.ts',
	driver: 'pg',
	out: './migrations',
	dbCredentials: {
		connectionString: process.env.DATABASE_URL
	},
	verbose: true,
	strict: true
} satisfies Config;

Generated code:

ALTER TABLE "products" DROP CONSTRAINT "products_tenant_config_id_tenant_configurations_tenant_id_fk";

ALTER TABLE "faq" DROP CONSTRAINT "faq_tenant_config_id_tenant_configurations_tenant_id_fk";

ALTER TABLE "gallery" DROP CONSTRAINT "gallery_tenant_id_tenants_id_fk";

ALTER TABLE "product_stages" DROP CONSTRAINT "product_stages_product_id_products_id_fk";

ALTER TABLE "sliders" DROP CONSTRAINT "sliders_tenant_config_id_tenant_configurations_tenant_id_fk";

ALTER TABLE "applications" DROP CONSTRAINT "applications_tenant_config_id_tenant_configurations_tenant_id_f";

ALTER TABLE "users_managers" DROP CONSTRAINT "users_managers_tenant_id_tenants_id_fk";

ALTER TABLE "tenant_configurations" DROP CONSTRAINT "tenant_configurations_tenant_id_tenants_id_fk";

ALTER TABLE "documents" DROP CONSTRAINT "documents_product_id_products_id_fk";

ALTER TABLE "users_portal" DROP CONSTRAINT "users_portal_tenant_id_tenants_id_fk";

ALTER TABLE "domains" DROP CONSTRAINT "domains_tenant_id_tenants_id_fk";

DROP INDEX IF EXISTS "applications_tenant_id_type_key";
DROP INDEX IF EXISTS "users_manager_tenant_id_key)";
CREATE UNIQUE INDEX IF NOT EXISTS "applications_tenant_id_type_key" ON "applications" ("type","tenant_config_id");
CREATE UNIQUE INDEX IF NOT EXISTS "users_manager_tenant_id_key)" ON "users_managers" ("email","tenant_id");
DO $$ BEGIN
 ALTER TABLE "products" ADD CONSTRAINT "products_tenant_config_id_tenant_configurations_tenant_id_fk" FOREIGN KEY ("tenant_config_id") REFERENCES "tenant_configurations"("tenant_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "faq" ADD CONSTRAINT "faq_tenant_config_id_tenant_configurations_tenant_id_fk" FOREIGN KEY ("tenant_config_id") REFERENCES "tenant_configurations"("tenant_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "gallery" ADD CONSTRAINT "gallery_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "product_stages" ADD CONSTRAINT "product_stages_product_id_products_id_fk" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "sliders" ADD CONSTRAINT "sliders_tenant_config_id_tenant_configurations_tenant_id_fk" FOREIGN KEY ("tenant_config_id") REFERENCES "tenant_configurations"("tenant_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "applications" ADD CONSTRAINT "applications_tenant_config_id_tenant_configurations_tenant_id_fk" FOREIGN KEY ("tenant_config_id") REFERENCES "tenant_configurations"("tenant_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "users_managers" ADD CONSTRAINT "users_managers_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "tenant_configurations" ADD CONSTRAINT "tenant_configurations_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "documents" ADD CONSTRAINT "documents_product_id_products_id_fk" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "users_portal" ADD CONSTRAINT "users_portal_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "domains" ADD CONSTRAINT "domains_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

[KranzAklilu]

I'm trying to figure this out too

[ivask2uu]

I have the same problem. It also deletes rows from db which is pretty annoying. This happens even when running it multiple times back to back, so the schema is exactly the same.

Any solutions/updates on this?

Using 'pg' driver for database querying
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

ALTER TABLE "lost_announcement" DROP CONSTRAINT "lost_announcement_last_approved_content_revision_id_lost_announ";

ALTER TABLE "lost_announcement" DROP CONSTRAINT "lost_announcement_pending_revision_id_lost_announcement_content";

ALTER TABLE "lost_announcement_content_revision" DROP CONSTRAINT "lost_announcement_content_revision_ann_id_lost_announcement_id_";

ALTER TABLE "lost_announcement_content_revision" DROP CONSTRAINT "lost_announcement_content_revision_reward_currency_code_currenc";

ALTER TABLE "sighting_announcement_content_revision" DROP CONSTRAINT "sighting_announcement_content_revision_ann_id_sighting_announce";

ALTER TABLE "sighting_announcement" DROP CONSTRAINT "sighting_announcement_last_approved_content_revision_id_sightin";

ALTER TABLE "sighting_announcement" DROP CONSTRAINT "sighting_announcement_pending_revision_id_sighting_announcement";

ALTER TABLE "map_entity" ALTER COLUMN "postgis_coordinates" SET DATA TYPE geometry(point);
DO $$ BEGIN
 ALTER TABLE "lost_announcement" ADD CONSTRAINT "lost_announcement_last_approved_content_revision_id_lost_announcement_content_revision_revision_id_fk" FOREIGN KEY ("last_approved_content_revision_id") REFERENCES "public"."lost_announcement_content_revision"("revision_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "lost_announcement" ADD CONSTRAINT "lost_announcement_pending_revision_id_lost_announcement_content_revision_revision_id_fk" FOREIGN KEY ("pending_revision_id") REFERENCES "public"."lost_announcement_content_revision"("revision_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "lost_announcement_content_revision" ADD CONSTRAINT "lost_announcement_content_revision_ann_id_lost_announcement_id_fk" FOREIGN KEY ("ann_id") REFERENCES "public"."lost_announcement"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "lost_announcement_content_revision" ADD CONSTRAINT "lost_announcement_content_revision_reward_currency_code_currency_code_fk" FOREIGN KEY ("reward_currency_code") REFERENCES "public"."currency"("code") ON DELETE set null ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "sighting_announcement_content_revision" ADD CONSTRAINT "sighting_announcement_content_revision_ann_id_sighting_announcement_id_fk" FOREIGN KEY ("ann_id") REFERENCES "public"."sighting_announcement"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "sighting_announcement" ADD CONSTRAINT "sighting_announcement_last_approved_content_revision_id_sighting_announcement_content_revision_revision_id_fk" FOREIGN KEY ("last_approved_content_revision_id") REFERENCES "public"."sighting_announcement_content_revision"("revision_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "sighting_announcement" ADD CONSTRAINT "sighting_announcement_pending_revision_id_sighting_announcement_content_revision_revision_id_fk" FOREIGN KEY ("pending_revision_id") REFERENCES "public"."sighting_announcement_content_revision"("revision_id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;


❯ No, abort
  Yes, I want to execute all statements

I've tested it on the following versions, and the issue still happens:

"drizzle-kit": "^0.24.0",
"drizzle-orm": "^0.33.0",

"drizzle-kit": "^0.25.0",
"drizzle-orm": "^0.34.0",

"drizzle-kit": "^0.27.0",
"drizzle-orm": "^0.36.0",

[ivask2uu]

Nevermind, I found the solution. The issue was caused by the long constraint names being truncated.

To fix it quickly, I updated the table and field names.

The specific problem with truncation can be seen in this example (among others):

The constraint name
sighting_announcement_last_approved_content_revision_id_sighting_announcement_content_revision_revision_id_fk (109 characters)
was truncated to
sighting_announcement_last_approved_content_revision_id_sightin (63 characters), which seems to be PostgreSQL's maximum length.

Now when I run drizzle-kit push I correctly have:

Using 'pg' driver for database querying
[✓] Pulling schema from database...

 Warning  You are about to execute the following statements:

ALTER TABLE "map_entity" ALTER COLUMN "postgis_coordinates" SET DATA TYPE geometry(point);

[✓] Changes applied

(Note: Ignore postgis_coordinates here, as I save it as geography, but Drizzle currently only supports geometry.)

It would be great to have an option to assign custom names to .references() constraints, as mentioned in this comment: #466 (comment)

[stefanosandes]

#3244