Prisma --> Convex
channelId String
channel Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)
channelId: v.string(),
channel: v.id("channels"),
The top is a Prisma schema definition, and the bottom is convex. Is that the correct schema translation from Prisma to convex? Thanks
11 Replies
Also, I have a conversations table to mimic a conversation between two people in Prisma and I'm trying to translate that to a convex schema, I'm not sure if I'm doing it correctly or I would have to create a new table to cover that relationship. And in the member table, I'm referencing that conversation with conversationsInitiated being the one we started with another member, and conversationsReceived being the one initiated by another member with us.
Prisma vs Convex
Compare Prisma examples with Convex
Hello, thanks for that resource! I just wanted to make sure if this is valid, and if it is, is it recommended?
conversations: defineTable({
memberOneId: v.id("members"),
memberTwoId: v.id("members"),
createdAt: v.string(),
updatedAt: v.string(),
})
.index("by_memberOneId", ["memberOneId"])
.index("by_memberOne_to_memberTwo", ["memberOneId","memberTwoId"])
.index("by_memberTwo_to_memberOne", ["memberTwoId","memberOneId"]),
The fields look right, but what are you trying to accomplish with those last two indexes?
If they're just to list all direct messages between two participants, you only need one of those last two indexes. The index will have all direct messages between the two member ids provided.
(usually having the combined index is only needed to check presence, ie "does this member/viewer have a convo with this other member", and you should only need one of them)
True, you would generally use the index to get the conversation id, and use the conversation id for querying the conversation messages.
Hello, thanks for the input! Yes, that's what I was looking to accomplish, whereby I could use the indexes to query conversations between two members regardless of who initiated it. I thought by just having one, I'd only be able to access those initiated by memberOne and not those initiated by memberTwo, or vice versa. But like you said it seems like the conversation id is what is received when you query by either of the last two indexes.
Just to make sure I understand, by using last index for example, we get the conversation Ids by memberTwo, and then if we want those between memberTwo and memberOne, we can add another query on top of that to get the conversation ids just between the both of them. And using those conversation Ids, we can then get the messages between the two.
And by having both the last two indexes we're being redundant because it still gives the same conversation Id. If we didn't have the last two indexes, we'd have to query the whole document to find convos involving memebrOne, as per the first index, and from the query that results, see if there is a convo between member Two and One which would stifle performance a bit perhaps. But with one of the last two combined indexes, the last one, its already done, and we can either query by member Two and then One if needed, but not member One and then Two, correct?
Also, if my thinking is not flawed, do we need the first index then, given we can accomplish that index with the second index, as well as accomplishing the last index with the second index, because we get the same conversation Id regardless?
Apologies for the late reply🙏
I see what you're saying now, a given member could be either memberOne or memberTwo so you'd need to check both. Got it.
I'd look to avoid the member one / member two dichotomy and normalize things a bit:
- a conversations table with no member ids
- a members table with just members
- a memberConversations table that has a memberId and conversationId field
This sets you up to handle conversations with any number of participants, and you could add a type field for indicating direct messages, which you could then programatically limit to two participants.
With this setup, you would get the list of conversations for both participants, probably also indexed to the type field so it's just direct conversations, and return the one that both members have in common.
All of that said, if you're in pretty deep with your current setup and don't want any big schema changes, you would indeed need the indexes you have in place, and probably an additional
by_memberTwoId as well, since you don't know whether a given member will be one or two for a conversation.ohh I see, yeah this is a more streamlined and flexible approach. It's worth the effort! Instead of two separate similar messages tables, channels, and direct, we streamline it to one that can be expanded as seen fit during mutations
memberConversations: defineTable({
memberId: v.id("members"),
conversationId: v.id("conversations"),
})
.index("by_member", ["memberId"])
.index("by_conversation", ["conversationId"]),
conversations: defineTable({
content: v.string(),
fileUrl: v.optional(v.string()),
type: MessageType,
deleted: v.boolean(),
updatedAt: v.string(),
}).index("by_type", ["type"]),
with this, conversations can be direct or group, and with the memberConversations, we can write a query that gets conversations and filters them by type, and using the conversationIds of those filtered conversations, we check if among those returned conversationIds, if the other user also shares the same conversationId, and from there return the similar conversation using that conversationId
please correct me if I understood it incorrectly, thanks!
Exactly!
awesome, thanks!!