vaughan

I think the [FoundationDB layer concept][1] said it well:

"When you choose a database today, you’re not choosing one piece of technology, you’re choosing three: storage technology, data model, and API/query language..."

I like the idea of [Apache Calcite][2] that provides an API to access the query planner. I think if you had more convenient access to some of the underlying components you could build a lot of cool stuff. There's too much magic where you punch in an SQL command or a config and hope it eventually does what you need.

I haven't look too much into the internals, but I'm keen to do so soon.

[1]: https://apple.github.io/foundationdb/layer-concept.html [2]: https://calcite.apache.org/

In the simplest case, I'm talking about regular SQL non-materialized views which are essentially inlined.

> incremental computation will provide both lower latency and greater resource efficiency.

Wish we had some better database primitives to assemble rather than building everything on Postgres - its not ideal for a lot of things.

> We made significant investment in investigating this approach coupled with basic incremental updating and have a few small projects in production that takes an approach similar to [this talk][1]. From https://github.com/hasura/graphql-engine/blob/master/archite...

Do you have/know of any findings/lessons learned from these projects?

[1]: https://www.postgresql.eu/events/pgconfeu2018/sessions/sessi...

Yep. I meant it was easy to do it the inefficient way where you just refresh the entire query when any table mentioned in the query changes. You would just have to also check if something was a view and recursively parse the SQL that is used in the view. Just use Postgres `LISTEN` and triggers or the WAL for change monitoring.

> how these changes affect your query

Yeh, this is where it gets tricky. I think it can be simpler though if the SQL is kept simple with less exotic sub-queries, CTEs, JOINs.

Thanks for the links.

    select l.name, i.name from list as l
    inner join list_items as li on li.list_id = l.id
    inner join items as i on i.id = li.item_id
    where items.completed = true

Your options are basically:

1. Polling - re-run query periodically

2. Monitor changes to tables using triggers or CDC

-- 2a. Re-run entire query when any table is modified with DML and diff against previous result

-- 2b. Only re-run query if the changes would effect the result (e.g. if we add an `item` with `item.completed = false` then we know that our above query will never need updating.)

-- 2c. Do an efficient diff using some relational algebra magic. See Postgres IVM link I posted.

I posted the same example [here][1] with some more details.

[1]: https://math.stackexchange.com/questions/4112326/how-can-i-u...

With React you can’t really do that. It’s always following an error message saying: “look near this component” which is often deep in the hierarchy of some third party package’s component hierarchy that is transpiled into a single line with no source maps.

I think there is a huge benefit in making things easy to step-through in a debugger when something goes wrong.

I'd love to get rid of all these client-side state management libraries and just drop an SQL DB in there and allow components to subscribe to changes to queries on record updates, and then sync it to a cloud DB.

One interesting problem I came across is how to know when your local DB has enough records to be able to run a given query. E.g. If your team has 100K tasks in their cloud DB, and you run `SELECT * FROM tasks WHERE tasks.assignee = user.id`, and then another query of `SELECT * FROM tasks WHERE tasks.assignee = user.id AND tasks.completed = true`, the second task is a subset of the first, so it doesn't need to hit the server except to check if any updates were made to the tasks table. Then apply this to all queries including ones with joins. Actually some fun relational algebra can be used figure it out - one of the perks of working with SQL. The problem is actually similar to how to [incrementally maintain a materialized view](https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...) which Postgres actually does not support (Oracle does).

I think we are all abusing GraphQL in this sense. Caching GraphQL data is a world of hurt.

The easiest way to sync is if your data is in the same model as the way it is stored, which is normalized if using SQL. Even when we do normalize most people will represent a 1-M as an array of foreign keys: `posts.comments = [1, 2, 3]` when in the db we use a foreign key on the `comment` entity: `comment.post_id = 1`. This causes such headaches for optimistic UI updates. It's a mess.

We should be using GraphQL to retrieve data and then render it directly.

Ultimately, I think we should be implementing our GraphQL API client-side against a local SQL DB that acts as a pass-through cache. Then your entire app runs offline and instantly respond to queries eliminating the need for a separate client-side cache, and you don't have to worry about about data model mismatch anymore.

> Depends on how much

I think all apps want this - unless they are SSRing with <100ms on every action.

SQL has always been a sort a magical, black-box in that you have very limited control over the query planner, and have to hint at it to do the right thing. (I guess `EXPLAIN` allows you to peak in the box a little)

