When we consume REST, GraphQL, or RPC APIs from the frontend, most of the time these api calls just end up being translated into SQL statements on the backend.
So why don’t we just write SQL in the frontend to begin with?
I’m serious.
To ensure a snappy app, we usually need a normalized cache on the frontend. When we start trying to do optimistic updates is when things get complicated really quickly. Unless our frontend data model maps exactly to our backend model, we have to do a bunch of quirky, manual cache updates. Just check out Apollo’s guide for optimistic UI — its intense! Swr and react-query also leave the manual query invalidation and cache updating to you.
Apollo GraphQL Optimistic UI Guide — Adding to a list
The hard truth is that if we want perfect optimistic UI updates, we are going to need to replicate our backend data model in our frontend. And if we are using an SQL database with relational data, then we should have an SQL database in the frontend. If you can make this work you also get offline support for free.
Think about all the code you normally write to process API responses on the frontend. I usually end up with a bunch of Lodash (groupBy, filter, map, reduce) to shape the data I get from the server. This always becomes unwieldy and I always end up wishing I could just use SQL.
An example might be a task management app like Asana, with a sidebar of Projects, and a list of Tasks next to it, with a task count showing next to each project.
We can pull all the data for the view in one query:
If you add a new task to a project, you need to update the project count in the sidebar. Ideally, you simply want to add a new Tasks entity and associate it with a Project. This is one line of SQL.
Then we run all our queries again, and our interface will be automatically consistent with our data model.
But if our API is not derived by an underlying relational model, and we want optimistic updates, we have to manually update our local model as seen in the Apollo example above — whereby the new comment is manually added to the cached query response. We also have to be careful to only re-trigger dependent queries because they will result in new fetches.
Whenever we render or modify a local entity that exists in one or more places in our UI, we want it to be consistent.
We used to be told our APIs must be REST. So that’s what we did.
Then GraphQL liberated us from REST, and said: it’s okay to do RPC again over a single endpoint. And the same people had their own protocol, so that’s what we did.
But now that it’s okay to do RPC again, maybe we can complete the loop, and it becomes okay to do…SQL again…just like we did when we built desktop apps.