Drizzle Team•17mo ago

How to use "onConflictDoUpdate" with composite key?

If a user sends a friend request while one exists it should update to pending. But the "friends" table is using a composite key. Table looks like this

export const friends = pgTable("friends", {
    senderUsername: varchar("sender_username").notNull(),
    recipientUsername: varchar("recipient_username").notNull(),
    sentAt: date("sent_at").defaultNow().notNull(),
    repliedAt: date("replied_at"),

    requestStatus: requestStatusEnum("request_status").notNull().default("PENDING")
},
    (t) => ({
        pk: primaryKey(t.senderUsername, t.recipientUsername) <- composite key
    }));

export const friends = pgTable("friends", {
    senderUsername: varchar("sender_username").notNull(),
    recipientUsername: varchar("recipient_username").notNull(),
    sentAt: date("sent_at").defaultNow().notNull(),
    repliedAt: date("replied_at"),

    requestStatus: requestStatusEnum("request_status").notNull().default("PENDING")
},
    (t) => ({
        pk: primaryKey(t.senderUsername, t.recipientUsername) <- composite key
    }));

query looks like this

    await db.insert(friends).values({
        senderUsername,
        recipientUsername
    }).onConflictDoUpdate({ target: friends.senderUsername, // <- how to do this?
        set: { requestStatus: REQUEST_STATUS.PENDING } });

    return {
        sendFriendRequestForm
    };

    await db.insert(friends).values({
        senderUsername,
        recipientUsername
    }).onConflictDoUpdate({ target: friends.senderUsername, // <- how to do this?
        set: { requestStatus: REQUEST_STATUS.PENDING } });

    return {
        sendFriendRequestForm
    };

Do I need to use a primary (non-composite) key in the friends table?

5 Replies

Noahh•17mo ago

I believe you can do target: [friends.senderUsername, friends.recipientUsername]

xamarotOP•17mo ago

thank you

Andrii Sherman•17mo ago

that's true, but still not a best solution I guess for now it's the only one but we will add some sort of callback, which can infer a list of composite keys for that so you can just choose any you need for now if you will change your schema and schema this primary pk, this code may be broken and won't show that something wrong. You will find that in runtime

Noahh•17mo ago

didn't know that, thanks!

iukea•17mo ago

😎

Gaming

Programming

How to use "onConflictDoUpdate" with composite key?