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

In this post I will walk through the options and drawbacks of working with many-repos, and introduce the idea of the multi-monorepo.

Monorepos are great

You checkout a single repo. Tooling configuration (lint, style, etc.) can be shared across all packages. Make a change to some code in a package/service. Use your IDE’s refactoring to find all usages of the modified code. All dependent packages’ tests are run. Publishable packages are rebuilt and published. Deployable packages are deployed. And all this can be done in a feature branch. You’ve heard this all before.

In a “many-repo” setup, the need to modify code in just one external repo quickly becomes a nightmare.

Problems with “many-repos”

Let’s take the example of needing to make a breaking API change to a transitive dependency of a service.

E.g. Service depends on Foo which depends on Bar. You make a change in Bar, which requires a change to Foo, which requires a change to Service.

Service depends on Foo depends on Bar

The key is being able to make these three changes on an atomic feature branch that is deployable to a test environment and can be checked-out, manually tested and reviewed on a co-workers computer, and discussed in an online PR tool.

To access dependencies in other repos your options are:

• git submodules
  Your repo stores a reference to a commit of another repo and pulls in the files via git cli commands.
• many-repo management cli tool
  You checkout multiple repos and symlink the packages between repos. These cli tools allow you to sync changes.
  E.g. meta, gita, gr
• package registries
  Importing published packages via a package registry like npm.
• git subtrees
  Store a copy of the code from the other repo in your git tree and sync changes back and forth.

Git submodules

Only store a reference to a commit so if you wanted to make a feature branch, you would need to manually create that branch in two other repos, and keep them in sync. This can be tedious and error-prone.

Many-repo management tools + symlinks

If you checkout multiple repos, you can use the aforementioned tools to branch multiple repos with one command. You would then symlink the packages together.

Symlinks in Node.js present many difficulties due to the way that the Node.js module resolver uses the `realpath` rather than the logical path to resolve dependencies, breaking peer dependency resolution in some cases.

And what happens if you need to branch from a branch? Or pull a deployed tag when fixing a production bug? It starts getting insane rather quickly. Whereas with a single repo everything is extremely simple.

Package registries

This is the most painful way. Sharing code via package registries means you would probably check out two repos for Foo and Bar, have a script to symlink everything together, make your changes, run tests in three separate repos, then unlink, publish alpha versions, and hope everything works.

The mentioned issues with symlinks above leads to tons of risk that what you have tested and was working locally will break when using published packages. If a bug appears that wasn’t covered by a test, then you have two more packages to publish and you will lean heavily on your tooling for this. If anything goes wrong, you are in trouble.

Git subtrees

Git subtrees involve syncing commits from other repos inside your actual repo. When a new commit is made in another repo, you run a command that pulls in those commits to a sub-folder in your code base. When you want to push updates back out, you filter all commits that touched that sub-folder and merge them back.

The advantage here is that there is a single commit SHA identifying all your deployed code and its dependencies. If you need to make changes to Foo and Bar, then you simply make the changes as one commit, test, push, deploy, done. No complicated tooling to think about, or anything that will prevent you from fixing your bug.

The question to be asked at this point though, is what is the point of us even having other repos? Why can’t we use just a monorepo?

What can’t we use just a monorepo?

Monorepos have their disadvantages too. Noisy commit logs, slower CI pipelines, confusing and unfriendly for open-source contributors (must checkout and build entire codebase usually).

But there is one main thing they cannot do. Access rights per package/service.

The most common example would be a company open-sourcing a package that is a dependency in their private codebase.

When this is necessary it brings us back to all the downfalls of many-repos mentioned above. I would argue that this is a huge disincentive to more companies open-sourcing more stuff.

The “multi-monorepo”

The multi-monorepo approach is simply a monorepo, with one or more other repos synced in using git-subtree.

You would create a private repository similar to this:

- package.json
- repos
  - org
    - services
      - service-a
    - libs
      - foo
      - bar
  - org-open-source
    - libs
      - some-open-source-thing
  - org-experiments
    - libs
      - some-unstable-thing
  - client-project

The idea is to separate your code into top-level folders based upon access rights and sync these to other monorepos. I keep these folders in a folder called repos. (NOTE: Some may not necessarily be separate git repos as I mention below but I couldn’t think of a better name.)

Splitting at the top-level makes it clear to your team what is open-source and what is confidential, as well as to simple tooling to verify no confidential code leaks into open-source world.

There are other reasons to split code at the top-level, not all of which would need to be synced to other monorepos, but more-so to replicate “many-repo” ergonomics (vague word but will make sense when you read below). These reasons include:

• Code stability/quality. In a many-repo world you would create a new repo for code experiments. Experiments might not require as high a level of test coverage or code quality checks as the rest of your code base. You would probably keep this in an ‘experiments’ branch and rebase often. Even so, the mental load is greatly reduced from having an entire other folder and the code quality rules can easily be set to ignore code in this folder.
• Working with other companies. Say your company is a web design agency. Maybe you have some libraries or a framework in active development that you use to support the projects you build for clients. You could work on your client’s project in your main monorepo alongside your framework or internal tooling. This framework or tooling would be published to a package registry. You would then sync your client’s code to a repo for them to use which would reference your open-source framework packages from the package registry.
• Solo developer working on many projects. Say you have a few private side-projects you are working on outside of your day job. You probably will want to share some code or tooling at one point between them. For example say you have 5 small websites and you don’t want to rewrite the same authentication code over and over. You could create a “repo” folder for each of the projects. If one starts to take off and you decide to hire someone to work on it — you just sync that to another repo.

Implementation

Currently I use the excellent git-subrepo project which is a shell script similar to subtree but easier to use. It’s quite a complicated script and when things go wrong it can be quite difficult to repair without spending a good bit of time grokking how things work. For this reason I was planning on rewriting it in TypeScript to make it easier to recover from bad state, and more helpful error messages — but I recently discovered adeira/shipit which I will try first.

Some other interesting similar tools:

• Facebook built FBShipIt to help them open-source code from their monorepo.
• I recently found adeira/shipit (from Kiwi.com devs) which is a FBShipIt implementation in JavaScript which I am very eager to try out. The project is active since Jun 2020.
  They call the master source-of-truth repo the “universe” repository.
• The PHP Symphony framework uses splitsh/lite shell script. There is a good presentation on that page about this topic and lots of good thinking.
  They call it a “mono/manyrepo”.
• korfuri/awesome-monorepo has a great list of monorepo tools that include some that allow repo syncing.
• Other cool monorepo tools you should checkout include Rush (from Microsoft), Nx, and the upcoming TurboRepo.

Where to next?

Monorepos have been growing in popularity for a while, yet the tooling is still not there yet, and they are only accessible to larger dev shops with the resources to invest in the tooling.

My hope is that multi-monorepo tooling will continue to improve over the coming years to allow more companies to easily open-source parts of their stack, and that all developers can reap the benefits of monorepos whilst remaining able to open-source their work, and to build more projects with more easily shared common code. IDE integration is also needed to allow seamless workflows.

I am working on a simple monorepo framework myself called Live, built upon the pnpm package manager and hope to open-source everything soon. The goal is to achieve a seamless multi-monorepo workflow with a simple CLI and IDE integrations for WebStorm and VSCode to allow developers to share more code between projects and with the open-source community.

Keen to hear your thoughts!

• https://twitter.com/vrouesnel
• https://github.com/vjpr