InfoQ

Alex Cole discusses dynamic web apps, how serverless solutions compare to in-house stacks, and how product development changes when individual engineers can own features end-to-end.


Everyone Can Be a Full-Stack Engineer

[Relationships](https://www.youtube.com/shorts/ALW6_y79HU0) are a key part of a relational database like <QuickConvex />. 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](https://stack.convex.dev/functional-relationships-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

![image](https://cdn.sanity.io/images/ts10onj4/production/d5f932d9aec16a2718db3172743b9b80f5db6f7e-3176x1544.png?w=800)

This is the simplest relationship:

```ts
defineSchema({
  students: defineTable({
    ...
  }),
  healthRecords: defineTable({
    studentId: v.id("students")
    ...
  }),
});
```

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

```js
const student = await db.get(record.studentId);
```

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

```js
const healthRecordId = await db.insert('healthRecords', {
  studentId: student._id,
  ...
});
```

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

## Back-reference

![](https://cdn.sanity.io/images/ts10onj4/production/44225e22c177e1b2991393180a46a18c9089e402-3176x1544.png?w=800)

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

```ts
defineSchema({
  students: defineTable({
    ...
  }),
  healthRecords: defineTable({
    studentId: v.id("students")
    ...
  }).index("by_studentId", ["studentId"]),
});
```

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

```js
const record = await db.query("healthRecords")
  .withIndex("by_studentId", q => q.eq("studentId", student._id))
  .unique()
```

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](https://stack.convex.dev/functional-relationships-helpers) 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:

```ts
defineSchema({
  students: defineTable({
    ...
    healthRecordId: v.optional(v.id("healthRecords")),
  }),
  healthRecords: defineTable({
    ...
    studentId: v.id("students"),
  }),
});
```

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

```js
const studentId = await db.insert("students", {...});
const healthRecordId = await db.insert('healthRecords', {studentId, ...});
await db.patch(student._id, { healthRecordId });
//code to create both objects
```

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)

![](https://cdn.sanity.io/images/ts10onj4/production/1fdccdb7fdfd240ce605711efbf03eac4f7b6ad4-3176x3176.png?w=800)

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:

```ts
defineSchema({
  submissions: defineTable({...}),
  participants: defineTable({
    ...
    submissionIds: v.array(v.id('submissions')),
  }),
});
```

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

```js
const submissions = await Promise.all(participant.submissionIds.map(db.get));
```

See the [Relationship Helpers post](https://stack.convex.dev/functional-relationships-helpers) 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.[^1]
    
    ```js
    const participants = await db.query('participants').collect();
    for (const participant of participants) {
      if (participant.submissionIds.find(id => id.equals(submission._id))) {
        return participant;
      }
    }
    ```
    
2. You are limited to 1024 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)

![](https://cdn.sanity.io/images/ts10onj4/production/8b47eb1f3ca96bba00da5eb13063d3e446d9d6d5-3176x3176.png?w=800)

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:

```js
posts: defineTable({
  authorId: v.id("users"),
  ...
}).index('by_authorId', ['authorId']);
```

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:

```js
const posts = await db.query("posts")
  .withIndex("by_authorId", q => q.eq("authorId", author._id))
  .collect();
```

See the [Relationship Helpers](https://stack.convex.dev/functional-relationships-helpers) post for a `getManyFrom` function to codify this.

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

```js
await db.insert("posts", {..., authorId: author._id});
```

# 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)

![](https://cdn.sanity.io/images/ts10onj4/production/061207caeb5de6f6061f314381a1fe1a78d149e5-3176x3176.png?w=800)

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:

```ts
defineSchema({
  users: defineTable({
    ... 
  }),
  channels: defineTable({ 
    ...
  }),
  channelUsers: defineTable({
    channelId: v.id("channels"),
    userId: v.id("users"),
    // Other fields related to the user, channel pairing
    // E.g. notification preferences or when they were last seen
  })
    .index("by_channelId", ["channelId"]) // Get users in a channel
    .index("by_userId", ["userId"]), // Get channels for a user
});
```

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:

```js
const channelUsers = await db.query("channelUsers")
  .withIndex("by_channelId", q => q.eq("channelId", channel._id))
  .collect();
const userIds = channelUsers.map(channelUser => channelUser.userId);
const users = await Promise.all(userIds.map(db.get));
```

See the [Relationship Helpers](https://stack.convex.dev/functional-relationships-helpers) post for a `getManyVia` helper function to make this easy.

**To add a user to a channel:**

```js
await db.insert('channelUsers', { channelId, userId });
```

**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.
    
    ```js
    const channelUsers = await db.query("channelUsers")
      .withIndex("by_channelId", q => q.eq("channelId", channel._id))
      .collect();
    const isInChannel = !!channelUsers.find(cu => cu.userId.equals(user._id);
    ```
    
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.
    
    ```js
    const channelUsers = await db.query("channelUsers")
      .withIndex("by_userId", q => q.eq("userId", user._id))
      .collect();
    const isInChannel = !!channelUsers.find(cu => 
    cu.channelId.equals(channel._id);
    ```
    
3. To look it up without iterating an array, you could have one of the indexes contain both fields:
    
    ```js
    .index("by_channelId", ["channelId"])
    .index("by_userId_channelId", ["userId", "channelId"]),
    ```
    
    This would allow you to check directly:
    
    ```js
    await db.query("channelUsers")
      .withIndex("by_userId_channelId", q =>
        q.eq("userId", user._id).eq("channelId", channel._id)
      )
      .unique(); // null if it's not found
    ```
    

## 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 1024 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

![](https://cdn.sanity.io/images/ts10onj4/production/9017503746dc19842ec8dfdcd0713bd42e9a1880-3176x3176.png?w=800)

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).

```jsx
defineSchema({
  roles: defineTable({
    description: v.string(),
    permissions: ...,
  }),
  users: defineTable({
    roles: v.array(v.id('roles')),
    name: v.string(),
    ...
  }),
});
```

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)

![](https://cdn.sanity.io/images/ts10onj4/production/9c8aa1f23fe87f0de1b02ce43757d3dea85b5cc2-3180x3180.png?w=800)

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.

```ts
defineSchema({
  subjects: defineTable({
    teachers: v.array(v.id('teachers')),
    title: v.string(),
  }),
  teachers: defineTable({
    subjects: v.array(v.id('subjects')),
    name: v.string(),
  }),
  ...
});
```

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.

```js
await db.patch(subject._id, { teachers: [ teacher._id, ...subject.teachers ] });
await db.patch(teacher._id, { subjects: [ subject._id, ...teacher.subjects ] });
```

**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](https://stack.convex.dev/functional-relationships-helpers) for [code](https://github.com/get-convex/convex-helpers/blob/main/convex/lib/relationships.ts) you can use, along with other helper functions. If you’re interested in talking about the approaches here, join us in [our Discord](https://convex.dev/community). 

[^1]: **Note:** To solve for (1), you could also store the `participantId` in the `submissions` table, so you have bi-directional references. This is similar to the one-to-one “Why not both” section - and comes with the same caution that you should make sure to keep them in sync.

In this post we’ll look at some patterns for structuring relationships in the Convex database.

Relationship Structures: Let's Talk About Schemas

There are as many ways to migrate data as there are databases, but here’s some basic information to set the stage.

# What is a migration?

Migrations are the process of changing the shape of data in your database. They often are comprised of **schema** changes and **data** changes. 

The **schema** changes capture the structure of your tables, i.e. the field names and types. With other databases, you need to explicitly tell it to add or change fields (columns).  For <QuickConvex />, you do not need to explain what changed, you merely change `schema.ts` into your desired shape.

The **data** changes are how to change your data from the existing schema into the desired one. If you’re adding a new `optional` field, it will be unset by default. However, if you are changing a type or removing a field, you’ll need to change the data to match your schema. In Convex, these take the form of writing a `mutation` script. See [this post](https://stack.convex.dev/migrating-data-with-mutations) for tips.

# When does a migration run?

## **Offline migrations**

For many businesses, especially those using SQL-based databases, the easiest option is to stop serving traffic, run the migration, and then start the code that references the new schema. This explains banners you see on websites alerting you to upcoming scheduled downtime. This is called an “offline” migration since the application is not serving traffic during the migration. With some advanced techniques, you can minimize the downtime. However, it still has some risks:

- If your new code has bugs, it is difficult to roll back to the previous database schema without losing data. If your old code doesn’t know about your new fields, you are left scrambling to patch your new code.
- If your migration fails partway through, you have to resolve the migration before the new code can be deployed. Deploying code that changes the database schema becomes a high-stress operation.

To do this with Convex, you could:

1. Define a mutation that changes a batch of your data. 
2. Configure your code to stop serving requests during the migration. This could happen via a code deployment, or by flipping a switch as discussed in a previous post on [feature gating](https://stack.convex.dev/feature-gating).
3. As quickly as possible, stop serving requests, run the migration, then deploy the code that references the new schema. See [here](https://stack.convex.dev/migrating-data-with-mutations) for how to migrate batches of data using Convex mutations.

## **Online migrations**

“Online” migrations, by comparison, allow the database to continue serving requests while you asynchronously update the data. This is preferable to avoid downtime but comes with added complexity to the developer. 

- You may query documents where only some of the documents have been migrated. You have to implement code to also handle the old schema in the interim.
- For SQL migrations using deferred index creation, you can’t deploy code using newly-defined indexes until they have been created, requiring two deploys instead of just one.
- Any rules or constraints you define may become more difficult to enforce and reason about in the interim. See [this post](https://www.cybertec-postgresql.com/en/triggers-to-enforce-constraints/) for an example of subtle gotchas while trying to implement a seemingly simple rule in Postgres.

While this seems daunting, this becomes a requirement for large systems where downtime is not acceptable. In fact, once a database gets large enough, many migrations can only be run asynchronously. I worked for a company with over 100M users where a deployment got stuck because a migration that had been accidentally configured to run synchronously kept timing out in a loop. Because of the company policy of never rolling back migrations, we ended up incurring downtime, increasing the timeout, and failing a lot of traffic so the migration could complete. While synchronous/offline/all-at-once migrations are convenient when you’re starting a project, I’d encourage you to use online migrations whenever possible. 

So how do we go about them safely?

# Best practices for changing data asynchronously

To manage these challenges, there are some best practices and patterns you can adopt to make it easier. Thankfully, when [schema validation](https://docs.convex.dev/database/schemas#schema-validation) is enabled, Convex guides you to do the right thing.

1. **Create new columns or fields, rather than changing their type.** If you rename or change a column’s type, you have to change all of your data between when your old and new code can execute safely. 
    
    **Convex will not let you change the type to something that doesn’t conform to the data in production.** Instead, you’ll need to do the (safe) transition of changing the data to a `v.union` of the old and new types. Once the data is all the new type, you’ll be able to change the schema to only be the new type. If you’re using typescript, this will help you write code to support both formats during the migration period, as the types will automatically be generated as a union.
    
2. **Don’t delete data.** Unless you’re really strapped for space, don’t risk losing critical data by dropping columns (or fields, in document databases). It sounds obvious, but it’s all too tempting to drop deprecated columns. Instead, mark the column as deprecated in code until the data has been unreferenced for a while and you’ve ensured the data is redundant.
    
    **Convex will not let you remove a field from a schema if that field still has data in the database.** You can mark a column as deprecated by using `v.optional` to allow the field to be unset, and add a comment in the schema declaration to help other developers understand why that field existed and encourage them to not use it. If you do want to delete data, you can set it to optional, run a migration to remove the field, then remove the field from the table.
    
3. **While the migration is happening, handle both the new and old data formats.** Deciding whether to read or write the old or new format is discussed below.
    
    **When using TypeScript with Convex, the types will conform to the data in the database**, as they are generated from the schema, which will be rejected during deployment if it doesn’t conform to the data.
    
4. **When possible, push changes to the schema separately from changes to the code.** By pushing a change to allow an optional new field in the schema before adding code to write or rely on the new field, you will be able to roll back or revert the new code in case of a bug, knowing your old code and schema accommodate the optional field.
5. **Once the migration is done, clean up the code to only reference the new format.** Keep in mind, the migration isn’t done until there are no more readers or writers of the old format and every row has been mutated.

# Dual read vs. dual write

To safely handle the intermediate data formats, you can dual-read or dual-write. In both cases, the migration requires all clients to be writing the new format and all old documents to have been processed. An optional step (0) here is to first deploy a schema that combines the old and new formats, with as little code changed as possible, to give you a safe point to roll back to, in case something goes wrong.

## Dual writes (preferred):

1. Deploy code that starts writing both formats, but still reads the old format.
2. Migrate the data asynchronously.
3. Update the code to read the new format, while still writing the old format.
4. Update the code to only read and write the new format.

This is preferable because you can gracefully roll back to older code if the code for the new format has errors. 

## Dual reads:

1. Deploy code that reads both formats, preferring the new format if present.
2. Deploy code that only writes the new format.
3. Migrate the data asynchronously.
4. Update the code to only read the new format.

I’d use this approach if I don’t want to write into two locations, such as when I’m worried that having two copies of the data may lead to inconsistencies and want to ensure there’s only a single source of truth. Another upside to this approach is that, if the migration requires writing to a new table, you temporarily double your reads versus temporarily doubling your writes, which can be more expensive if the data isn’t all in one document. 

However, this approach makes it hard to roll back from stage (2) to (1) if there is a bug related to using the new format. In phase (1) you deployed code that didn’t exercise the new format branch until the new code was written. 

## Why not both?

You can actually do both of these strategies at the same time:

1. Deploy code that writes both formats and reads both formats.
2. Migrate the data asynchronously.
3. Update the code to only read and write the new format.

This is more work and code branches to maintain, but it allows you to start reading the new format for newly updated rows before doing the async migration, while still having the fallback of the old format.

# Summary

We covered migrations at a high level and walked through some best practices for doing them. To see how to implement migrations in Convex, check out [this post](https://stack.convex.dev/migrating-data-with-mutations).

[^1]: “Backfill” here means filling in old data, as opposed to “Livefill” which updates new data as it’s being inserted or updated. You generally want to start by updating new data, then go back and update the bounded amount of old data. If you finish a backfill before you’ve started updating the new data, you could end up with some data in the old format.

There are as many ways to migrate data as there are databases, but here’s some basic information to set the stage.

Intro to Migrations

I’ve been using the Dashboard a lot while developing my own apps, so I asked Arnold, the engineer behind the Dashboard, to put together some tips for using it effectively. I hope you find it as useful as I do!

So you’ve got your <QuickConvex /> app set up, eh? Maybe you’ve worked through the [tutorial](https://docs.convex.dev/tutorial/welcome-to-convex) or tried out one of our [demo apps](https://github.com/get-convex/convex-demos). Perhaps you’ve even built your own app already!

Now that you’re familiar with Convex, I’d like to show you some of the tools you can use in the dashboard to level up your development productivity.

## 1. Iterate on and test new functions

To iterate quickly, it’s nice to be able to isolate your code and test backend functionality on its own, without needing to integrate into the frontend. Good news — you can do this straight from the Convex dashboard’s [Functions page](https://docs.convex.dev/using/dashboard#testing-functions). To get started, click the “Run function” button found near your function’s implementation. From there, you can subscribe to live query results and run one-off actions and mutations. If you’re running `npx convex dev` in your code repo, any edits to the functions will sync automatically to your dev instance, so you can quickly edit and re-run your functions as you iterate.

<QuickVid src="https://static.convex.dev/stack/5-dashboard-tricks/run-functions.webm" caption="An example of testing a “list messages” and “send message” Convex function using the dashboard" />

## 2. Seed and prototype data

Before you’ve gotten your functions fully wired up, you can use the dashboard to create tables and documents.

Convex provides two types of editors for editing documents — the default editor provides a simpler view of your data, broken down by field. Clicking the “Edit as JavaScript” button allows you to edit data using the same syntax you would in JavaScript code.

<QuickVid src="https://static.convex.dev/stack/5-dashboard-tricks/prototyping.webm" caption="An example of adding a `channels` table to my chat app project, and associating an existing message with a new channel." />

## 3. Search and destroy — data filters

Got a heap of data to sift through in your tables? Dashboard filters have got you covered. Find this handy button on the [Data page](https://docs.convex.dev/using/dashboard#filtering-documents), and use it to narrow down the results shown to just the documents you’re looking for.

<QuickVid src="https://static.convex.dev/stack/5-dashboard-tricks/filters.webm" caption="An example of filtering a list of chat messages by channel ID" />

## 4. Upload files

Did you know that in addition to documents, Convex allows you to store and retrieve files, such as images and videos? Well now ya do — you can [upload files programmatically](https://docs.convex.dev/using/file-storage) in your Convex functions, or upload individual files using the [File storage tab on the Data page](https://docs.convex.dev/using/dashboard#file-storage).

<QuickVid src="https://static.convex.dev/stack/5-dashboard-tricks/upload-file.webm" caption="Uploading a file to convex" />

## 5. Edit your app’s configuration live

Since Convex queries are [fully reactive](https://docs.convex.dev/understanding/convex-fundamentals/functions#reactivity), updating your data in the dashboard will inform all clients subscribed to the related data that their queries should be re-run. See [this post](https://stack.convex.dev/feature-gating) on using Convex to implement a basic kill switch.

Did you know you can use the Convex Dashboard to run functions, enter seed data, filter & delete data, upload files, and more?

5 Dashboard Tricks

# An old but a great idea

With new backends-as-a-service like Convex, we’re deciding our schemas when we feel like it,  storing documents instead of flat columns, and updating data reactively with ease. These great new patterns for storing and syncing data may make it seem like every concept from legacy databases is about to be tossed out the window.

But some ideas are evergreen. Like today’s topic, ACID. ACID is awesome.

But what is it, exactly? Here’s a Wikipedia summary:

> **ACID** is a set of properties of [database transactions](https://en.wikipedia.org/wiki/Database_transaction) intended to guarantee data validity despite errors, power failures, and other mishaps.
> 

In this article, we’ll focus on the most interesting two of the four[^1] properties: **I**solation and **A**tomicity. 

# Inside Isolation

Consider a simple transaction which deducts $3 from a bank account:

```tsx
var balance = readBalance();
balance -= 3; // bought a box of milk duds
writeBalance(balance);
```

Seems straightforward enough? But without isolation guarantees, the following could happen when two transactions run concurrently:

| Step | TX1 operation | TX1 var | TX1 db | TX2 operation | TX2 var | TX2 db |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | var balance = readBalance(); | 10 | 10 |  | - | - |
| 2 |  | 10 | 10 | var balance = readBalance(); | 10 | 10 |
| 3 | balance -= 3; | 7 | 10 |  | 10 | 10 |
| 4 |  | 7 | 10 | balance -= 3; | 7 | 10 |
| 5 | writeBalance(balance); | 7 | 7 |  | 7 | 7 |
| 6 |  | 7 | 7 | writeBalance(balance); | 7 | 7 |

Each transaction individually did something reasonable. But because they ran at the same time, effectively someone got a free box of milk duds! Three dollars just disappeared, since ($10 - ($3 * 2)) should leave only $4 left in the account.

The problem here is the transactions aren’t strongly *isolated* from each other. If you’re familiar with multithreaded programming, you may be thinking right now: “oh, we just need a lock around these transactions!” And in fact, that is how many databases solve this problem, a method often called *pessimistic locking*. Another valid approach, which happens to be the one that Convex uses, is [optimistic concurrency control](https://docs.convex.dev/understanding/deep-dive/occ).

But whatever method your database uses, it really should give you strong transactional isolation[^2] so you can confidently write your transactions without worrying about the complications of data races like these.

# Isolation among friends

Granted, you’re probably not building an online bank. But nearly any application you can imagine benefits from transactional isolation!

Take [Let’s Go](https://github.com/get-convex/lets-go), for example, a social gathering app built on top of Convex. In Let’s Go, you can create an event you plan, assign it a fixed number of empty seats, and then invite your whole friend group to join you if they’re free.

As an example, Ruchi might have two extra tickets to a hockey game. Let’s Go allows her to create a “Hockey Night with Ruchi” event with two seats available. Then she can invite all her friends and the first two that are interested and available can grab those seats and go watch the game with Ruchi.

But what if this particular hockey team is on a *huge* winning streak right now and attending the games is a hot ticket? Far more than two of Ruchi’s friends may all jump at the opportunity to tag along.

In this circumstance, we’d risk encountering a bug similar to the bank account one outlined above. If we experience a data race between checking if there is still a seat available and claiming that seat, Let’s Go could erroneously promise three of Ruchi’s friends a night out even though there are only two tickets available.

Let’s walk through the Convex mutation function that represents [the transaction that has an attendee join an event](https://github.com/get-convex/lets-go/blob/main/convex/createAttendee.ts).

First, we grab the event from the database:

```tsx
async ({ db }, user, { eventId }: CreateAttendeeInput) => {
    const event = await db.get(eventId);
    if (!event) {
        return null;
    }
```

Next, we check to see that the number of attendees for this event is fewer than the number of free seats the event creator specified. Otherwise, the transaction can’t continue!

```tsx
		// Ensure there's still an open slot.
    const attendees = await db
        .query("attendees")
        .filter((q) => q.eq(q.field("eventId"), eventId))
        .collect();
    if (attendees.length >= event?.slots) {
        return null;
    }
```

This function has an additional check… it makes sure you’re not *already* one of the attendees. Otherwise, one of Ruchi’s friends could claim both tickets:

```tsx
    // Prevent inserting duplicate attendees.
    const existingAttendee = await db
        .query("attendees")
        .filter((q) =>
        q.and(
            q.eq(q.field("eventId"), eventId),
            q.eq(q.field("userId"), user._id)
        )
        )
        .first();
    if (existingAttendee) {
        return null;
    }
```

Finally, if *everything* still looks good, we add them to the attendees table for this event.

```tsx
    db.insert("attendees", {
        userId: user._id,
        eventId,
    });

    return eventId;
}
```

Hopefully, the parallels to our banking example are clear. Strong transaction isolation[^2] is necessary to ensure all of these critical validations remain true when we finally commit the attendee to the event. Otherwise, our app may place friendships in peril!

# Atomic Scorekeeping

Isolation is great, but what about *Atomicity*? Let’s begin to understand atomicity’s utility by contrasting it with isolation.

In transactions, isolation only matters when you execute:

- At least one read. If you don’t base your write on a read, then you’re not exposed to a data race.
- At least one write. If you don’t write anything in your transaction, none of this matters!

In contrast, atomicity only matters when your transaction contains:

- **Two** or more writes

Why two or more? Because atomicity is about ensuring that transactions with multiple writes either entirely succeed or fail. Stated another way, either all writes commit, or none do.

Let’s see why this matters in a baseline banking context again by modeling a transaction transferring $5 from account *A* to account *B*.

```tsx
var aAmount = readFromA();
var bAmount = readFromB();
writeToA(aAmount - 5);
// CRASH???
writeToB(bAmount + 5);
```

Imagine one of Ruchi’s friends, absolutely livid about an unfulfilled-hockey-seat, unplugged our bank’s non-ACID database server during this transaction. If our transaction stopped running after taking the $5 out of account *A*, but before putting it into account *B*, the money in transit would just disappear out of our system!

ACID databases with the appropriate **atomicity** ensure that this never happens. Upon failure, the entire transaction would be rolled back, and account A would still have the $5.

But when the stakes are even greater than money? When we involve victory, pride, and competitive Wordle?

[Fast5](https://fast5.live) is a head-to-head Wordle-style game built on Convex. You compete against a friend to see who can guess a series of five words the fastest. And of course, we’re keeping score.

Specifically, when the game is over, the user account with the higher score gets credited with a win, and the loser's account... well, you get the idea.

This might sound too simple to need ACID. But imagine each player’s `User` object that holds their win/loss totals is a separate record in our database. Just as in our bank transfer transaction, we need to ensure the win increment and loss increment both succeed so we don’t “lose” a win or a loss.

Here’s how we do [win/loss accounting in Fast5](https://github.com/get-convex/fast5/blob/main/convex/common.ts#L72):

```tsx
export async function recordGameStats(db: DatabaseWriter, game: any) {
  var user1 = await db.get(game.user1);
  var user2 = await db.get(game.user2);
  if (game.winner === 1) {
    // user 1 wins, user 2 loses
    user1.wins += 1;
    user2.losses += 1;
  } else if (game.winner === 2) {
    // user 2 wins, user 1 loses
    user2.wins += 1;
    user1.losses += 1;
  } else if (game.winner === 3) {
    // tie!
    user1.ties += 1;
    user2.ties += 1;
  } else {
    throw 'unknown winner code';
  }
  // write out both user records with updated win/loss/tie info
  db.patch(user1._id, user1);
  // what happens if we crash here? Need atomicity!
  db.patch(user2._id, user2);
} 
```

In short, this transaction calls `db.get` to retrieve both users’ records, adjusts each win/loss/draw counts according to the outcome of the game, and then updates both records with the new tallies using `db.patch`. And without *********atomicity*********, we’d “lose” a win or a loss if we crashed after only updating one record.

But since this is Convex, this never happens!

# ACID keeps things basic

If you reflect back on the code in this article, there’s nothing special about it. In fact, we’ve talked a lot about these subtle and crucial concepts, but then the *actual code* was pretty unexciting and obvious.

And that’s precisely why ACID is so key! When the underlying system supports ACID semantics, the developer is free to write code the intuitive way and ignore the complexities of concurrency and failure.

So keep taking a regular dose of ACID!

[^1]:While we’re not spending much time on them, ACID’s **C**onsistency amounts to more or less “follow your Atomicity + Isolation rules uniformly in every context”, and **D**urability means “don’t lose committed data.”

[^2]:Convex provides **serializable** isolation, which is the strictest and safest isolation level. Other databases often provide weaker isolation levels by default (sometimes called “read committed”, “read uncommited”, “repeatable read”, etc). Check carefully if you want to be absolutely sure your application is correct!

When your database supports ACID semantics, you're free to write code the intuitive way and ignore the complexities of concurrency and failure.

Don't Drop ACID

Many backends support paginating over large datasets. Some backends reactively push updates to the client when the data changes, but few support reactivity and pagination at the same time.

Convex is the reactive backend-as-a-service for modern application developers. Our reactive pagination allows developers to build apps with “Load More” buttons and “infinite scroll” UIs without giving up the automatic reactivity they love.

Reactive pagination presents some unique technical challenges. What happens if items are added or removed from a page? Will pages overlap? Will items be missed or rendered twice?

This post describes Convex’s solutions to these problems. But first, let’s start with the basics.

## Offset Pagination

The simplest form of pagination is *offset pagination*. In this model, clients request results in incremental pages based on:

1. The number of items to load in this page.
2. An offset to begin this page at.

For example, if we wanted to paginate a large list of fruits, we could load the first 5 items with an API call like `getFruits({ offset: 0, numItems: 5})`:

```json
[
  "Apple",
  "Banana",
  "Currant",
  "Durian",
  "Elderberry"
]
```

Then to load the next 5 we could call `getFruits({ offset: 5, numItems: 5})`:

```json
[
  "Fig",
  "Grapefruit",
  "Huckleberry",
  "Jackfruit",
  "Kiwi"
 ]
```

One problem with this model is that it performs poorly if items are added or removed between API calls. For example, say that `"Elderberry"` is removed from the list after the first API call. Then `"Grapefruit"` will become the sixth item in the list and the start of our second page. 

Now our second page loaded with `getFruits({ offset: 5, numItems: 5})` would become:

```json
[
  "Grapefruit",
  "Huckleberry",
  "Jackfruit",
  "Kiwi",
  "Lemon"
 ]
```

Putting these pages together, the client would see a list like:

```
       ┏ Apple
       ┃ Banana
page 1 ┫ Currant
       ┃ Durian
       ┗ Elderberry ← Removed after page 1 is loaded
         Fig
       ┏ Grapefruit
       ┃ Huckleberry
page 2 ┫ Jackfruit
       ┃ Kiwi
       ┗ Lemon
         ...
```

**Note that `"Fig"` isn’t in either page!** It was the 6th item when we loaded page 1, so it wasn’t included. By the time we loaded the 2nd page, it moved to the 5th position so it wasn’t included on page 2 either.

These types of correctness issue are a reason that many apps use cursor-based pagination instead.

## Cursor-based Pagination

The standard approach to pagination uses *cursors* to identify where a page begins and ends within the list. Cursors are opaque strings that encode positions in a list.

Every time a client requests a page of items, they receive a cursor to continue the pagination along with their results. For example calling `getFruits({ numItems: 5 })` could return:

```json
{
  page: [
    "Apple",
    "Banana",
    "Currant",
    "Durian",
    "Elderberry"
  ],
  continueCursor: "u1oU8i23WMATVwA2CneZ"
}
```

If the client wants to load the next page, they can include the continuation cursor from the last page in their request to continue where they left off. In this example they would call `getFruits({ numItems: 5, cursor: "u1oU8i23WMATVwA2CneZ" })` and get:

```json
{
  page: [
    "Fig",
    "Grapefruit",
    "Huckleberry",
    "Jackfruit",
    "Kiwi"
  ],
  continueCursor: "y45NqH6EAwknDtdeF7Y7"
}
```

Client-side these can be concatenated together to form a single list. Visually, this looks like

```
       ┏ Apple
       ┃ Banana
page 1 ┃ Currant
       ┃ Durian
       ┗ Elderberry
       ┏ Fig
       ┃ Grapefruit
page 2 ┃ Huckleberry
       ┃ Jackfruit
       ┗ Kiwi
         ...
```

## The Challenge of Reactivity

Most paginated apps are nonreactive; if you load a page of items, the client won’t notice if those items change. In Convex, all queries are automatically reactive. If the underlying data changes, Convex automatically reruns the query function and pushes a new result to the client. Naively paginating in this model can cause some interesting bugs to show up.

To see the problem, **imagine that a user inserts `"Coconut"` into the list** alphabetically after `"Banana"`. 

In our example, the client loaded 2 pages by calling `getFruits({ numItems: 5 })` and `getFruits({ numItems: 5, cursor: "u1oU8i23WMATVwA2CneZ" })`. The first 5 elements of the list have changed, so `getFruits({ numItems: 5 })` will recompute and automatically push the new results to the client. Now it returns:

```json
{
  page: [
    "Apple",
    "Banana",
    "Coconut",
    "Currant",
    "Durian"
  ],
  continueCursor: "6FybVkA6X0abzz0XCVP3"
}
```

Now `“Durian”` is the 5th item of the list and the cursor `"6FybVkA6X0abzz0XCVP3"` refers to the position after `“Durian”`.

The second query for `getFruits({ numItems: 5, cursor: "u1oU8i23WMATVwA2CneZ" })` hasn’t changed because the 5 items after `"u1oU8i23WMATVwA2CneZ"` are the same as before.

Now, our list looks like this:

```

       ┏ Apple
       ┃ Banana
page 1 ┫ Coconut ← New item inserted
       ┃ Currant
       ┗ Durian
         Elderberry
       ┏ Fig
       ┃ Grapefruit
page 2 ┫ Huckleberry
       ┃ Jackfruit
       ┗ Kiwi
         ...
```

**Now `“Elderberry”` is between our two pages!** It dropped off of page 1 when we added the new item but page 2 still begins at the same spot. From a user’s perspective, they would no longer see `“Elderberry”` in UI!

Similarly, if we had instead removed an item from the list (say `"Currant"`) the two pages would overlap:

```
       ┏   Apple
       ┃   Banana
page 1 ┫   Durian
       ┃   Elderberry
       ┗ ┏ Fig
         ┃ Grapefruit
page 2 ━━┫ Huckleberry
         ┃ Jackfruit
         ┗ Kiwi
           ...
```

This also would create a confusing user experience. Why is `"Fig"` listed twice?

Many apps solve these problems with simple heuristics like “remove the duplicates and hope the data doesn’t change too much while paginating.” **At Convex, we believe that correctness should come for free**, so this won’t fly.

## Making it Correct

To ensure that the concatenated list never has duplicates or misses items, we have to do something a little different. The key insight is to **pin the endpoints of each page.**

In this example we initially had that:

Page 1: 

- Start: beginning of the list
- End: cursor `"u1oU8i23WMATVwA2CneZ"`

Page 2:

- Start: cursor  `"u1oU8i23WMATVwA2CneZ"`
- End: cursor `"y45NqH6EAwknDtdeF7Y7"`

There was a gap between the pages because the end of page 1 moved to no longer match the start of page 2. If we can instead keep these endpoints constant, this won’t be a problem.

In our original example, the start of each page was a constant (it’s the `cursor` argument to the function), but the end of the page varied based on what position was 5 elements after the start.

In this new model, **Convex will “remember” the end of each page** as well. Whenever the query is recomputed, Convex ignores the `numItems` parameter and instead returns all items until the end cursor. This switches each page from a limit query (fetch 5 items) into a range query (fetch between cursors).

In our example, if we add `"Coconut"` to the list, page 1 would recompute and become:

```json
{
  page: [
    "Apple",
    "Banana",
    "Coconut",
    "Currant",
    "Durian",
    "Elderberry"
  ],
  continueCursor: "u1oU8i23WMATVwA2CneZ"
}
```

Now page 1 has 6 items! It initially had 5, but now it grew to include a 6th. Putting this together with page 2, we can see that our pages continue to have no gaps or overlap:

```
       ┏ Apple
       ┃ Banana
page 1 ┃ Coconut ← New item inserted
       ┃ Currant
       ┃ Durian
       ┗ Elderberry
       ┏ Fig
       ┃ Grapefruit
page 2 ┃ Huckleberry
       ┃ Jackfruit
       ┗ Kiwi
         ...
```

Similarly, if we instead remove `"Currant"` from the list, page 1 will keep its endpoint and shrink to 4 items:

```
       ┏ Apple
       ┃ Banana
page 1 ┃ Durian
       ┗ Elderberry
       ┏ Fig
       ┃ Grapefruit
page 2 ┃ Huckleberry
       ┃ Jackfruit
       ┗ Kiwi
         ...
```

No matter what happens, the user will see a valid prefix of the list!

Convex implements this logic to pin the endpoints of each page completely internally. When you load a paginated query, Convex internally stores the endpoint of the page. Any time the query function is rerun, Convex uses that endpoint; no additional configuration needed!

## Using it!

Using Convex’s reactive pagination is a breeze. To write a paginated query function, just call `.paginate` on the query:

```tsx
export default query(async ({ db }, { paginationOpts }) => {
  return await db.query("fruits").paginate(paginationOpts);
});
```

Then, you can load pages of this query with our `usePaginatedQuery` React hook:

```tsx
const { results, status, loadMore } = usePaginatedQuery("listFruits", {}, {
  initialNumItems: 5,
});
```

To learn more about Convex pagination [check out the docs](https://docs.convex.dev/using/pagination). Convex handles the complexity of making it correct and reactive so you don’t have to!

Paginating over large datasets is tricky when the data is changing. Naive approaches result in missing or duplicated data. The trick is to rewrite limit queries (fetch 5 items) into range queries (fetch between cursors). Convex handles this automatically!

Fully Reactive Pagination

*Convex added index support in our [0.1.5 release](https://blog.convex.dev/announcing-convex-0-1-5/). This post is taken from our [Introduction to Indexes and Query Performance](https://docs.convex.dev/understanding/indexes-and-query-perf) documentation.*

How do I ensure my Convex [database queries](https://docs.convex.dev/using/database-queries) are fast and efficient? When should I define an [index](https://docs.convex.dev/using/indexes)? What is an index?

This document explains how you should think about query performance in Convex by describing a simplified model of how queries and indexes function.

If you already have a strong understanding of database queries and indexes you can jump straight to the reference documentation instead:

- [Database Queries](https://docs.convex.dev/using/database-queries)
- [Indexes](https://docs.convex.dev/using/indexes)

## **A Library of Documents**

You can imagine that Convex is a physical library storing documents as physical books. In this world, every time you add a document to Convex with `[db.insert("books", {...})](https://docs.convex.dev/api/interfaces/server.DatabaseWriter#insert)` a librarian places the book on a shelf.

By default, Convex organizes your documents in the order they were inserted. You can imagine the librarian inserting documents left to right on a shelf.

If you run a query to find the first book like:

```js
const firstBook = await db.query("books").first();
```

then the librarian could start at the left edge of the shelf and find the first book. This is an extremely fast query because the librarian only has to look at a single book to get the result.

Similarly, if we want to retrieve the last book that was inserted we could instead do:

```js
const lastBook = await db.query("books").order("desc").first();
```

This is the same query but we've swapped the order to descending. In the library, this means that the librarian will start on the right edge of the shelf and scan right-to-left. The librarian still only needs to look at a single book to determine the result so this query is also extremely fast.

## **Full Table Scans**

Now imagine that someone shows up at the library and asks "what books do you have by Jane Austen?"

This could be expressed as:

```js
const books = await db
  .query("books")
  .filter(q => q.eq(q.field("author"), "Jane Austen"))
  .collect();
```

This query is saying "look through all of the books, left-to-right, and collect the ones where the `author` field is Jane Austen." To do this the librarian will need to look through the entire shelf and check the author of every book.

This query is a *full table scan* because it requires Convex to look at every document in the table. The performance of this query is based on the number of books in the library.

If your Convex table has a small number of documents, this is fine! Full table scans should still be fast if there are a few hundred documents, but if the table has many thousands of documents these queries will become slow.

In the library analogy, this kind of query is fine if the library has a single shelf. As the library expands into a bookcase with many shelves or many bookcases, this approach becomes infeasible.

## **Card Catalogs**

How can we more efficiently find books given an author?

One option is to re-sort the entire library by `author`. This will solve our immediate problem but now our original queries for `firstBook` and `lastBook` would become full table scans because we'd need to examine every book to see which was inserted first/last.

Another option is to duplicate the entire library. We could purchase 2 copies of every book and put them on 2 separate shelves: one shelf sorted by insertion time and another sorted by author. This would work, but it's expensive. We now need twice as much space for our library.

A better option is to build an *index* on `author`. In the library, we could use an old-school [card catalog](https://en.wikipedia.org/wiki/Library_catalog) to organize the books by author. The idea here is that the librarian will write an index card for each book that contains:

- The book's author
- The location of the book on the shelves

These index cards will be sorted by author and live in a separate organizer from the shelves that hold the books. The card catalog should stay small because it only has an index card per book (not the entire text of the book).

When a patron asks for "books by Jane Austen", the librarian can now:

1. Go to the card catalog and quickly find all of the cards for "Jane Austen".
2. For each card, go and find the book on the shelf.

This is quite fast because the librarian can quickly find the index cards for Jane Austen. It's still a little bit of work to find the book for each card but the number of index cards is small so this is quite fast.

## **Indexes**

Database indexes work based on the same concept! With Convex you can define an *index* with:

```js
import { defineSchema, defineTable } from "convex-dev/schema";
import { v } from "convex/values";

export default defineSchema({
  books: defineTable({
    author: v.string(),
    title: v.string(),
    text: v.string(),
  }).index("by_author", ["author"]),
});
```

then Convex will create a new index called `by_author` on `author`. This means that your `books` table will now have an additional data structure that is sorted by the `author` field.

You can query this index with:

```js
const austenBooks = await db
  .query("books")
  .withIndex("by_author", q => q.eq("author", "Jane Austen"))
  .collect();
```

This query instructs Convex to go to the `by_author` index and find all the entries where `doc.author === "Jane Austen"`. Because the index is sorted by `author`, this is a very efficient operation. This means that Convex can execute this query in the same manner that the librarian can:

1. Find the range of the index with entries for Jane Austen.
2. For each entry in that range, get the corresponding document.

The performance of this query is based on the number of documents where `doc.author === "Jane Austen"` which should be quite small. We've dramatically sped up the query!

## **Backfilling and Maintaining Indexes**

One interesting detail to think about is the work needed to create this new structure. In the library, the librarian must go through every book on the shelf and put a new index card for each one in the card catalog sorted by author. Only after that can the librarian trust that the card catalog will give it correct results.

The same is true for Convex indexes! When you define a new index, the first time you run `npx convex push` Convex will need to loop through all of your documents and index each one. This is why the first push after the creation of a new index will be slightly slower than normal; Convex has to do a bit of work for each document in your table.

Similarly, even after an index is defined, Convex will have to do a bit of extra work to keep this index up to date as the data changes. Every time a document is inserted, updated, or deleted in an indexed table, Convex will also update its index entry. This is analogous to a librarian creating new index cards for new books as they add them to the library.

If you are defining a few indexes there is no need to worry about the maintenance cost. As you define more indexes, the cost to maintain them grows because every `insert` needs to update every index. This is why Convex has a limit of 32 indexes per table. In practice most applications define a handle of indexes per table to make their important queries efficient.

## **Indexing Multiple Fields**

Now imagine that a patron shows up at the library and would like to check out *Foundation* by Isaac Asimov. Given our index on `author`, we can write a query that uses the index to find all the books by Isaac Asimov and then examines the title of each book to see if it's *Foundation*.

```js
const foundation = await db
  .query("books")
  .withIndex("by_author", q => q.eq("author", "Isaac Asimov"))
  .filter(q => q.eq(q.field("title"), "Foundation"))
  .unique();
```

This query describes how a librarian might execute the query. The librarian will use the card catalog to find all of the index cards for Isaac Asimov's books. The cards themselves don't have the title of the book so the librarian will need to find every Asimov book on the shelves and look at its title to find the one named *Foundation*. Lastly, this query ends with `[.unique](https://docs.convex.dev/api/interfaces/server.Query#unique)` because we expect there to be exactly one result.

This query demonstrates the difference between `[withIndex](https://docs.convex.dev/database/indexes/#querying-documents-using-indexes)` and `[filter](https://docs.convex.dev/database/reading-data#filtering)`. `withIndex` only allows you to restrict your query based on the index. You can only do operations that the index can do efficiently like finding all documents with a given author.

`filter` on the other hand allows you to write arbitrary, complex expressions but it won't be run using the index. Instead, `filter` expressions will be evaluated on every document in the range.

Given all of this, we can conclude that **the performance of indexed queries is based on how many documents are in the range**. In this case, the performance is based on the number of Isaac Asimov books because the librarian will need to look at each one to examine its title.

Unfortunately, Isaac Asimov wrote [a lot of books](https://en.wikipedia.org/wiki/Isaac_Asimov_bibliography_(alphabetical)). Realistically even with 500+ books, this will be fast enough on Convex with the existing index, but let's consider how we could improve it anyway.

One approach is to build a separate `by_title` index on `title`. This could let us swap the work we do in `.filter` and `.withIndex` to instead be:

```js
const foundation = await db
  .query("books")
  .withIndex("by_title", q => q.eq("title", "Foundation"))
  .filter(q => q.eq(q.field("author"), "Isaac Asimov"))
  .unique();
```

In this query, we're efficiently using the index to find all the books called *Foundation* and then filtering through to find the one by Isaac Asimov.

This is okay, but we're still at risk of having a slow query because too many books have a title of *Foundation*. An even better approach could be to build a *compound* index that indexes both `author` and `title`. Compound indexes are indexes on an ordered list of fields.

```js
import { defineSchema, defineTable } from "convex-dev/schema";
import { v } from "convex/values";

export default defineSchema({
  books: defineTable({
    author: v.string(),
    title: v.string(),
    text: v.string(),
  }).index("by_author_title", ["author", "title"]),
});
```

In this index, books are sorted first by the author and then within each author by title. This means that a librarian can use the index to jump to the Isaac Asimov section and quickly find *Foundation* within it.

Expressing this as a Convex query this looks like:

```js
const foundation = await db
  .query("books")
  .withIndex("by_author_title", q => q.eq("author", "Isaac Asimov").eq("title", "Foundation"))
  .unique();
```

Here the `withIndex` expression tells Convex to only consider documents where the author is Isaac Asimov and the title is *Foundation*. This is only a single document so this query will be quite fast!

Because this index sorts by `author` and then by `title`, it also efficiently supports queries like "All books by Isaac Asimov that start with F." We could express this as:

```js
const asimovBooksStartingWithF = await db
  .query("books")
  .withIndex("by_author_title", q =>
    q.eq("author", "Isaac Asimov").gte("title", "F").lt("title", "G")
  )
  .collect();
```

This query uses the index to find books where `author === "Isaac Asimov" && "F" <= title < "G"`. Once again, the performance of this query is based on how many documents are in the index range. In this case, that's just the Asimov books that begin with "F" which is quite small.

Also note that this index also supports our original query for "books by Jane Austen." It's okay to only use the `author` field in a `withIndex` expression and not restrict by title at all.

Lastly, imagine that a library patron asks for the book *The Three-Body Problem* but they don't know the author's name. Our `by_author_title` index won't help us here because it's sorted first by `author`, and then by `title`. The title, *The Three-Body Problem*, could appear anywhere in the index!

The Convex TypeScript types in the `withIndex` make this clear because they require that you compare index fields in order. Because the index is defined on `["author", "title"]`, you must first compare the `author` with `.eq` before the `title`.

In this case, the best option is probably to create the separate `by_title` index to facilitate this query.

## **Conclusions**

Congrats! You now understand how queries and indexes work within Convex!

Here are the main points we've covered:

1. By default Convex queries are *full table scans*. This is appropriate for prototyping and querying small tables.
2. As your tables grow larger, you can improve your query performance by adding *indexes*. Indexes are separate data structures that order your documents for fast querying.
3. In Convex, queries use the *`withIndex`* method to express the portion of the query that uses the index. The performance of a query is based on how many documents are in the range.
4. Convex also supports *compound indexes* that index multiple fields.

To learn more about queries and indexes, check out our reference documentation:

- [Database Queries](https://docs.convex.dev/using/database-queries)
- [Indexes](https://docs.convex.dev/using/indexes)

How do I ensure my Convex database queries are fast and efficient? When should I define an index? What is an index?

This document explains how you should think about query performance in Convex by describing a simplified model of how queries and indexes function.

Intro to Convex Query Performance

### Recap:

In Mid May 2022, Convex released [Fast5](https://fast5.live/), a multiplayer Wordle-style racing game as a demonstration of the [Convex reactive backend as a service](https://convex.dev/). Throughout May and June, we'll be featuring articles diving into how we approached various engineering challenges while creating Fast5.

Previous posts:

- [Introducing Fast5](https://stack.convex.dev/introducing-fast5/)
- [Local and Remote State](https://stack.convex.dev/local-remote-state-fast5/)

---

A big part of Convex's magic is enabling developers to manage distributed global state as a smooth extension of their app's existing local state: storing and retrieving data is done in JavaScript / TypeScript, queries can leverage the full power of JavaScript functions, and you can share code and typing information between the browser and the backend.

And yet, Convex by design remains quite explicit about the division between the code that runs in each place: your Convex functions are in a folder called `convex/`, and are pushed to your backend deployment using `npx convex push`.

If the goal is to be seamless as possible, and we're using the same tools and languages, you might ask: why does *anything* really need to run on the server anymore? And even if it does, do I, the developer, need to be so involved?

Yes! Even in a serverless world, the backend computing context being a real, discrete environment that you have control and influence over is not only necessary but hugely beneficial.

And while there are compelling benefits related to performance, asynchronous execution of background tasks, driving workflow—today, let's talk about *trust.* Trust is the particular backend benefit that Fast5 most needs.

## The backend is the boss

Apps like Fast5 can utilize the fact that the backend can act as a trusted authority in an otherwise untrusted system. Why untrusted? Because we know anything that runs in the browser can be observed by and even manipulated by the user. Heck, as web developers, we poke at 3rd party apps in Chrome developer tools all the time!

On the other hand, the code and state that runs on the backend can only be observed and manipulated by the app author. Our user's browsers can't get to it, no matter how badly they might want to.

In the case of Fast5, this matters because both players are trying to guess the secret word:

![Game board with your guesses on the left and partner's on the right, hidden](https://cdn.sanity.io/images/ts10onj4/production/537a2800bb4834d7644807a37be5ac172b4af18a-2000x1213.png?w=450)

**Dunno what the word is, but it has "LO" and "C" in it!**

We could still build Fast5 on a "dumb" database without real, trusted backend computing, but the game would be really vulnerable to cheating by impish web developers: without the trusted backend checking each guess for matching letters, the only entity left to do the job is the player's own browser! So we would *have* to make the secret word available to the web browsers since they're responsible for checking the next guess and advancing the game.

As you can imagine, after a little bit of time in the console finding the variable holding the "secret" word, and a little bit of scripting, the aforementioned impish web dev would be crushing all challengers with *immediate* correct guesses every round.

## Secret-keeping server

Let's look at how Convex utilizes the distinct backend execution environment to have *true* secrecy and to inhibit cheating.

First, in each round, a secret word has to be picked! From the Convex mutation [createRound](https://github.com/get-convex/fast5/blob/main/convex/createRound.ts):

```tsx
const word = WORDS[Math.floor(Math.random() * WORDS.length)];
const round = {
  word: word,
  user1: {
    guesses: [],
    spying: false,
  },
  user2: {
    guesses: [],
    spying: false,
  },
  winner: null,
  overflow: false,
};
const id = await db.insert("rounds", round);
```

**A new round is created with a random secret word and stored**

In this Convex function, a random word is chosen from the `WORDS` word list, and then a new round is created. Of course, the word needs to be stored permanently in the backend at round creation time, otherwise, we'd have no way to consistently check for matches.

But what information is relayed to browsers as the round progresses? From [queryRound](https://github.com/get-convex/fast5/blob/main/convex/queryRound.ts):

```tsx
return {
    word: round.winner ? round.word : null, // no winner, no word reveal
    user1: {
      guesses: guesses1,
      scores: scores1,
      spying: round.user1.spying,
    },
    user2: {
      guesses: guesses2,
      scores: scores2,
      spying: round.user2.spying,
    },
    winner: round.winner ?? null,
    overflow: round.overflow,
  };
```

**The state of the round is returned from Convex to the browsers**

As you can see, the `word` is only returned back to the browser if the round has a `winner`—if the round is over. Otherwise, the word field is `null` and the browsers have to keep guessing.

## Secret for you, not for me

The target word is secret from both users until guessed by either. But some information is known by *one user* and secret from the other.

In Fast5, as in wordle, you have full information about which of *your* letters are hits and misses in the hidden word. However, unless you are choosing to "spy" on your opponent (and thus accepting the associated scoring penalty), you only get to see your opponent's matching tiles, not matching letters. Check it out:

![Game board with your guesses on the left and partner's on the right, hidden](https://cdn.sanity.io/images/ts10onj4/production/537a2800bb4834d7644807a37be5ac172b4af18a-2000x1213.png?w=450)

**All my info is on the left, but no letters on the right.**

To accomplish this, first, we need to have an iron-clad way to identify who is calling our Convex functions. Fast5 uses [Convex's integrated auth support](https://docs.convex.dev/using/users-and-auth) with this helper function:

```tsx
export const getUser = async (db, auth): Promise<User> => {
  const identity = await auth.getUserIdentity();
  if (!identity) {
    throw new Error(
      'Unauthenticated call to function requiring authentication'
    );
  }
  const user = await db
    .table('users')
    .filter((q: any) =>
      q.eq(q.field('tokenIdentifier'), identity.tokenIdentifier)
    )
    .unique();
  return user;
};
```

The user object returned from this function is guaranteed to be backed by a proper OAuth 2 identity provider.

Then, when we return all the guess-matching info in the round, we filter each user's hits and misses based on which user is asking for it:

```tsx
const user = await getUser(); // Use above for authenticated user 
  var { guesses, scores } = buildGuessState(
    round,
    round.user1,                 // Give us user1's guess matches
    round.user2.spying,          // Is user2 "spying" on user1?
    user._id.equals(game.user1), // Is the authenticated user user1?
    over
  );
```

**The guess-hinting for a particular user in `buildGuessState` only includes information on specific letters if these are the user's own guesses, or if they are "spying" on the other user**

Using these two methods, Convex supports giving each player in Fast5 a view of the game that hides secret information to ensure fair play.

# **What's next?**

In the next blog post, we'll explore how Convex's support for true ACID transactions is used to ensure that only one player wins each round.

Why does anything really need to run on the server anymore? And even if it does, do I, the developer, need to be so involved?

Yes! Even in a serverless world, the backend computing context being a real, discrete environment that you have control and influence over is not only necessary but hugely beneficial.

Backend Matters (Making Fast5 #2)

Relational databases like MySQL and PostgreSQL have been the backbone of web
apps since the first interactive web apps existed. They power many of the most
popular web apps on the internet and have a proven track record of reliability.

At Convex, we have spent our entire careers working with these technologies and
understanding their strengths and weaknesses. Relational databases are a great
tool for storing and querying data but operate at the wrong level of abstraction
for managing global state in modern web applications.

Fundamentally, we believe that web developers should spend their time building
their products not managing their infrastructure or wrangling with SQL.

## Using the Database

Despite being a standard piece of many web app stacks, relational databases are
surprisingly difficult to use.

### Database Schema

Before you begin using a relational database, you must define your schema. This
involves answering questions like:

- What tables does my app need?
- What columns should my tables have?
- What types are these columns?
- What indexes will I need?

The problem here is that you must make all of these decisions _before you begin
building your app_. Correctly picking a schema means you need to plan your
entire data model before you begin using the database at all. Inevitably you'll
make mistakes and require time-consuming migrations to adjust your schema to
your app's actual needs.

Convex doesn't require you to define your schema upfront. Instead, you can
[immediately start storing and querying documents](https://docs.convex.dev/understanding/convex-fundamentals/storage).
No setup needed.

That being said, Convex also recognizes that schemas are a powerful way to
ensure data consistency in large applications. When you're ready, you can
[define a schema](https://docs.convex.dev/using/schemas) to codify your data modeling decisions.

### Query Language

MySQL and Postgres both use SQL as their query language. Reading and writing
data requires constructing SQL statements. This is additional work that has
nothing to do with building your app. Worse yet, if you make a mistake
constructing your SQL statements you may end up with
[SQL injection](https://owasp.org/www-community/attacks/SQL_Injection) security
bugs or surprisingly slow queries that fail to use the indexes you defined.

To solve this friction, many developers use object-relational mappings (ORMs)
like Prisma to convert between SQL rows and native objects. While ORMs can help
alleviate some of this friction they also commonly produce surprising SQL. Most
large web apps built with ORMs inevitably fall back to writing some queries with
raw SQL to ensure they execute efficiently.

On the other hand, [Convex database queries](https://docs.convex.dev/using/database-queries) are
written entirely in TypeScript and JavaScript. There is no additional language
to learn and no need for a clunky ORM.

Additionally, the Convex database query builder is designed to make the
performance of a query explicit. [Indexed queries](https://docs.convex.dev/using/indexes) both specify
what index they should be executed over and how they should use that index. This
is in sharp contrast to the MySQL or PostgreSQL query planners which try to
automatically decide which index to use and how, sometimes with surprising or
even devastating results.

## Managing the Infrastructure

Managing a relational database is a large undertaking. Traditionally this has
involved:

- Deciding on your database configuration.
- Picking a replication and backup plan.
- Provisioning and managing servers to run the database.

If you make a mistake at any step of this process, at best you'll have a
temporary outage and at worst you'll permanently lose or corrupt customer data.
There's a reason that many companies hire large teams of database admins and
site reliability engineers to manage their databases.

To alleviate these pains, there has been a recent explosion of managed database
products like Amazon RDS, Heroku Postgres, DigitalOcean MySQL, PlanetScale, and
more. These products remove a lot of the burden of getting set up on a
relational database.

Like these managed databases, Convex handles configuring, provisioning, and
replicating your database. Unlike these managed databases, Convex also handles a
lot more. As we'll see below, Convex also eliminates the need to manage separate
backend servers, build a cache layer, or bolt on reactivity.

### Backend Servers

Relational databases are not suited to be directly accessed by client
applications. This is for a few reasons:

- Security: End users should not have unfettered access to read and edit data in
  the database.
- Performance: Loading all the data that the client needs often requires many
  database queries. To build a fast web application all of this data should be
  sent to the client in a single round trip.
- Sharing Business Logic: There should be a central place to put code that is
  shared between your different platforms like web, mobile, and your public API.
- Protocol: SQL databases don't even speak a protocol that is usable from the
  browser.

To solve these concerns, most web architectures place some kind of backend
server between the client and database. Sometimes this is a web server that
renders HTML; other times it's a RESTful HTTP or GraphQL server.

Adding this additional layer creates a new set of complexities. Now you need to
configure, provision, and manage _another_ set of servers to host your backend.
Even if you opted for a managed database, you'll still have to make some hard
decisions about how to architect and scale your backend.

Convex obviates the need for backend servers because it's your backend too! Your
Convex [functions](https://docs.convex.dev/understanding/convex-fundamentals/functions) serve as your
backend's public API. As a developer, you still implement the backend logic that
is important to your app without any of the backend infrastructure management.

### Caching

Improving the performance of web apps built on relational databases is
difficult. You can eke some additional performance out of your database by
tweaking configuration, adding indexes, and using read replicas, but eventually
many teams add on an additional caching layer.

A common architecture is to use an in-memory data store like Redis or Memcached.
This is essentially a second database that you, as the developer, must keep in
sync with your existing MySQL or Postgres instance. This requires all of the
work to manage another database that we've already discussed plus an additional
problem: cache invalidation.

> There are only two hard things in Computer Science: cache invalidation and
> naming things.
>
> — Phil Karlton

Keeping your cache consistent with your relational database is notoriously
difficult and error-prone. Incorrect caching code creates subtle bugs where rare
race conditions and crashes corrupt the cache and send incorrect data to your
users. Tracking down caching bugs can take months of work for experienced
infrastructure engineers.

On Convex caching is completely automatic. Convex
[caches the results of your query functions](https://docs.convex.dev/understanding/convex-fundamentals/functions#caching)
and recomputes the values when the underlying data changes.

### Reactivity

Traditional relational databases are nonreactive. Developers can use SQL to
query the current state of the database, but there is no built-in ability to
subscribe to updates to those query results. Relational databases leave
implementing reactivity up to the developer.

The most common techniques for creating realtime, reactive applications on top
of relational databases are polling or pushing updates.

#### Polling

Polling requires no additional infrastructure; the client simply reruns its
queries at a regular interval. The problem with polling is that it requires
making a tradeoff between data freshness and database load. Frequent polling
will overwhelm your database with requests; polling rarely will leave your users
looking at stale data.

#### Pushing Updates

Scaling your application will normally require moving off of polling and pushing
updates to your clients instead.

There are two broad ways that teams push updates using a relational database:

- Using a relational database that has built-in Pub/Sub capabilities like
  PostgreSQL.
- Deploying a separate Pub/Sub cluster like Apache Kafka or Redis Pub/Sub.

Even if your database supports Pub/Sub that only solves part of the problem. The
database will give you a stream of events, but your application will still be
responsible for connecting those events to the SQL queries and API responses
used in your app and pushing updates over a WebSocket. Comprehensively
implementing realtime updates is a huge undertaking.

Realistically, most web developers never attempt this and instead settle for
showing their users stale, non-reactive data.

#### Convex

Compare this situation to Convex. In the
[`ConvexReactClient`](https://docs.convex.dev/api/classes/react.ConvexReactClient), all queries are
[fully reactive](https://docs.convex.dev/understanding/convex-fundamentals/client-libraries#reactivity)
and receive realtime updates. That's it.

Convex understands which query functions depend on what data. When the
underlying data changes, Convex reruns the query function and pushes the new
result all the way to the UI.

Reactivity shouldn't require making complex tradeoffs or deploying additional
infrastructure. On Convex, you get correct reactivity automatically.

## Bottom Line

Relational databases are powerful tools for persisting data, but using them to
build interactive web apps requires a lot of work. A web app architecture built
on a relational database is an entire cluster of databases, backends, caching
nodes, and Pub/Sub servers. And even after you invest in all of that
infrastructure you'll still need to explicitly define your database schema
upfront and write cumbersome SQL queries.

At Convex we believe that web developers shouldn't have to worry about any of
this. We can handle the servers, caching, and reactivity and you can focus on
your product.

What are you waiting for? [Get started with Convex!](https://docs.convex.dev/tutorial)


Relational databases are powerful tools for persisting data, but using them to build interactive web apps requires a lot of work. A web app architecture built on a relational database is an entire cluster of databases, backends, caching nodes, and Pub/Sub servers. And even after you invest in all of that infrastructure you'll still need to explicitly define your database schema upfront and write cumbersome SQL queries.

At Convex we believe that web developers shouldn't have to worry about any of this. We can handle the servers, caching, and reactivity and you can focus on your product.

Fundamentals