drizzle-orm: [BUG]: drizzle-kit generates incorrect foreign key references to a table from a different schema when using pgSchema
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
About this issue
- Original URL
- State: closed
- Created a year ago
- Reactions: 19
- Comments: 41 (5 by maintainers)
Any update on this? It’s a bit tedious to have to reapply the patch with every update.
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.
8 months and still not fixed, i think i need to switch to another orm
I also just came across this issue.
@AndriiSherman How much longer until you’re ready to open source
drizzle-kitso we can help fixing these issues?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
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.
Yes, I am using
@0.20.6. Unless I’m mistaken, the code you need to patch is now inbin.cjs, notutils.js.Edit: updated for
0.20.7(this is getting really annoying).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?
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…
FWIW, I’ve resorted to solving this with a patch until it’s fixed 😅
Only fixes PostgreSQL…
Fixed in
drizzle-kit@0.20.10You are way off man and this discussion is pointless. I have multiple git accounts from my company. I never said I “own” them nor did I say they must fix it “now/immediately”. I only raised concerns about the non-communication and the lack off. Because many devs are eager to try drizzle for it’s native sql-like experience. But if the Tooling is giving a hard time, it pushes back devs to other tools if they don’t get attention.
Everybody who used the migration tools was complaining about this and our complaints was that there was ZERO communication from Drizzle about this Blocking issue. If they would just ack the issue and say something align “we Will check and fix it next or in the coming weeks/month” was also fine. At least we know they understood.
If there was a way for other devs to help contributing to this issue it would also been fine. But there is none because it’s not open source.
This long-standing issue was just ignored or missed as they self confirmed. Period. I don’t care what else you think about this, everybody else feels exact the same about this situation as Drizzle confirmed it was on their end. Drizzle also apologized for the non-communication which I appreciate.
Only because some people make awareness on social media it “suddenly” gets attention? That’s no coincidence.
The problem was lack off communication. Nothing more, nothing less. Stop turning this discussion into something it isn’t.
@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!!
Thanks! Going to fix it
@AndriiSherman
I have tested the beta and confirmed, the beta update is indeed fixing the issue. Also many thanks for fixing this for push, that’s amazing and very much appreciated.
I’m not aware about the identical naming of tables in schema’s, that’s not a use case we do. But also thanks for the heads up about this “known” issue. But I wouldn’t call this is specific “drizzle-related”. I think with many other tools, it would result in a same error/problem. Naming tables identical in schema’s is just not good practice.
Nice work and many thanks 🔥 Now we have extra reasons to enjoy Drizzle even more over Prisma 😉
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.
@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.
Oh yeah, I forgot to say THANKS for fixing the issue (in beta).
I can move forward now and start getting my projects deployed with Drizzle and start encouraging others to give it a look.
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 withintrospectionandpush, so now bothintrospectandpushshould work for these cases.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-kitleaving 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.@AndriiSherman I see you’re assigned to this, any news?
Using beta version generates correct SQL for migrations 👍
Hey @codeagencybe.
Just updated to drizzle-kit@0.20.10-3c347a7 per your request, tested and didn’t see any issues.
So far, it looks usable for me.
Coming here to bump this issue.
The patch work with well with
pnpmthanks @avanderberghSame issue here.