# TL;DR

There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.

```typescript
import { filter } from "convex-helpers/server/filter";
// Change this
ctx.db.query("posts")
	.filter((q) => ...limited functionality...)
	.paginate(opts);
// Into this
filter(ctx.db.query("posts"),
	(post) => ...unlimited functionality...
).paginate(opts);
```

# The filtering problem

To read data from the Convex database, you write queries to fetch data and display it in your app, and each query filters to return only the data it wants.

You can make your queries fast with indexes, discussed [below](#Optimize-with-indexes). But to get started, or if your filter is complicated enough, you won’t be using indexes. You’ll be using plain query filters, like this:

```typescript
export const messages = query({
  args: { channel: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("posts")
      .filter((q) => q.eq(q.field("channel"), args.channel)
      .collect();
  },
});
```

This is equivalent to the following query in SQL, assuming the table has no indexes:

```sql
SELECT * FROM messages WHERE channel = "$channel"
```

At first glance, Convex’s filter syntax is limited. There’s `q.eq`, `q.lt`, `q.or`, and a few more, but nothing advanced. You can’t manipulate strings or loop over arrays. Newcomers to Convex may think these complex patterns are impossible. But in fact the patterns are still possible, and more powerful patterns are available than you might expect from SQL or another query language. That’s because your Convex app can leverage a TypeScript runtime to run arbitrary code *within* the database.

# Filter too complex? Do it in TypeScript

> Disclaimer
> 
> Do this while prototyping or if you know the query doesn’t have to scale. If you need to scale, use indexes as described [below](#optimize-with-indexes).
    

Let’s work with an example. We’ll have a table of posts, like you might display in a Twitter-like feed, where each post has a short array of tags.

```typescript
export default defineSchema({
  posts: defineTable({
    body: v.string(),
    tags: v.array(v.string()),
  }),
});
```

That’s our data model, and we want to build a query that looks up all posts with a given tag. We’re building a simple search box where you can type in “happy” and posts tagged as “happy” show up.

My first instinct would be to use `.filter`, like the following. However, this doesn’t work because database query `.filter` doesn’t support array containment.

```typescript
// This query is what we want.
export const postsWithTag = query({
  args: { tag: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("posts")
      // Doesn't work because q.arrayIncludes doesn't exist.
      .filter((q) => q.arrayIncludes(q.field("tags"), args.tag)
      .collect();
  },
});
```

So what can we do?

Well, remember that Convex’s runtime can run arbitrary TypeScript and JavaScript. So we can fix the example by replacing the database query `.filter` with a TypeScript array `.filter`.

```typescript
export const postsWithTag = query({
  args: { tag: v.string() },
  handler: async (ctx, args) => {
    const allPosts = await ctx.db.query("posts").collect();
    return allPosts.filter((post) => post.tags.includes(args.tag));
  },
});
```

And that’s it! The `await ctx.db.query("posts").collect()` returns an array of all posts, which we then filter to only the posts which include `args.tag` in their array of tags. Syntactically, we swapped the order so `.collect()` comes before `.filter()`.

### Collecting only some of the results

Swapping the order of `.collect()` and `.filter()` works and even has the same performance (more on that below), but what if you don’t need all the results? When using `.first()`, `.unique()`, `.take()`, or `.paginate()`, you don’t need to collect all documents before filtering; you want to apply the filter as you go. Even in this case, we can use TypeScript for filtering; it’s just not a simple `Array.filter()` method call.

I implemented a function in the [“convex-helpers” npm package](https://www.npmjs.com/package/convex-helpers) that does the right thing under the hood: the `filter` helper.

```typescript
import { filter } from "convex-helpers/server/filter";

export const firstPostWithTag = query({
  args: { tag: v.string() },
  handler: (ctx, args) => {
    return filter(
      ctx.db.query("posts"),
      (post) => post.tags.includes(args.tag),
    ).first();
  },
});
```

The `filter` function in “convex-helpers” allows you to attach a custom filter to any query:

- It can use `.first()`, `.unique()`, `.take(n)`, `.paginate(opts)`, `.collect()`, or `.next()` to get results.
- It can use indexes to efficiently scope down and order the data being filtered: `.withIndex(...)` or `.withSearchIndex(...)`.
- It works with `order("desc")` to reverse the order.
- It can accept and execute an `async` predicate function

Since it works for any query, you can give your Convex filters superpowers by replacing `X.filter((q) => ...).Y` with `filter(X, (doc) => ...).Y`.

Note the `filter` helper looks at documents one at a time. So doing `filter(db.query("posts"), predicate).first()` stops when it finds a single post for which `predicate(post)` is true. This is equivalent to `db.query("posts").filter(predicate).first()` and better than `db.query("posts").collect().filter(predicate)[0]` which would look at every post. 

### Performance of TypeScript filters is the same as SQL unindexed `WHERE` clauses

It may look like the TypeScript filter is slower than the database query filter. After all, surely telling `db.query` you only want *some* posts must be faster than asking `db.query` for *every* post and then removing the ones you don’t want.

However, that would be incorrect. Both the `db.query` filter and the TypeScript filter are running in the Convex runtime, which you can think of as running “in the database.” The difference is almost entirely syntactic.[^1] Trying to fit a complex filter in a `db.query` filter isn’t worth it, when you can just use TypeScript.

[^1]: `db.query` filters run in Rust, while TypeScript/JavaScript filters run in … JavaScript. So a `db.query(tbl).filter(pred)` may be slightly faster than a `filter(db.query(tbl), pred)` because the latter does context-switching between JavaScript and Rust. But they scan the same data under the hood, and performance differences from context switching are overshadowed by any changes that affect the number of documents scanned.

Let’s compare to using a SQL server, say Postgres, directly.

```sql
SELECT * FROM posts WHERE 'happy'=ANY(tags);
```

In Postgres, this would scan all `posts`, filter them by tags in the Postgres server, and return the matching posts to the client. Convex with the TypeScript filter does the same: it scans all `posts`, filters them by tags in the Convex server, and returns the matching posts to the client. Postgres isn’t magical; it has to look at all `posts` to do the query, and so does Convex.

Let me reiterate: there is no reason to use `db.query(...).filter(...).collect()` over `filter(db.query(...), predicate).collect()`. 

- Both of them scan the same documents
- They both execute the filter within the Convex runtime
- They both rerender `useQuery` hooks at the same rate — whenever anything in the table changes
- In mutations, they would cause mutations to retry at the same rate — because Convex mutations use optimistic concurrency control
- Both of them are equivalent in performance to an unindexed SQL `WHERE` clause

### TypeScript filters are powerful and easy to write

Other databases have entire libraries of custom filter syntax, and they’re all different. Here’s array containment:

- Postgres uses `'happy'=ANY(tags)`
- MySQL uses `FIND_IN_SET('happy', tags) > 0`
- MongoDB uses `tags: { "$in" : ["happy"]}}`

In Convex you can use TypeScript or JavaScript, which means not only can you use the `Array.includes` [function](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/includes) you’re already familiar with (i.e. `post.tags.includes("happy")`), but you have the full power of recursive syntax and npm libraries at your disposal.

Want to search for tags that would be too large to print? In Convex you could do this

```typescript
function tagsOverflow(post: Doc<'posts'>) {
  const tagString = post.tags.map((tag) => '#' + tag).join(', ');
  return tagString.length > 100;
}
const allPosts = await ctx.db.query("posts").collect();
const overflowingPosts = allPosts.filter(tagsOverflow);
```

I can write this filter with rudimentary TypeScript knowledge, without searching StackOverflow. I would need help to write this same filter on a different database, if it’s even possible.

And you’re not limited to single-table filters. You can do arbitrary joins against other tables, with no worries about race conditions because Convex queries are transactional. And you can sort by an arbitrary combination of fields, using TypeScript’s `Array.sort`.

<BuildWithConvex />

# Optimize with Indexes

Before optimizing, make sure you need to. If you only have a few hundred documents, filtering in TypeScript is fine and should be the default while prototyping.

Check out “[Queries that scale](https://stack.convex.dev/queries-that-scale)” for tips and tricks on writing scalable queries. That article give widely useful tips, while this section doubles down on the example given above, describing every possible way you could address it.

Let’s walk through the ways to optimize the posts-and-tags query at the expense of data model complexity, mutation performance, and even the performance of other queries.

### Array `includes` with index and a join

We want to find all posts with a given tag, so let’s add an index on the tags. 

What if we add `.index("by_tags", ["tags"])` to the posts table?

An index sorts the documents in a table, allowing queries to binary-search for a range and iterate over it. If we wanted to query for posts with `tags` exactly equal to `["happy"]`, we could use this index. We could even use it to find posts where `tags` starts with `["happy", ...]` since those are all together in the index. But sorting by `tags` doesn’t place `["happy", "joyful"]` next to `["wonderful", "happy"]`; the post with tags `["sad", "corgi"]` would be in between (because “h” < “s” < “w”).

So we need a way to group all posts with common tags together. We can do this by adding a new table:

```typescript
export default defineSchema({
  posts: defineTable({
    body: v.string(),
    tags: v.array(v.string()),
  }),
  tagged_posts: defineTable({
    tag: v.string(),
    post: v.id("posts"),
  }).index("by_tag", ["tag"]),
});
```

Whenever a post is inserted, modified, or deleted, the “tagged_posts” table must be updated in sync. This requires changing mutations, but don’t worry about race conditions when updating both tables, because mutations are committed as transactions.[^3]

[^3]: If you don’t want to manually manage the extra table, use https://stack.convex.dev/ents . If you want nicer query syntax, use https://stack.convex.dev/functional-relationships-helpers. 

Once your new table is set up, the query can use the index.

```typescript
export const postsWithTag = query({
  args: { tag: v.string() },
  handler: async (ctx, args) => {
    const taggedPosts = await ctx.db.query("tagged_posts")
      .withIndex((q) => q.eq("tag", args.tag))
      .collect();
    return await Promise.all(
      taggedPosts.map((taggedPost) => ctx.db.get(taggedPost.post))
    );
  },
});
```

This is *the* efficient way to look up posts by tag. It looks up exactly the post ids associated with each tag, and joins against the posts table.

### Advanced indexes

In addition to standard indexes, Convex also offers full text search and vector indexes. If you store data in specific ways, you can leverage these indexes to get you the results you want. If we store tags in a space-separated string instead of an array, we can use full text search.

```typescript
export default defineSchema({
  posts: defineTable({
    body: v.string(),
    // e.g. "happy joyful", "wonderful happy", "sad corgi"
    tags: v.string(),
  }).searchIndex("search_tags", { searchField: "tags" }),
});
export const postsWithTag = query({
  args: { tag: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db.query("posts")
      .withSearchIndex("search_tags", (q) => q.search("tags", args.tag))
      .collect();
  },
});
```

The behavior has slightly changed, due to the nature of full text search. If a tag contains a space, it’s equivalent to having two tags. The query can now search for tags with fuzzy search, so misspellings are tolerated. And you can search for multiple tags at once.

Using full text search can work for our tags example, but it’s tricky to generalize to other complex filters.[^4] Similarly, vector indexes can organize your data by distances between vectors, which are often used with AI-computed embeddings but can be any vectors.

[^4]: An enterprising engineer used Convex’s full text search to build a geospatial index, with Uber’s H3 library: https://github.com/sujayakar/geospatial-convex. It wasn’t easy, but the example shows surprising ways you can use indexes to organize data. 

### Escape hatch: pagination

You may have noticed that TypeScript filters can work with pagination. This can return small or empty pages to the client, which can slow down load times and cost database bandwidth as it traverses the entire table across many pages. But it always works! Each query only looks at a small page of data, so they can run within Convex’s query limits.

```typescript
import { filter } from "convex-helpers/server/filter";

export const postsWithTag = query({
  args: { tag: v.string(), paginationOpts: paginationOptsValidator },
  handler: (ctx, args) => {
    return filter(
      ctx.db.query("posts"),
      (post) => post.tags.includes(args.tag),
    ).paginate(args.paginationOpts);
  },
});
```

### Combining indexes with TypeScript filters

Often you can use an index to narrow down the results, and attach a TypeScript filter on the results.

```typescript
import { filter } from "convex-helpers/server/filter";

export const postsWithTagAndAuthor = query({
  args: { author: v.id("users"), tag: v.string() },
  handler: (ctx, args) => {
    return filter(
      ctx.db.query("posts")
        .withIndex("by_author", q => q.eq("author", args.author)),
      (post) => post.tags.includes(args.tag),
    ).collect();
  },
});
```

Maybe there are tons of posts in the database, but each author has only written a small number. Then you can look through the author’s posts with an index, and use a TypeScript filter to identify the posts with a tag.

### Denormalizing properties

If there’s a specific query you need to make faster, you can consider storing extra fields on your document to improve query performance. Suppose your app’s home page shows posts tagged as “important”, so you want fast lookup for that specific tag.

```typescript
// Add field to schema
export default defineSchema({
  posts: defineTable({
    body: v.string(),
    tags: v.array(v.string()),
    isImportant: v.boolean(),
  }).withIndex("by_important", ["isImportant"]),
});
// Set field when inserting and updating
await ctx.db.insert("posts", {
  body,
  tags,
  isImportant: tags.includes("important"),
});
// Now you can query for the denormalized field with an index
// Remember to keep it in sync when there's a db.patch or db.replace.
await ctx.db.query("posts")
  .withIndex("by_important", q => q.eq("important", true))
  .collect();
```

As a general pattern, you can store booleans or other types on your documents to speed up lookup by those properties.

# Recap

Complex filters work differently in Convex compared to other databases. Instead of restricted custom syntax, you can leverage TypeScript to do any filter you want. The built-in `db.query(...).filter(...)` can be replaced with the equally efficient and more powerful `filter` from convex-helpers. If you’re ready to optimize beyond filters, use indexes to speed up your queries. [Read more about that here](https://stack.convex.dev/queries-that-scale). Happy querying!

There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.

Using TypeScript to Write Complex Query Filters

With <QuickConvex/> you can implement authorization a number of ways. In this post we’ll look at implementing a row-level security abstraction by adding a layer of indirection which will validate the authorization of the user to read, write, or modify each document they interact with.

# Authorization: limiting access control

One of the goals in making a secure app is restricting which users can do what. Logged-out sessions should not be allowed to view private data, and logged-in users shouldn’t be able to delete other users’ data. I’ve worked on locking down access to Dropbox files, Spark deployments, and [private messages](https://stack.convex.dev/end-to-end-encryption-with-convex). So I got to thinking about how developers using Convex can manage permissions in their apps.

## Authorization via code

Convex allows developers to write arbitrary Javascript, which runs on the server within a transaction, that can check authorization. If you want to enforce an authorization rule, you can define it in code and check it whenever you want. Maybe before a user can “Like” a post, you require that the authenticated user is connected to the post’s author through the graph of friendships.

```tsx
export default likePost = mutation(async ({db, auth}, {postId}) => {
	const post = await db.get(postId);
	if (!await connectedInGraph(db, await auth.getUserIdentity(), post.author)) {
		throw new Error("you can't like that");
	}
	await db.patch(postId, {likes: post.likes + 1});
});
```

Although Convex allows flexible rules, it could become unruly if you need to do the same authorization check in different places. “Liking” a post should run the same authorization code as “Sharing” or “Reposting,” because the check isn’t a property of the mutation as much as it’s a property of the post. Even if everything is configured perfectly, it could all fall apart if a new engineer joins the team, makes a new mutation, and forgets the authorization check. Suddenly you have an [IDOR vulnerability](https://www.varonis.com/blog/what-is-idor-insecure-direct-object-reference). 

## Authorization via row-level security

We want some way of saying “if you’re accessing data in the ‘posts’ table, you need to run the access check.” Finding the right layer to put this access check can be tricky — I spent a year at Dropbox moving access checks for files into a central service. One layer that works well is row-level-security (RLS) where authorization is defined on individual rows, and the checks automatically run whenever code tries to read or write the row. How would you build that in Convex?

Let’s design a simple app where users create messages. Only the message’s author can edit a message or publish it. Logged-in users can view all messages, but logged-out sessions can only view published messages. We can codify these rules in code.

```tsx
// in convex/rls.js
export const {withQueryRLS, withMutationRLS} =
	RowLevelSecurity<{ auth: Auth }, DataModel>(
  {
    messages: {
		  read: async ({ auth }, message) => {
	      const identity = await auth.getUserIdentity();
	      if (identity === null) {
	        return message.published;
	      }
	      return true;
	    },
			write: async ({ auth }, message) => {
	      const identity = await auth.getUserIdentity();
	      if (identity === null) {
	        return false;
	      }
	      return message.author === identity.tokenIdentifier;
	    },
	  },
  }
);
```

Here we use some [middleware](https://stack.convex.dev/sessions-wrappers-as-middleware), defined in [`rowLevelSecurity.ts`](https://github.com/get-convex/convex-helpers/blob/npm/0.1.1/convex/lib/rowLevelSecurity.ts) in the [convex-helpers](https://github.com/get-convex/convex-helpers/) repo.

```jsx
// in convex/messages.js
import { withQueryRLS, withMutationRLS } from "./rls";

export const list = query(withQueryRLS(async ({ db }) => {
  return await db.query("messages").collect();
}));
export const publish = mutation(withMutationRLS(async ({ db }, { messageId }) => {
  await db.patch(messageId, {published: true});
}));
```

The middleware functions wrap queries and mutations, taking the `db` argument and wrapping it. The wrapper intercepts each row that would be returned from `db.get` or `db.query` and filters out rows based on your `read` rules. It intercepts each row that would be modified by `db.patch`, `db.replace`, or `db.delete` and confirms that the write is allowed by `modify` rules, and `db.insert` with the `insert` rules.

Now we have defined the authorization checks in a single place. The access rules can depend on the authorized user through `auth` and they can do database reads through `db`. Convex runs functions close to the database and caches query results, making it efficient to run the same authorization check on each document. However, you can also compose this pattern with other wrappers to provide user-level, team-level, or otherwise checks.

# Extending access functions

## Composing RLS with data-fetching functions

The rules you define for reading and writing documents are given the context that is provided to the function, including the `db`, `auth`, and other objects. If you want to optimize and avoid fetching the same thing multiple times, you can compose `RowLevelSecurity` with [middleware functions like `withUser`](https://stack.convex.dev/wrappers-as-middleware-authentication), which look up the user and pass it in the context  object to the function. 

```jsx
export const list = query(withUser(withQueryRLS(async ({ db, user }) => {
  return await db.query("messages").collect();
})));
```

In this case, it will also be passed to the authorization rules, making it easy to write rules involving `user` properties:

```jsx
// in convex/rls.js
export const {withQueryRLS, withMutationRLS} =
	RowLevelSecurity<{ auth: Auth }, DataModel>(
  {
    messages: {
		  read: async ({ user }, message) => {
        // Only show published posts to logged-in, active users.
	      if (user?.status === "active") {
	        return true;
	      }
	      return message.published;
	    },
...
```

You could extend this pattern to also look up the user’s team, role, and more.

## Filtering out fields

This middleware pattern can be extended to filtering out individual document fields, or blocking writes to certain fields. You could have the authorization function return the modified object, for instance. By building this abstraction in a library, you’re free to replace and extend the interface to meet your needs.

## Mixing RLS with bespoke authorization rules

One somewhat-obvious thing to point out is that, while you can use this abstraction to add RLS to your app, you can also decide where to not use it, or when to do other, more complex authorization with regular functions. Convex queries and mutations run on the server, so you can safely write access checks in code, whereas with some other platforms you’re limited to a special authorization markup and your code only runs on the client.

# Summary

In this post we looked at adding row-level security to endpoints by wrapping the database interface with per-document checks. As long as your documents express a logical concept that can have access control rules, you can implement security in your Convex app today, by copying [`rowLevelSecurity.ts`](https://github.com/get-convex/convex-helpers/blob/npm/0.1.1/convex/lib/rowLevelSecurity.ts) into your project.

This is one of many ways to authorize access for your Convex app. Please let us know what your favorite way of managing authorization is in [our Discord](https://convex.dev/community). Thanks for reading.

Implementing row-level security on Convex as a library. Wrap access to the database with access checks written in plain old JS / TS.

Row Level Security: Wrappers as "Middleware"

What's the maximum level of privacy an application can offer? Platforms like Convex encrypt data at rest but allow you to interact with it in unencrypted form via user-defined functions running on the server. This is tremendously useful but occasionally there's data that you don't want to exist in unencrypted form *anywhere*.

Consider a scenario where I need to send a crypto passphrase to my partner. That passphrase should be encrypted on a computer that I control, decrypted on a computer my partner controls, and not be accessible anywhere else. This is called end-to-end encryption, because only the endpoints of the communication — my partner and I — can see the secret passphrase.

I wanted to see what it would take to build end-to-end encryption on top of Convex, so I built Whisper.

### How to use Whisper

Check out [https://whisper-convex.vercel.app/](https://whisper-convex.vercel.app/), the final product.


![Screenshot of the app in use](https://cdn.sanity.io/images/ts10onj4/production/611e92f2d3c2d82aa2219e49372ba4eb3477fcc8-2258x960.gif)

1. Type in a secret message
2. The message is encrypted with an optional client-side password and stored in Convex.
3. Send the private URL to the recipient(s), through an external secure channel.
4. A recipient retrieves the encrypted message from Convex, and decrypts the message in their browser.

**Features:**

1. No login or setup, for either the sender or recipient.
2. In case the URL is intercepted, the sender can see the IP address of everyone who uses the URL to read the secret message, in a list that reactively updates.
3. The message expires after a certain number of accesses, or a configurable duration.
4. When the message expires, a [scheduled mutation](https://docs.convex.dev/using/scheduling) deletes it from Convex so no one can access it, even with the URL.

### How Whisper works

The full source code is at [https://github.com/ldanilek/whisper](https://github.com/ldanilek/whisper/blob/main/README.md), and I’ll highlight some of the key components here.

To create a Whisper — an encrypted secret message — we use AES symmetric encryption before calling the `createWhisper` mutation in Convex. This code runs in the browser, so the raw URL and secret are never sent to Convex.

```ts
if (password.length === 0) {
  password = uuid.v4();
}
const encryptedSecret = CryptoJS.AES.encrypt(secret, password).toString();
const passwordHash = hashPassword(password);
await createWhisperMutation(
	name, encryptedSecret, passwordHash, creatorKey, expiration,
);
```

Accessing the Whisper requires password hash to match, and it’s a mutation so the access can be recorded. This code runs in a transaction on Convex servers.

```ts
// accessWhisper.ts
export default mutation({
  args: {
      whisperName: v.string();
      passwordHash: v.string();
      accessKey: v.string();
      ip: v.union(v.string(), v.null());
  },
  handler: async (
    { db },
    {
      whisperName,
      passwordHash,
      accessKey,
      ip,
    }
  ) => {
    const whisperDoc = await getValidWhisper(db, whisperName, true);
    if (!timingSafeEqual(whisperDoc.passwordHash, passwordHash)) {
      throw Error("incorrect password");
    }
    await db.insert("accesses", {
      name: whisperName,
      accessKey,
      ip,
    });
  },
});
```

Once the access is registered, we use a Convex query to read the encrypted message, and AES to decrypt it.

```tsx
const SecretDisplay = ({name, accessKey, password}) => {
  const encryptedSecret = useQuery(api.readSecret.default, name, accessKey);
  return <div>{
    encryptedSecret ?
		CryptoJS.AES.decrypt(encryptedSecret, password).toString(CryptoJS.enc.Utf8) 
		: "Loading..."
  }</div>;
}
```

To delete expired secrets, we schedule a mutation to delete the encrypted message.

```tsx
// inside createWhisper.ts
await scheduler.runAt(expireTime, internal.deleteExpired.default, whisperName, creatorKey);
// inside expireNow.ts
await db.patch(whisperDoc!._id, {
  encryptedSecret: "",
});
```

Implementing end-to-end encryption on top of Convex to build a secret-sharing app.

Lee Danilek

All posts by Lee