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.<p>It was torpedoed because it was SQL-based (and not trendy "key value" and "web scale").<p>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".<p>[0] <a href="https://www.w3.org/TR/webdatabase/" rel="nofollow">https://www.w3.org/TR/webdatabase/</a>
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.<p>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!<p>[1] <a href="https://wicg.github.io/file-system-access/#api-filesystemwritablefilestream" rel="nofollow">https://wicg.github.io/file-system-access/#api-filesystemwri...</a> <a href="https://caniuse.com/native-filesystem-api" rel="nofollow">https://caniuse.com/native-filesystem-api</a><p>[2] <a href="https://beepb00p.xyz/myinfra.html" rel="nofollow">https://beepb00p.xyz/myinfra.html</a>
> SQLite, even though it’s implemented on top of IndexedDB, easily beats out IndexedDB in every single performance metric. The absurdity!<p>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.<p>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.
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.<p>I ended up switching to <a href="https://github.com/agershun/alasql" rel="nofollow">https://github.com/agershun/alasql</a> 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.)<p>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.<p>Just something to be aware of if you're investigating it.<p>If there's a high-level library that makes more effective use of memory with sql.js under the hood let me know.<p>Unlike absurd-sql I don't need the results to be permanent. I just wanted an in-memory SQL for joining, filtering, grouping data.<p>[0] <a href="https://github.com/multiprocessio/datastation" rel="nofollow">https://github.com/multiprocessio/datastation</a>
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?
> Every [IndexedDB] library I looked at was messy and made performance even worse<p>Seconded – I was pretty dismayed when I saw the IndexedDB helper library landscape.<p>I ended up making <a href="https://github.com/TehShrike/small-indexeddb" rel="nofollow">https://github.com/TehShrike/small-indexeddb</a> which is ~50 lines to make it less onerous to work directly with the IDBObjectStore.
> browsers may delete your IndexedDB database under certain conditions<p>Safari will happily delete your IndexedDB database after 7 days of inactivity.<p>It deletes "all of a website’s script-writable storage after seven days of Safari use without user interaction on the site". That includes:<p>- Indexed DB<p>- LocalStorage<p>- Media keys<p>- SessionStorage<p>- Service Worker registrations and cache<p>Source: <a href="https://webkit.org/blog/10218/full-third-party-cookie-blocking-and-more/" rel="nofollow">https://webkit.org/blog/10218/full-third-party-cookie-blocki...</a><p>Found via: The pain and anguish of using IndexedDB: problems, bugs and oddities - <a href="https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2adff49a" rel="nofollow">https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2...</a>
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.
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.<p>Having said that, I do like the idea of Sqlite in the front end for localstorage.
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 ??
This is great to see, and a project I considered attempting myself for a bit. I’m excited to test it out.<p>@jlongster I have a question about this:<p>> The backend calls it [Atomics.wait] to wait on the result from the worker and blocks until it’s done.<p>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?<p>————<p>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 <i>after</i> the reads for the current page load are complete.<p>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.<p>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 <i>the computer</i> is sometimes the only fix.<p>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.
<p><pre><code> 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.
</code></pre>
Another approach than writing the data to a server could be to allow the user to store it on their own hard disk.<p>This could be done via the File System Access API:<p><a href="https://developer.mozilla.org/en-US/docs/Web/API/File_System_Access_API" rel="nofollow">https://developer.mozilla.org/en-US/docs/Web/API/File_System...</a><p>The API already works nicely in Desktop Chrome:<p><a href="https://googlechromelabs.github.io/text-editor/" rel="nofollow">https://googlechromelabs.github.io/text-editor/</a>
And here I thought the most popular way to use SQL on the web was with a backend API. Shows what I know...<p>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?<p>/s
What's kind of bonkers here is that IndexedDB uses sqlite as its backend. So, this is sqlite (WASM) -> IndexedDB -> sqlite (native).<p>The Internet is a wild place...
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.<p>Only mad because it’s so much better in every way.
If I understand this correctly the db would be on the client/browser. Thus any persistence would happen via local storage or such. But:<p>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.<p>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.
Have you seen <a href="https://github.com/WebReflection/sqlite-worker" rel="nofollow">https://github.com/WebReflection/sqlite-worker</a>
Just a month ago I was talking with a person that told it is impossible to use SQL in the frontend.<p>It is a great project and I hope one day we will be able to use it in production.
> But that’s it. That’s the only catch.<p>I'd like to add one drawback with this solution: Complexity.<p>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).
if you could enable WAL without a drop in performance, you might be able to use something like litestream to sync to the backend<p>this may be the most performant/secure/cheapest b2b saas stack ever<p>every customer gets own sqlite database, downloads it to their browser on first load, each db gets synced to s3<p>everything is served statically from s3 as well
Highly entertaining and informative article/project - thanks for taking the time to write about it.<p>This is really cool, I wonder if it could be built into something like <a href="https://datasette.io/" rel="nofollow">https://datasette.io/</a> - without the need for a python runtime.
So good to see persistent dbs coming to the web. I also started using <a href="https://github.com/WebReflection/sqlite-worker" rel="nofollow">https://github.com/WebReflection/sqlite-worker</a> which is pretty similar
Reading Mozilla docs I get the impression that your data could get nuked in indexdb if it needs to clear space.<p>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….
Opening this, on desktop, left me speechless with an open mouth: <a href="https://archive.jlongster.com/" rel="nofollow">https://archive.jlongster.com/</a><p>You can explore it with the mouse cursor.
"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.
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.<p>that would solve the safari indexeddb 7 day ttl issue to start with.<p>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.
> that allows SQLite to read/write from IndexedDB in small blocks, just like it would a disk<p>So it sounds like IndexedDB was the right abstraction all along.
I stopped reading after this<p>"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."<p>RDBMS all the way baby