17 ms·

A future for SQL on the web

925 points by rasmusfabbe 5y ago

timdorr 5y ago
What's kind of bonkers here is that IndexedDB uses sqlite as its backend. So, this is sqlite (WASM) -> IndexedDB -> sqlite (native). The Internet is a wild place...
- thayne 5y ago
  That's even mentioned in the article.
- knubie 5y ago
  Wait, I thought IndexedDB was implemented with LevelDB [0] in Chrome? [0] https://en.wikipedia.org/wiki/LevelDB#Usage https://en.wikipedia.org/wiki/LevelDB#Usage Edit: Sorry, just re-read the article. The author does mention that Chrome's IndexedDB isn't implemented in SQLite.
- eurasiantiger 5y ago
  Literally why it’s called absurd-sql.
- leros 5y ago
  I'm going to build a business that offers SQLite as a web service. It will be backed by a P2P network of browser instances storing data in IndexedDB. Taking investment now.
  - CraftThatBlock 5y ago
    Not enough blockchain! Needs more Web 4.0
    - short_sells_poo 5y ago
      Came here just to say this. Each SQL program needs to run on a blockchain so that there's no central authority that can unduly influence the data.
      - ghostbrainalpha 5y ago
        I legitimately can no longer tell if this was being suggested sarcastically, or if you guys are being serious.
        da_chicken 5y ago
        The point is if the VCs can tell if we're being sarcastic or serious.
        short_sells_poo 5y ago
        To be clear: I was being sarcastic. But if I were to inject our comment chain into <insert random crypto appreciation thread here> it'd fit right in, proving your point that the level of silliness is getting to Monty Python levels. We need The Colonel to barge onto the stage and shut it down I feel.
        mst 5y ago
        [x] Yes.
  - remus 5y ago
    Surely what the world really needs is a new, faster implementation of IndexedDB? I propose writing it on top of this sqlite implementation, so we get the full indexedDB on sqlite on indexedDB on sqlite experience.
  - westurner 5y ago
    TIL, about Graph "Protocol for building decentralized applications quickly on Ethereum" https://github.com/graphprotocol https://github.com/graphprotocol https://thegraph.com/docs/indexing https://thegraph.com/docs/indexing > Indexers are node operators in The Graph Network that stake Graph Tokens (GRT) in order to provide indexing and query processing services. Indexers earn query fees and indexing rewards for their services. They also earn from a Rebate Pool that is shared with all network contributors proportional to their work, following the Cobbs-Douglas Rebate Function. > GRT that is staked in the protocol is subject to a thawing period and can be slashed if Indexers are malicious and serve incorrect data to applications or if they index incorrectly. Indexers can also be delegated stake from Delegators, to contribute to the network. > Indexers select subgraphs to index based on the subgraph’s curation signal, where Curators stake GRT in order to indicate which subgraphs are high-quality and should be prioritized. Consumers (eg. applications) can also set parameters for which Indexers process queries for their subgraphs and set preferences for query fee pricing. It's Ethereum though, so it's LevelDB, not SQLite on IndexedDB on SQLite.
  - ampdepolymerase 5y ago
    This could actually work for certificate attestation if baked directly into the browser. https://github.com/google/certificate-transparency https://github.com/google/certificate-transparency
  - mst 5y ago
    First, you need to write an ipfs implementation that uses indexedb so that can handle the sync ...
thwarted 5y ago
Sounds more like SQLite in the browser.
- jacobmischka 5y ago
  Did you read the article? That's literally what it is.
  - thwarted 5y ago
    Yes, and it literally says "SQL on the web" and "SQLite on the web". Are we really confusing "the web" with "the browser" now, especially since we use "the browser" to implement apps that don't have a need to access "the web"?
    - jacobmischka 5y ago
      Ah, alright fair.
EvanAnderson 5y ago
This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a standard, all things considered. I still believe it was torpedoed because of lack of trendiness and "not invented here". [0] https://www.w3.org/TR/webdatabase/ https://www.w3.org/TR/webdatabase/
- jlongster 5y ago
  Yeah, it adds to the absurdity of all of this. Although I do empathize with browers vendors. I worked at Mozilla at the time and was aware that this is a lot of things to think about when integrating something onto the web. I get why it happened, but practically speaking maybe it should have won. It's not like Chrome seems to care much about cross-browser standards these days. I'm hopeful for a storage layer like this though: https://web.dev/storage-foundation/ https://web.dev/storage-foundation/ It might actually be a better outcome if we get a storage layer with close to native performance, and then you can compile and db/lib/etc and it gets to use it.
  - 7952 5y ago
    I think offset based file access could be really powerful just based on what people are achieving in the browser with things like Flat buffers, proto buffers and even http range queries.
- daleharvey 5y ago
  The excuse was that a standard needs to have multiple implementations otherwise we are standardising implementation details and bugs. Hindsight shows that was entirely correct, as SQLite bugs were then found that could be exploited directly via WebSQL, Firefox of course was not vunerable. (https://hub.packtpub.com/an-sqlite-magellan-rce-vulnerability-exposes-billions-of-apps-including-all-chromium-based-browsers/ https://hub.packtpub.com/an-sqlite-magellan-rce-vulnerabilit...) As a sidenote, I worked a lot with the WebSQL API and it was not a very good API in the slightest, immaturity may excuse some of its flaws, and it isnt like Safari did a much better job with IndexedDB, its just a buggy browser and thats where WebSQL was used most, but a large part of the problem is that it was bolting an API that assumed a single threaded client when that is not the reality with web pages where multiple tabs exist
renke1 5y ago
What would be the best way to do migrations with absurd-sql?
- jlongster 5y ago
  I just include a list of migration files in the app, iterate through them in startup and make sure they are all applied. It's pretty simple, but yeah you have to think about this if doing local apps
rektide 5y ago
James is one of the world's great techno-adventurers, & getting to para-socially share in wild adventures like this makes living on Spaceship Earth more lovely & lively! James has also done cool projects like sweet.js macros, helped kick off Firefox devtool's transition to react (iirc), oh and lead the basically industry standard JS formatter Priettier project. I'm forgetting a dozen other things over the years but it's always been fun. Just a heads-up, the File System Access API[1] is underway in Chrome, which potentially removes nearly all of the absurdity here. It has other benefits too. A web page using this could write a .sql file on to your drive, that other programs could then access. One of the other bright stars in my world is Karli Koss, who has an extensive personal data-extraction setup for a ridiculously colossal variety of services & devices[2]. A vast amount of this massive massive data-gathering framework is just reading sqlite databases of the various devices and apps. If the web can help participate more actively, can let apps write sql files to store state: so much the better I say. Help externalize your state beyond the browser, please! [1] https://wicg.github.io/file-system-access/#api-filesystemwritablefilestream https://wicg.github.io/file-system-access/#api-filesystemwri... https://caniuse.com/native-filesystem-api https://caniuse.com/native-filesystem-api [2] https://beepb00p.xyz/myinfra.html https://beepb00p.xyz/myinfra.html
- shekhirin 5y ago
  Several months ago I've made a proof-of-concept of exactly what you're talking about, feel free to check it out: https://shekhirin.com/sqlite-fs/ https://shekhirin.com/sqlite-fs/. I recommend downloading sample DB, writing some dummy query like "SELECT BILLINGCOUNTRY, COUNT(INVOICEID) FROM INVOICE GROUP BY 1 ORDER BY 2 DESC" and then pressing Execute. I've been planning to write an extensive article about it and open sourcing the solution cleaning up the code a little bit, but still haven't got much time to do so.
  - rektide 5y ago
    now let's see what it takes to make absurd-fs, where we use https://github.com/guardianproject/libsqlfs https://github.com/guardianproject/libsqlfs to make a filesystem on top of sqlite on top of the File System Access API. gotta keep ourselves fully looped! ⥀ (is there perchance a repo available with your work? that'd be lovely to see.)
