Don’t we all just want to use SQL on the frontend?
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.
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.
Why hasn’t this been tried?
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.
Security concerns are probably also a significant reason why people may have brushed it aside. Without built-in safety it can be super dangerous.
How is this going to work?
We already have have SQLite in the browser via wasm that we could use for this. A pure SQLite JS implementation would be cool though.
We need a restrictive SQL parser to run server-side to restrict what can be run and prevent SQL injection. Maybe we need a query-builder/ORM to generate a safe intermediary SQL language in JSON so that we can validate it. Maybe taking some inspiration from Prisma’s type-safe data-mapper client could be used to help people write safe queries that won’t fail server-side validation unexpectedly.
On the server, maybe views and row-level security as @unodgs has written about here could help. Don’t forget that GraphQL can also have over-fetching security vulnerabilities if resolvers are not carefully implemented, so its not as crazy as it seems.
For mutations, because they are quite dangerous and require a lot of validation, we could fallback to REST, GraphQL mutations, RPC. Unless there is great value in allowing bulk INSERT/UPDATE as a sub-query of a SELECT, its probably better to avoid sending them server-side, and only use them to update our local data model.
Interesting
Maybe SQL could be our unified data model if we can get Postgres’ foreign data wrapper extension to work in the browser, wrapping LocalStorage, Chrome Extension APIs, IndexedDB, etc. Even perhaps third-party APIs — in a similar way that GraphQL federation works.
An advantage of a local effortlessly-normalized cache is we don’t have to worry so much about how we write our queries, because they will most likely hit the cache, and we can rely on some smart logic to retrieve the data that is missing.
With the WAL (write ahead log) we also have the ability to time-travel like Redux promised us. This would also come in handy perhaps in efficiently syncing changes, and rolling back optimistic UI updates on server error.
Maybe things like SSR and PJAX will mean we rely less on client-side state in the future though, and UI updates will come as HTML over WebSockets or something like that.
Could all a user’s data be represented by a single SQLite database synced frontend/backend that can be downloaded to provide a completely offline experience or data takeout feature?
Maybe we could also explore local-state as SQL too, à la Apollo local-state.
Other approaches
There is so much tooling and momentum around GraphQL, it would be good to salvage it. Maybe we could build a local SQL-based cache for GraphQL queries, and use something like Prisma’s data model definition schema to map our GraphQL queries to our local SQLite database.
Something other than SQLite. It’s sad that WebSQL disappeared but as I recently heard Jake Archibald mention on a JS Jabber podcast episode, what would be better is a byte-level storage api so that people can implement their own efficient SQL engines in the browser.
Why now?
SQL is everywhere today. No-code tools are all embracing SQL. Google Sheets, Airtable, Retool. The best way to unleash your non-technical employees is to get them access to your data via SQL. Everyone wants it. That’s why there is such a plethora of new tools out there.
SQLite is being mentioned a lot too recently, which is one of the reasons I thought about writing this down. If more people are comfortable using it
Thoughts?
What am I missing? I’m keen to play around with this to see if it’s workable, because the one thing for sure is that the way we currently do optimistic UI updates with Apollo and react-query is untenable.
—
HN Discussion: https://news.ycombinator.com/item?id=26822884
