Alex Cole

3 years ago

Convex vs. Relational Databases

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. 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 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 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 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 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 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 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, all queries are fully reactive 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!