7 ms·

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’

by neilk 3y ago

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 3y ago
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 3y ago
  IndexDB is even better, it supports a wider variety of data serialization, can be queried and versioned
- justincormack 3y ago
  There is a literal SQL store in the browser its the sqlite Wasm port. Its just panning out a little differently.
  - curtisblaine 3y ago
    Which works only on Chrome, IIRC.
    - justincormack 3y ago
      Not true, it is supported in all the major browsers.
- carlsverre 3y ago
  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).
- LamaOfRuin 3y ago
  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 https://en.wikipedia.org/wiki/Web_SQL_Database
  - LAC-Tech 3y ago
    Firefox fought against WebSQL. Firefox then re-implemented indexedDB with SQLite on their own browser. Firefox has now largely faded into obscurity.
    - Moomoomoo309 3y ago
      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.
      - LAC-Tech 3y ago
        The important thing is - Firefox has been slowly dying for a decade and SQLite has taken over the world.
        veidelis 3y ago
        Firefox is nowhere near dead on my devices!
    - neftaly 3y ago
      The issue was that a specific library would be pinned at a specific version for the rest of the history of the web. As good as SQLite is, I hope to hell we're not still stuck using it to handle mining operations in the oort cloud in 200 years.
      - josephg 3y ago
        This is why wasm is great. Webpages can just ship whatever version of SQLite they want. And/or eventually migrate to something better.
- wouldbecouldbe 3y ago
  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 3y ago
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 3y ago
  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/ https://fireproof.storage/
  - 616c 3y ago
    Very exciting I shall as I was a fan of your prior project!
BackBlast 3y ago
I find that moving the full query system into the front end is where most front end devs really want to be. They want a full power query system for the data instead of continuous rounds of re-inventing the transport layer, REST, GraphQL, *RPC, etc. It's hard to adopt such a system in most traditional web shops with their specialized backend and frontend teams. You're pulling out the database, backend, transport, and auth layers and replacing them with this single block system. Most system architects grew up in the backend so they are generally pretty ignorant of this issue. As it touches both sides extensively you're probably not fitting this into an existing system, which leaves only green field new development. Finally your backend is not an AWS or Asure service, neither is it lambda friendly. All of this means that most architect types I talk to will never touch it. This style of system mostly already exists with old tech, CouchDB+PouchDB. Which works pretty well for some things. The downsides are that the query system isn't really ideal and the auth and data scoping system is pretty foreign to most people. The easiest model to work with is when the data is totally owned by a single user, and then you use the out-of-the-box database-per-user model. High data segmentation with CRDTs removes a lot of conflict issues. It has scaling issues though, CouchDB has really high CPU requirements when you're connecting 10k to 100k users. The tech is long in the tooth though it is maintained. On the system design side it gets really complicated when you start sharing data between users, which makes it rather unsuitable as you're just moving the complexity rather than solving it. This approach seems to hit the same target though will likely have similar scaling issues. Look forward to see the evolution of the system. Looks like a first step into the world.
- cratermoon 3y ago
  Yay, we're moving back to fat clients! What has been is what will be, and what was done is what will be done, there is nothing new under the sun.
  - BackBlast 3y ago
    I'm on the fat client train with my company and I nudge my clients that way if they're open. It's just a great way to build a system.

by neilk 3y ago

giancarlostoro 3y ago

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 3y ago
IndexDB is even better, it supports a wider variety of data serialization, can be queried and versioned
justincormack 3y ago
There is a literal SQL store in the browser its the sqlite Wasm port. Its just panning out a little differently.
- curtisblaine 3y ago
  Which works only on Chrome, IIRC.
  - justincormack 3y ago
    Not true, it is supported in all the major browsers.
carlsverre 3y ago
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).
LamaOfRuin 3y ago
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 https://en.wikipedia.org/wiki/Web_SQL_Database
- LAC-Tech 3y ago
  Firefox fought against WebSQL. Firefox then re-implemented indexedDB with SQLite on their own browser. Firefox has now largely faded into obscurity.
  - Moomoomoo309 3y ago
    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.
    - LAC-Tech 3y ago
      The important thing is - Firefox has been slowly dying for a decade and SQLite has taken over the world.
      - veidelis 3y ago
        Firefox is nowhere near dead on my devices!
  - neftaly 3y ago
    The issue was that a specific library would be pinned at a specific version for the rest of the history of the web. As good as SQLite is, I hope to hell we're not still stuck using it to handle mining operations in the oort cloud in 200 years.
    - josephg 3y ago
      This is why wasm is great. Webpages can just ship whatever version of SQLite they want. And/or eventually migrate to something better.
wouldbecouldbe 3y ago
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 3y ago

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 3y ago
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/ https://fireproof.storage/
- 616c 3y ago
  Very exciting I shall as I was a fan of your prior project!

BackBlast 3y ago

I find that moving the full query system into the front end is where most front end devs really want to be. They want a full power query system for the data instead of continuous rounds of re-inventing the transport layer, REST, GraphQL, *RPC, etc. It's hard to adopt such a system in most traditional web shops with their specialized backend and frontend teams. You're pulling out the database, backend, transport, and auth layers and replacing them with this single block system. Most system architects grew up in the backend so they are generally pretty ignorant of this issue. As it touches both sides extensively you're probably not fitting this into an existing system, which leaves only green field new development. Finally your backend is not an AWS or Asure service, neither is it lambda friendly. All of this means that most architect types I talk to will never touch it. This style of system mostly already exists with old tech, CouchDB+PouchDB. Which works pretty well for some things. The downsides are that the query system isn't really ideal and the auth and data scoping system is pretty foreign to most people. The easiest model to work with is when the data is totally owned by a single user, and then you use the out-of-the-box database-per-user model. High data segmentation with CRDTs removes a lot of conflict issues. It has scaling issues though, CouchDB has really high CPU requirements when you're connecting 10k to 100k users. The tech is long in the tooth though it is maintained. On the system design side it gets really complicated when you start sharing data between users, which makes it rather unsuitable as you're just moving the complexity rather than solving it. This approach seems to hit the same target though will likely have similar scaling issues. Look forward to see the evolution of the system. Looks like a first step into the world.

cratermoon 3y ago
Yay, we're moving back to fat clients! What has been is what will be, and what was done is what will be done, there is nothing new under the sun.
- BackBlast 3y ago
  I'm on the fat client train with my company and I nudge my clients that way if they're open. It's just a great way to build a system.