14 ms·
Accidental database programming
- garylensvig 3y ago[dead]
- yewenjie 3y agoIs 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 3y agoI 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 3y agoIt’s not exactly clear in the article, but there is a client part: https://github.com/orbitinghail/sqlsync/blob/main/GUIDE.md https://github.com/orbitinghail/sqlsync/blob/main/GUIDE.md > Step 2: Install and configure the React library
- jokethrowaway 3y agoNot involved with the project but - this is a database which run client side and sync with a database on the server
- carlsverre 3y agoPrecisely! 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 3y agoUnless I misunderstood, feels like I’ve been doing this with Ember Data since ~2013. https://guides.emberjs.com/release/models/ https://guides.emberjs.com/release/models/ There’s also https://orbitjs.com/ https://orbitjs.com/
- no_wizard 3y agoEmber.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.
- HatchedLake721 3y agoEmber Engines :) It's not being used more for the same reason Ruby on Rails is not used more. Both are batteries included frameworks, with a much steeper learning curve than e.g. <script src="https://cdn.com/vue.js https://cdn.com/vue.js"> Plus people like to tinker and wire up things together themselves, like router, rendering, state management, testing, etc. That's more exciting than `ember new project`. I was like that 10 years ago too, but now when I learned the ropes I just want to focus on shipping and providing value, rather than wasting my life wiring up 17 javascript packages of the month together.
- robocat 3y agoI 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.
- 3y ago
- matlin 3y agoI'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 agopostgres has some capability to do that, but does need a server.
- matlin 3y agoYeah 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 agoCould you explain in more detail the use case for subscribing to the actual queries (instead of the change events)?
- culi 3y agoI like the implications of this to a "local first" architecture
- pqdbr 3y agoCan 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 3y agoWebsockets tracking in the browser can be weird. Try refreshing the page while you have WS selected in the Network tab
- pqdbr 3y agoI did that!
- carlsverre 3y agoGood 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 3y agoThe 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 3y agoThis 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 https://www.sqlite.org/fts5.html
- recusive_story 3y agoLocal to a webpage, do you feel building wrapper over indexedDB instead of sqlite would be better idea?
- carlsverre 3y agoThat'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.
- deleted 3y ago[deleted]
- bob1029 3y agoTrying 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 3y agoIt'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 3y agoNot 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.
- pas 3y ago
- athrowaway3z 3y agoThis 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 3y agoTotally 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 3y agoAnyone 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 3y agoYou’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 3y agoNot 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
- neilk 3y agoI’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 3y agoGenuinely 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 3y agoIndexDB is even better, it supports a wider variety of data serialization, can be queried and versioned
- justincormack 3y agoThere is a literal SQL store in the browser its the sqlite Wasm port. Its just panning out a little differently.
- curtisblaine 3y agoWhich works only on Chrome, IIRC.
- padjo 3y agoMany 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 3y agoI'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 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.
- spacebanana7 3y agoI once did this with a calorie counting app. Even with hundreds of thousands of foods in the database, the app took much less space than most media apps or games.
- jacobsenscott 3y agoThe correct answer is to move the UI back to the server, where the database already is, and just send html to the client side html renderer (or "web browser"). This whole post is just "front end and gone off the rails, over the cliff, and into the ocean."
- padjo 3y agoIt depends.
- nikita 3y agoMy 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 3y agoThat'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 3y agoDon'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 3y agoI 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.
- leafmeal 3y agoI feel like you might be miss the point of the parent comment. Synchronizing between databases is a notoriously difficult problem. It's really hard to do while avoiding race conditions. To be fair, I don't understand the "git rebase" technique your project uses, but I'm doubtful it solves these problems. The underlying issue is that users of SQLsync are going to assume that consistency works, but in suble and unsuspecting ways it won't. As far as I can tell, the only solutions that handle distributed consistency are those that use CRDTs.
- carlsverre 3y agoI've thought a lot about this, but sure there are still some rough edges to figure out. See this comment for a bit more of a deep dive into how SQLSync works and why it might be possible to make it easier for devs to write correct code that converges. https://news.ycombinator.com/item?id=38502987 https://news.ycombinator.com/item?id=38502987 Also, I'm considering adding a CRDT column type to SQLite to enable embedded collaborative use cases while using the relational model for the rest of your data's structure.
- 6gvONxR4sf7o 3y ago
- cdchn 3y agoGive 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 3y agoCollollary: 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 3y agoBest to have one less major level of abstraction where that’s happening then.
- TravisCooper 3y agoThis is the proper take
- CharlesW 3y agoAfter a bit of digging I learned that RYG is this person, for anyone else who's curious: https://fgiesen.wordpress.com/about/ https://fgiesen.wordpress.com/about/
- deleted 3y ago[deleted]
- svilen_dobrev 3y agoi 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 3y agoDoes it needs to download whole database on startup, or can sync only what client queried?
- carlsverre 3y agoCurrently it's full db sync. Partial replication is in research.
- m9t 3y agoOffline/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/ https://electric-sql.com/ [2] https://powersync.com/ https://powersync.com/
- carlsverre 3y agoIndeed 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 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)
- 3y ago
- vemv 3y agoThis 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 3y agoBut, 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 https://inertiajs.com
- FridgeSeal 3y agoI 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.
- qudat 3y agoYeah, they are building SPAs incorrectly by relying on something like `react-query` which is waterfall rendering land. People don't truly understand the sacrifice they are making by using `react-query`. It isn't designed for large scale SPAs, it's designed for small sites that just need to fetch a little data.
- FridgeSeal 3y agoI can’t help but feel, if SPA’s keep getting “built wrong”, then at some point, we ought to look at why people keep building them wrong.
- reddalo 3y agoI agree, most SPA's are very poorly designed. But others work so well that you don't even notice it. Random examples: Gmail, Notion.
- nusmella 3y agoAn 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 3y agoLooks 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/ https://jedivcs.sourceforge.net/
- hnlmorg 3y agoThere were loads of VCSs that operated this way. And I don’t miss them one bit.
- carlsverre 3y agoI'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 3y agoSounds like Lotus Notes.
- 3y ago
- frenchman99 3y agoInteresting. How does server side validation and access control work with this?
- carlsverre 3y agoGood 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 3y agoPlease, please don't store any passwords, health, or financial information in your stack unless you solve this!!!!
- frenchman99 3y agoThanks for explaining!
- uberdru 3y agoStop writing blog posts about why you should stop building databases.
- zlg_codes 3y agoStop writing comments about blog posts that ask to stop building databases. We can keep this going guys.
- swader999 3y agoMy 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 3y agoThis 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 3y agoIn other words, "let me show you how to turn your super cool project into a CRUD app".
- carlsverre 3y agoAuthor 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 3y agoThankyou for the very informative article, I appreciate the irony using a post titled "Stop building databases" to announce a new database :-)
- carlsverre 3y agoYou'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. :)
- dev-tacular 3y agoThe title certain piqued my interest!
- kaishin 3y agoAny plans to support mobile? That’s where I’d consider using this most.
- carlsverre 3y agoYes! I'm looking at how to evolve this into a native SQLite extension which would dramatically improve distribution.
- onemiketwelve 3y ago
- crubier 3y agoThis 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 3y agoYea, 100% agree. I'm starting to figure out what that looks like in this issue: https://github.com/orbitinghail/sqlsync/issues/19 https://github.com/orbitinghail/sqlsync/issues/19
- remram 3y ago> LoFi (Local first) Are we set on this abbreviation? Cause it's super confusing.
- crubier 3y agoI agree it's super confusing actually, lol. I've seen people using it and it's cool, but a non-overloaded one would be better!
- roenxi 3y agoThere 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.
- totalhack 3y agoHave you ever looked at adding a semantic layer on top of a db for those that prefer to avoid direct SQL?
- lmm 3y agoYou'd need databases that were open to splitting out and layering like that, and I don't think any major SQL databases are. (I mean, most SQL databases are still written in C-family languages; splitting out the PostgreSQL parser into its own library took 20 years and still hasn't really been done properly, they do some weird sync thing from the mainline postgresql codebase rather than being the actual trunk for parser development and having postgresql proper use it as a dependency). Maybe you could do something with H2?
- holoduke 3y agoWhat is exactly hard about sql? Every dev imho should know it. And sql syntax is good and proven too be long lasting. Maybe investing some time in actually learning it instead of bashing it will help you further.
- ozim 3y agoIsn’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 3y agoReminds me of an observation that any sufficiently large C / C++ program ends up writing it's own garbage collector
- KolmogorovComp 3y agoHow does this compare to using directly an ORM lib that supports browser like TypeORM [0] via SQL.js [1]? [0] https://typeorm.io/ https://typeorm.io/ [1] https://typeorm.io/supported-platforms#browser https://typeorm.io/supported-platforms#browser
- carlsverre 3y agoGood 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 3y agoReminds me of the observation that for a sufficiently complex C program, one starts to build their own garbage collector.
- foobarbecue 3y agoSo this person is building meteor.js minimongo, but for sqlite. Awesome!
- zlies 3y agoI'm trying to achieve something similar with SignalDB: https://signaldb.js.org/ https://signaldb.js.org/ It uses signals for reactivity and is framework agnostic with a mongodb-like query syntax
- socketcluster 3y agoI also built a similar serverless project but with a focus on no-code. I built a support chat app with it (which we use ourselves) using only declarative HTML components. Supports both public chat and private chat with authentication. It's fully working now but I'm now focusing on walkthroughs/guides. https://saasufy.com/ https://saasufy.com/
- cheema33 3y agoI am getting the impression that this might work for small data sets. Does it work for large data sets? My webapp has to work with 100s of GBs of data in MS SQL Server. Admittedly, I haven't yet read the linked article. But, I plan to.
- pas 3y agoHow much data a single user sees at a time?
- carlsverre 3y agoCurrently, SQLSync copies the entire SQLite database to every client. So in order to support large datasets you would need to split up the data over many databases. Some applications can do this, some can't. It depends on the specific business and usage requirements. Perhaps in the future, SQLSync will support partial replication which would enable this use case. As always there are trade-offs to consider with both approaches.
- unoti 3y agoOne other piece of prior art is Lotus Notes, in which the database was stored locally and synced in the background. https://en.wikipedia.org/wiki/HCL_Notes https://en.wikipedia.org/wiki/HCL_Notes
- byteCoder 3y ago"There are only two hard things in Computer Science: cache invalidation and naming things." -- Phil Karlton
- carlsverre 3y agoI was so tempted to put this quote in the post! One of my faves. I'm also a fan of all the different variations: https://martinfowler.com/bliki/TwoHardThings.html https://martinfowler.com/bliki/TwoHardThings.html
- swyx 3y agoas a frontend dev that went fullstack, i had a similar path too: https://dx.tips/oops-database https://dx.tips/oops-database
- carlsverre 3y agoswyx! I can't believe I missed this post. This is gold. Thanks for sharing!
- swyx 3y agohaha yours is better. nice work and keep up the good fight. am in sf if you ever want to get coffee
- hot_gril 3y agoComing from the backend world, I've only done small frontends that don't get this complicated. Assuming there are legit reasons for a FE to have complex state, a relational database is probably one of the first things you need. It's almost an automatic decision on a backend to have one, same should apply here. Using SQLite instead of Redux sounds reasonable.
- qudat 3y ago> Using SQLite instead of Redux sounds reasonable. The trick is reactivity. `redux` handles surgically updating the view based state changes. It's not enough to have sqlite, you also need reactivity. Further, apply strict types on top of sqlite/SQL is another big challenge.
- hot_gril 3y agoOh right, you'd need pubsub to make this efficient.
- grhmc 3y ago[flagged]
- TheRealPomax 3y ago"where is the frontend optimized database stack we deserve?" it's SQLite3. It's always SQLite3.
- curtisblaine 3y agoQuestion: what's the browser compatibility?
- carlsverre 3y agoCurrently got it working across Safari/FF/Chrome + iOS Safari + a couple android devices I was able to test. Requires fairly recent versions everywhere though. You can test out your own device using this demo: https://sqlsync-todo.pages.dev/ https://sqlsync-todo.pages.dev/ Please file an issue if you find it fails somewhere :)
- anoy8888 3y agoanything similar for react native for mobile apps?
- matharmin 3y agoThere are a couple of similar projects - ElectricSQL, cr-sqlite and PowerSync (which I'm working on) all support React Native.
- daaahhdekfbb 3y agoI misinterpreted the title, I clicked because I thought it was literally about how we should stop building and designing more actual databases, which is something that I agree with and is perhaps a hotter take than the one in the article. There are hundreds of subtly different database out there, but there are only a dozen or two that really ought to exist. So stop making new databases for every little thing folks. They are
- chrisjc 3y agoSorry for perhaps reducing what SQLSync does to any over simplistic description, but is it effectively applying the CRDT pattern on top of the client (SQLSync/sqlite) and servers (Some OLTP) individual operation logs? edit: I mean rebase, not CRDT
- carlsverre 3y agoGood question. I see you edited your post but i'll be clear - SQLSync doesn't use CRDTs in it's current sync architecture. Basically each client maintains their own log of mutations. These mutations are applied optimistically to the local instance of SQLite, and in parallel we replicate the log to the server. On the server side, it reads from all of the individual client logs in a deterministic order and applies the mutations to it's own SQLite database. Under the hood SQLSync hijacks all of the writes to storage and organises them into a format that's easy to replicate. Finally, the the storage log from the server is replicated back down to each of the clients. This leaves the clients in a weird position as they have two versions of the database which may have diverged (probably). So to finish this up, the clients throw away their local changes and reset to the server state (i.e. git reset --hard). Then since there may be some mutations that were executed on the client but not yet run on the server the client simply re-runs those mutations (i.e. git rebase). In this way the system continuously keeps itself in sync with the server and other clients. Conflicts are handled by logic in the reducer which is able to inspect the state of the database to figure out what to do. Yes, this does require writing the reducer code carefully - however in testing I've found this not to be too bad because: 1. A lot of SQL operations automatically converge pretty nicely (and you probably already have to think about them converging in your existing REST api's or w/e backend api you are writing). Think about patterns like `insert...on conflict do...` for example. 2. Since the reducer is isolated and comes with a type description of all possible mutations, it's very easy to unit test the reducer with different orderings. Basically think of it as deterministic simulation testing for your API. Something that's pretty hard to do with normal backend architectures without a lot of mocking and architecture. Hopefully that helps!
- pipeline_peak 3y agofrontend “engineer”
- sublinear 3y agoNo.
- duck 3y agoOff-topic, but was these diagrams created by hand or is there an app that will make hand-drawn diagrams like this?
- superfrank 3y agoI don't know what program the author used, but I'd be surprised if he did them by hand. Excalidraw (https://excalidraw.com/ https://excalidraw.com/) will give a very similar feel.
- carlsverre 3y agoYup, I used excalidraw!
- duck 3y agoThanks!
- hax0ron3 3y agoRelational databases are mathematically elegant and useful in certain ways for arbitrary data retrieval and manipulation, but I don't see why they would be the best fit browser-side for any but the absolute largest and most complicated front-end systems. Is the typical in-browser code really complex enough that one would want to introduce the object-relational mismatch and all of its associated issues? Most in-browser code wants to operate on a limited set of very well-defined tree data structures, which is a bad fit for relational databases because relational databases are designed to represent a generic graph that can be virtualized into a tree arbitrarily and as necessary.
- zubairq 3y agoHaving embedded SQLlite in a programming tool on the browser myself I found this very interesting. I am always happy when these ideas become more mainstream!
- foobarbecue 3y agoFunny how the first two submissions didn't catch on here. I guess the titles weren't catchy enough. https://news.ycombinator.com/from?site=sqlsync.dev https://news.ycombinator.com/from?site=sqlsync.dev
- floodle 3y agoIt can also just be pure chance. Sometimes there is no pattern.
- shepherdjerred 3y agoI've found that TanStack Query [0] solved all of my problems around data fetching for the front end. It supports many different frameworks and supports plenty of advanced features. SQLSync also looks very interesting. I'll try it out in my next project! [0]: https://tanstack.com/query/latest/ https://tanstack.com/query/latest/
- Sleaker 3y agoSo frontend and backend don't really mean a whole lot. You build apps to do some work with a specific deployment in mind and use the tools available to make it performant?
- miellaby 3y agoSo an Sqlite based pouchdb
- fdeth 3y ago> writing all the data mutation logic within a reducer that can be easily shared on both the client and the server This part… makes me feel odd. Is it actually realistic? Reducers for everything, keeping WASM and backend code in sync. Sounds like a lot of work and cognitive overhead.
- carlsverre 3y agoThe idea is that the reducer is your backend code. Think about it like writing your normal API layer. You handle requests and make changes to the database. Since it's Wasm, SQLSync automatically runs it on both the client and the server. Thus you've written your backend once but ran it everywhere. As for the question of "what if my backend should access stuff that the client doesn't have access to" - the answer is that the reducer doesn't need to do the same thing in both environments. It's easy to call out from the Reducer into an Auth API which only runs on the server. SQLSync will guarantee that everyone reaches a consistent state even if the same mutation results in different outcomes when run locally vs remotely.
- fdeth 3y agoSounds pretty cool on paper.
- gboza1 3y ago[flagged]