eatonphil 5y ago
sql.js is pretty hard to use as is otherwise you run out of memory really quickly. I was trying to use it as the in-memory SQL flavor for an open source data ide [0] but my naive approach of `SELECT * FROM VALUES (...), ...` would run out of memory after only a few hundred rows. I ended up switching to https://github.com/agershun/alasql https://github.com/agershun/alasql which could handle up to 80MB of data or so. (I haven't yet tested on larger datasets so I don't know the actual limits.) I don't think this is a fundamental limitation of sql.js as the linked article proves that you can implement custom paging for sql.js. But unless you do that (which I haven't spent the time to figure out how to do) then sql.js will run out of memory very quickly. Just something to be aware of if you're investigating it. If there's a high-level library that makes more effective use of memory with sql.js under the hood let me know. Unlike absurd-sql I don't need the results to be permanent. I just wanted an in-memory SQL for joining, filtering, grouping data. [0] https://github.com/multiprocessio/datastation https://github.com/multiprocessio/datastation
- lovasoa 5y ago
  When did you make your tests, and with which browser ? Did you use a prepared statement to fetch your results ? Raw sql.js is limited by the browser's wasm memory limit, but 80Mb should not cause an issue...
- wffurr 5y ago
  Emscripten wasm binaries with memory growth enabled can use up to 2 GB heap. That's very surprising that you're hitting a memory limit.
- jlongster 5y ago
  Something sounds wrong with your setup. I've had no problems!
TehShrike 5y ago
> Every [IndexedDB] library I looked at was messy and made performance even worse Seconded – I was pretty dismayed when I saw the IndexedDB helper library landscape. I ended up making https://github.com/TehShrike/small-indexeddb https://github.com/TehShrike/small-indexeddb which is ~50 lines to make it less onerous to work directly with the IDBObjectStore.
- LAC-Tech 5y ago
  I've had good experiences with https://github.com/jakearchibald/idb https://github.com/jakearchibald/idb It's basically a promise-based version of the standard API.
  - TehShrike 5y ago
    Yeah, Jake made idb not too long after I made small-indexeddb. I think it's one of the most reasonable options (and it has TypeScript types!), but it's still about 5x as much code as small-indexeddb.
PaulHoule 5y ago
Looks like fun. So far I've found IndexedDB to be outright depressing in it's limitations.
icodar 5y ago
Have you seen https://github.com/WebReflection/sqlite-worker https://github.com/WebReflection/sqlite-worker
- jlongster 5y ago
  All that does it export the entire db and write it down whenever something changes.
