[BUG]: drizzle-kit generates incorrect foreign key references to a table from a different schema when using pgSchema

[cs-clarence]

What version of drizzle-orm are you using?

0.26.1

What version of drizzle-kit are you using?

0.18.1

Describe the Bug

I have this file that has two schemas, iam and chat. The iam scheme has users table and chat schema has chatRooms table. That chatRooms table has a foreign key that references the primary key for users.

import { bigserial, pgSchema, uniqueIndex, varchar } from "drizzle-orm/pg-core";

export const iamSchema = pgSchema("iam");
const iamTable = iamSchema.table;

export const users = iamTable(
  "users",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    username: varchar("username").notNull(),
    emailAddress: varchar("email_address").notNull(),
    passwordHash: varchar("password_hash").notNull(),
  },
  (users) => {
    return {
      usernameIndex: uniqueIndex("username_idx").on(users.username),
      emailAddressIndex: uniqueIndex("email_address_idx").on(
        users.emailAddress,
      ),
    };
  },
);

export const chatSchema = pgSchema("chat");

export const chatTable = chatSchema.table;

export const chatRooms = chatTable("chat_rooms", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
});

export const chatRoomMemberships = chatTable("chat_room_memberships", {
  chatRoomID: bigserial("chat_room_id", { mode: "bigint" })
    .notNull()
    .references(() => chatRooms.id),
  userID: bigserial("user_id", { mode: "bigint" })
    .notNull()
    .references(() => users.id),
});

Using drizzle-kit generate:pg generates the following sql:

-- snipped
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "chat"."chat_room_memberships" ADD CONSTRAINT "chat_room_memberships_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "chat"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

The foreign key references "chat"."users"("id") instead of "iam"."users"("id").

Expected behavior

The migration file generated by drizzle-kit should correctly reference table and columns from another schema in foreign keys.

Environment & setup

Postgres Fedora Linux

[AndriiSherman]

Thanks! Going to fix it

[xprnio]

Having the same issue, are there any updates on this so far?

[lucianogreiner]

Same issue here.

[austinm911]

Would like this as well

[thiagoferolla]

Having the same issue, any updates on this?

Using drizzle-kit 0.19.13 and drizzle-orm 0.28.2

[avanderbergh]

I also just came across this issue.

@AndriiSherman How much longer until you're ready to open source drizzle-kit so we can help fixing these issues?

[avanderbergh]

FWIW, I've resorted to solving this with a patch until it's fixed 😅

