[HN Gopher] Stop building databases
___________________________________________________________________
Stop building databases
Author : thunderbong
Score : 234 points
Date : 2023-12-01 17:19 UTC (5 hours ago)
(HTM) web link (sqlsync.dev)
(TXT) w3m dump (sqlsync.dev)
| yewenjie wrote:
| Is this supposed to be run on the server? Then how does it really
| solve the frontend side of issues, I'm just trying to understand.
| ranting-moth wrote:
| I sometimes see interesting links on HN but when I click on
| them and skim through, I still have no idea what exactly it
| does. This is one of them.
| hk__2 wrote:
| It's not exactly clear in the article, but there is a client
| part:
| https://github.com/orbitinghail/sqlsync/blob/main/GUIDE.md
|
| > Step 2: Install and configure the React library
| jokethrowaway wrote:
| Not involved with the project but - this is a database which
| run client side and sync with a database on the server
| carlsverre wrote:
| Precisely! The same database (SQLite) is running on both the
| client and the server. SQLSync provides a custom storage
| layer to SQLite that keeps everything in sync. As changes are
| made locally (optimistically) they are synced into the cloud
| where they are eventually applied to the primary db replica.
| The clients subscribe to changes from the primary db and then
| rebase themselves to stay in sync.
|
| I really need to write up a detailed overview of how this
| works! Thanks for the feedback!
| HatchedLake721 wrote:
| 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/
| no_wizard wrote:
| Ember.js has had more innovations contributed to modern
| framework ideas than any other framework, really.
|
| EmberData, Ember Routing, Ember Multi Applications (can't
| remember what its called, but its a precursor to
| microfrontends) all in one CLI tooling etc.
|
| I could never understand what holds Ember back from being more
| used. I think it used to be performance but I think they
| addressed that with Glimmer many years ago.
| robocat wrote:
| I think you have misunderstood?
|
| The article is responding to the pattern of yet another custom
| data model and custom data API (a 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.
| carlsverre wrote:
| First, thanks!
|
| > It isn't clear how they manage the subscription to data
| updates/inserts/deletions - it mentions supporting triggers,
| but that feels icky to me.
|
| Architecture post coming soon. In the meantime, I want to
| clarify that SQLSync does not use triggers for sync. Instead,
| I hijack SQLites page storage and added page replication to
| it. Writes are consolidated through an API I call the
| "reducer" which allows SQLSync to keep track of which logical
| writes correspond to which sets of page changes. The actual
| sync is pretty dumb: we run the reducer on both the client
| and the server. The client replicates down server pages, and
| then periodically throws out local changes, resets to the
| server state, and then replays any mutations that haven't yet
| been acked on the server.
| frenchman99 wrote:
| You say things like "X is pretty dumb" and then go on
| saying stuff I don't understand. Pretty annoying if you ask
| me.
|
| And that's despite me having worked with Cassandra, Kafka,
| Postgres and a variety of programming languages, DevOps
| tools, having worked with Vuejs and React.
| mst wrote:
| Could you clarify which of:
|
| - page storage
|
| - reducers
|
| - replaying mutations
|
| - acks
|
| you're unclear on?
| matlin wrote:
| 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
| johnny22 wrote:
| postgres has some capability to do that, but does need a
| server.
| matlin wrote:
| 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
| culi wrote:
| I like the implications of this to a "local first" architecture
| pqdbr wrote:
| Can someone explain me how it's syncing the state between two
| different devices without any activity in the Network tab in
| DevTools, not even WS traffic?
|
| I get that you can sync state between browser tabs, but I'm
| trying on two different devices (iPhone and Desktop).
|
| And as far as I can tell, the Wasm layer can't perform network
| requests directly.
|
| UPDATE: In the console tab I can see 'coordinatorUrl:
| 'wss://sqlsync.orbitinghail.workers.dev', but I was expecting to
| see this Websockets connection in the Network tab, and it isn't.
| jasonjmcghee wrote:
| Websockets tracking in the browser can be weird. Try refreshing
| the page while you have WS selected in the Network tab
| pqdbr wrote:
| I did that!
| carlsverre wrote:
| Good catch! SQLSync runs in a shared worker to enable cross-tab
| reactivity and centralise both local storage and the
| replication system. You can inspect the worker at the magic
| url: chrome://inspect/#workers
| tootie wrote:
| The thing I've used for this kind of problem is fusejs which is a
| lightweight search index. You can load it with a list of JSON
| documents and do structured or fuzzy string searches. I find it
| pretty well-suited to the kind of frontend experiences I need a
| lot of data for.
| carlsverre wrote:
| This is cool! Thanks for sharing. Sounds like Fuse would be a
| great solution for a read-only index. But what if you want to
| collaborate on the data with other people?
|
| FWIW check out SQLite's full text search extension:
| https://www.sqlite.org/fts5.html
| recusive_story wrote:
| Local to a webpage, do you feel building wrapper over indexedDB
| instead of sqlite would be better idea?
| carlsverre wrote:
| That's a great way to accomplish local storage, but requires a
| bit of gymnastics to build sync. By controlling the database
| entirely, SQLSync can provide very ergonomic sync to the
| developer and performance for the user.
|
| So it's not that one is better than the other. Just the
| capabilities, performance, and test-ability differs.
| bob1029 wrote:
| Trying to synchronize state between client & server is a cursed
| problem.
|
| You can sidestep it altogether if you make mild UX sacrifices and
| revert to something more akin to the PHP/SSR model. SPA is nice,
| but multipart form posts still work. Just the tiniest amount of
| javascript can smooth out most of the remaining rough edges.
|
| Our latest web products utilize the following client-side state:
| 3rd party IdP claims for auth 1st party session id in query
| args The current document
|
| For the first item, I genuinely don't even know where this is
| stored. It's Microsoft's problem, not ours. All other state lives
| on the server. We treat the client more or less like a dumb
| terminal that punches <form>s all day. We don't even use first
| party cookies or local storage to get the job done. This
| dramatically improved our development experience for iOS/Safari
| targets.
|
| So, I would ask this: What is the actual experience you are
| trying to offer, and why does it justify decoupling of client &
| server state?
| threatofrain wrote:
| It's a very common trend for consumer-facing GUI's to have
| optimistic rendering, and if you're doing that then you're
| juggling client/server state. I still see spinning loaders here
| and then but they're generally for initial content load; e.g.,
| does Gmail make you wait when you archive an email?
| _heimdall wrote:
| Not the GP, but I would include optimistic rendering on the
| list of common patterns that really are a bad idea.
|
| Optimistic rendering means your frontend and backend are
| tightly coupled, error recovery and synchronization is much
| more complex, and you are locked into (likely heavy) frontend
| rendering patterns that add even more complexity and
| coupling.
|
| We've spent well over a decade trying to avoid the fact that
| frontend actions require backend logic to complete. Its a
| losing battle that's just made worse by trying to paper over
| it.
|
| Edit to clarify: this isn't a direct comment on the OP tool.
| I haven't used this tool directly but having a hunch it does
| solve some important use cases for common patterns.
| obeavs wrote:
| ElectricSQL has made massive strides towards solving this fwiw.
| Write sqllite in client, guarantee sync to postgres.
|
| See: https://news.ycombinator.com/item?id=37584049
| athrowaway3z wrote:
| This looks interesting and i might give it a try, but after
| watching the talk i'm still a bit unclear why you choose for
| wasm-in-wasm for the reducer.
|
| I suspect you would be better off by creating a rust reducer
| trait, and lifting that wasm-in-wasm complexity into a new crate
| implementing the reducer trait through wasm-in-wasm for the
| people who want that.
|
| But maybe i'm missing something.
| carlsverre wrote:
| Totally fair question. Nothing is set in stone - but this
| approach made it very easy for me to run precisely the same
| code on both a generic backend (CloudFlare Durable Objects) and
| in the frontend stack.
|
| As for wasm-in-wasm specifically. It would be nice to
| experiment with the component model to load reducers alongside
| SQLSync - but the UX isn't quite there yet.
| 0xbadcafebee wrote:
| 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
| different API requests.
|
| The browser is annoying to control, so you're trying to make
| javascript jump through hoops to do what you want...
| SQLSync is [..] designed to synchronize web application state
| between users, devices, and the edge.
|
| So you want your stateless application to be stateful, to sync
| that state with some other state, so you can cache data easier,
| so you can make the browser pretend it's not a browser.
|
| Can we not just admit the browser is a shitty client app? I get
| that like 50% of the software developers in the world have made
| the browser their literal livelihood and career, but jesus
| christ, we need to end this madness. Either make a full blown VM
| and just write normal client applications that run in it, or stop
| trying to make client applications out of javascript.
|
| It's insane how much of the world's engineering effort and
| technology investment money has gone into putting lipstick on a
| pig. If we took all the money invested in browser ecosystems and
| put it towards spaceflight, we'd be living on Mars right now.
| neilk wrote:
| 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 a WASM app, and it has a decent
| presentation layer. But it doesn't have to be!
| maclockard wrote:
| 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
| DaiPlusPlus wrote:
| > Browsers offer a level of isolation and safety that you
| generally don't get with native desktop apps.
|
| They didn't originally: Java <applets> and ActiveX
| <objects> 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.
| e_y_ wrote:
| I can't speak for ActiveX since I avoided IE like the
| plague, but Java applets were sandboxed. Just that the
| sandbox had a lot of holes.
| DaiPlusPlus wrote:
| Huh - you're right: http://www.securingjava.com/chapter-
| two/
| maclockard wrote:
| > make the browser a full blown VM and just write normal
| programs that run in it
|
| This is actually happening, albeit slowly, with recent efforts
| around WASM etc. If you want a fun hypothetical of where this
| all goes, check out the talk "The Birth & Death of JavaScript".
| Link here: https://www.destroyallsoftware.com/talks/the-birth-
| and-death...
| mhaberl wrote:
| > Anyone remember when "frontend applications" were actual
| applications and not web pages?
|
| I do. And also I remember building those apps. It was not as
| simple as building webapps today.
|
| Yes, there are downsides to this model (a lot of them) BUT you
| can whip up a simple app with a beautiful UI in a couple of
| hours today. It was not like that 25 years ago.
| rglover wrote:
| > It's insane how much of the world's engineering effort and
| technology investment money has gone into putting lipstick on a
| pig.
|
| I'm a JavaScript developer and yes, this is deeply disturbing.
| Even more so after I built a framework [1] that just copycats
| what PHP/Rails does (treat the server like a server, and the
| client/browser like a client--no SPA spaghetti). It works, it's
| fast, and makes development 100x easier.
|
| I applied the same heuristic to deployments, eschewing all of
| the containers dogma and lo and behold, you can achieve great
| performance (and iterative speed) with old school, minimal tech
| (bare metal/VPS and just install deps and code directly on the
| machine).
|
| [1] https://github.com/cheatcode/joystick
| bradley13 wrote:
| 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.
| PH95VuimJjqBqy wrote:
| years and years ago on a C++ forum someone made an
| observation that was eerily similar to yours. I still
| remember it to this day as it stuck in my head.
|
| They made an observation that our industry goes in cyclical
| centralize/de-centralize cycles and that we we were (at the
| time) entering into a centralization cycle.
|
| Now here I am reading a comment that we're going back into a
| de-centralization cycle and I wouldn't be surprised if you're
| the same poster.
|
| probably 15-20 years ago (maybe more?) I made a prediction
| that I still think will come true.
|
| The OS will become the "browser" and applications will run
| directly on the OS and will access local resources through
| standardized interfaces. WebAssembly and things like WebGL
| are already moving us in that direction. Honestly HTML5 was
| the first time I recognized standard updates as moving us
| towards that reality with things like localStorage, etc.
|
| I honestly think if someone more imaginative had the helm at
| MS when the cloud started getting big they would have eaten
| google's lunch by leveraging their desktop dominance into the
| web. Instead they dd inane things like display websites on
| the desktop (win98 IIRC).
| bradley13 wrote:
| Could be - I've been making this observation for a long
| time. The cycles keep going. On the other hand, probably
| lots of other people have commented on it as well...
|
| You may be right about the browser becoming the OS.
| Chromebooks were already a step in that direction. But
| JS/HTML/CSS really is a horrible combination for
| application programming. If the browser does become the OS,
| can we please get decent technology to work with?
| PH95VuimJjqBqy wrote:
| I expect we'll get back to native applications and move
| away from js/html/css.
| PH95VuimJjqBqy wrote:
| I completely agree with you, I'm going to copy part of another
| comment I made
|
| -----
|
| probably 15-20 years ago (maybe more?) I made a prediction that
| I still think will come true.
|
| The OS will become the "browser" and applications will run
| directly on the OS and will access local resources through
| standardized interfaces. WebAssembly and things like WebGL are
| already moving us in that direction. Honestly HTML5 was the
| first time I recognized standard updates as moving us towards
| that reality with things like localStorage, etc.
|
| I honestly think if someone more imaginative had the helm at MS
| when the cloud started getting big they would have eaten
| google's lunch by leveraging their desktop dominance into the
| web. Instead they did inane things like display websites on the
| desktop (win98 IIRC).
|
| -----
| Hammershaft wrote:
| I totally agree! I would love a VM designed for development
| simplicity & performance that is built with a standard protocol
| & interface for accessing arbitrary VM applications over the
| net.
| msie wrote:
| What I find annoying is the still-existing problem that
| sometimes apps don't load properly and you have to refresh the
| browser. You don't get this with desktop apps. There are some
| caching capabilities in browsers but they are not being used by
| anyone to cache app code and resources. If I'm using an app for
| the first time it should properly load all code and resources
| or else report an error.
| msie wrote:
| I won't forget that someone at Google didn't have the courage
| to enable Dart in Chrome as a successor to Javascript. And
| someone killed SQLLite as a in-browser db.
| neilk wrote:
| 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 syncs from
| multiple clients with a clever but simple reactive algorithm.
|
| It's a radical attack on the whole problem. Much of our work as
| developers is about syncing data. When you start looking at React
| and REST APIs as a kind of sync procedure, this approach can open
| a lot of new possibilities. You don't have to write a weird
| bespoke database of trees of objects fetched and cached from the
| API any more. You can just update and query it locally, with all
| the power of a relational database.
| giancarlostoro wrote:
| 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).
| no_wizard wrote:
| IndexDB is even better, it supports a wider variety of data
| serialization, can be queried and versioned
| carlsverre wrote:
| 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).
| justincormack wrote:
| There is a literal SQL store in the browser its the sqlite
| Wasm port. Its just panning out a little differently.
| LamaOfRuin wrote:
| 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
| LAC-Tech wrote:
| Firefox fought against WebSQL. Firefox then re-implemented
| indexedDB with SQLite on their own browser. Firefox has now
| largely faded into obscurity.
| Moomoomoo309 wrote:
| Tbf, the WebSQL standard was not well-written from how
| I've heard that story told. It was bug-for-bug exactly
| standardized to a particular version of SQLite, which is
| not a good way to write a standard.
| wouldbecouldbe wrote:
| Because if this works it's amazing. Realtime sync with
| offline support out of the box, while not having to develop
| state management on client and api, but in one place. Those
| are very hard problems, done with less development. Will
| definitely give it a shot.
| commonenemy wrote:
| That sounds awfully like Couchbase, which allows you to
| query/update databases that will sync to remote and the back to
| peers. And you can control the process (auth/business logic)
| with sever side JavaScript plugin with ease.
| jchanimal wrote:
| Creator of Couchbase Mobile here -- I'm doing a new web-based
| thing[1] with a similar reactive API. I'm hoping that my
| encrypted block replication makes it more of a "data
| anywhere" solution than a "local first" database. But the
| paradigm of powerful databases in the browser is definitely
| one I'm glad to see becoming popular.
|
| [1] https://fireproof.storage/
| padjo wrote:
| Many times the thought "what if we just shipped the database to
| the client" has crossed my mind in large multi tenant apps where
| individual datasets were relatively small. I've never gone far
| with it as it seems sufficiently outside the norm to be a cursed
| architectural pattern. Would be nice to find out I was wrong.
| carlsverre wrote:
| I'm also interested to find out if it's cursed :) So far it's
| been a lot better than I expected. Apps like https://sqlsync-
| todo.pages.dev are trivialised with this pattern.
|
| Tons of work to do in order to really prove it out though. But
| I'm pretty excited to keep pushing and see where this goes.
| nikita wrote:
| My understanding is that we can sync any sqlite state to any
| other sqlite state using custom built replication.
|
| Is this how it works and how does it update all the web
| components?
|
| Would it work with all the frameworks or a custom framework is
| needed?
| carlsverre wrote:
| That's the dream! Currently SQLSync wraps SQLite in a fairly
| heavy weight way as it needs to both intercept the storage tier
| (to replicate page changes) as well as the mutation layer (to
| provide the reducer api). I'm interested in making it lighter
| weight and perhaps a regular SQLite extension you could install
| into any instance of SQLite.
|
| As for the web integration, SQLSync works with any framework
| but currently only ships with a React library. Most of it's
| logic is framework agnostic though.
|
| SQLSync also provides a query subscription layer that is table-
| level reactive. What this means it that the client API can
| subscribe to a query which will automatically re-run when any
| table dependencies change. I'm exploring more granular
| reactivity, however for many datasets re-running on table
| change is sufficient when coupled with OLTP query patterns and
| small-medium sized data.
| lifeisstillgood wrote:
| 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 -
| carlsverre wrote:
| I agree! One of my goals is to make the mental model of SQLSync
| easy to grok for the developers using it. I'm biased, but I
| find the rebase model much easier to understand than CRDTs.
| cdchn wrote:
| 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
| hughesjj wrote:
| Collollary: if you don't represent state in more than one
| place, you'll eventually run into loss of availability
| (accessibility), integrity, of existence of data
|
| Thus, bugs forever is a given.
| Waterluvian wrote:
| Best to have one less major level of abstraction where that's
| happening then.
| TravisCooper wrote:
| This is the proper take
| CharlesW wrote:
| After a bit of digging I learned that RYG is this person, for
| anyone else who's curious: https://fgiesen.wordpress.com/about/
| svilen_dobrev wrote:
| 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 :)
| garaetjjte wrote:
| Does it needs to download whole database on startup, or can sync
| only what client queried?
| carlsverre wrote:
| Currently it's full db sync. Partial replication is in
| research.
| m9t wrote:
| Offline/local-first based on SQLite seems hot right now. Third
| one I'm reading about this week. And it sounds good to me!
|
| But how does it compare to ElectricSQL[1] and PowerSync[2]?
|
| [1] https://electric-sql.com/ [2] https://powersync.com/
| carlsverre wrote:
| Indeed it's a very hot space! So exciting to see all the
| different approaches.
|
| ElectricSQL and PowerSync are both tackling the very hard
| problem of partial replication. The idea is to build a general
| solution which allows a traditional centralized db to
| bidirectionally sync only what's needed on the client side -
| while still supporting optimistic mutations (and all the
| consistency/conflict stuff that goes along with that).
|
| The downside is implementation complexity. Both require the
| ability to keep track of precisely the set of data on each
| client in order to push out changes to only that subset of the
| overall database. In addition, specifying which subsets of the
| database state to pull down requires a new DSL and is a new
| thing to learn (and optimize). That said, I'm stoked they are
| taking on this extremely hard problem so when SQLSync is ready
| for partial replication someone will have already figured out
| the best practices.
|
| SQLSync, on the other hand, only supports full db sync. So
| every client will see a consistent view of the entire database.
| You might immediately wonder if this is a good idea - and for
| some apps, it's not. But consider a personal finance app. The
| main goal is cross device sync, cloud backup, offline capable,
| etc. In this case having the entire db stored on every device
| is probably what you want. Another example is a document
| oriented data model, such as Airtable. Each Airtable could be a
| distinct database, thus leaving it up to the client to manage
| which tables they care about.
|
| (added in edit:) By focusing on full db sync, the sync engine
| is much simpler than solutions that support partial
| replication. One benefit of this is that the backend is very
| lightweight. Currently the demo (https://sqlsync-
| todo.pages.dev) runs entirely within Cloudflare Durable Objects
| using very little storage and CPU time.
|
| SQLSync has a ton of work to do to make these use cases
| possible (still very much a prototype), but my initial tests
| have been extremely promising. Hope this helps!
|
| (edit: clarified language regarding centralized dbs and full db
| sync. Also added paragraph regarding full db sync)
| goleary wrote:
| >But consider a personal finance app. The main goal is cross
| device sync, cloud backup, offline capable, etc. In this case
| having the entire db stored on every device is probably what
| you want.
|
| A bit confused by this. If I'm a developer of a PFM, I don't
| want anything but a single user's financial data synced to
| their device. This sounds like partial replication to me.
| carlsverre wrote:
| Precisely. In the SQLSync model - every user would have a
| private database just for their data. For example, this is
| how the todo list demo works: https://sqlsync-
| todo.pages.dev
|
| (Note: currently SQLSync's server tier doesn't support
| auth, just random 128bit ids. Auth will come as it matures
| - but don't use this for anything super secure at the
| moment).
| rococosbasilisk wrote:
| Phillip from PowerSync here, always good to see more people
| working on problems in this space.
|
| A few things to clarify:
|
| > _one multi-tenant centralized db to bidirectionally sync_
|
| PowerSync supports syncing from multiple databases.
|
| > _The downside is complexity._
|
| I'd say this is true if you're building a partial replication
| system yourself. PowerSync gives you a ready-built system
| that's been proven at scale and therefore lets you avoid most
| of that complexity.
|
| > _SQLSync, on the other hand, is full db sync._
|
| It's just as easy to sync the full db with PowerSync as do
| partial sync.
|
| Edit: formatting
| carlsverre wrote:
| Thanks for the clarifying points Phillip. I'm a big fan of
| PowerSync! Exciting to see you guys go after the partial
| replication problem.
|
| I've adjusted my comment to be more clear and hopefully
| more fair. I didn't mean to mis-imply anything about your
| service.
| rococosbasilisk wrote:
| No worries Carl, cheers!
| vemv wrote:
| This seems to be one of those problems that entirely disappears
| by ditching SPAs.
|
| Using solutions from the Hotwire or htmx family would mean that a
| query is just a server query - making those fast is a better-
| understood problem.
| reddalo wrote:
| _But_ , if I can be honest, solutions such as Hotwire or
| Livewire are not as snappy as a SPA.
|
| I personally prefer InertiaJs [1], which is some kind of front-
| end router system with its state synced with the server in an
| "old style" fashion.
|
| [1] https://inertiajs.com
| FridgeSeal wrote:
| I don't know what SPA's you have the pleasure of using, but
| most SPA's I'm subjected to are an exercise in molasses like
| interactions and loading spinners.
| myaccountonhn wrote:
| Recently gave htmx a spin. It is absolutely bananas how much
| complexity it removes and how much more productive you become
| as a result. The fact that you can use whatever stack you want
| is also such a blessing. I tried it with ocaml + web components
| and it's a 10/10 super productive experience. Only need one
| build tool that compiles faster than I can blink, no wiring
| needed between frontend and backend to map json, it is just
| insanely productive.
| threatofrain wrote:
| This isn't a problem of only websites. Should mobile and
| desktop ecosystems start making a big move for thin-client like
| the browser? Should a simple app like Apple Reminders or Google
| Tasks have the GUI pause if there are delays or connection
| issues?
| vemv wrote:
| Read-only access for coarse-grained pages (as opposed to
| building a fine-grained ad-hoc DB) seems something reasonable
| (and easy) to cache for any kind of frontend.
|
| That would allow offline viewing to a variety of apps,
| regardless of approach.
|
| Last time I checked Google Docs doesn't primarily allow
| _editing_ offline files, which hints how hard it is to
| support substantial features beyond mere reading.
| pphysch wrote:
| The authorization story in letting the client upload a new
| version of the application database (after a drop in
| connectivity) sounds like a total nightmare.
|
| I just don't think there are "embarrassingly client-side, but
| also needs a server for some reason" web apps that would
| benefit from this in the real world. Even Google's or Apple's
| version of the simple Todo app has a lot of (useful)
| integrations, which means having some level of trust with the
| client.
| skybrian wrote:
| It disappears if your customers have reliable networks, and
| they are either close enough to the datacenter that the
| database is in, or you have sufficiently smart database
| replication. So, often, the problem comes back, but you're
| synchronizing between datacenters.
|
| Running server-side does seem to be one of the problems SQLSync
| wants to handle? I wonder how well it does at that compared to
| other ways of doing it?
| vemv wrote:
| Precisely an implied part of my point was, server-side
| caching, DB replication, CAP, etc are all relatively well-
| understood problems.
|
| One can solve those without reinventing the database, as the
| article denounces.
| willsmith72 wrote:
| i don't get it, how does that solve the same problem for an
| interactive website?
| vemv wrote:
| If you want new data, you just fetch it again from the
| server, and the server returns inherently-fresh data,
| reasonably fast, along with the HTML fragments necessary for
| a re-render (over ajax or websockets)
| willsmith72 wrote:
| i thought the whole premise of the article was that you
| don't want to do that, you want to cache some stuff, and
| instead of writing the cache stuff (a db) yourself, use a
| real db in your frontend.
|
| if you wanted to just fetch data from your server, it's not
| a problem anyway, right? a spa can also just fetch fresh
| data from a server. the whole point of the frontend cache
| was avoiding optimising ux/latency, e.g. for apps with
| global users but not globally deployed servers
| gonzo41 wrote:
| These people will be blown away by server side rendering.
| Caches in front of API's and light weight front ends.
| nusmella wrote:
| An old company I worked for used project management software with
| a check-in/out mechanism for making changes. When you "check out"
| a project it downloads a copy that you change locally, then
| "check in" uploads it back to the server. A project is "locked"
| while in the "checked out" state. We all felt it was an archaic
| mechanism in a word of live updating apps.
|
| After 10 years of building SPA "web apps", that data
| synchronization mechanism feels ahead of its time.
| fjcp wrote:
| Looks very similar to JEDI [0], an early Delphi VCS system that
| worked that way. It gave us the tranquility to know that no
| conflict would appear, as only one developer could work with a
| locked/checked out file at a time. There was no merge those
| days. In contrast, files that were frequently changed in every
| task would always cause a blocking between developers.
|
| [0] https://jedivcs.sourceforge.net/
| hnlmorg wrote:
| There were loads of VCSs that operated this way. And I don't
| miss them one bit.
| carlsverre wrote:
| I'm a fan of this approach. SQLSync effectively is doing this
| continuously - however it would be possible to coordinate it
| explicitly, thus enabling that kind of check in/out approach.
| As for single-owner lock strategies, I think you could also
| simulate that with SQLSync - although you may not need to
| depending on the app. If the goal is to "work offline" and then
| merge when you're ready, SQLSync provides this pattern out of
| the box. If the goal is only one client can make any changes,
| then some kind of central lock pattern will need to be used
| (which you could potentially coordinate via SQLSync).
| alberth wrote:
| Sounds like Lotus Notes.
| sodapopcan wrote:
| Your comment reminds me of
| https://joearms.github.io/published/2014-06-25-minimal-viabl...
| RHSeeger wrote:
| Sounds like RCS [1]. I remember, back when a company I worked
| for switched from RCS to CVS, one of my coworkers was annoyed
| that CVS didn't support locking checkouts.
|
| [1] https://en.wikipedia.org/wiki/Revision_Control_System [2]
| https://en.wikipedia.org/wiki/Concurrent_Versions_System
| ethbr1 wrote:
| And, of course, the default mode of Microsoft Team Foundation
| Server [0], decades after there were better patterns.
|
| So many forgotten locks from lazy devs...
|
| [0] https://en.m.wikipedia.org/wiki/Azure_DevOps_Server#TFVC
| throwaheyy wrote:
| Are you sure? My experience of using TFVC was that it would
| warn you if someone else had opened the file for editing
| but would not actually lock it. Multiple people could edit
| the same file concurrently with standard
| automerging/conflict resolution afterwards.
| RHSeeger wrote:
| I'm definitely not sure. Could very well be the
| transition from CVS to Subversion that I'm remembering.
| It's been a long time :)
| nemo44x wrote:
| Back in the early days of TFS I was briefly at a company
| that went all in on MS tools. TFS was used and to avoid the
| lock each developer had a clone made and after checking
| their clone in the "TFS Guy" in the office would merge it.
| He also had to merge things when later checking had
| conflicting changes.
|
| Now, the best part of this shit show was they had ~30
| different customers and each of these customers had a clone
| of the main thing that would be customized. So the "TFS
| Guy" had to determine if to keep in the customer clone only
| or to propagate to the main and then to all the other
| clones!
|
| Needless to say the "TFS Guy" made a lot of money.
| JohnFen wrote:
| I have to use TFS for a couple of projects where I work.
| I really wish we had a "TFS Guy"!
| partomniscient wrote:
| Now I feel old, I remember "Anything but sourcesafe" [0],
| which was a followup to "Visual Sourcesafe Version Control
| tunsafe at any speed", and having my trust evapourate when
| I found out Microsoft didn't dogfood their own version
| control system.
|
| So long ago I can't remember exactly which but I was
| running a local cvs and/or subversion repository for my own
| work just to avoid issues like the above. s [0]
| https://blog.codinghorror.com/source-control-anything-but-
| so...
|
| [1] https://developsense.com/visual-sourcesafe-version-
| control-u...
|
| To get back on topic, the key thing an explicit database
| gives you is a purpose built-language (and data-integrity
| enforcement etc. if you do it properly), that everyone
| knows. (Or used to? SQL is getting more hidden by
| abstraction layers/eco-systems these days). I'm old, so I
| reach for my older, well understood tools over new and
| exciting. Get off my lawn. It may be over-architecting, but
| I'm also not working in maximising 'performance in
| milli/micro-seconds is vital' high load environments, or
| releasing updated software every other day.
|
| The other issue is tool/eco-system fragmentation.
|
| But when you're young and have the energy and mental
| capacity to abstract out the wahoo for
| effeciency/performance, you do, because you can, because
| its better at the time. In our day everyone was writing
| code to write to code which were effectively the pre-
| cursors to ORM's. It's just part of being young and
| committed to your craft, and wanting to get better at it -
| this is a good thing!
|
| It's only as you get older you start to appreciate the
| "Less is More" around same time that job ads appear with
| "Must have 3 years of SQL-Sync experience" (no offence
| intended here). There are both costs and benefits but which
| and how much of each you only find out years later.
| ozim wrote:
| It solves so many problems and makes it so easy to implement if
| you go this way.
|
| But just like mentioned it is hard to convince people that it
| is what they actually want.
|
| People fall into some grand illusion that everything should be
| always available but in reality then one person is doing
| changes at a time and if somehow 2 or more people have to work
| on something - more often than not they should be talking or
| communicating with each other anyway to synchronize.
|
| Even with GIT and fully distributed development you cannot
| solve conflicts automagically. You still have to communicate
| with others and understand context to pick correct changes.
| calvinmorrison wrote:
| you can only have one person work on the code at a time? that
| seems, very very obviously dumb
| SkyMarshal wrote:
| Multiple people can work on the code simultaneously and
| asynchronously, but conflict resolution must be done
| synchronously.
| ozim wrote:
| I can change A to B on my own, you can change A to C on
| your own.
|
| At some point we have to communicate which change is
| correct.
|
| It does not have to be synchronous and it might be via
| commit message - but still change alone is not enough for
| conflict resolution.
|
| If you edit word document and someone then changes
| something there is no commit message but might be comment
| on document, email or im.
| frenchman99 wrote:
| Interesting. How does server side validation and access control
| work with this?
| carlsverre wrote:
| Good questions!
|
| Validation can be handled in the reducer layer. Since the
| reducer logic re-runs on the server, it can do anything
| including reaching out to server-side resources like
| authorization or other dbs to ensure the client isn't doing
| anything fishy. It's also possible to use the full capabilities
| of SQLite to execute automatic in-db validation via triggers,
| constraints, checks, etc.
|
| Access control is more difficult. Currently SQLSync is full db
| sync, so a user either has read or read+write access to an
| entire db. Validation can stop certain users from writing to
| portions of the db that they don't have permissions to - but
| the only way to restrict reads is to split the data across
| multiple dbs. For some apps this is not ok - and thus I'm
| researching different ways to achieve partial replication.
| fghorow wrote:
| Please, please don't store any passwords, health, or
| financial information in your stack unless you solve this!!!!
| uberdru wrote:
| Stop writing blog posts about why you should stop building
| databases.
| swader999 wrote:
| My front end db would look a lot different than the back end. A
| lot of mutations involve submitting work and waiting for
| distributed jobs to roll up into some kind of partial answer.
| This worked, That part didn't etc. Long running transactions,
| workflow that spans months until the final sign off.
|
| I do need better in the moment state in the client though. Was
| looking at react query with perhaps websockets for cache
| invalidation. It's nice to see this sqlsync idea too though to
| consider.
| carlsverre wrote:
| This is solid feedback. One integration I'm stoked to
| build/find is some kind of durable job queue that can sync
| directly with SQLSync. Would be so nice to just commit a job
| request into the db and know it will eventually run and update
| the state. If anyone wants to experiment with this let me know!
| :)
| amelius wrote:
| In other words, "let me show you how to turn your super cool
| project into a CRUD app".
| carlsverre wrote:
| Author here. Finally got through most of the questions, will keep
| checking periodically for ones I've missed (has someone built a
| better way to track HN discussions?).
|
| I'm very happy to see the discussion thus far. This first post
| focused on the parts of frontend engineering that motivated me to
| create SQLSync rather than specifically how SQLSync works. I'll
| be addressing that in my next post. Thanks for all the great
| questions! Keep them coming :)
| tegmarks wrote:
| Thankyou for the very informative article, I appreciate the
| irony using a post titled "Stop building databases" to announce
| a new database :-)
| carlsverre wrote:
| You're welcome! And I'm glad you enjoyed it. Once I thought
| of that title I had to use it.
|
| My only saving grace is that technically I didn't make a new
| DB - just using good ol SQLite. Mostly. :)
| crubier wrote:
| This is very exciting, I really love the LoFi (Local first) wave.
|
| The need to write the reducer in Rust for now is a big bummer.
| Rust is cool, but JS is easier to get started quick.
|
| ElectricSQL is an alternative which is cool too.
| carlsverre wrote:
| Yea, 100% agree. I'm starting to figure out what that looks
| like in this issue:
| https://github.com/orbitinghail/sqlsync/issues/19
| roenxi wrote:
| There is an interaction here between the "what gets measured gets
| managed" principle and the sunk cost fallacy.
|
| The problem with databases is actually complexity. Any individual
| feature is more or less safe, but around the time reliability,
| caching and indexes get matched together there is a complexity
| explosion and it doesn't (normally, anyhow) make sense to
| implement a domain-specific DB (call is a DSD?).
|
| But, around the time a company has invested in implementing those
| 3 features and discovered that it has sunk a lot of resources
| into the DSD, is politically averse to recommending it be
| stripped out and there is a high real cost to taking out the tech
| debt in one go.
|
| Really the problem here is SQL's syntax. If using a basic
| relational database was a pleasant experience that involved some
| familiar C-like syntax instead of broken English people would be
| more tempted to go with a DB instead of rolling their own. The
| NoSQL databases were a good step in that direction, but then they
| by and large overfocused on big data instead of everyday
| usefulness. Things like Redis took hold which is nice.
|
| Making it easy to run SQL is a reasonable approach, but the
| problem is that the good databases - I like postgres - are SQL
| native and it is hard to get efficiency without speaking the DB's
| language. We really need a PostgresPostSQL database that is a
| perfect postgres clone but primary parser supports a language
| with good syntax.
| ozim wrote:
| Isn't this idea something like couch-db? Then there is pouch-db
| which is browser implementation using local storage.
|
| So nothing new but it is not bad not to be first. Maybe it is bad
| not knowing prior work and writing up your idea like it is
| something no one ever thought earlier about ;)
| rcvassallo83 wrote:
| Reminds me of an observation that any sufficiently large C / C++
| program ends up writing it's own garbage collector
| KolmogorovComp wrote:
| How does this compare to using directly an ORM lib that supports
| browser like TypeORM [0] via SQL.js [1]?
|
| [0] https://typeorm.io/ [1] https://typeorm.io/supported-
| platforms#browser
| carlsverre wrote:
| Good question! You can use a ORM with SQLSync. Currently
| SQLSync doesn't provide an ORM layer for two reasons: 1. there
| are many that exist, it's better to integrate 2. it's a
| prototype so I started with the lowest common denominator which
| is raw SQL.
|
| SQL.js is an inspiring project in the space and led to official
| upstream support for compiling SQLite to Wasm. Without these
| projects SQLSync would have been much more difficult (akin to
| the original difficulty of SQL.js in the first place). That
| said, SQLSync is also unique from SQL.js in that it includes a
| synchronization layer that coordinates with the SQLSync server
| to provide real time collaboration between users.
| rcvassallo83 wrote:
| Reminds me of the observation that for a sufficiently complex C
| program, one starts to build their own garbage collector.
| foobarbecue wrote:
| So this person is building meteor.js minimongo, but for sqlite.
| Awesome!
| zlies wrote:
| I'm trying to achieve something similar with SignalDB:
| https://signaldb.js.org/ It uses signals for reactivity and is
| framework agnostic with a mongodb-like query syntax
___________________________________________________________________
(page generated 2023-12-01 23:00 UTC)