Ian Macartney

2 years ago

Relationship Structures: Let's Talk About Schemas

Relationships are a key part of a relational database like Convex. But how do you use them? In this post we’ll look at some patterns for structuring relationships in the Convex database. See this post on Relationship Helpers for code examples to simplify and compose queries.

Typically, relationships are characterized as being:

• One-to-one: Every document of type A corresponds to at most one document of type B. For example, you may store a user’s preferences separately from the main user document.
• Many-to-one: Every A document can relate to zero or more B documents, but each B document is related to at most one A document. For example, a conversation may have many messages, but each message is only in one conversation.
• Many-to-many: Every A document can relate to zero or more B documents, and every B document can relate to zero or more A documents. For example, a post may have many tags, and every tag can be on many posts.

One-to-one

To structure a one-to-one relationship, you can have one document hold an ID to another, or have both hold IDs. With the example of storing a student health record as a separate document from the student, this allows you to fetch a student without loading the health record, while still having fast access to it in situations where you need it.

Direct reference

An arrow points from a left circle to a right circle

This is the simplest relationship:

1defineSchema({
2  students: defineTable({
3    ...
4  }),
5  healthRecords: defineTable({
6    studentId: v.id("students")
7    ...
8  }),
9});
10

You can then access the student from a health record like:

1const student = await ctx.db.get(record.studentId);
2

You can create a health record associated with a student like this:

1const healthRecordId = await ctx.db.insert('healthRecords', {
2  studentId: student._id,
3  ...
4});
5

This works well, but what if you have a student and want to look up the health record?

Back-reference

An arrow points from a right circle to a left circle

Another way to use a single reference is to have an index on the field with the referenced ID:

1defineSchema({
2  students: defineTable({
3    ...
4  }),
5  healthRecords: defineTable({
6    studentId: v.id("students")
7    ...
8  }).index("studentId", ["studentId"]),
9});
10

We create the record in the same way as direct references, but now we can look up the record from the student:

1const record = await ctx.db.query("healthRecords")
2  .withIndex("studentId", q => q.eq("studentId", student._id))
3  .unique()
4

This uses the index to find the record pointing to this student. Since we expect there to only be one, we use unique() so it will throw an error if we end up with more than one.

See the Relationship Helpers post for a getOneFrom helper.

What if we want to easily look up in both directions without a schema?

Why not both?

For one-to-one relationships, it’s possible to store an ID in both places:

1defineSchema({
2  students: defineTable({
3    ...
4    healthRecordId: v.optional(v.id("healthRecords")),
5  }),
6  healthRecords: defineTable({
7    ...
8    studentId: v.id("students"),
9  }),
10});
11

When you create the related document, you can update both documents to have each others’ IDs.

1const studentId = await ctx.db.insert("students", {...});
2const healthRecordId = await ctx.db.insert('healthRecords', {studentId, ...});
3await ctx.db.patch(student._id, { healthRecordId });
4//code to create both objects
5

This ensures you can always do a “point lookup” (which are very fast) without using an index. However, keep in mind that duplicating this state could lead to inconsistencies if you update one record but forget to update the other. It’s up to you to keep both records in sync. Thankfully, since Convex mutations are transactions, you can update both in the same mutation and not worry about any other queries or mutations reading one but not the other.

Next, let’s look at what happens when you have many documents holding the same reference.

One-to-many

When one document is related to many documents — for instance, one user may author many posts — you have a one-to-many relationship.

Direct references (simple)

A circle on the left points to three circles on the right

If you only have a few references, you might find it easier to store an array of IDs instead of querying back-references with an index (which we’ll see next). For instance, let’s say you have a competition where each participant can submit up to 5 works of art. You can directly store links to the art on the participant:

1defineSchema({
2  submissions: defineTable({...}),
3  participants: defineTable({
4    ...
5    submissionIds: v.array(v.id('submissions')),
6  }),
7});
8

Then you can directly get each participant’s art without using an index:

1const submissions = await Promise.all(participant.submissionIds.map(db.get));
2

See the Relationship Helpers post for a getAll helper to fetch an array of IDs, and more information on Promise.all and map.

However, this comes with some important design considerations:

1. You can’t index an array in Convex, so you won’t be able to query for the participant who submitted a given submission ID. You’d have to scan all participants and check the submissionIds array in each.¹

   1const participants = await ctx.db.query('participants').collect();
   2for (const participant of participants) {
   3  if (participant.submissionIds.find(id => id.equals(submission._id))) {
   4    return participant;
   5  }
   6}
   7

2. You are limited to 8192 entries in an array, and we recommend having on the order of 10 items in arrays, so you should only use this where there’s a natural limit to how many references there will be.

Back-references (scalable)

Three circles on the right point to a circle on the left

Similar to one-to-one relationships, you can use back-references to keep track of all the posts referring to an author. You can think of this as a many-to-one relationship, as many posts hold a reference to one author. This scales better than the array, since no one document is getting larger every time there’s another post by the same author. You can define an index on those references to look up which posts have a certain author:

1posts: defineTable({
2  authorId: v.id("users"),
3  ...
4}).index('authorId', ['authorId']);
5

Similarly to one-to-one, this allows you to look up the author of a post directly. We’ll similarly use the index for the back-reference (author → posts). The difference: instead of unique(), we use collect() to get all of the posts:

1const posts = await ctx.db.query("posts")
2  .withIndex("authorId", q => q.eq("authorId", author._id))
3  .collect();
4

