[HN Gopher] A future for SQL on the web (2021)
___________________________________________________________________
A future for SQL on the web (2021)
Author : thunderbong
Score : 64 points
Date : 2023-12-31 11:56 UTC (11 hours ago)
(HTM) web link (jlongster.com)
(TXT) w3m dump (jlongster.com)
| AlexErrant wrote:
| We now have absurd-sql, wa-sqlite, and the official SQLite
| project all running in a browser. Their tradeoffs are discussed
| here https://www.powersync.com/blog/sqlite-persistence-on-the-web
|
| That article was written in July, and in the intervening time,
| Chrome's announced an intent to ship "Multiple Readers and
| Writers in File System Access API" aka `readwrite-unsafe` [0] in
| v121 (stable in 2 weeks) which could help improve SQLite's
| performance even more.
|
| [0] https://chromestatus.com/feature/5172892632875008
|
| Edit: It looks like wa-sqlite already has a prototype VFS taking
| advantage of the above feature https://github.com/rhashimoto/wa-
| sqlite/discussions/116
| Ciantic wrote:
| I couldn't find do these wrappers support read-only SQLite
| databases with HTTP range requests, like in this famous post
| [1]. Phiresky's wrapper supports it, but it seems to be
| rebuilding the whole sql.js [2], I'd rather have it as VFS on
| top of sqlite.org's own WASM module. I like the idea of HTTP
| range requests, but I don't want to run a fork, that will be
| unmaintained in few years.
|
| [1]: https://news.ycombinator.com/item?id=27016630
|
| [2]: https://github.com/phiresky/sql.js-httpvfs
| samwillis wrote:
| It would be quite hard to do a HTTP range request VFS for the
| official build as it's sync only, lots of hoops to jump
| through. The wa-sqlite build on the other hand is available
| as both a sync and async build, and so should be quite
| possible (you can easily build an async VFS). Roy has a great
| range of example VFSs you can learn from too.
| sgbeal wrote:
| > It would be quite hard to do a HTTP range request VFS for
| the official build as it's sync only, lots of hoops to jump
| through.
|
| As the developer of that build, i wholeheartedly confirm
| that. wa-sqlite makes use of asyncify, which is a feature
| we do not want to make use of in the canonical distribution
| because (to make a long story short) it's third-party
| voodoo which can be pulled out from under us, or break in
| incompatible ways, at any time, whereas the sqlite project
| has a long history of posting only its own code, without
| third-party dependencies. It's JS/WASM build necessarily
| depends on Emscripten, but we've also reimplemented all of
| that glue except for the parts which provide the WASM
| imports, which are closely tied to the compilation process
| so cannot simply be swapped out.
|
| > Roy has a great range of example VFSs you can learn from
| too.
|
| FWIW, his work has been a tremendous inspiration for the
| sqlite project's JS code, and the 2nd OPFS VFS is a direct
| port of one of his VFSes.
| samwillis wrote:
| The work that both Roy and yourself have put into SQLite
| in the browser is awesome, there are so many exciting
| things coming that are going to be built on top of it.
| Thank you!
| sgbeal wrote:
| > "Multiple Readers and Writers in File System Access API" aka
| `readwrite-unsafe` in v121 (stable in 2 weeks) which could help
| improve SQLite's performance even more.
|
| As your EDIT notes, Roy (wa-sqlite) has already experimented
| with this and reports great results. Experimenting with this in
| the sqlite project's own OPFS VFS (see https://sqlite.org/wasm)
| is pending, but the feature is not yet widespread enough to
| replace the current VFSes. We've no information on how long it
| will take for the other browsers to catch up with that API.
| Until then, the sqlite project offers two OPFS VFSes, one of
| which trades speed for a moderate degree of cross-tab
| concurrency and another which offers tremendous speed but a
| complete lack of concurrency.
| samwillis wrote:
| Whenever WASM SQLite comes up there is discussion as to if it
| would have been better for WebSQL to have become a cross browser
| standard.
|
| I believe this route with WASM is the correct one. WebSQL would
| have been tied to one single version of SQLite, with no
| alternative implementation.
|
| With browsers adopting safe low level APIs like WASM and OPFS it
| enables a much broader range of databases to be available in the
| browser. We already have SQLite, DuckDB and various vector dbs.
|
| OPFS is still under active development, but with some of the
| changes coming to it in 2024 it's going to become significantly
| better to use.
|
| All of this is part of the enabling tech behind "local-first"
| apps. I'm somewhat biased as I work on ElectricSQL (we sync
| Postgres on a server to SQLite in the browser), but 2024 is going
| to be a supper exciting time for local-first software.
| pjot wrote:
| What are your thoughts around the various vector dbs (pinecone,
| etc)? DuckDB or pgVector or with all of the work going forward
| in Arrow being able to already support vectors/arrays, it seems
| that the specific "vector" class of db is hype/marketing.
| simonw wrote:
| Personally I think vector lookups make more sense as a new
| kind of index for existing databases, as opposed to an
| entirely new class of database.
| samwillis wrote:
| Completely agree.
|
| Have you my chance come across a good SQLite vector
| extension that works in the browser? I haven't found one
| yet.
| simonw wrote:
| sqlite-vss mentions WASM in a few places -
| https://github.com/search?q=repo%3Aasg017%2Fsqlite-
| vss%20was... - I'm not sure how easy it is to get that
| running in the browser though.
| sgbeal wrote:
| > Whenever WASM SQLite comes up there is discussion as to if it
| would have been better for WebSQL to have become a cross
| browser standard.
|
| An oft-neglected detail in such discussion is that WebSQL was
| main-thread-only. When WebSQL was designed that was not a
| serious issue, but it would have been in conflict with the
| directions web design has since taken, making WebSQL a non-
| starter for many modern apps.
|
| > WebSQL would have been tied to one single version of SQLite,
| with no alternative implementation.
|
| Not only that, but with a castrated feature set (e.g. only
| implicit transactions and lack of many of sqlite's SQL
| functions). When trying to benchmark WebSQL vs the sqlite
| project's WASM build, that castration makes it difficult to get
| apples-to-apples comparisons.
| aziis98 wrote:
| How does this handle permissions over data? I wouldn't want a
| user accessing private information of another. How would you
| "hide parts of a table" to a client-side reader?
| IMTDb wrote:
| All data are local; this allows you to use sql.js in the
| browser with persistence.
|
| Unless the developer adds synchronisation primitive to sync
| data between multiple clients all data are local.
| bbor wrote:
| Synchronization in this space being covered under the term
| "Local First" Web Applications, if anyone wants to look into
| it more. Almost all the frameworks I saw in that space have
| access controls built in - both between clients and for
| privileged server-only data.
|
| I agree with the other commenter though that by putting a DB
| on the client, you're implicitly trusting that client more
| than usual to only make acceptable changes to the DB. If
| you're working in a sensitive context, I think Local First
| means you'll have to spend some extra time planning out data
| security
| samtho wrote:
| You really have to shift thinking away from the centralized-
| database model in order to fully understand why this question
| seems ill-informed.
|
| If you recall from the SQLite story, it was created, in-part,
| to provide a local cache to an offline device yet still have
| the same query interface that developers enjoy. Because the
| model of access is shifted to the device, anything you populate
| a client-side SQL database with is already data that is
| accessible by the client.
| munchor wrote:
| If you're looking for a very real use case of SQLite in the
| browser, there's SQLSync[1]. It was recently discussed on HN[2].
|
| [1]: https://sqlsync.dev/posts/stop-building-databases/,
| https://sqlsync.dev/
|
| [2]: https://news.ycombinator.com/item?id=38489307
| throwawaaarrgh wrote:
| The problem it's trying to solve is better addressed by
| removing the need to cache so much data. It's hard enough to
| manage one database without then having to deal with
| replication, and cache invalidation on top of it all.
|
| Treating a browser like a real client application is putting
| lipstick on a pig. The harder you try, the uglier it gets.
| lewisjoe wrote:
| I have a pet idea to write a Notion-like tool that works like an
| old-school desktop app - meaning you should be able to export
| your pages or the entire database as a single sql file and it
| should be open-able from somewhere else.
|
| I also want to leverage the web platform (mainly because it's the
| right thing to do and partly because the web is the only one that
| offers a decent rich-editor environment without as much plumbing
| needed if I build natively). So basically a browser app that
| behaves fully like a desktop app without any needing internet
| connection.
|
| And for that idea to become reality james long's absurd sql is
| basically the key. Without such persistent sql based db to work
| with, that ideas is never going to materialize :)
| eudoxus wrote:
| Have you checked out Anytype[0]. Was recently posted/discussed
| here on HN a few days ago[1].
|
| No affiliation, just interesting project that aligns with your
| description.
|
| [0]: https://anytype.io/
| [1]:https://news.ycombinator.com/item?id=38794733
| bbor wrote:
| Just leaving a short mention for ElectricSQL, which is explicitly
| built by the co-inventors of CRDTs to solve the exact issues
| discussed here. The docs use wa-SQLite by default, but AFAIK it's
| flexible. It's one of those projects where you can just tell how
| smart the design is based on how helpful and clear the docs are
| dang wrote:
| Discussed at the time:
|
| _A future for SQL on the web_ -
| https://news.ycombinator.com/item?id=28156831 - Aug 2021 (218
| comments)
___________________________________________________________________
(page generated 2023-12-31 23:01 UTC)