Drizzle Team•11mo ago

Circular FK reference

How do I avoid a circular reference on a fk builder? I know you cast with AnyPgColumn when using .reference on a column... but I'm using the fk builder which has a circular reference

24 Replies

GeorgeOP•11mo ago

export const users = pgTable(
    "users",
    {
        id: char("id", { length: ULID_LENGTH }).notNull().primaryKey(),
        primaryEmailId: char("primary_email_id", { length: ULID_LENGTH }).notNull()
    },
    (columns) => ({
        primaryEmailIdForeignKey: foreignKey({
            columns: [columns.primaryEmailId],
            foreignColumns: [emails.id],
        })
            .onUpdate("restrict")
            .onDelete("restrict"),
    }),
);

export const emails = pgTable(
    "emails",
    {
        id: char("id", { length: ULID_LENGTH }).notNull().primaryKey(),
        userId: char("user_id", { length: ULID_LENGTH }),
    },
    (columns) => ({
        userIdForeignKey: foreignKey({
            columns: [columns.userId],
            foreignColumns: [users.id],
        })
            .onUpdate("restrict")
            .onDelete("cascade"),
    }),
);

export const users = pgTable(
    "users",
    {
        id: char("id", { length: ULID_LENGTH }).notNull().primaryKey(),
        primaryEmailId: char("primary_email_id", { length: ULID_LENGTH }).notNull()
    },
    (columns) => ({
        primaryEmailIdForeignKey: foreignKey({
            columns: [columns.primaryEmailId],
            foreignColumns: [emails.id],
        })
            .onUpdate("restrict")
            .onDelete("restrict"),
    }),
);

export const emails = pgTable(
    "emails",
    {
        id: char("id", { length: ULID_LENGTH }).notNull().primaryKey(),
        userId: char("user_id", { length: ULID_LENGTH }),
    },
    (columns) => ({
        userIdForeignKey: foreignKey({
            columns: [columns.userId],
            foreignColumns: [users.id],
        })
            .onUpdate("restrict")
            .onDelete("cascade"),
    }),
);

here's a dumbed down version of my schema where the problem lies

Angelelz•11mo ago

Does that cause a circular reference?

GeorgeOP•11mo ago

I think so. The type of users and the type of emails both become any with the fks The rest of the tables are fine Also, when I removed one of the fks, they both work which is why I think it's a circular reference

Angelelz•11mo ago

I think this case is not documented is because this case doesn't exists You wouldn't be able to inserte anything into either of those tables What are you trying to accomplish?

GeorgeOP•11mo ago

Yeah that’s the initial problem I had, but I made the user id column on the emails table nullable, so I just now insert with a transaction to make sure a user is indeed tied to an email. A user can have many emails, and have a primary email Users can add/remove emails, so that’s why I normalize using another table. The default primary email when a user is inserted is the one they sign up with

    private createRaw(data: UserService.CreateRawData): Promise<User> {
        return this.drizzle.transaction(async (drizzle) => {
            const [email] = await drizzle
                .insert(emails)
                .values({
                    id: ulid(),
                    email: data.email,
                    safeEmail: data.safeEmail,
                    emailVerifiedAt: null,
                })
                .returning({
                    id: emails.id,
                });

            if (!email) return drizzle.rollback();

            const [user] = await drizzle
                .insert(users)
                .values({
                    id: ulid(),
                    primaryEmailId: email.id,
                    firstName: data.firstName,
                    lastName: data.lastName,
                    password: data.password,
                })
                .returning();

            if (!user) return drizzle.rollback();

            await drizzle
                .update(emails)
                .set({
                    userId: user.id,
                })
                .where(eq(emails.id, email.id));

            return user;
        });
    }

    private createRaw(data: UserService.CreateRawData): Promise<User> {
        return this.drizzle.transaction(async (drizzle) => {
            const [email] = await drizzle
                .insert(emails)
                .values({
                    id: ulid(),
                    email: data.email,
                    safeEmail: data.safeEmail,
                    emailVerifiedAt: null,
                })
                .returning({
                    id: emails.id,
                });

            if (!email) return drizzle.rollback();

            const [user] = await drizzle
                .insert(users)
                .values({
                    id: ulid(),
                    primaryEmailId: email.id,
                    firstName: data.firstName,
                    lastName: data.lastName,
                    password: data.password,
                })
                .returning();

            if (!user) return drizzle.rollback();

            await drizzle
                .update(emails)
                .set({
                    userId: user.id,
                })
                .where(eq(emails.id, email.id));

            return user;
        });
    }

here's the method for creating a user I guess an alternate way of doing this is by having a pivot table to sit between the users and emails table which has a user_id and email_id a user still has a primary_email_id, but that has a direct reference to emails.id... this way wouldn't be circular and would guarantee emails have an associated user

Angelelz•11mo ago

Interesting, I've never seen such a case Let me think about this

GeorgeOP•11mo ago

Alrighty! Thanks for your help. Do you have a buy me a coffee link?

Angelelz•11mo ago

Have you tested this in a database without drizzle? just raw sql?

GeorgeOP•11mo ago

The queries work, yes... but only because user_id can be null on the emails table (otherwise, the constraints conflict eachother)... it's just the tables are inferred as any because of the circular reference

Angelelz•11mo ago

Should I have one? lol

GeorgeOP•11mo ago

I think if you put effort into helping people, you deserve to be rewarded in some way :)

Angelelz•11mo ago

I think that if you found help here while working with drizzle, you could sponsor the project if it's in your capabilities

GeorgeOP•11mo ago

Sure, if that's what you'd like I think this should work... but if you think of anything else, lmk!

Angelelz•11mo ago

Yeah, give me a couple minutes

GeorgeOP•11mo ago

No rush :)

Angelelz•11mo ago

I found a solution Requires you to define the foreignKey for at least one of the tables outside of said table And be explicit with the types, at least for that foreign key

Angelelz•11mo ago

I just moved it to typescript playground

TS Playground - An online editor for exploring TypeScript and JavaS...

The Playground lets you write TypeScript or JavaScript online in a safe and sharable way.

GeorgeOP•11mo ago

Ahh cool, thanks so much!

Angelelz•11mo ago

Unfortunately it's a typescript limitation. And this is honestly a weird edge case

GeorgeOP•11mo ago

Yeah. Thanks for the help and solution!

GeorgeOP•11mo ago

https://i.imgur.com/hOrzoP8.png

Imgur

Angelelz•11mo ago

Hey thanks for the support man!

GeorgeOP•11mo ago

Of course. Thanks again for the help. Impressed with drizzle!!! I’ve been with prisma for a long time but drizzle is definitely feeling good

BEEIRL•6mo ago

@Angelelz kinda stuck at the same problem here in mysql. would u say that this is still the way to go?

Gaming

Programming

Circular FK reference