lioeters 5y ago
> browsers may delete your IndexedDB database under certain conditions Safari will happily delete your IndexedDB database after 7 days of inactivity. It deletes "all of a website’s script-writable storage after seven days of Safari use without user interaction on the site". That includes: - Indexed DB - LocalStorage - Media keys - SessionStorage - Service Worker registrations and cache Source: https://webkit.org/blog/10218/full-third-party-cookie-blocking-and-more/ https://webkit.org/blog/10218/full-third-party-cookie-blocki... Found via: The pain and anguish of using IndexedDB: problems, bugs and oddities - https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2adff49a https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2...
- jlongster 5y ago
  Yep, this is the biggest problem (although I haven't seen it happen after 7 days, at least on desktop). We will provide a new backend for the Storage Foundation API when it's available.
  - mst 5y ago
    Clearly the solution to this is to keep a query log in an extra table and periodically stream that to the server as a form of logical replication (plus perhaps being able to load the initial database state from the server side as well, maybe even on-demand using the GH pages trickery until a write forces materialisation into IndexedDB). As a bonus point this effectively adds yet another level of "Yo, Dawg" which I can't not love just as a matter of principle.
    - jlongster 5y ago
      People are already trying to get me to hook up https://litestream.io/ https://litestream.io/ to it
- adam12 5y ago
  Tim Cook basically lied to Congress when he stated that developers can create web apps as an alternative to using the app store. Edit: In order for this to be true, Apple (at the very least) needs to enable push notifications and an install prompt for progressive web apps.
pinkie123 5y ago
qwufeshdshwaDWKASwhwhawhhwshuah
mg 5y ago
While in-memory databases have their uses, it kneecaps SQLite into something far less useful. To build any kind of app with it, we need the ability to write and persist. Another approach than writing the data to a server could be to allow the user to store it on their own hard disk. This could be done via the File System Access API: https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System... The API already works nicely in Desktop Chrome: https://googlechromelabs.github.io/text-editor/ https://googlechromelabs.github.io/text-editor/
- jlongster 5y ago
  Did you read the post? This project does exactly that. (but focuses in IndexedDB for now because it's the only cross-browser thing that works. I actually tried a webkitFileSystem backend and it was slower)
  - mg 5y ago
    Well, I woudln't call using IndexedDB "exactly that". As IndexedDB is rather fleeting. You don't use a server, that is correct. But IndexedDB goes away under many circumstances. Saving a file via the File System Access API would give the user peace of mind that it is safe. I did not see any mention of the File System Access API in your post.
    - jlongster 5y ago
      Read harder. https://jlongster.com/future-sql-web#more-than-just-another-database https://jlongster.com/future-sql-web#more-than-just-another-...
      - dang 5y ago
        Hey, can you please not do this ("Did you read the post?", "Read harder", etc.), even when someone else hasn't read an article? I understand how frustrating it can be when people don't read what you write very closely (believe me, I understand), but it's one of the tropes that degrade discussion and we're trying to avoid sinking to that level here. "Please don't comment on whether someone read an article. "Did you even read the article? It mentions that" can be shortened to "The article mentions that."" https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
bob1029 5y ago
> SQLite, even though it’s implemented on top of IndexedDB, easily beats out IndexedDB in every single performance metric. The absurdity! This really is quite incredible. Same idea extends to your filesystem too. Tracking millions of 1KB objects on disk? You could load the whole set into memory substantially faster from SQLite using the same disk. If WAL is enabled with reasonable sync flags, the same applies going back out to disk as well. SQLite is the most powerful dependency that our product uses today. We have been using it in production as the sole persistence mechanism for 100% of our data for the last 5-6 years now. Recently, we have started leveraging the actual SQL engine to process all of our business logic as well.
- justsomeuser 5y ago
  Nice. What kind of business logic are you using SQL queries for?
  - bob1029 5y ago
    Any sort of decision point that tends to vary between our customers. We are getting really tired of maintaining custom code piles.
    - danlugo92 5y ago
      By that you mean several piles in different languages? Or one pile for each customer?
      - bob1029 5y ago
        One for each. Our solution compiles to a single executable.
        justsomeuser 5y ago
        Would you not have unique code per customer regardless of if the code is SQL queries or C#?
deleted 5y ago
[deleted]
jeffbee 5y ago
So, why is IndexedDB so slow on Chrome? Obviously LevelDB doesn't need 10ms for a point read. If it did, nobody would use it for anything. 10ms is a hell of a long time. Is it spawning a process to perform the read or ??
- jlongster 5y ago
  Reads aren't as bad, but any kind of writes seem terrible. Take "10ms" with a grain of salt and view the numbers yourself here: https://priceless-keller-d097e5.netlify.app/ https://priceless-keller-d097e5.netlify.app/ I was profiling on an older computer. On my newer one, summing 100 items takes ~8ms (use the raw idb mode). When I said "simple operations" I meant simple queries that you'd expect apps to write, not just 1 single read/write. It is a little faster for each read/write, but there seems to be a bottom floor. Even if reading an item itself is fast, opening a transaction is slow. So any query, even if it only reads one item, is going to suffer the perf hit of opening a transaction. It's only twice as fast as Firefox, so overall IDB is still super slow when compared to running the same queries with native SQLite. We're talking summing 100 items taking ~.01ms or less. I have no idea why it's so slow.
The_rationalist 5y ago
If only mozilla hadn't screwed up
tomaszs 5y ago
Just a month ago I was talking with a person that told it is impossible to use SQL in the frontend. It is a great project and I hope one day we will be able to use it in production.
Macha 5y ago
Hmm... I was in the middle of rewriting an application of mine from JSON stringify into localStorage to IndexedDB, but was having issues with the API being so clunky. This is a tempting alternative. It does increase size from ~200kb by a whole mb, but the app's usage patterns are such that people open it and then use it for extended periods of time in the background.
- SilverRed 5y ago
  Careful with IndexedDB. There was a post here recently about how it is 100% broken on safari right now.
  - Macha 5y ago
    Honestly it's a hobby project at the moment. Mac users can just use Chrome or Firefox. It's not worth my money to get the electron build running for mac, or my time to work around safari bugs. The design is very much a 00s throwback with a lot of density and power compared to more modern apps in the same category, so Mac users are unlikely to like it anyway. I was just tired of the older apps lacking modern features I like while the apps with the modern features were clearly mobile/touch first and so much slower for bulk operations than their older competitors as a consequence.
collaborative 5y ago
So good to see persistent dbs coming to the web. I also started using https://github.com/WebReflection/sqlite-worker https://github.com/WebReflection/sqlite-worker which is pretty similar
sosodev 5y ago
Thank you for this. I've been hoping for something like this for ages.
danielovichdk 5y ago
I stopped reading after this "If you are writing a web app today, you’ll probably choose IndexedDB to store data. It’s the only option for something database-like that works across all browsers." RDBMS all the way baby
- mst 5y ago
  Then you stopped reading before the part where he implemented SQLite using IndexedDB for the storage, thereby completely missing the point. It's a fun article, I'd recommend trying reading all of it.
guyromm 5y ago
i wonder if it's possible to plug any kind of streaming replication onto this. i don't have much sqlite experience, but maybe someone here has an idea if it would be possible to run litestream or something of the sort, as both master and slave - in the browser. that would solve the safari indexeddb 7 day ttl issue to start with. and if replication could be made to work on top of something like webrtc we're looking at a great foundation to start building distributed, decentralized browser apps.
- breckenedge 5y ago
  There’s also CouchDB/PouchDB made for this use case.
  - guyromm 5y ago
    that one i did take for a spin. i must say that the experience is quite horrible - that torture of having to write map/reduce functions, added with some erratic behavior in regards to data integrity (inserted entries silently discarded, sync to the remote couchdb instance working somewhat whimsically). as soon as your dataset is sizable in any regard (tens of thousands of records in a collection, if i recall the terminology) it begins to just break apart. was writing a browser extension, and used pouch with the hope of keeping its persistence local and avoid needing a server. seeing that it leaks tried to trade it for a couchdb server. seeing how bad sync is, and that couch is not very comfortable to work with either ended up throwing the thing in favor of a postgresql+postgrest backend.
djhworld 5y ago
Highly entertaining and informative article/project - thanks for taking the time to write about it. This is really cool, I wonder if it could be built into something like https://datasette.io/ https://datasette.io/ - without the need for a python runtime.
ofrzeta 5y ago
"In all browsers except Chrome, IndexedDB is implemented using SQLite". That's a strange way to phrase the status quo. That is Firefox and ... Opera? While Chrome includes Edge.
- TingPing 5y ago
  Opera is Chrome based. WebKit is the other.
- DangitBobby 5y ago
  It would include Safari, Firefox, IE11, and Edge (before it became Chrome Skin) as well right?
jacobpedd 5y ago
This is incredibly frustrating to read as someone who just spent a week writing logic to dump sql.js queries into json persisted with LocalStorage. Only mad because it’s so much better in every way.
- jitl 5y ago
  You can’t trust localStorage anyways - it will silently drop some writes in Chrome and Firefox. Some years ago, we used localStorage for queuing writes to the backend and found that concurrent access to localStorage caused data loss - some transactions never came back out of localStorage. One of our engineers wrote a stress test and confirmed the issue. We switched to IndexedDB after that.
Jyaif 5y ago
> that allows SQLite to read/write from IndexedDB in small blocks, just like it would a disk So it sounds like IndexedDB was the right abstraction all along.
- aikah 5y ago
  No it's not, it has a terrible API. Not sure why people here are still defending that mess imposed by Mozilla when developers could have had something better, websql. Another spec that failed from a practical perspective because of Mozilla's reluctance to implement key aspects of it is web components.
swlkr 5y ago
if you could enable WAL without a drop in performance, you might be able to use something like litestream to sync to the backend this may be the most performant/secure/cheapest b2b saas stack ever every customer gets own sqlite database, downloads it to their browser on first load, each db gets synced to s3 everything is served statically from s3 as well
OOPMan 5y ago
And here I thought the most popular way to use SQL on the web was with a backend API. Shows what I know... On a similar note, have this nagging feeling that we used to have this ability to use SQL in client-side applications. I just can't recall how? /s
stevage 5y ago
Fascinating. I'm really curious what the use case is that so many people seem to have. Why do you need so much data in the browser, and to be doing queries and data manipulation there? Where does the data come from? Don't you need to sync it back to a server somewhere?
- quickthrower2 5y ago
  This might be useful for a desktop-app like experience on the web. Imagine something like excel but you want to open a 100mb file and work with it right away. It can sync to the server as you are working but you just want to get working now. Another use is privacy centric apps that send nothing to the server, using the web as a kind of “install” platform but nothing else.
- rattray 5y ago
  This lets you write any Serious App with "single-player data" as offline-first (though yes you still need to handle syncing to the cloud somehow – jlongster has done some very cool stuff for that too, looking forward to him sharing more about that).
  - stevage 5y ago
    Yeah, I mean I get that in theory, I just can't think of many examples? I guess it must really be desktop apps that are delivered as web apps.
- warvstar 5y ago
  We have a gaming platform where users can download and play full games in the browser. We don't use indexedDB though, we use the Cache API.
- SilverRed 5y ago
  It can be used as a better form of cache to make actions happen faster. So an instant messaging app for example can cache messages and stuff in a db so it does not have to refetch everything every time you switch chats.
quickthrower2 5y ago
Reading Mozilla docs I get the impression that your data could get nuked in indexdb if it needs to clear space. Attack vector might be to register 1000 domains then get a page to load each of those to fill up its 2Gb quota? Just guessing….
keithnz 5y ago
the only thing I don't like is that SQL is a second class "stringified" citizen in this world (and often in any environment where you want to use SQL). It's missing all the advantages of syntax checking and dynamically building queries. In my C# projects, I tend to always work with SQL files which then get embedded into C# so I can always query against a DB and build queries more REPL like. Having said that, I do like the idea of Sqlite in the front end for localstorage.
galaxyLogic 5y ago
If I understand this correctly the db would be on the client/browser. Thus any persistence would happen via local storage or such. But: 1. People often reset the cache on their browser, after all it is just a cache. SO a big benefit of databases which is persistent data is kind of not there. Juts rest your cache. 2. The second great benefit of databases is that they are multi-user. There can be in fact millions of users. But this benefit would not be there if the database lives and executes in the browser.
jitl 5y ago
This is great to see, and a project I considered attempting myself for a bit. I’m excited to test it out. @jlongster I have a question about this: > The backend calls it [Atomics.wait] to wait on the result from the worker and blocks until it’s done. Does this mean the main (UI) thread is blocked during queries? Or are there more threads, like UI <- async messages -> SQLite main <- Atomics blocking -> SQLite FS backend? ———— At Notion, we’ve used IndexedDB for two purposes: (1) to durably persist a queue of changes to send to our backend, and (2) in the desktop app, to LRU cache the page data we read from the server to accelerate reads. Both of these used localStorage years ago, but we ported to IndexedDB because of data loss on localStorage. Porting was fine for the write queue, but we really noticed the slow when we tried porting the data cache. To get close to the original performance we coalesce reads, and we delay writes to the cache significantly so they can batch more effectively into a single readwrite transaction that we send after the reads for the current page load are complete. That worked okay, but it was annoying to maintain the IDB cache code because our Android and iOS apps used SQLite for their caches, and it’s so much easier to add new queries using SQL compared to writing IDB iterations - and it’s faster. So we switched to using native SQLite via a bridge to a Node process. Now with absurd-sql, maybe we could bring the same caching logic to browsers. The thing stopping me is how unreliable we’ve found IndexedDB to be - aside from the optimization work. We notice a lot of bugs in IDB implementations on different browsers. In Safari (especially on iOS) there’s a bunch of spooky issues that have caused stalls or spurious errors, sometimes requiring an app restart before the IDB database can be re-opened. Forget it on Android - weird vendor webview patches mean your storage might get cleared out from under you. On Firefox, we notice that sometimes the IndexedDB database doesn’t create all the object stores we request for some reason. Even on Chrome, IndexedDB can suddenly start refusing writes in the middle of a session with no clear explanation, and on Windows restarting the computer is sometimes the only fix. If we can share SQLite queries with our native apps then maybe it’s worth wading deeper into these issues… but it really does feel like building on quicksand.
- jlongster 5y ago
  > Does this mean the main (UI) thread is blocked during queries? Or are there more threads, like UI <- async messages -> SQLite main <- Atomics blocking -> SQLite FS backend? The latter! Your app running queries must be on a worker, and then the IDB backend will spawn another worker. `Atomics.wait` is not even available on the main thread. Ideally in the future, there will be a better storage API that we don't even need all the Atomics silly-ness (hopefully it provides Sync methods) That's really cool re: Notion! That's exactly the kind of thing I want too: a way to just build apps the same way everywhere, on mobile/desktop/web. You are right about various issues, and I personally don't have to worry much about it on my app because I have native mobile apps and I don't support the web version on mobile. I intentionally do that -- the mobile web is just too broken in too many ways. My impression is the IDB is more stable on desktop, but because mobile is more memory sensitive there are more issues there. However, you should try it out! I definitely discovered a lot of weird things; I definitely was able to get Safari into a weird state the required a complete app restart. Here's the thing though: I found ways around them. If you do a lot of read requests in a certain way, Safari will lock up permanently. However, if you make sure to wait until the `readonly` transaction is finished before starting a new one, the problem goes away. I was able to reliably reproduce that problem and it went away with that fix. I think absurd-sql is so promising because it normalizes the patterns of how IDB is accessed, and it already includes fixes for a bunch of edge cases. There are probably more, but try it out! If you run into an edge case, we can tweak the IDB backend until it works. We can paper over these issues in the underlying backend and you don't have to worry about it because you aren't directly managing IDB read/writes.
scns 5y ago
Opening this, on desktop, left me speechless with an open mouth: https://archive.jlongster.com/ https://archive.jlongster.com/ You can explore it with the mouse cursor.
KETpXDDzR 5y ago
> But that’s it. That’s the only catch. I'd like to add one drawback with this solution: Complexity. From my experience, complexity can easily lead to more problems than what it solves. With all the "blackboxes", e.g., WASM, JS, SQLite, IndexDB, ..., it might be hard to find bugs. Most of the tools used are somewhat stable and mature tough. SQLite, for example, has a whooping 100% test coverage (line coverage at least).
KETpXDDzR 5y ago
That reminds me of benchmarking key-value stores. Surprisingly for me at that time, SQLite crushed all famous KV stores.

925 points by rasmusfabbe 5y ago

What's kind of bonkers here is that IndexedDB uses sqlite as its backend. So, this is sqlite (WASM) -> IndexedDB -> sqlite (native). The Internet is a wild place...

thayne 5y ago
That's even mentioned in the article.
knubie 5y ago
Wait, I thought IndexedDB was implemented with LevelDB [0] in Chrome? [0] https://en.wikipedia.org/wiki/LevelDB#Usage https://en.wikipedia.org/wiki/LevelDB#Usage Edit: Sorry, just re-read the article. The author does mention that Chrome's IndexedDB isn't implemented in SQLite.
eurasiantiger 5y ago
Literally why it’s called absurd-sql.
leros 5y ago
I'm going to build a business that offers SQLite as a web service. It will be backed by a P2P network of browser instances storing data in IndexedDB. Taking investment now.
- CraftThatBlock 5y ago
  Not enough blockchain! Needs more Web 4.0
  - short_sells_poo 5y ago
    Came here just to say this. Each SQL program needs to run on a blockchain so that there's no central authority that can unduly influence the data.
    - ghostbrainalpha 5y ago
      I legitimately can no longer tell if this was being suggested sarcastically, or if you guys are being serious.
      - da_chicken 5y ago
        The point is if the VCs can tell if we're being sarcastic or serious.
      - short_sells_poo 5y ago
        To be clear: I was being sarcastic. But if I were to inject our comment chain into <insert random crypto appreciation thread here> it'd fit right in, proving your point that the level of silliness is getting to Monty Python levels. We need The Colonel to barge onto the stage and shut it down I feel.
      - mst 5y ago
        [x] Yes.
- remus 5y ago
  Surely what the world really needs is a new, faster implementation of IndexedDB? I propose writing it on top of this sqlite implementation, so we get the full indexedDB on sqlite on indexedDB on sqlite experience.
- westurner 5y ago
  TIL, about Graph "Protocol for building decentralized applications quickly on Ethereum" https://github.com/graphprotocol https://github.com/graphprotocol https://thegraph.com/docs/indexing https://thegraph.com/docs/indexing > Indexers are node operators in The Graph Network that stake Graph Tokens (GRT) in order to provide indexing and query processing services. Indexers earn query fees and indexing rewards for their services. They also earn from a Rebate Pool that is shared with all network contributors proportional to their work, following the Cobbs-Douglas Rebate Function. > GRT that is staked in the protocol is subject to a thawing period and can be slashed if Indexers are malicious and serve incorrect data to applications or if they index incorrectly. Indexers can also be delegated stake from Delegators, to contribute to the network. > Indexers select subgraphs to index based on the subgraph’s curation signal, where Curators stake GRT in order to indicate which subgraphs are high-quality and should be prioritized. Consumers (eg. applications) can also set parameters for which Indexers process queries for their subgraphs and set preferences for query fee pricing. It's Ethereum though, so it's LevelDB, not SQLite on IndexedDB on SQLite.
- ampdepolymerase 5y ago
  This could actually work for certificate attestation if baked directly into the browser. https://github.com/google/certificate-transparency https://github.com/google/certificate-transparency
- mst 5y ago
  First, you need to write an ipfs implementation that uses indexedb so that can handle the sync ...

thwarted 5y ago

Sounds more like SQLite in the browser.

jacobmischka 5y ago
Did you read the article? That's literally what it is.
- thwarted 5y ago
  Yes, and it literally says "SQL on the web" and "SQLite on the web". Are we really confusing "the web" with "the browser" now, especially since we use "the browser" to implement apps that don't have a need to access "the web"?
  - jacobmischka 5y ago
    Ah, alright fair.

EvanAnderson 5y ago

This is funny and sad to me. We had SQLite in the browser[0]. I only did a little bit of work with it but it seemed actually pretty nice. It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale"). There was the whole excuse that the specification was "whatever SQLite does" and, therefore, not suitable for being a standard. There would be worse things than SQLite upon which to base a standard, all things considered. I still believe it was torpedoed because of lack of trendiness and "not invented here". [0] https://www.w3.org/TR/webdatabase/ https://www.w3.org/TR/webdatabase/

jlongster 5y ago
Yeah, it adds to the absurdity of all of this. Although I do empathize with browers vendors. I worked at Mozilla at the time and was aware that this is a lot of things to think about when integrating something onto the web. I get why it happened, but practically speaking maybe it should have won. It's not like Chrome seems to care much about cross-browser standards these days. I'm hopeful for a storage layer like this though: https://web.dev/storage-foundation/ https://web.dev/storage-foundation/ It might actually be a better outcome if we get a storage layer with close to native performance, and then you can compile and db/lib/etc and it gets to use it.
- 7952 5y ago
  I think offset based file access could be really powerful just based on what people are achieving in the browser with things like Flat buffers, proto buffers and even http range queries.
daleharvey 5y ago
The excuse was that a standard needs to have multiple implementations otherwise we are standardising implementation details and bugs. Hindsight shows that was entirely correct, as SQLite bugs were then found that could be exploited directly via WebSQL, Firefox of course was not vunerable. (https://hub.packtpub.com/an-sqlite-magellan-rce-vulnerability-exposes-billions-of-apps-including-all-chromium-based-browsers/ https://hub.packtpub.com/an-sqlite-magellan-rce-vulnerabilit...) As a sidenote, I worked a lot with the WebSQL API and it was not a very good API in the slightest, immaturity may excuse some of its flaws, and it isnt like Safari did a much better job with IndexedDB, its just a buggy browser and thats where WebSQL was used most, but a large part of the problem is that it was bolting an API that assumed a single threaded client when that is not the reality with web pages where multiple tabs exist

renke1 5y ago

What would be the best way to do migrations with absurd-sql?

jlongster 5y ago
I just include a list of migration files in the app, iterate through them in startup and make sure they are all applied. It's pretty simple, but yeah you have to think about this if doing local apps

rektide 5y ago

James is one of the world's great techno-adventurers, & getting to para-socially share in wild adventures like this makes living on Spaceship Earth more lovely & lively! James has also done cool projects like sweet.js macros, helped kick off Firefox devtool's transition to react (iirc), oh and lead the basically industry standard JS formatter Priettier project. I'm forgetting a dozen other things over the years but it's always been fun. Just a heads-up, the File System Access API[1] is underway in Chrome, which potentially removes nearly all of the absurdity here. It has other benefits too. A web page using this could write a .sql file on to your drive, that other programs could then access. One of the other bright stars in my world is Karli Koss, who has an extensive personal data-extraction setup for a ridiculously colossal variety of services & devices[2]. A vast amount of this massive massive data-gathering framework is just reading sqlite databases of the various devices and apps. If the web can help participate more actively, can let apps write sql files to store state: so much the better I say. Help externalize your state beyond the browser, please! [1] https://wicg.github.io/file-system-access/#api-filesystemwritablefilestream https://wicg.github.io/file-system-access/#api-filesystemwri... https://caniuse.com/native-filesystem-api https://caniuse.com/native-filesystem-api [2] https://beepb00p.xyz/myinfra.html https://beepb00p.xyz/myinfra.html

shekhirin 5y ago
Several months ago I've made a proof-of-concept of exactly what you're talking about, feel free to check it out: https://shekhirin.com/sqlite-fs/ https://shekhirin.com/sqlite-fs/. I recommend downloading sample DB, writing some dummy query like "SELECT BILLINGCOUNTRY, COUNT(INVOICEID) FROM INVOICE GROUP BY 1 ORDER BY 2 DESC" and then pressing Execute. I've been planning to write an extensive article about it and open sourcing the solution cleaning up the code a little bit, but still haven't got much time to do so.
- rektide 5y ago
  now let's see what it takes to make absurd-fs, where we use https://github.com/guardianproject/libsqlfs https://github.com/guardianproject/libsqlfs to make a filesystem on top of sqlite on top of the File System Access API. gotta keep ourselves fully looped! ⥀ (is there perchance a repo available with your work? that'd be lovely to see.)

eatonphil 5y ago

sql.js is pretty hard to use as is otherwise you run out of memory really quickly. I was trying to use it as the in-memory SQL flavor for an open source data ide [0] but my naive approach of `SELECT * FROM VALUES (...), ...` would run out of memory after only a few hundred rows. I ended up switching to https://github.com/agershun/alasql https://github.com/agershun/alasql which could handle up to 80MB of data or so. (I haven't yet tested on larger datasets so I don't know the actual limits.) I don't think this is a fundamental limitation of sql.js as the linked article proves that you can implement custom paging for sql.js. But unless you do that (which I haven't spent the time to figure out how to do) then sql.js will run out of memory very quickly. Just something to be aware of if you're investigating it. If there's a high-level library that makes more effective use of memory with sql.js under the hood let me know. Unlike absurd-sql I don't need the results to be permanent. I just wanted an in-memory SQL for joining, filtering, grouping data. [0] https://github.com/multiprocessio/datastation https://github.com/multiprocessio/datastation

lovasoa 5y ago
When did you make your tests, and with which browser ? Did you use a prepared statement to fetch your results ? Raw sql.js is limited by the browser's wasm memory limit, but 80Mb should not cause an issue...
wffurr 5y ago
Emscripten wasm binaries with memory growth enabled can use up to 2 GB heap. That's very surprising that you're hitting a memory limit.
jlongster 5y ago
Something sounds wrong with your setup. I've had no problems!

TehShrike 5y ago

> Every [IndexedDB] library I looked at was messy and made performance even worse Seconded – I was pretty dismayed when I saw the IndexedDB helper library landscape. I ended up making https://github.com/TehShrike/small-indexeddb https://github.com/TehShrike/small-indexeddb which is ~50 lines to make it less onerous to work directly with the IDBObjectStore.

LAC-Tech 5y ago
I've had good experiences with https://github.com/jakearchibald/idb https://github.com/jakearchibald/idb It's basically a promise-based version of the standard API.
- TehShrike 5y ago
  Yeah, Jake made idb not too long after I made small-indexeddb. I think it's one of the most reasonable options (and it has TypeScript types!), but it's still about 5x as much code as small-indexeddb.

PaulHoule 5y ago

Looks like fun. So far I've found IndexedDB to be outright depressing in it's limitations.

icodar 5y ago

Have you seen https://github.com/WebReflection/sqlite-worker https://github.com/WebReflection/sqlite-worker

jlongster 5y ago
All that does it export the entire db and write it down whenever something changes.

lioeters 5y ago

> browsers may delete your IndexedDB database under certain conditions Safari will happily delete your IndexedDB database after 7 days of inactivity. It deletes "all of a website’s script-writable storage after seven days of Safari use without user interaction on the site". That includes: - Indexed DB - LocalStorage - Media keys - SessionStorage - Service Worker registrations and cache Source: https://webkit.org/blog/10218/full-third-party-cookie-blocking-and-more/ https://webkit.org/blog/10218/full-third-party-cookie-blocki... Found via: The pain and anguish of using IndexedDB: problems, bugs and oddities - https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2adff49a https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2...

jlongster 5y ago
Yep, this is the biggest problem (although I haven't seen it happen after 7 days, at least on desktop). We will provide a new backend for the Storage Foundation API when it's available.
- mst 5y ago
  Clearly the solution to this is to keep a query log in an extra table and periodically stream that to the server as a form of logical replication (plus perhaps being able to load the initial database state from the server side as well, maybe even on-demand using the GH pages trickery until a write forces materialisation into IndexedDB). As a bonus point this effectively adds yet another level of "Yo, Dawg" which I can't not love just as a matter of principle.
  - jlongster 5y ago
    People are already trying to get me to hook up https://litestream.io/ https://litestream.io/ to it
adam12 5y ago
Tim Cook basically lied to Congress when he stated that developers can create web apps as an alternative to using the app store. Edit: In order for this to be true, Apple (at the very least) needs to enable push notifications and an install prompt for progressive web apps.

pinkie123 5y ago

qwufeshdshwaDWKASwhwhawhhwshuah

mg 5y ago

While in-memory databases have their uses, it kneecaps SQLite into something far less useful. To build any kind of app with it, we need the ability to write and persist. Another approach than writing the data to a server could be to allow the user to store it on their own hard disk. This could be done via the File System Access API: https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System... The API already works nicely in Desktop Chrome: https://googlechromelabs.github.io/text-editor/ https://googlechromelabs.github.io/text-editor/

jlongster 5y ago
Did you read the post? This project does exactly that. (but focuses in IndexedDB for now because it's the only cross-browser thing that works. I actually tried a webkitFileSystem backend and it was slower)
- mg 5y ago
  Well, I woudln't call using IndexedDB "exactly that". As IndexedDB is rather fleeting. You don't use a server, that is correct. But IndexedDB goes away under many circumstances. Saving a file via the File System Access API would give the user peace of mind that it is safe. I did not see any mention of the File System Access API in your post.
  - jlongster 5y ago
    Read harder. https://jlongster.com/future-sql-web#more-than-just-another-database https://jlongster.com/future-sql-web#more-than-just-another-...
    - dang 5y ago
      Hey, can you please not do this ("Did you read the post?", "Read harder", etc.), even when someone else hasn't read an article? I understand how frustrating it can be when people don't read what you write very closely (believe me, I understand), but it's one of the tropes that degrade discussion and we're trying to avoid sinking to that level here. "Please don't comment on whether someone read an article. "Did you even read the article? It mentions that" can be shortened to "The article mentions that."" https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

bob1029 5y ago

> SQLite, even though it’s implemented on top of IndexedDB, easily beats out IndexedDB in every single performance metric. The absurdity! This really is quite incredible. Same idea extends to your filesystem too. Tracking millions of 1KB objects on disk? You could load the whole set into memory substantially faster from SQLite using the same disk. If WAL is enabled with reasonable sync flags, the same applies going back out to disk as well. SQLite is the most powerful dependency that our product uses today. We have been using it in production as the sole persistence mechanism for 100% of our data for the last 5-6 years now. Recently, we have started leveraging the actual SQL engine to process all of our business logic as well.

justsomeuser 5y ago
Nice. What kind of business logic are you using SQL queries for?
- bob1029 5y ago
  Any sort of decision point that tends to vary between our customers. We are getting really tired of maintaining custom code piles.
  - danlugo92 5y ago
    By that you mean several piles in different languages? Or one pile for each customer?
    - bob1029 5y ago
      One for each. Our solution compiles to a single executable.
      - justsomeuser 5y ago
        Would you not have unique code per customer regardless of if the code is SQL queries or C#?

deleted 5y ago

[deleted]

jeffbee 5y ago

So, why is IndexedDB so slow on Chrome? Obviously LevelDB doesn't need 10ms for a point read. If it did, nobody would use it for anything. 10ms is a hell of a long time. Is it spawning a process to perform the read or ??

jlongster 5y ago
Reads aren't as bad, but any kind of writes seem terrible. Take "10ms" with a grain of salt and view the numbers yourself here: https://priceless-keller-d097e5.netlify.app/ https://priceless-keller-d097e5.netlify.app/ I was profiling on an older computer. On my newer one, summing 100 items takes ~8ms (use the raw idb mode). When I said "simple operations" I meant simple queries that you'd expect apps to write, not just 1 single read/write. It is a little faster for each read/write, but there seems to be a bottom floor. Even if reading an item itself is fast, opening a transaction is slow. So any query, even if it only reads one item, is going to suffer the perf hit of opening a transaction. It's only twice as fast as Firefox, so overall IDB is still super slow when compared to running the same queries with native SQLite. We're talking summing 100 items taking ~.01ms or less. I have no idea why it's so slow.

The_rationalist 5y ago

If only mozilla hadn't screwed up

tomaszs 5y ago

Just a month ago I was talking with a person that told it is impossible to use SQL in the frontend. It is a great project and I hope one day we will be able to use it in production.

Macha 5y ago

Hmm... I was in the middle of rewriting an application of mine from JSON stringify into localStorage to IndexedDB, but was having issues with the API being so clunky. This is a tempting alternative. It does increase size from ~200kb by a whole mb, but the app's usage patterns are such that people open it and then use it for extended periods of time in the background.

SilverRed 5y ago
Careful with IndexedDB. There was a post here recently about how it is 100% broken on safari right now.
- Macha 5y ago
  Honestly it's a hobby project at the moment. Mac users can just use Chrome or Firefox. It's not worth my money to get the electron build running for mac, or my time to work around safari bugs. The design is very much a 00s throwback with a lot of density and power compared to more modern apps in the same category, so Mac users are unlikely to like it anyway. I was just tired of the older apps lacking modern features I like while the apps with the modern features were clearly mobile/touch first and so much slower for bulk operations than their older competitors as a consequence.

collaborative 5y ago

So good to see persistent dbs coming to the web. I also started using https://github.com/WebReflection/sqlite-worker https://github.com/WebReflection/sqlite-worker which is pretty similar

sosodev 5y ago

Thank you for this. I've been hoping for something like this for ages.

danielovichdk 5y ago

I stopped reading after this "If you are writing a web app today, you’ll probably choose IndexedDB to store data. It’s the only option for something database-like that works across all browsers." RDBMS all the way baby

mst 5y ago
Then you stopped reading before the part where he implemented SQLite using IndexedDB for the storage, thereby completely missing the point. It's a fun article, I'd recommend trying reading all of it.

guyromm 5y ago

i wonder if it's possible to plug any kind of streaming replication onto this. i don't have much sqlite experience, but maybe someone here has an idea if it would be possible to run litestream or something of the sort, as both master and slave - in the browser. that would solve the safari indexeddb 7 day ttl issue to start with. and if replication could be made to work on top of something like webrtc we're looking at a great foundation to start building distributed, decentralized browser apps.

breckenedge 5y ago
There’s also CouchDB/PouchDB made for this use case.
- guyromm 5y ago
  that one i did take for a spin. i must say that the experience is quite horrible - that torture of having to write map/reduce functions, added with some erratic behavior in regards to data integrity (inserted entries silently discarded, sync to the remote couchdb instance working somewhat whimsically). as soon as your dataset is sizable in any regard (tens of thousands of records in a collection, if i recall the terminology) it begins to just break apart. was writing a browser extension, and used pouch with the hope of keeping its persistence local and avoid needing a server. seeing that it leaks tried to trade it for a couchdb server. seeing how bad sync is, and that couch is not very comfortable to work with either ended up throwing the thing in favor of a postgresql+postgrest backend.

djhworld 5y ago

Highly entertaining and informative article/project - thanks for taking the time to write about it. This is really cool, I wonder if it could be built into something like https://datasette.io/ https://datasette.io/ - without the need for a python runtime.

ofrzeta 5y ago

"In all browsers except Chrome, IndexedDB is implemented using SQLite". That's a strange way to phrase the status quo. That is Firefox and ... Opera? While Chrome includes Edge.

TingPing 5y ago
Opera is Chrome based. WebKit is the other.
DangitBobby 5y ago
It would include Safari, Firefox, IE11, and Edge (before it became Chrome Skin) as well right?

jacobpedd 5y ago

This is incredibly frustrating to read as someone who just spent a week writing logic to dump sql.js queries into json persisted with LocalStorage. Only mad because it’s so much better in every way.

jitl 5y ago
You can’t trust localStorage anyways - it will silently drop some writes in Chrome and Firefox. Some years ago, we used localStorage for queuing writes to the backend and found that concurrent access to localStorage caused data loss - some transactions never came back out of localStorage. One of our engineers wrote a stress test and confirmed the issue. We switched to IndexedDB after that.

Jyaif 5y ago

> that allows SQLite to read/write from IndexedDB in small blocks, just like it would a disk So it sounds like IndexedDB was the right abstraction all along.

aikah 5y ago
No it's not, it has a terrible API. Not sure why people here are still defending that mess imposed by Mozilla when developers could have had something better, websql. Another spec that failed from a practical perspective because of Mozilla's reluctance to implement key aspects of it is web components.

swlkr 5y ago

if you could enable WAL without a drop in performance, you might be able to use something like litestream to sync to the backend this may be the most performant/secure/cheapest b2b saas stack ever every customer gets own sqlite database, downloads it to their browser on first load, each db gets synced to s3 everything is served statically from s3 as well

OOPMan 5y ago

And here I thought the most popular way to use SQL on the web was with a backend API. Shows what I know... On a similar note, have this nagging feeling that we used to have this ability to use SQL in client-side applications. I just can't recall how? /s

stevage 5y ago

Fascinating. I'm really curious what the use case is that so many people seem to have. Why do you need so much data in the browser, and to be doing queries and data manipulation there? Where does the data come from? Don't you need to sync it back to a server somewhere?

quickthrower2 5y ago
This might be useful for a desktop-app like experience on the web. Imagine something like excel but you want to open a 100mb file and work with it right away. It can sync to the server as you are working but you just want to get working now. Another use is privacy centric apps that send nothing to the server, using the web as a kind of “install” platform but nothing else.
rattray 5y ago
This lets you write any Serious App with "single-player data" as offline-first (though yes you still need to handle syncing to the cloud somehow – jlongster has done some very cool stuff for that too, looking forward to him sharing more about that).
- stevage 5y ago
  Yeah, I mean I get that in theory, I just can't think of many examples? I guess it must really be desktop apps that are delivered as web apps.
warvstar 5y ago
We have a gaming platform where users can download and play full games in the browser. We don't use indexedDB though, we use the Cache API.
SilverRed 5y ago
It can be used as a better form of cache to make actions happen faster. So an instant messaging app for example can cache messages and stuff in a db so it does not have to refetch everything every time you switch chats.

quickthrower2 5y ago

Reading Mozilla docs I get the impression that your data could get nuked in indexdb if it needs to clear space. Attack vector might be to register 1000 domains then get a page to load each of those to fill up its 2Gb quota? Just guessing….

keithnz 5y ago

the only thing I don't like is that SQL is a second class "stringified" citizen in this world (and often in any environment where you want to use SQL). It's missing all the advantages of syntax checking and dynamically building queries. In my C# projects, I tend to always work with SQL files which then get embedded into C# so I can always query against a DB and build queries more REPL like. Having said that, I do like the idea of Sqlite in the front end for localstorage.

galaxyLogic 5y ago

If I understand this correctly the db would be on the client/browser. Thus any persistence would happen via local storage or such. But: 1. People often reset the cache on their browser, after all it is just a cache. SO a big benefit of databases which is persistent data is kind of not there. Juts rest your cache. 2. The second great benefit of databases is that they are multi-user. There can be in fact millions of users. But this benefit would not be there if the database lives and executes in the browser.

jitl 5y ago

This is great to see, and a project I considered attempting myself for a bit. I’m excited to test it out. @jlongster I have a question about this: > The backend calls it [Atomics.wait] to wait on the result from the worker and blocks until it’s done. Does this mean the main (UI) thread is blocked during queries? Or are there more threads, like UI <- async messages -> SQLite main <- Atomics blocking -> SQLite FS backend? ———— At Notion, we’ve used IndexedDB for two purposes: (1) to durably persist a queue of changes to send to our backend, and (2) in the desktop app, to LRU cache the page data we read from the server to accelerate reads. Both of these used localStorage years ago, but we ported to IndexedDB because of data loss on localStorage. Porting was fine for the write queue, but we really noticed the slow when we tried porting the data cache. To get close to the original performance we coalesce reads, and we delay writes to the cache significantly so they can batch more effectively into a single readwrite transaction that we send after the reads for the current page load are complete. That worked okay, but it was annoying to maintain the IDB cache code because our Android and iOS apps used SQLite for their caches, and it’s so much easier to add new queries using SQL compared to writing IDB iterations - and it’s faster. So we switched to using native SQLite via a bridge to a Node process. Now with absurd-sql, maybe we could bring the same caching logic to browsers. The thing stopping me is how unreliable we’ve found IndexedDB to be - aside from the optimization work. We notice a lot of bugs in IDB implementations on different browsers. In Safari (especially on iOS) there’s a bunch of spooky issues that have caused stalls or spurious errors, sometimes requiring an app restart before the IDB database can be re-opened. Forget it on Android - weird vendor webview patches mean your storage might get cleared out from under you. On Firefox, we notice that sometimes the IndexedDB database doesn’t create all the object stores we request for some reason. Even on Chrome, IndexedDB can suddenly start refusing writes in the middle of a session with no clear explanation, and on Windows restarting the computer is sometimes the only fix. If we can share SQLite queries with our native apps then maybe it’s worth wading deeper into these issues… but it really does feel like building on quicksand.

jlongster 5y ago
> Does this mean the main (UI) thread is blocked during queries? Or are there more threads, like UI <- async messages -> SQLite main <- Atomics blocking -> SQLite FS backend? The latter! Your app running queries must be on a worker, and then the IDB backend will spawn another worker. `Atomics.wait` is not even available on the main thread. Ideally in the future, there will be a better storage API that we don't even need all the Atomics silly-ness (hopefully it provides Sync methods) That's really cool re: Notion! That's exactly the kind of thing I want too: a way to just build apps the same way everywhere, on mobile/desktop/web. You are right about various issues, and I personally don't have to worry much about it on my app because I have native mobile apps and I don't support the web version on mobile. I intentionally do that -- the mobile web is just too broken in too many ways. My impression is the IDB is more stable on desktop, but because mobile is more memory sensitive there are more issues there. However, you should try it out! I definitely discovered a lot of weird things; I definitely was able to get Safari into a weird state the required a complete app restart. Here's the thing though: I found ways around them. If you do a lot of read requests in a certain way, Safari will lock up permanently. However, if you make sure to wait until the `readonly` transaction is finished before starting a new one, the problem goes away. I was able to reliably reproduce that problem and it went away with that fix. I think absurd-sql is so promising because it normalizes the patterns of how IDB is accessed, and it already includes fixes for a bunch of edge cases. There are probably more, but try it out! If you run into an edge case, we can tweak the IDB backend until it works. We can paper over these issues in the underlying backend and you don't have to worry about it because you aren't directly managing IDB read/writes.

scns 5y ago

Opening this, on desktop, left me speechless with an open mouth: https://archive.jlongster.com/ https://archive.jlongster.com/ You can explore it with the mouse cursor.

KETpXDDzR 5y ago

> But that’s it. That’s the only catch. I'd like to add one drawback with this solution: Complexity. From my experience, complexity can easily lead to more problems than what it solves. With all the "blackboxes", e.g., WASM, JS, SQLite, IndexDB, ..., it might be hard to find bugs. Most of the tools used are somewhat stable and mature tough. SQLite, for example, has a whooping 100% test coverage (line coverage at least).

KETpXDDzR 5y ago

That reminds me of benchmarking key-value stores. Surprisingly for me at that time, SQLite crushed all famous KV stores.