Matt Luo

2 hours ago

Beyond SQL: How Convex Reimagines the Modern Database — A ClarityText Startup Story

Beyond SQL: How Convex Reimagines the Modern Database — A ClarityText Startup Story is a guest Stack Post from the community Convex Champion, Matt Luo.

To someone new to Convex, it might look like Convex is in the database category. And that’s a reasonable interpretation. It’s difficult to explain what Convex is, since it straddles multiple well-defined categories. If I were to describe Convex in one sentence, without any regard for removing technical jargon, I’d say that Convex’s primary focus is providing deterministic state for an app’s backend. But see, that sentence is difficult to communicate with.

Part of my motivation to write this article is to explain Convex’s value proposition without the constraint of oversimplifying it in order to fit it into a pithy social media post.

I’ll use my own app, ClarityText, to illustrate how Convex is used in a real world business use case. ClarityText is a group chat platform for business. A customer can turn their chat history into an AI-powered knowledge base and get discovered in Google search and AI responses.

Let me talk through some of the key pieces of the Convex offering that replaces my need to incorporate another framework or vendor into ClarityText’s tech stack. I think seeing the use of Convex in this way helps to learn about Convex’s value proposition through analogies to other vendors.

Database solutions

When I was initially shopping around for a database, I inadvertently looked for the hosting of databases, like cloud hosting for MySQL, and no further features. As a startup founder, this was a mistake. As I went through the shopping process and learned more, I realized that there are table stakes features my app would need to have any chance against today’s competition.

These days, buying a hosted solution for MySQL and PostgreSQL, with nothing else, is actually more time consuming than using a back end as a service would be.

With a database and nothing else, I would have to do all the work in my MVP of:

1. Ensuring type safety with an ORM.
2. Making sure I don't create a security vulnerability with publicly exposed endpoints.
3. Doing caching, since that's probably a table stakes requirement for a consumer app these days.
4. Make a realtime front end, which is also probably a table stakes requirement for a consumer app MVP these days

So, I think the back end as a service category, i.e. Convex, Supabase, and to a lesser extent, Firebase, can get a founder like me started faster toward an MVP.

Let me expound on each of these items.

ORM (object relational manager)

Type safety is a key native value proposition for an app based on Convex. It was appealing to me that I didn’t need a standalone ORM library.

At the time I started ClarityText, which was the late summer of 2024, the ORM industry was in a transitional state between popular libraries. This would have caught my startup in a time without a great ORM.

The longstanding popular ORM library was Prisma. It was a mature product with a syntax for edge relations. At the time, Prisma had a lot of negative social media posts about it for being slow. It had just gone through a large re-write into Rust, but the performance seemed to have gotten worse to many developers. Popular developer influencers like Codedamn made lengthy videos about why Prisma was slow. It also had just raised a lot of VC capital to build a business that was fundamentally about database and code analytics, with ORM as more of a tool for them to get customers.

Drizzle was an up and coming ORM library that was rapidly gaining popularity. I used it in an early prototype to map my React app with MySQL and Postgres. It worked for its advertised scope of features. But I wasn’t really excited about it. There wasn’t any remarkable ergonomic aspect to it. It was a utilitarian tool just to map a table. It could do a subset of what SQL could do, and then let the developer execute raw SQL as well. It seemed that most of the developer excitement about it was that the syntax more resembled SQL than Prisma’s did, and that it did not have the performance problems that Prisma had.

Drizzle also was at an early stage in development, and so were the ancillary database startups that marketed it. At the time, a Postgres hosting company, Neon, had a connector for Drizzle, but it was in beta.

In the Convex experience, I got a much deeper end to end type safety experience. I did not need an ORM at all. The return from the query to Convex itself would map to the variables in my code. And the Convex documentation provided many various ways to do the mapping.

I think this end to end type safety contributes to what Convex calls, the “Zen of Convex”. It enabled ergonomics I did not anticipate, such as

