Offline first support

[MHOOO]

Hi there, internet connectivity in Germany is terrible, which so far has led me to avoid using grist-core for data that I require while on the move. I've been researching on this topic a bit, and it seems grist-core uses typeorm under the hood. I wondered whether offline use might be possible by adding support for an offline first database to typeorm, such as mozillas kinto or pouchdb. These would allow caching any changes locally and then sync once a connection has been re-established. My questions would be:

• Do you see any issues on the side of grist-core that might lead into problems regarding this approach?
• From your docs, you distinguish between databases for tables & metadata, do both implementations actually use typeorm or do you make use of other layers / apis to query & update the sqlite databases?

[paulfitz]

Grist's origin was as a standalone, downloaded program. Then, work was done to network it in an end-to-end encrypted way, between individual devices, via a dumb hub. This idea excited people, but no-one seemed willing to actually install and pay for it. So we tried Grist as a conventional SaaS app, and that is when we started accumulating users.

There's definitely a case for using an offline first approach. I used to regularly avoid using Grist in the tunnel between NJ and NYC. It would be an engineering challenge, but not insurmountable.

Grist has two levels. There's a "home" database, which keeps track of users/sites/workspaces/documents, and that uses postgresql/sqlite via typeorm. Then, each individual document has its own database, which is sqlite accessed directly via node-sqlite3.

For offiline first use, you could get good mileage by focusing on the individual document level. Grist has features at this level that are quite compatible with working offline, due to its history.

[MHOOO]

Yes, that makes sense. One of the reasons I replaced a dedicated app for time tracking with grist-core is exactly because it allows me to use it on several devices without requiring any setup.

I took a quick glance around the code. The way I see it, we have:

• on the client-side: TableData.ts which sends "actions" to the server
• on the server-side:
  • somewhere, above actions are being interpreted and forwarded to DocStorage.ts which executes commands against an SQLiteDB instance.
  • There is also
  • filterUtils.ts which postprocesses downloaded / copied documents
  • gristify.ts which adds/removes metadata to sqlite tables
  • HostedStorageManager which uses some of that metadata

My initial Plan was to:

1. Figure out what to do with server accesses to the DB outside of DocStorage.ts. Should those stay server side or also move to the client?
2. Create an abstraction layer around SQLiteDB to support various backends
   1. one backend for the existing SQLite
   2. one for an offline-first supporting DB (e.g. PouchDB, RxDB, Kinto)
   3. a mechanism to control which backend to use
3. Move DocStorage.ts from server to client
   1. an additional forwarder backend that relays API calls to the server in case of the SQLite3 backend being used
4. Refactor migrations to be backend agnostic
5. and all of the above would require regression testing a fair amount to be sure I don't break anything

One major issue I still see is how all offline-first databases that I have found are document / NoSQL based. This effectively means all DB statements would have to be rewritten in a manner that supports both words: SQL & NoSQL[^1]. I was thinking of something like Orbit to create a backend-agnostic schema to allow interfacing with either world, but that would be a core change with a dependency that I'm not entirely comfortable with[^2].

The topic is very intriguing, but from my fairly broad overview, it appears this is more of a workload than I was initially hoping for. In any case, I will keep thinking about this. Maybe I'll come up with another way[^3]. If I've overlooked a simpler solution, definitely pass it along.

[^1]: Couchbase and its "SQL++" N1QL implementation is the only DB I found which supports SQL statements [^2]: It's been around since 2014 and still appears to be maintaned. But it has not seen any major release yet [^3]: Use of CRDTs via yjs comes to mind

[alvorithm]

Hi @MHOOO are you still interested in this? I came across ElectricSQL and on first inspection it seems like it could be used without having the impedance mismatch between relational and nonrelational data stores? Central DB is Postgres and local terminals are SQLite.

I am unfortunately not a TS programmer and this is just a surface evaluation, but wondering if you are still keen on disconnected operation (am in a bus in Germany ;)) and what you @paulfitz and @MHOOO think of this approach.