See the Relationship Helpers post for a getManyFrom function to codify this.

You create the post with a link to the author like so:

1await ctx.db.insert("posts", {..., authorId: author._id});
2

Many-to-many

Some relationships don’t allow you to just store a single ID. In a group messaging application, for example, keeping track of which users are in which channels requires a many-to-many relationship. A user can be in many channels and a channel can have many users. In these situations, there are a few options, but the most common one is to use a table that has a reference to both.

Relationship table (scalable)

Diagram

There is a lot of terminology you could use here: a “join” table, an “edge,” or an “association” table to name a few. The premise is simple, however:

1defineSchema({
2  users: defineTable({
3    ... 
4  }),
5  channels: defineTable({ 
6    ...
7  }),
8  channelUsers: defineTable({
9    channelId: v.id("channels"),
10    userId: v.id("users"),
11    // Other fields related to the user, channel pairing
12    // E.g. notification preferences or when they were last seen
13  })
14    .index("channelId", ["channelId"]) // Get users in a channel
15    .index("userId", ["userId"]), // Get channels for a user
16});
17

You have a table where every document represents a pairing of a user and a channel. To find the users in a channel, it’s similar to the one-to-many lookup above, combined with a db.get on each associated record:

1const channelUsers = await ctx.db.query("channelUsers")
2  .withIndex("channelId", q => q.eq("channelId", channel._id))
3  .collect();
4const userIds = channelUsers.map(channelUser => channelUser.userId);
5const users = await Promise.all(userIds.map(db.get));
6

See the Relationship Helpers post for a getManyVia helper function to make this easy.

To add a user to a channel:

1await ctx.db.insert('channelUsers', { channelId, userId });
2

To figure out if a user is in a channel, you have 3 options:

1. Fetch with the channel index and look for the user.

   1const channelUsers = await ctx.db.query("channelUsers")
   2  .withIndex("channelId", q => q.eq("channelId", channel._id))
   3  .collect();
   4const isInChannel = !!channelUsers.find(cu => cu.userId.equals(user._id);
   5

2. Fetch with the user and look for the channel. I’d prefer this one since users are more likely to have a lower limit of how many channels they can be in, versus how many users can be in a channel.

   1const channelUsers = await ctx.db.query("channelUsers")
   2  .withIndex("userId", q => q.eq("userId", user._id))
   3  .collect();
   4const isInChannel = !!channelUsers.find(cu => 
   5cu.channelId.equals(channel._id);
   6

3. To look it up without iterating an array, you could have one of the indexes contain both fields:

   1.index("channelId", ["channelId"])
   2.index("userId_channelId", ["userId", "channelId"]),
   3

   This would allow you to check directly:

   1await ctx.db.query("channelUsers")
   2  .withIndex("userId_channelId", q =>
   3    q.eq("userId", user._id).eq("channelId", channel._id)
   4  )
   5  .unique(); // null if it's not found
   6

Direct References (simple)

While the relationship table is the most flexible and performant, there are situations where you can get away without a relationship table + indexes, similar to the one-to-many direct references approach. I’ll repeat the same caution about this approach:

This comes with some important design considerations:

1. You can’t index an array in Convex, so you won’t be able to query for the documents that reference some ID. You’d have to scan all documents and check the array in each.
2. You are limited to 8192 entries in an array, and we recommend having on the order of 10 nested elements, so you should only use this where there’s a natural limit to how many references there will be.

Directed graph without a reverse lookup

Diagram

One example when this could make sense is storing references on a user to a small number of “roles,” where you have a cap on how many roles a user can have. This is presuming that you commonly need to look up the roles for a user, but don’t need to look up users by role. And when you do want to look up by role, you’re ok using a paginated query to scan the user table (e.g. from an admin page).

1defineSchema({
2  roles: defineTable({
3    description: v.string(),
4    permissions: ...,
5  }),
6  users: defineTable({
7    roles: v.array(v.id('roles')),
8    name: v.string(),
9    ...
10  }),
11});
12

Here you can follow the direct reference approach for one-to-many to access roles for each user.

Directed graph with a reverse lookup (solving design consideration 1)

Diagram

When you want to be able to look up in both directions, and still avoid making a relation table, you could store an array of IDs on both documents. This is similar to one-to-many with direct references, except having an array on both sides instead of a single ID on one side. As an example, consider a small school where each teacher might teach a handful of subjects (e.g. “8th grade math”) and each subject might have only a handful of teachers.

1defineSchema({
2  subjects: defineTable({
3    teachers: v.array(v.id('teachers')),
4    title: v.string(),
5  }),
6  teachers: defineTable({
7    subjects: v.array(v.id('subjects')),
8    name: v.string(),
9  }),
10  ...
11});
12

As long as you keep the lists up to date (and in case it wasn’t clear, they won’t stay up to date automatically), you can easily look up a teacher’s subjects and a subject’s teachers, without having to define and use an intermediary table.

1await ctx.db.patch(subject._id, { teachers: [ teacher._id, ...subject.teachers ] });
2await ctx.db.patch(teacher._id, { subjects: [ subject._id, ...teacher.subjects ] });
3

Reminder: this should only be used in situations where the lists will be small and you’re willing to keep them in sync.

Summary

In this post we looked at how to structure various relationships. See the post on Relationship Helpers for code you can use, along with other helper functions. If you’re interested in talking about the approaches here, join us in our Discord.