• Intellisense in VS Code
• I can execute npx convex dev to start a Convex local development environment, which would give me error messages about how my database schema was not in sync with my functions and current data.
• When new technologies came out, like the model context protocol, AI assistants had an easy time interpreting my repo.

Performant database configuration

One relief for me was not having to decide on a database server size. With a database hosting service like Neon, I had to choose a server size and would need define how to auto scale it in the future. For a database expert, it’s probably fine to tune and configure everything. But I know enough about databases to know the vast surface area of my ignorance about database management.

So Convex replaces the need for something like a database deployment service. One instructive vendor to learn all about the tech involved with that is PlanetScale. PlanetScale has done a lot of work managing the connection pool for its customers. The connection pool is often a point of failure for an application with a lot of simultaneous activity to serve. PlanetScale runs instances of MySQL over a Vitess cluster. It is like an extreme example of horizontal scaling.

Also, Convex replaces the need to go back and forth with a SQL query explainer and planner. When working with SQL, it’s important to figure out whether the query you wrote is indeed using the indexes you expect it to use. Sometimes, someone very proficient with SQL can explicitly call a particular index, but it’s not the focus of the SQL language design to make that easy.

With Convex, I use Typescript to define my business logic for my database queries. I imperatively invoke the database indexes. It’s great. I just assume it’s Convex responsibility to execute the query optimally. And I can tell the dev experience will keep getting better. The Convex dashboard recently released a function health dashboard. And I think it’s inevitable that there will be some AI powered validation and guidance when I define my functions in the future.

System Design related solutions

A significant reason why ClarityText is using Convex is because Convex has already solved a lot of System Design related problems. The way I think about it, I can flip through the book, System Design Interview - An Insider's Guide, by ByteByteGo (Alex Xu), and think oh, this is already part of the Convex product.

The most important aspect of this is the state management of my entire app, back and forth between the front end and the back end. The most palpable benefit I get as a developer is a realtime front end, by default. Earlier in my career, and during my evaluation of other database products, I always had to refresh the browser tab in order to see my changed results on the front end. It was a profound moment for me to just simply add a ConvexProvider component to my React code, and just change data in the database and see the front end automatically updated.

So, this “sync engine” value proposition replaces a high-effort, expensive vendor like Ably. For a messaging app built on Postgres, Ably might be a good solution to keep the front end in sync. But there’s a significant amount of documentation and expertise needed to make it happen.

Convex’s “deterministic state” design enables another important benefit: query caching. From my perspective as an engineering manager, it helps that developers shift the responsibility to Convex to do the query caching. It would be much more difficult to bring another big piece into the tech stack like Memcached. We’d have to figure out as a team how to invalidate the cache. And at the React level, with Convex we don’t have as strong a need to do complicated memoizations. It’s very satisfying to simply see the cached indicator for a query in the Convex dashboard.

Regarding application security, I admit I do not completely understand public function security, but I know that many Firebase applications have gotten hacked because of allowing too much database access on the client side. You can easily see examples by searching for “firepwn.” With Convex, there are ways to distinguish public versus private functions. And for public functions, I can add argument validators and return validators to enforce data types. This replaces the need for complicated API gateway vendors like Kong.

Common application needs

Rate limiting packages

Every app exposed to the Internet probably has some rate limiting features in it. Upstash has a popular package for rate limiting, based on Redis. The Convex rate limiter component replaces this package, and has better ergonomics because it is easier to associate user activity with a foreign key to my users table in Convex than it is with a mapping to some table in Redis.

Materialized counting libraries

Some new application developers are surprised to learn how slowly a SELECT(*) in SQL performs. The two counting Convex components, sharded counter and aggregate components, use computer science techniques to denormalize the counting under the hood.

Ci cd.

Convex preview deployments are really useful. The ecosystem of Convex, Vercel, and GitHub make preview deployments easy to use. In our team, each time we have a PR, we have an accompanying Convex preview deployment hosted on a Vercel preview deployment. Even if we don’t click into the preview deployment, it is important to know whether the PR will build in Vercel before we merge it into our main branch.