Live data from Hacker News

Accidental database programming

sqlsync.dev

41–50 of 310 posts

Re: Accidental database programming

#41

Unless I misunderstood, feels like I’ve been doing this with Ember Data since ~2013. https://guides.emberjs.com/release/models/ There’s also https://orbitjs.com/

I think you have misunderstood?

The article is responding to the pattern of yet another custom data model and custom data API (à la Ember).

Instead provide an SQL database (the well proven SQLite) within the front end and use SQL to interact. And sync data from-to the backend DB.

Which one could then slap on a model or ORM layer on top of - should that be one's bent.

It isn't clear how they manage the subscription to data updates/inserts/deletions - it mentions supporting triggers, but that feels icky to me.

Re: Accidental database programming

#42
post #21

I’m familiar with this project - the creator is a friend. I’ll try to get him on here to answer questions. He’s a seasoned database architect. With SQLsync he’s made a way for frontend developers to query and update a remote database as if it was completely located right in the browser. Because it basically is. The power of WASM makes it possible to ship a whole SQLite database to the browser. The magic is in how it…

Genuinely curious why not just cache the relevant bits in LocalStorage / SessionStorage? I seem to remember Chrome trying to add a literal SQL database to the browser, but it never panned out, localStorage became king. I don't mean to downplay the usefulness, just I usually opt for what the browser gives me. I'm huge on WASM and what it will do for the browser as it matures more (or grows in features).

Good question.

First to address the main point: why not cache the relevant bits in some kind of local storage. SQLSync plans on doing this, specifically using OPFS for performance (but will have fallbacks to localstorage if needed).

Second to address the question of why not use built in kv stores or browser side databases. One answer is another question: how do you solve sync?

One approach is using a data model that encodes conflict handling directly, like CRDTs. This approach is easier to put into general kv stores, as syncing requires simply exchanging messages in any order. I find this solution is well suited to unstructured collaboration like text editing, but makes it harder to coordinate centralised changes to the data. Centralised changes are nice when you start introducing authentication, compaction, and upgrades.

Another approach is doing something similar to how Git Rebase works. The idea is to let the application state and server state diverge, and then provide an efficient means for the app to periodically reset to the latest server state and replay any unacked mutations. This approach requires the ability to re-run mutations efficiently as well as efficiently track multiple diverging versions of the database state. It's certainly possible to build this model on top of local storage.

For SQLSync, I found that by controlling the entirety of SQLite and the underlying storage layer I was able to create a solution that works across platforms and offers a fairly consistent performance profile. The same solution runs in native apps, browser sessions (main thread or workers), and on serverless platforms. One of my goals is to follow the lead of SQLite and keep my solution fairly agnostic to the platform (while providing the requisite hooks for things like durable storage).

Re: Accidental database programming

#43
post #21

I’m familiar with this project - the creator is a friend. I’ll try to get him on here to answer questions. He’s a seasoned database architect. With SQLsync he’s made a way for frontend developers to query and update a remote database as if it was completely located right in the browser. Because it basically is. The power of WASM makes it possible to ship a whole SQLite database to the browser. The magic is in how it…

Genuinely curious why not just cache the relevant bits in LocalStorage / SessionStorage? I seem to remember Chrome trying to add a literal SQL database to the browser, but it never panned out, localStorage became king. I don't mean to downplay the usefulness, just I usually opt for what the browser gives me. I'm huge on WASM and what it will do for the browser as it matures more (or grows in features).

There is a literal SQL store in the browser its the sqlite Wasm port. Its just panning out a little differently.

Re: Accidental database programming

#44
Don't give the user a mental model that reality can break ... badly, or invisibly

I fear sync'ing databases instead of client server models is one of those - either your sync mechanism will just melt, or there are deep assumptions not met

Inwoukd feel safer building a set of CRDT primitives to work with if I feel the need for fast UI and stick with forms submit for everything else -

Re: Accidental database programming

#45
Give someone state and they'll have a bug one day, but teach them how to represent state in two separate locations that have to be kept in sync and they'll have bugs for a lifetime -ryg

Re: Accidental database programming

#46

