4 ms·

I'm currently writing a very similar article about "full-stack databases" which highlights the same pattern where many apps end recreating the logic of our bac

by matlin 3y ago

I'm currently writing a very similar article about "full-stack databases" which highlights the same pattern where many apps end recreating the logic of our backend and database in the frontend client code. The solution we're promoting is to choose a database that can run on both the server and in the client and then sync between them. The reason we aren't using Sqlite for our product is because Sql is frankly not the right tool for querying data for an application. It doesn't easily map to the data-structures you want in your client code and nearly all SQL databases have no way to subscribe to changes to a query without polling the query repeatedly. So if you like the idea of having a complete database on your client but also want deep integration with Typescript/Javascript check out what we're building at https://github.com/aspen-cloud/triplit https://github.com/aspen-cloud/triplit

johnny22 3y ago
postgres has some capability to do that, but does need a server.
- matlin 3y ago
  Yeah you can subscribe to overall changes to the data on a row by row basis but can't subscribe to an actual query. Many apps and libraries imitate reactive queries by just refetching all queries from Postgres when any data changes or just repeatedly polling the query every 10 seconds or so but this puts a lot of strain on the database. You can just subscribe to the replication stream but then you're left trying to reconstruct your queries in your application code which is extremely error prone and painful
  - cpursley 3y ago
    Could you explain in more detail the use case for subscribing to the actual queries (instead of the change events)?
culi 3y ago
I like the implications of this to a "local first" architecture
qudat 3y ago
> It doesn't easily map to the data-structures you want in your client code I disagree. Normalizing data is critical for FE reactive applications, keeping data up-to-date basically requires it; all CRUD operations are much easier to handle.
Hithredin 3y ago
Realm Sync, Mongo + Kotlin MP will cover basically all platforms (server, web, mobile, desktop)... at a cost. Actually interested by alternatives. Will this be part of your article?
matharmin 3y ago
SQLite does actually provide the mechanisms required to listen for changes via update hooks. It's unfortunate that many SQLite bindings don't expose that. I'm using it in a very simple way - automatically rerun the query if data in the underlying table changes. It's perhaps not as efficient as incrementally updating the results, but with how fast queries in SQLite usually are, I find that doesn't really matter.
cpursley 3y ago
Actually, Postgres provides a great way to subscribe to real time changes via the WAL. I even maintain an open source library for this: https://github.com/cpursley/walex https://github.com/cpursley/walex

by matlin 3y ago

johnny22 3y ago

postgres has some capability to do that, but does need a server.

matlin 3y ago
Yeah you can subscribe to overall changes to the data on a row by row basis but can't subscribe to an actual query. Many apps and libraries imitate reactive queries by just refetching all queries from Postgres when any data changes or just repeatedly polling the query every 10 seconds or so but this puts a lot of strain on the database. You can just subscribe to the replication stream but then you're left trying to reconstruct your queries in your application code which is extremely error prone and painful
- cpursley 3y ago
  Could you explain in more detail the use case for subscribing to the actual queries (instead of the change events)?

culi 3y ago

I like the implications of this to a "local first" architecture

qudat 3y ago

> It doesn't easily map to the data-structures you want in your client code I disagree. Normalizing data is critical for FE reactive applications, keeping data up-to-date basically requires it; all CRUD operations are much easier to handle.

Hithredin 3y ago

Realm Sync, Mongo + Kotlin MP will cover basically all platforms (server, web, mobile, desktop)... at a cost. Actually interested by alternatives. Will this be part of your article?

matharmin 3y ago

SQLite does actually provide the mechanisms required to listen for changes via update hooks. It's unfortunate that many SQLite bindings don't expose that. I'm using it in a very simple way - automatically rerun the query if data in the underlying table changes. It's perhaps not as efficient as incrementally updating the results, but with how fast queries in SQLite usually are, I find that doesn't really matter.

cpursley 3y ago

Actually, Postgres provides a great way to subscribe to real time changes via the WAL. I even maintain an open source library for this: https://github.com/cpursley/walex https://github.com/cpursley/walex