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".
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-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
Looks at Chrome
And yet we're are now at a point where Chrome rams its own APIs through standards bodies, and there are no (and often won't be) any independent competing implementations.
That way you never have to deal with browser incompatibility or unchangeable specifications.
For those who are able to stomach an uncomfortable political history instead of an easy, technical answer, you can take a look at [2]. It's interesting that 7 years later, many the folks who pushed hard to get rid of SQL in favor of NoSQL seem to no longer occupy positions of prominence in the industry.
[1] https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_A...
[2] https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...
The issue is not just that there wasn't a competing implementation at the time, it is that you could not feasibly create a competing implementation. The set of features supported by SQLite is _massive_. And even you stick with SQLite, which version? And which extensions? Which features do you remove because they're not safe or not applicable for web?
Alternatively you could start from the ground up and create a new database spec that is similar to SQLite, but more limited in scope. But then it will not be directly compatible with SQLite, and need a translation layer. The browser would likely have to re-implement large parts of SQLite for this to work. And you'd miss out on so much of the functionality that makes SQLite attractive to use.
Arguably that would still have been better than IndexedDB. All that IndexedDB has going for it, is that it is simple to implement. But even then Safari manages to get the implementation horribly broken so often.
What we need instead is to have a low-level storage api that can be used to implement other databases as libraries, including SQLite. The Storage Foundation API, mentioned in the post, might just give us that.
>The issue is not just that there wasn't a competing implementation at the time, it is that you could not feasibly create a competing implementation. The set of features supported by SQLite is _massive_. And even you stick with SQLite, which version? And which extensions? Which features do you remove because they're not safe or not applicable for web?
This seems like a case of perfect being the enemy of good.
Otherwise you'd have to deal with different versions of SQLite in different browsers, most likely outdated, with many options turned off. SQLite is full of quirks and gotchas, so it's safest to ship your version in your app.
I would have loved WebSQL, but it is reasonable to require multiple implementations for full standardization.
One could also have embedded a trimmed-down PostgreSQL or MariaDB into browsers.
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/
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.
I think devs should be happy, not sad. It looks like we’re finally getting it right, and will have a feasible way to add the incredible sqlite to the set tools we have available to make web apps.
Hopefully absurd-sql keeps going and browsers adopt a good storage standard.
This is almost certainly not even close to correct. There are substantial reasons why it wouldn't be a good idea, but this might be the biggest one: it's very hard to adequately sandbox an external C library.
(... and, also, Apple probably would prefer that the web didn't exist at all, but that's a different pandora's box...)
This is also how I justify my opinion that the "no independent implementations" was more of an afterthought excuse, and less of a primary motivation on Mozilla's part.
(I'm a little pressed on time here, but I could probably find more examples if I had more time to search... Sorry!)
Quoting Jonas Sicking from a W3C IRC log[0]:
> we've talked to a lot of developers
...
> the feedback we got is that we really don't want SQL
Quoting Maciej Stachowiak from the "public-webapps@w3.org" list[1]:
> Hixie has said before he's willing to fully spec the SQL dialect used by Web Database. But since Mozilla categorically refuses to implement the spec (apparently regardless of whether the SQL dialect is specified),
...
> At the face-to-face, Mozilla representatives said that most if not all of the developers they spoke to said they wanted "anything but SQL" in a storage solution.
The comments relating to JOIN in the comments on Mozilla's blog post comparing IndexedDB and WebSQL[2] betray the anti-SQL (and, arguably, anti-relational database) stance at Mozilla.
Mozilla's people didn't like SQL, so any excuse for dismissing WebSQL (or even a simplified SQL dialect that didn't have the "quirks" of SQLite) was a foregone conclusion.
And now here we are shipping >1MB transpiled WASM payloads around when we could have agreed on a query language feature set implemented in native code using a consistent back-end standard storage format.
[0] https://www.w3.org/2009/11/02-webapps-irc
[1] https://lists.w3.org/Archives/Public/public-webapps/2009OctD...
[2] https://hacks.mozilla.org/2010/06/comparing-indexeddb-and-we...
I feel like "just sqlite" was really a very practical idea and now, because it has already failed once, we can‘t really try it again, or can we?
Having the spec basically be "bundle one particular piece of software into the browser" might not be in the spirit of web standards, but on the other hand SQLite is so widely bundled into everything, does it really matter?
If you look at the recent StackOverflow survey, the majority of the developers only have around 5 years of professional experience in the industry.
SQL has been around since the 1970s and is still around in force for reason. There's a good chance a lot of developers, especially the enormous number on the front end, do not have experience with SQL or are just getting into back-end work where they are exposed to it.
There have been a lot of SQLite articles recently, as well as node.js libraries to query SQL. I have a hunch this may be why.
Which version do you want to bundle? With which compile flags and which extensions? Do you have a checklist for bug-for-bug compatibility? Because you'll definitely need one when a future SQLite releases a security patch and breaks a million webpages.
overall, if you looked at HN like five years ago, every DB headline was key/value, mongodb, maybe some cassandra / couchdb, links to the "web scale" cartoon.
these days, it's SQL SQL SQL, with a heavy dose of SQLite and PostgreSQL. SQL survived the key/value fad despite the nebulousness of a workable "standard" (yes there's a SQL standard but no vendor DB implements all of it or doesn't add many many features, syntaxes, and behaviors on top of it). In particular SQLite recently seems to look to Postgresql for guidance on new syntaxes such as how it implemented upsert, it's RETURNING syntax is explicitly from PostgreSQL, and it interestingly uses the same "VACUUM" term for db utility cleanup.
SQL has survived every fad since the 1970s:
Stonebraker "What Goes Around Comes Around"
https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...
Whenever I need simple (but indexed) key-value (unless that's a hi-load server-side) I always just use SQLite anyway. I really don't understand why do we need any data storage other than SQLite (and HDF5 perhaps) to exist on the client side.
You can put anything in an SQLite record so it can certainly be used as a key-value table. Where at least the values can be arbitrary binary blobs.
Using SQLite results in a new, built-in reliance on a C library. Even though most languages can use C library bindings, it does present some issues in some build scenarios, such as static builds, and, given the enormous scope of SQLite, would prevent anyone from ever achieving a 100% rust browser.
At any rate, if that really is your goal for some reason, rendering engine is going to be your first problem anyway. Then a JS runtime.
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-filesystemwri... https://caniuse.com/native-filesystem-api
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.
gotta keep ourselves fully looped! ⥀
(is there perchance a repo available with your work? that'd be lovely to see.)
It doesn't. Because there are now 4 different file access api proposals. At least one of them is already in Chrome (File System Access). [1]
Both Safari and Mozilla are unwilling to implement any of the four until the mess is cleaned up, and there's a single coherent proposal.
Chrome, of course, doesn't care. Storage Foundation API in addition to File System Access they already implemented.
[1] https://github.com/WICG/storage-foundation-api-explainer/iss...
https://archive.jlongster.com/Open-Sourcing-My-Gambit-Scheme...
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.
So many apps could have just saved to a .db file instead of coming up with their own binary format. So many others could have just embedded a tiny, easily sandboxed, Lua library and added scripting languages, rather than embedding larger ones (or worse yet, implementing their own scripts).
I ended up switching to 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.
Raw sql.js is limited by the browser's wasm memory limit, but 80Mb should not cause an issue...
Another use is privacy centric apps that send nothing to the server, using the web as a kind of “install” platform but nothing else.
Seconded – I was pretty dismayed when I saw the IndexedDB helper library landscape.
I ended up making https://github.com/TehShrike/small-indexeddb which is ~50 lines to make it less onerous to work directly with the IDBObjectStore.
https://github.com/jakearchibald/idb
It's basically a promise-based version of the standard API.
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-blocki...
Found via: The pain and anguish of using IndexedDB: problems, bugs and oddities - https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2...
(And also, I've observed that webapps that notice they're on an iOS device can insist on being "added to the home screen" before they'll do anything, and so ensure their data stays around.)
The problematic aspect of this, is that there's no equivalent of this "exemption by user explicitly expressing the desire to keep the app around" for non-mobile Safari.
Maybe for desktop Safari, the data should be kept around if the web app's rel="canonical" URL is bookmarked in the browser?
Or desktop Safari could just support regular Progressive Web App standards, and so show an "Install App" indicator (like e.g. desktop Chrome does for this webapp: https://www.soundslice.com/). But I have a feeling Apple will never support this on desktop...
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.
Although I agree that it's a fairly minor win in the grand scheme of how handicapped PWA's are on iOS. And like you said, the lack of install prompts and tucking away the PWA "installation" option in the share menu makes it less than intuitive and requires manual awareness efforts by devs[2].
[1] Last section of https://webkit.org/tracking-prevention/
This makes total sense to me, and I’m happy Facebook can’t store tracking data in my Safari for longer than 7 days.
Can you run a PWA on an iPhone? Yes, you can even pin them to the start screen to keep data long term, as others have pointed out.
Are you able to use all features other browsers enable? No, only the ones Apple allows.
Nevermind that there's nothing strictly stopping GM from changing the designs for Buicks to include a vehicle capable of a fifth wheel mount.
Also in my opinion websites have no business storing data on my device through a browser. If I want that behavior I would use a native app.
We will provide a new backend for the Storage Foundation API when it's available.
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.
Webkit says in your source:
> It is not the intention of Intelligent Tracking Prevention to delete website data for first parties in web applications.
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.
Having said that, I do like the idea of Sqlite in the front end for localstorage.
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.
@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.
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.
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.
This could be done via the File System Access API:
https://developer.mozilla.org/en-US/docs/Web/API/File_System...
The API already works nicely in Desktop Chrome:
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
The Internet is a wild place...
[0] 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.
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.
Only mad because it’s so much better in every way.
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.
It is a great project and I hope one day we will be able to use it in production.
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).
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
This is really cool, I wonder if it could be built into something like https://datasette.io/ - without the need for a python runtime.
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….
You can explore it with the mouse cursor.
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.
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.
So it sounds like IndexedDB was the right abstraction all along.
Another spec that failed from a practical perspective because of Mozilla's reluctance to implement key aspects of it is web components.
So far I've found IndexedDB to be outright depressing in it's limitations.
"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
It's a fun article, I'd recommend trying reading all of it.