Anyone remember when "frontend applications" were actual applications and not web pages? I'm willing to bet we have reached that point where new devs literally do not remember that time. There comes a time [..] where we [..] need to cache data from an API. It might start off benign – storing a previous page of data for that instant back button experience, implementing a bit of undo logic, or merging some state from d…

History doesn't repeat itself, but it does rhyme. One upon a dark age, we had mainframes and dumb terminals. Then came the first age of the PC - let's pull everything to the client. Then came servers and "thin clients". With faster processors and cheaper storage came the second age of the PC, with only the permanent data storage left on the server. As the Internet grew, centralization came back: web services and the cloud, with clients just serving dumb web pages.

And now we see the beginning of a migration back to client-side computation and storage.

Somehow, though, this latest iteration doesn't make a lot of sense. It's hard enough maintaining data consistency on a web service that may be used by hundreds or thousands of people. Imagine when this data is cached in microdatabases in unreliable browsers.

On top of that, the browser makes an absolutely horrible programming environment for client-side apps. For the programming part, Javascript is an poor language, so you wind up using heavy-duty frameworks like React to make it tolerable. For the UI representation, that's just not what HTML/CSS were ever meant for. So you get frameworks there as well. Young developers think this is just the way it is. No actually, it's more like the worst of all possible worlds. Using something like JavaFX or (I know, I know) even Visual Basic, you can produce a functional, robust UI with a tiny fraction of the effort.

Re: Accidental database programming

#47
post #21

I’m familiar with this project - the creator is a friend. I’ll try to get him on here to answer questions. He’s a seasoned database architect. With SQLsync he’s made a way for frontend developers to query and update a remote database as if it was completely located right in the browser. Because it basically is. The power of WASM makes it possible to ship a whole SQLite database to the browser. The magic is in how it…

Genuinely curious why not just cache the relevant bits in LocalStorage / SessionStorage? I seem to remember Chrome trying to add a literal SQL database to the browser, but it never panned out, localStorage became king. I don't mean to downplay the usefulness, just I usually opt for what the browser gives me. I'm huge on WASM and what it will do for the browser as it matures more (or grows in features).

FWIW, Web SQL was always fine, but could never be standardized, because no one was ever going to redo all the work sqlite has done (when every browser already uses sqlite).

https://en.wikipedia.org/wiki/Web_SQL_Database

Re: Accidental database programming

#48
post #30

Earlier quoted context omitted.

You’re not wrong, but the web won because it had a superior delivery system: URLs. Everything weird about the web era of development has been about contorting everything to be URL-oriented. But consider how WASM is now turning the browser into an app delivery client. Not a “html and json bodged into an app”, but a real honest to god app. This project happens to be browser based because that’s convenient place to put…

Not just delivery, but also security. Browsers offer a level of isolation and safety that you generally don't get with native desktop apps. Things like iOS do bridge the gap a bit more though

> Browsers offer a level of isolation and safety that you generally don't get with native desktop apps.

They didn't originally: Java and ActiveX originally weren't sandboxed and had free run of the visitor's computer.

All major OSes today now have built-in support for process/app sandboxing. I suppose if the "rich client" frontend model (WPF, etc) was more popular then I expect desktop OS application isolation to have been introduced much sooner.

Security development happens where the market demands it, and rarely does it happen where it's actually needed.

Re: Accidental database programming

#49

Earlier quoted context omitted.

Genuinely curious why not just cache the relevant bits in LocalStorage / SessionStorage? I seem to remember Chrome trying to add a literal SQL database to the browser, but it never panned out, localStorage became king. I don't mean to downplay the usefulness, just I usually opt for what the browser gives me. I'm huge on WASM and what it will do for the browser as it matures more (or grows in features).

FWIW, Web SQL was always fine, but could never be standardized, because no one was ever going to redo all the work sqlite has done (when every browser already uses sqlite). https://en.wikipedia.org/wiki/Web_SQL_Database

Firefox fought against WebSQL. Firefox then re-implemented indexedDB with SQLite on their own browser. Firefox has now largely faded into obscurity.

Re: Accidental database programming

#50
i used couchdb (on server, with touchdb on android and ios, pouchdb on web, ..) for this kind of thing. Clients were directly connected to cursors over the localdb. How and when that localdb was exchanging data with server-or-others, was not any more Client's problem :)
Post reply on HN