diff --git a/index.cjs b/index.cjs
index d6085807f138fca830a8fb8bf9470779f091fa61..7518cfa82962ca53498bf194c19fe0394e59def1 100755
--- a/index.cjs
+++ b/index.cjs
@@ -4903,6 +4903,7 @@ var init_pgSchema = __esm({
       name: stringType(),
       tableFrom: stringType(),
       columnsFrom: stringType().array(),
+      schemaTo: stringType().optional(),
       tableTo: stringType(),
       columnsTo: stringType().array(),
       onUpdate: stringType().optional(),
@@ -5061,7 +5062,7 @@ var init_pgSchema = __esm({
         return result;
       },
       squashFK: (fk4) => {
-        return `${fk4.name};${fk4.tableFrom};${fk4.columnsFrom.join(",")};${fk4.tableTo};${fk4.columnsTo.join(",")};${fk4.onUpdate ?? ""};${fk4.onDelete ?? ""}`;
+        return `${fk4.name};${fk4.tableFrom};${fk4.columnsFrom.join(",")};${fk4.schemaTo || ""};${fk4.tableTo};${fk4.columnsTo.join(",")};${fk4.onUpdate ?? ""};${fk4.onDelete ?? ""}`;
       },
       squashPK: (pk) => {
         return `${pk.columns.join(",")}`;
@@ -5085,6 +5086,7 @@ var init_pgSchema = __esm({
           name,
           tableFrom,
           columnsFromStr,
+          schemaTo,
           tableTo,
           columnsToStr,
           onUpdate,
@@ -5094,6 +5096,7 @@ var init_pgSchema = __esm({
           name,
           tableFrom,
           columnsFrom: columnsFromStr.split(","),
+          schemaTo,
           tableTo,
           columnsTo: columnsToStr.split(","),
           onUpdate,
@@ -12210,12 +12213,15 @@ ${withStyle.errorWarning(`We've found duplicated unique constraint names in ${so
           const onUpdate = fk4.onUpdate;
           const reference = fk4.reference();
           const tableTo = (0, import_drizzle_orm6.getTableName)(reference.foreignTable);
+          // Patched
+          const schemaTo = reference.foreignTable[Symbol.for("drizzle:Schema")];
           const columnsFrom = reference.columns.map((it) => it.name);
           const columnsTo = reference.foreignColumns.map((it) => it.name);
           return {
             name,
             tableFrom,
             tableTo,
+            schemaTo,
             columnsFrom,
             columnsTo,
             onDelete,
@@ -15963,6 +15969,7 @@ ${BREAKPOINT}ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT ${statement.newCo
       convert(statement) {
         const {
           name,
+          schemaTo,
           tableFrom,
           tableTo,
           columnsFrom,
@@ -15975,7 +15982,7 @@ ${BREAKPOINT}ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT ${statement.newCo
         const fromColumnsString = columnsFrom.map((it) => `"${it}"`).join(",");
         const toColumnsString = columnsTo.map((it) => `"${it}"`).join(",");
         const tableNameWithSchema = statement.schema ? `"${statement.schema}"."${tableFrom}"` : `"${tableFrom}"`;
-        const tableToNameWithSchema = statement.schema ? `"${statement.schema}"."${tableTo}"` : `"${tableTo}"`;
+        const tableToNameWithSchema = schemaTo ? `"${schemaTo}"."${tableTo}"` : `"${tableTo}"`;
         const alterStatement = `ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT "${name}" FOREIGN KEY (${fromColumnsString}) REFERENCES ${tableToNameWithSchema}(${toColumnsString})${onDeleteStatement}${onUpdateStatement}`;
         let sql = "DO $$ BEGIN\n";
         sql += " " + alterStatement + ";\n";

Only fixes PostgreSQL...

[vafanassieff]

Coming here to bump this issue.

The patch work with well with pnpm thanks @avanderbergh

[louneskmt]

Any update on this? It's a bit tedious to have to reapply the patch with every update.

[fergalmoran]

This is incredible? How can a bug like this be open for 6 months? I'm looking into drizzle for our team but this is a show stopper for us and a HUGE turn off?

[emmnull]

I don't understand why this has been moved to the public roadmap's backlog when there is a fix already identified by community users. Unless there's some complexity I am not understanding, this is a clear low hanging fruit and eliminating the burden of having to re-patch after each update would improve DX for a few of us.

[louneskmt]

@AndriiSherman I see you're assigned to this, any news?

[hexcowboy]

the patch is no longer working for the current drizzle-kit version and i've spent too much time trying to update the patch without luck. instead i'm resulting to change both the schema and snapshot manually (not sure if snapshot is necessary) as a more permanent solution

0000_x.sql

< ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
---
> ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE cascade ON UPDATE no action;

0000_snapshot.json

< "tableTo": "users",
---
> "tableTo": "auth.users",

for anyone else encountering this more-than-mildly infuriating issue

[emmnull]

the patch is no longer working for the current drizzle-kit version and i've spent too much time trying to update the patch without luck. instead i'm resulting to change both the schema and snapshot manually (not sure if snapshot is necessary) as a more permanent solution

0000_x.sql

< ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
---
> ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "auth"."users"("id") ON DELETE cascade ON UPDATE no action;

0000_snapshot.json

< "tableTo": "users",
---
> "tableTo": "auth.users",

for anyone else encountering this more-than-mildly infuriating issue

Hmmm, the patch seems to work on my side. What breakage are you observing, is it successfully generating migrations with wrong schema names or is it throwing a malformed migration error when you attempt to run drizzle-kit generate ?

[hexcowboy]

Hmmm, the patch seems to work on my side.

are you using [email protected]? most of the code was moved to util.js and there are pretty significant changes there from the patch file.

the issue is the same as described in the OP. the schema is not being prepended to the table name so the migration is not successful.

[emmnull]

Hmmm, the patch seems to work on my side.

are you using [email protected]? most of the code was moved to util.js and there are pretty significant changes there from the patch file.

the issue is the same as described in the OP. the schema is not being prepended to the table name so the migration is not successful.

Yes, I am using @0.20.6. Unless I'm mistaken, the code you need to patch is now in bin.cjs, not utils.js.

Edit: updated for 0.20.7 (this is getting really annoying).

diff --git a/bin.cjs b/bin.cjs
index 8e30f3c742f58d349be800a0cd107d8cd597b540..9daa5f490fbf50563fb200c6a0bf759833975ce3 100755
--- a/bin.cjs
+++ b/bin.cjs
@@ -4906,6 +4906,7 @@ var init_pgSchema = __esm({
       name: stringType(),
       tableFrom: stringType(),
       columnsFrom: stringType().array(),
+      schemaTo: stringType().optional(),
       tableTo: stringType(),
       columnsTo: stringType().array(),
       onUpdate: stringType().optional(),
@@ -5064,7 +5065,7 @@ var init_pgSchema = __esm({
         return result;
       },
       squashFK: (fk4) => {
-        return `${fk4.name};${fk4.tableFrom};${fk4.columnsFrom.join(",")};${fk4.tableTo};${fk4.columnsTo.join(",")};${fk4.onUpdate ?? ""};${fk4.onDelete ?? ""}`;
+        return `${fk4.name};${fk4.tableFrom};${fk4.columnsFrom.join(",")};${fk4.schemaTo || ""};${fk4.tableTo};${fk4.columnsTo.join(",")};${fk4.onUpdate ?? ""};${fk4.onDelete ?? ""}`
       },
       squashPK: (pk) => {
         return `${pk.columns.join(",")};${pk.name}`;
@@ -5089,6 +5090,7 @@ var init_pgSchema = __esm({
           name,
           tableFrom,
           columnsFromStr,
+          schemaTo,
           tableTo,
           columnsToStr,
           onUpdate,
@@ -5098,6 +5100,7 @@ var init_pgSchema = __esm({
           name,
           tableFrom,
           columnsFrom: columnsFromStr.split(","),
+          schemaTo,
           tableTo,
           columnsTo: columnsToStr.split(","),
           onUpdate,
@@ -12303,12 +12306,14 @@ ${withStyle.errorWarning(`We've found duplicated unique constraint names in ${so
           const onUpdate = fk4.onUpdate;
           const reference = fk4.reference();
           const tableTo = (0, import_drizzle_orm5.getTableName)(reference.foreignTable);
+          const schemaTo = reference.foreignTable[Symbol.for("drizzle:Schema")];
           const columnsFrom = reference.columns.map((it) => it.name);
           const columnsTo = reference.foreignColumns.map((it) => it.name);
           return {
             name,
             tableFrom,
             tableTo,
+            schemaTo,
             columnsFrom,
             columnsTo,
             onDelete,
@@ -16289,7 +16294,7 @@ ${BREAKPOINT}ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT ${statement.newCo
         const fromColumnsString = columnsFrom.map((it) => `"${it}"`).join(",");
         const toColumnsString = columnsTo.map((it) => `"${it}"`).join(",");
         const tableNameWithSchema = statement.schema ? `"${statement.schema}"."${tableFrom}"` : `"${tableFrom}"`;
-        const tableToNameWithSchema = statement.schema ? `"${statement.schema}"."${tableTo}"` : `"${tableTo}"`;
+        const tableToNameWithSchema = schemaTo ? `"${schemaTo}"."${tableTo}"` : `"${tableTo}"`
         const alterStatement = `ALTER TABLE ${tableNameWithSchema} ADD CONSTRAINT "${name}" FOREIGN KEY (${fromColumnsString}) REFERENCES ${tableToNameWithSchema}(${toColumnsString})${onDeleteStatement}${onUpdateStatement}`;
         let sql2 = "DO $$ BEGIN\n";
         sql2 += " " + alterStatement + ";\n";

[pawanmkr]

8 months and still not fixed, i think i need to switch to another orm

[codeagencybe]

@AndriiSherman what is the problem here? Why is this bug so long standing? This is really annoying. Community already made a few patches yet Drizzle is complete ignoring here. Unacceptable!!

[xprnio]

@pawanmkr @codeagencybe Although I feel just as both of you (and everybody else that's here waiting), let's give them the benefit of the doubt and assume that the current issue is related to the holidays. Let's give them a bit of a breather, for example a week or so, and hope they'll start the new year off with working with the community instead of against it.

[codeagencybe]

@pawanmkr @codeagencybe Although I feel just as both of you (and everybody else that's here waiting), let's give them the benefit of the doubt and assume that the current issue is related to the holidays. Let's give them a bit of a breather, for example a week or so, and hope they'll start the new year off with working with the community instead of against it.

Holidays?? This bug is ongoing for 8+ months lol. This is not related to holidays. And besides the community already gave working solutions in the past, they could have easy picked it up and make sure it gets vetted in the official releases and updates.

8 months later: nothing, nada, zero engagement... This is not some small futile problem, it causes real serious problems if your migrations get screwed up.

Problems like this that keep being ignored is what pushes people back to Prisma and others.

[emmnull]

There very well could be some complexity to this issue that is being overlooked by discussions in this thread while being silently acknowledged by the core team (things like ensuring backward/forward compatibility of generated metadata, migrations scripts to patch erroneous upstream migrations, etc.). I would give them benefit of the doubt, but what is justifiably bothersome is how there's been a longstanding unfulfilled promise of open-sourcing drizzle-kit leaving the community completely unequipped to help identify and approach this issue and others more rigorously or even make PRs that could offload some work and improve our DX. There's a distinct problem of communication that - although it could be attributed to lack of time - is starting to feel like an intentional lack of transparency from the outside.

[codeagencybe]

Whatever the reason is, there is no communication at all. Not even a simple ack "we are aware and working on it" or whatever. It's completely ghosted, that bothers me most. I can understand sometimes bugs can be hard and take time. But at least communicate back with your community. Where are we at now? Nobody knows.

And as you say, it's completely closed black box now. So nobody from community can even do something to get a minimal progress. It's already a miracle some smart people here managed to get some quick patch in the past so we all can continue...

[fergalmoran]

There very well could be some complexity to this issue that is being overlooked by discussions in this thread while being silently acknowledged by the core team....

I'm not sure you're correct here as there have been numerous patches submitted to resolve this issue, but with the best will in the world it's still egregious that we have had precisely zero feedback from the drizzle team on this.

[emmnull]

I'm not sure you're correct here as there have been numerous patches submitted to resolve this issue

As far as i know, there was only a single patch proposed (targeting different lines depending on the patched package version), and after it is used once, omitting its added schemaTo keys or simply changing its name in subsequent patches leads to failures when generating new migrations. This to me sounds like there's at least some more work needed to handle errors or ensure better lateral compatibility. But again, I totally agree that how this issue is unfolding highlights a bigger communication problem.

[thefrana]

I would not be surprised if this issue was ignored just for the sake of the meme 😀 it is already starting on X

[AndriiSherman]

Should be fixed in drizzle-kit@beta. Please confirm that it works for you so I can release it to the latest version. I also fixed the issue with introspection and push, so now both introspect and push should work for these cases.

Note: The only thing that is not fixed and is a different issue is when you name tables the same across schemas, but that was not the case for this specific issue.

[xprnio]

See, @codeagencybe. I told you, just give the guys some breathing room for the holidays. All is well that ends well

[codeagencybe]

See, @codeagencybe. I told you, just give the guys some breathing room for the holidays. All is well that ends well

lol no, they just picked it up because I ranted it on Twitter and other people complained about the same. This is not about "the holidays". I know a lot devs that decided to drop Drizzle and go back to Prisma or others, myself included if this wasn't picked up fast. This is a serious show stopper for many people that actually build production software. I'm not toying around with the next shiny AI playground.

Anyways, it's good that it's now finally got attention and fixed. That's the end result that matters. But they could have done much better effort in communicating with their community or better open source the kit so the community could have fixed it months ago.

[AndriiSherman]

Open-sourcing drizzle-kit won't expedite the fixing process. I still need time to review pull requests and merge them. It's primarily a matter of time and receiving support, both in terms of time and donations, from companies utilizing Drizzle in a production environment and relying on it.

I can say the same about better communication. As core library developers, we are grappling with numerous issues/feature requests, addressing and fixing them while juggling our main jobs. I believe every library developer has had these types of conversations at least once

[maietta]

Should be fixed in drizzle-kit@beta. Please confirm that it works for you so I can release it to the latest version. I also fixed the issue with introspection and push, so now both introspect and push should work for these cases.

Note: The only thing that is not fixed and is a different issue is when you name tables the same across schemas, but that was not the case for this specific issue.

Hey, I was about to give up on this. I can confirm switching this beta version did in fact fix my issue.