I found [Apache Calcite](https://calcite.apache.org/docs/algebra.html) rather interesting. It provides the primitives that a DBMS is built from.

I would be interested to compare how PSQL is built in comparison. I would love a more layered/pluggable database that everyone can build off of. I think FoundationDB had this approach.

I think we can achieve a huge simplification in the industry if we develop a universal state-management solution that runs in the frontend and backend.

Without a consistent model, we are forced to write so much additional code to manage caching, optimistic UI updates, and offline capabilities.

Current RDBMS' are not ideal (most data is better represented as a graph), but because the relational model is so widely used in the backend, they are a good candidate for a universal data store on frontend and backend. And it is possible to represent graph-like structures in RDBMS' too, but it makes SQL querying very unfriendly.

GraphQL is an ideal language for declaratively specifying what data you need for the UI layer. If you doubt this, play around with the [Github GraphQL API][1]. It's an incredible experience to be able to explore and pluck exactly what you need. SQL and REST don't come close to this experience.

But GraphQL is essentially providing us a nicer way to write SQL compared to using JOINs, and to output it in a nested format for convenient rendering with nice typing. But it lacks a huge amount of flexibility.

So we should keep GraphQL as our API, but we should implement it client-side against a data model similar to what we use in our cloud db: SQL, and then do real-time syncing or use it as the model for our server-cache.

An added benefit is we don't have to worry anymore about what is local-state and what is server-persisted, or worry about choosing a good state management solution client-side.

The necessary pieces to make this happen don't exist at the moment, but I would have liked to use Prisma as the DB API for in-browser SQLite in the beginning.

I think there is then the opportunity to go full-stack and offer developers a client-side GraphQL library that talks to an in-browser SQL DB and to end the constant turnover in state-management libraries. I think every frontend developer is overwhelmed at this point by the proliferation of these libraries, and the simplicity they go seeking in things like "just use React.Context api", don't offer them what they will eventually need which is optimistic UI updates, offline modes, real-time collaboration, local-first experiences, etc.

I think we are all still in search of the right state management solution and it would be cool if Prisma did it!

[1]: https://docs.github.com/en/graphql/overview/explorer

I see all state as a stream of events building a graph, then we run queries on that graph. Then it’s all about optimizing the queries which we do.

We’ve kind of shoehorned our use cases into whatever dbs we had available and have probably been overwhelmed by the speed we needed to scale our cloud storage resulting in sub-optimal solutions. Or maybe it’s just wishful thinking that there will exist a db to do it all.

Every component’s data requirements are essentially an SQL query, and a lot of client-state could probably be represented in further SQL queries (filtering, aggregation).

Doing joins makes things much more consistent too when the same entities are referenced in multiple places in the UI.

Just like React works declaratively by re-rendering the virtual dom, wouldn’t it be great if we just re-ran a declarative query for the data too?

Kind of like Redux (global state) I guess but a relational model version.

Something undervalued is the debuggability of using a single language. There are so many times I need to debug some tooling, and being able to to just open the stack trace and place a breakpoint/console.log is incredibly valuable. I do it all the time for webpack/babel.

Having to switch to a new toolchain to debug something is tedious, and often impossible to reach the same convenience of JS-based tools.

I think the future is taking small pieces of JS-based tools and adding native extensions to speed up parts of them.

> We were resolved that using strings representing SQL commands lacked the elegance of a “web native” JavaScript API, and started looking at alternatives.

> In another article, we compare IndexedDB with Web SQL Database, and note that the former provides much syntactic simplicity over the latter.

IndexedDB and "syntactic simplicity". For anyone who has worked with that API, boy oh boy.

[1]: https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

Apollo GraphQL is the same company that brought us Meteor by the way.

If you take a look at what you need to write to get good optimistic UI updates it is crazy compared to what Meteor was.

https://www.apollographql.com/docs/react/performance/optimis...

But as you said, if you need optimistic UI updates and you have 200 engineers you will get it done, but developer efficiency seems to not be popular at the moment.

The advantage of this design is you get an app that is extremely responsive and offline capable. Then you just need a reliable way to sync changes. And you always have the option of just re-downloading the entire team db again if it gets corrupted somehow. You could even have logic to indicate the sync status of each table so that you don't need to download the whole table...you could just populate it as your need the data...like a cache.

It might actually be quite easy to version an SQL api, because you have the DDL migrations files and could actually make things backwards compatible to some degree. You would have a translation layer to modify the queries. Of course, your migrations would need to be more detailed tracking how things map to each other. But probably it would get too complex in the end. But not that much different to changes to a REST api really.

Yeh, that's the best idea. A babel plugin could do it easily. I already have have watcher script calling `graphql-codegen` to add types to my graphql queries, which works fine in the workflow.

The article is less about remotely executing SQL, but more about having a relational data model as a cache in the frontend.

However, when sending the query through to the backend on a cache miss, would be useful to only allow queries that have been extracted like so.

Its similar to how Apollo does query [caching][1].

I really like it!

[1]: https://www.apollographql.com/docs/apollo-server/performance...

I think the read/write-through cache to a backend database is the missing piece...at least as far as I can see.

https://softwareengineering.stackexchange.com/questions/2202...

>Byte-level storage

Would be cool.

---

https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

> From article: we think developer aesthetics are an important consideration...we were resolved that using strings representing SQL commands lacked the elegance of a “web native” JavaScript API, and started looking at alternatives.

Seems a bit short-sighted considering SQL is super popular today on the backend.

I think it's just inefficiently implemented. `git subtree` is just a shell script using other git commands. Take a look at the `--rejoin` flag.

Basically, you only need other to create other git repos depending on access rights. That is, proprietary code vs open-source.

The biggest mistake is thinking: "I want to open-source these two packages, so I will create two repos and make them public and then add them as submodules in my private company repo". The problem though is that it's such a natural thought, and people don't realize the pain they are about to inflict.

You could branch the other project but then you have to coordinate this yourself. There is a project called Meta that can help but to me feels like it can quickly get out of control.

Do you find this a problem?