[HN Gopher] A future for SQL on the web
___________________________________________________________________
A future for SQL on the web
Author : rasmusfabbe
Score : 619 points
Date : 2021-08-12 14:33 UTC (8 hours ago)
(HTM) web link (jlongster.com)
(TXT) w3m dump (jlongster.com)
| Jyaif wrote:
| > 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 wrote:
| 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.
| sosodev wrote:
| Thank you for this. I've been hoping for something like this for
| ages.
| icodar wrote:
| Have you seen https://github.com/WebReflection/sqlite-worker
| jlongster wrote:
| All that does it export the entire db and write it down
| whenever something changes.
| timdorr wrote:
| 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...
| knubie wrote:
| Wait, I thought IndexedDB was implemented with LevelDB [0] in
| Chrome?
|
| [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.
| thayne wrote:
| That's even mentioned in the article.
| eurasiantiger wrote:
| Literally why it's called absurd-sql.
| leros wrote:
| 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 wrote:
| Not enough blockchain! Needs more Web 4.0
| short_sells_poo wrote:
| 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 wrote:
| I legitimately can no longer tell if this was being
| suggested sarcastically, or if you guys are being
| serious.
| mst wrote:
| [x] Yes.
| short_sells_poo wrote:
| 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.
| da_chicken wrote:
| The point is if the VCs can tell if we're being sarcastic
| or serious.
| mst wrote:
| First, you need to write an ipfs implementation that uses
| indexedb so that can handle the sync ...
| westurner wrote:
| TIL, about Graph "Protocol for building decentralized
| applications quickly on Ethereum"
| https://github.com/graphprotocol
|
| 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 wrote:
| This could actually work for certificate attestation if baked
| directly into the browser.
|
| https://github.com/google/certificate-transparency
| remus wrote:
| 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.
| quickthrower2 wrote:
| 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....
| pinkie123 wrote:
| qwufeshdshwaDWKASwhwhawhhwshuah
| thwarted wrote:
| Sounds more like SQLite in the browser.
| jacobmischka wrote:
| Did you read the article? That's literally what it is.
| djhworld wrote:
| 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/ - without the need for a python
| runtime.
| [deleted]
| danielovichdk wrote:
| 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 wrote:
| 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.
| TehShrike wrote:
| > 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
| which is ~50 lines to make it less onerous to work directly with
| the IDBObjectStore.
| LAC-Tech wrote:
| I've had good experiences with
|
| https://github.com/jakearchibald/idb
|
| It's basically a promise-based version of the standard API.
| TehShrike wrote:
| 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.
| stevage wrote:
| 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?
| rattray wrote:
| 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).
| quickthrower2 wrote:
| 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.
| renke1 wrote:
| What would be the best way to do migrations with absurd-sql?
| jlongster wrote:
| 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
| jeffbee wrote:
| 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 wrote:
| 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/
|
| 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.
| jacobpedd wrote:
| 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.
| ofrzeta wrote:
| "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 wrote:
| Opera is Chrome based. WebKit is the other.
| The_rationalist wrote:
| If only mozilla hadn't screwed up
| bob1029 wrote:
| > 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 wrote:
| Nice. What kind of business logic are you using SQL queries
| for?
| bob1029 wrote:
| Any sort of decision point that tends to vary between our
| customers. We are getting really tired of maintaining custom
| code piles.
| danlugo92 wrote:
| By that you mean several piles in different languages? Or
| one pile for each customer?
| tomaszs wrote:
| 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.
| mg wrote:
| 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...
|
| The API already works nicely in Desktop Chrome:
|
| https://googlechromelabs.github.io/text-editor/
| jlongster wrote:
| 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 wrote:
| 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 wrote:
| Read harder. https://jlongster.com/future-sql-web#more-
| than-just-another-...
| dang wrote:
| 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
| jlongster wrote:
| Sure thing dang, thanks! Sorry!
| rektide wrote:
| 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-
| filesystemwri... https://caniuse.com/native-filesystem-api
|
| [2] https://beepb00p.xyz/myinfra.html
| jlongster wrote:
| Thank you!
| x0x0 wrote:
| This is amazing. Thanks for sharing the link to karlicoss'
| site.
| shekhirin wrote:
| 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/.
|
| 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 wrote:
| now let's see what it takes to make absurd-fs, where we use
| 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.)
| EvanAnderson wrote:
| 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/
| gunapologist99 wrote:
| Another, probably not insurmountable, issue with SQLite in the
| browser (without having a formal specification that could
| produce cross-language alternatives):
|
| 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.
| Cyberdog wrote:
| I find this an odd comment. Is a 100% Rust browser something
| anyone is clamoring for?
|
| 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.
| TehShrike wrote:
| I used it back in the day and was quite sad when it
| disappeared. Nolan Lawson does a good job telling the sordid
| tale at https://nolanlawson.com/2014/04/26/web-sql-database-in-
| memor...
| SahAssar wrote:
| The justification was more that the standardization process
| requires at least 2 independent implementations, and nobody was
| working on rewriting SQLite from scratch.
|
| I would have loved WebSQL, but it is reasonable to require
| multiple implementations for full standardization.
| mschuster91 wrote:
| > and nobody was working on rewriting SQLite from scratch.
|
| One could also have embedded a trimmed-down PostgreSQL or
| MariaDB into browsers.
| fzzzy wrote:
| That sounds great! Let's do it.
| SahAssar wrote:
| I don't know the reasoning being not doing it, but I'd
| guess it's related to those DB engines not being geared or
| interested in embedded use-cases and that no SQL engine
| seems to entirely agree with any other how to handle
| certain parts of SQL.
| mschuster91 wrote:
| Indeed, it would have been a lot of work trimming down
| any of the major FOSS RDBMS towards embedding them in a
| browser.
|
| Regarding the SQL dialect - that could have been handled
| by specifying a standard SQL dialect that's then
| dynamically translated to the target engine's dialetc.
| coliveira wrote:
| I don't buy this reasoning because they could have
| standardized only the interface, letting it open for
| developers to choose SQLite or some other future
| implementation. There is no need to standardize SQLite
| behavior, including bugs.
| aseipp wrote:
| What actually happens in that scenario is that one group
| now writes an adapter on top of SQLite to make it
| "standardized" to whatever interface you design, increasing
| the complexity and scope for bugs. To be clear: you're
| literally reimplementing something that SQLite already
| does, except probably much worse, in the name of the
| "standard."
|
| Then that adapter is carbon copied by everyone, because
| again, nobody is going to reimplement an 80kLOC SQL
| database as well as whatever 10k LOC
| parser/lexer/typechecking adapter someone wrote, if they
| can avoid it. Then everyone just uses that forever, and
| you're back to square 1, using one implementation
| everywhere, which is the exact situation standards are
| supposed to avoid anyway.
|
| The working group was correct to reject a "compromise" like
| that because that's never how it works out in practice;
| it's a submarine suggestion from the start. And a big part
| of this is all because, as evidenced by numerous responses
| in this thread, modern computer programmers seem to value
| their own immediate satisfaction and time over literally
| _every_ other potential concern, no matter how significant.
| SahAssar wrote:
| That's not how the process works though. You could argue
| that they should change the process, but the reasoning
| behind it seems solid to me:
|
| > Implementation experience is required to show that a
| specification is sufficiently clear, complete, and relevant
| to market needs, to ensure that independent interoperable
| implementations of each feature of the specification will
| be realized.
|
| From
| https://www.w3.org/2015/Process-20150901/#implementation-
| exp...
| pornel wrote:
| I think the current state is fine. You ship your WASM-blob of
| SQLite, which has the exact bug-compatible version of SQLite
| that you've tested your app against. The browsers are not
| burdened with maintaining a huge API surface that can "break
| the web".
|
| 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.
| [deleted]
| daleharvey wrote:
| 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-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
| BulgarianIdiot wrote:
| > The excuse was that a standard needs to have multiple
| implementations otherwise we are standardising implementation
| details and bugs.
|
| _Looks at Chrome_
| hitekker wrote:
| I view the "standards" argument as a red herring for building
| a NoSQL db in the browser. Which, to this day, is slow, buggy
| and requires third party libraries to be usable [1]
|
| 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...
| tehbeard wrote:
| Having worked with IndexedDB quite a bit at my job, I can
| level three criticisms at IndexedDB.
|
| 1. The API is the dogshit hot mess you'd expect for a pre
| promise/async API.
|
| 2. The lack of partial/computed secondary indexes.
|
| 3. Apple/Safari does EVERYTHING in their power to break it.
| I refuse to believe it's incompetence at this point, it's
| actively malicious.
| daleharvey wrote:
| I am familiar with Nolans article, I created PouchDB (the
| project he is discussing), you seem to have misred the post
| as it discusses the technical nuance and tradeoffs involved
| in the decision at many points entirely agreeing with the
| position against WebSQL. While Nolan came to the a
| different conclusion than I did (a point he made in the
| post) he laid out challenges very well and made it very
| clear there was no obvious technical answer.
|
| Regardless of how you view it, the benefit of hindsight
| shows the exact thing that people warned would happen did
| in fact happen (a widespread venerability in SQLite exposed
| across various browsers). Its also a fairly strange point
| to be personally insulting people involved in the process
| whose careers are doing perfectly well.
| hitekker wrote:
| Edit: Nolan replied before and corrected me. I've removed
| my misinterpretation and kept my main point below.
|
| Back in the day, there were people who strongly suggested
| MongoDB and IndexedDB were the future, and that
| PostgreSQL, MySQL, SQLite were trash. I've noticed the
| folks who rode that hype-train moved into other kinds of
| occupations that aren't exactly engineering-focused
| anymore.
| nolanl wrote:
| I wrote that article 7 years ago, and FWIW I would side
| more with Dale these days. It's probably a good thing we
| didn't just slap a half-baked API on top of SQLite and
| call it a web standard.
|
| The biggest problem is that yeah, WebSQL tends to be
| faster than IndexedDB. Or at least it was back when I was
| working on PouchDB. Biggest issue IIRC was that joins
| were faster in SQLite than implementing the same thing in
| userland on top of IndexedDB. Browsers eventually shipped
| getAll/getAllKeys which also helped with cursor slowness.
|
| I haven't looked much at the Storage Foundation API [1],
| but it seems like a more reasonable approach moving
| forward. Just give developers the low-level tools and let
| them build SQLite on top of it. Also the Chromium devs
| have been working on relaxed durability, which apparently
| improves IDB perf in some scenarios [2] (although still
| not as fast as Firefox it seems [3]).
|
| [1]: https://github.com/WICG/storage-foundation-api-
| explainer
|
| [2]:
| https://www.chromestatus.com/feature/5730701489995776
|
| [3]: https://bugs.chromium.org/p/chromium/issues/detail?i
| d=102545...
| andai wrote:
| PROLOGUE
|
| Six houses, all alike in dignity, In fair IRC, where we lay
| our scene, From ancient grudge to new mutiny, Where civil
| blood makes civil hands unclean. From Oracle, that SQL seer
| of IndexedDB, To Google, the stronghold of search, We add
| Mozilla, the Web SQL killa, And Apple, peering from its
| mobile perch. Here, a storage war would set keys to clack,
| Tongues to wag, and specs to shatter, There was also
| Microsoft and Opera, Who don't really seem to matter.
|
| THE PLAYERS
|
| NIKUNJ MEHTA, of House ORACLE, an instigator JONAS SICKING,
| of House MOZILLA, an assassin MACIEJ STACHOWIAK, of House
| APPLE, a pugilist IAN FETTE, of House GOOGLE, a pleader
| CHARLES MCCATHIENEVILE, of House OPERA, a peacemaker
|
| ACT 1
|
| SCENE: A dark and gloomy day in Mountain View, or perhaps a
| bright and cheery one, depending on your IRC client's color
| scheme.
| dmitriid wrote:
| > The excuse was that a standard needs to have multiple
| implementations otherwise we are standardising implementation
| details and bugs.
|
| 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.
| qwerty456127 wrote:
| > It was torpedoed because it was SQL-based (and not trendy
| "key value" and "web scale").
|
| 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.
| gunapologist99 wrote:
| > It was torpedoed because it was SQL-based (and not trendy
| "key value" and "web scale").
|
| 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...)
| zzzeek wrote:
| first thought, was there a "standard" for key/value stores?
| mongodb the reference implementation?
|
| 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.
| apavlo wrote:
| > SQL survived the key/value fad
|
| SQL has survived _every_ fad since the 1970s:
|
| Stonebraker "What Goes Around Comes Around"
|
| https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape.
| ..
| jbergens wrote:
| I think sql is a bad fit for front end code in js. We may
| have ended up with another fight over ORMs. The Vietnam war
| of computer science as I think it was called.
| devwastaken wrote:
| No, SQLite was not safe to be used with arbitrary queries.
| There were multiple memory vulnerabilities that allowed escape
| of the browser sandbox.
|
| https://www.sqlite.org/cves.html
| infogulch wrote:
| This article is about how to defend against potential websql
| vulns. https://www.sqlite.org/security.html
| nightpool wrote:
| This article only contains mostly information on adding
| more limits to avoid DoS--nothing there would harden WebSQL
| against SQLite vulnerabilities except for
| SQLITE_DBCONFIG_DEFENSIVE, which was only added in
| _response_ to the zero-day bugs that were found in Chrome
| after implementing SQLite.
| matharmin wrote:
| SQLite as a library is amazing, SQLite as a spec for a standard
| web API is not.
|
| 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.
| kall wrote:
| The sad irony is that if HN is anything to go by, SQLite is
| super trendy now.
|
| 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?
| otabdeveloper4 wrote:
| There's no such thing as "the SQLite" that you could even
| theoretically bundle.
|
| 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.
| EMM_386 wrote:
| > The sad irony is that if HN is anything to go by, SQLite is
| super trendy now.
|
| 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.
| kall wrote:
| Yeah I think so too. It's a cliche by now to say these
| things go in cycles but they do. If we like this part of
| the cycle, let's ride it and let SQL get hyped, even though
| its been around forever and never went away.
| 411111111111111 wrote:
| What gives you the confidence that it's going on circles?
| I cannot see our industry repeating the mantra that NoSQL
| solves all our persistence problems ever again. There are
| use cases for them, but not everything is a good fit and
| I'm pretty sure we found that out as an industry.
|
| I'm sure there are always going to be individuals that
| will claim that they're better at everything, but that
| doesn't really mean anything. There are people that
| believe in a flat earth as well after all
| regularfry wrote:
| The cycles are longer than you're imagining. Relational
| database folk have been railing against the incursions of
| one generation of non-relational database type or another
| since at least the 1980's.
| smackeyacky wrote:
| Actually, relational databases are the relative
| newcomers. The limitations of things like CODASYL were
| well known to 1960s programmers and Codd's relational
| model sought to address them.
|
| Why the non-relational databases keep getting reinvented
| has been a bit of a mystery when there is already a rich
| history of development to look to. I get the feeling a
| lot of the industry isn't much into history, especially
| of pre-micro computer systems.
|
| Network databases, key-value stores, graph databases,
| commercial offerings like Pick... you'd think the NoSQL
| people would have looked into it all before proclaiming
| their new found solutions, but apparently not.
| [deleted]
| coliveira wrote:
| The cycles don't happen exactly. But I'm pretty sure they
| will come up with something new that in the end is the
| same as NoSQL and give it a trendy name, so young
| developers will believe they found gold and run with
| it... It is just the way these things keep happening.
| kwhitefoot wrote:
| > not trendy "key value"
|
| 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.
| jmull wrote:
| Building off what others have pointed out about sqlite not
| being a good choice for a browser standard, I just want to note
| that the approach laid out in the article is exactly the right
| approach to integrate sqlite into a web app: a web app links a
| version from a well-maintained "distro". The issues inherent in
| trying to use an implementation as a specification and standard
| go away, while a "distro" maintained by experts will lower the
| bar to entry.
|
| 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.
| jlongster wrote:
| 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/
|
| 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 wrote:
| 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.
| swlkr wrote:
| 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
| collaborative wrote:
| So good to see persistent dbs coming to the web. I also started
| using https://github.com/WebReflection/sqlite-worker which is
| pretty similar
| lioeters wrote:
| > 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-
| blocki...
|
| Found via: The pain and anguish of using IndexedDB: problems,
| bugs and oddities -
| https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2...
| lowwave wrote:
| I wish every browser does that! Web site has no business
| storing data on my computer more than 4093 bytes (that is
| already too much) per domain in my computer just because I
| visited a web page. 10 MB - 10GB of data is too much.
| sroussey wrote:
| Yes, send all your data to someone's server instead.
| tshaddox wrote:
| If you're concerned about a web site silently having access
| to personal data from your phone, surely it doesn't matter
| whether that web site is storing it silently in your
| device's browser storage, or storing it on the web site's
| servers.
|
| If, on the other hand, you're explicitly uploading or
| entering data on a web site, well then yes, the default
| assumption would certainly be that the data is getting
| saved on the web site's servers (if it's being saved at
| all). That's how the web has worked for a very long time,
| with the exception of cookies and a few other more recent
| but not widely known storage mechanisms which in most cases
| aren't considered sufficiently robust for persistent on-
| device storage.
| beebeepka wrote:
| I think that's a valid concern. Stuff like this should only
| be allowed after a prompt where the user explicitly allows
| it, just like any other permission
| kitsunesoba wrote:
| I would say it's even more important in the case of a web
| app that stores data locally, because it's imperative that
| the user understands that data is tied to that particular
| web browser and device, and that clearing site data (which
| I've known non-technical users to do periodically to reduce
| tracking footprint), changing devices, etc can/will result
| in data loss.
|
| Oddly enough the handful of web apps I've known to use
| local storage don't communicate this at all. Developers
| seemingly treat data in browser local storage as existing
| indefinitely and unlikely to be deleted, when in fact it's
| probably the most easily accidentally lost form of storage
| an app can have.
| derefr wrote:
| I would point out that the article you linked mentions that
| webapps "added to the home screen" on iOS Mobile Safari are
| exempt from this garbage-collection process. In such a case,
| the "add to the home screen" step is being taken as the user
| expressing the explicit desire to have that data around
| indefinitely. Which seems sensible to me; webapps _should_ be
| treated as ephemeral-by-default, and only be allowed persistent
| storage if the user goes to lengths (i.e. beyond just clicking
| "Accept" on a modal) to express their desire to keep the data.
|
| (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...
| tarsinge wrote:
| PWA are not a standard, they are a Google concept pushed
| through Chrome (OS). I'm fine with Apple never supporting it.
| mst wrote:
| So long as I can also use them in firefox they're
| 'unstandardised but not single vendor' and at that point
| I'm not _as_ troubled about it.
| TingPing wrote:
| Firefox doesn't support PWAs...
| mst wrote:
| They don't support the same precise system, but you can
| e.g. definitely do installable apps on Firefox for
| Android: https://developer.mozilla.org/en-
| US/docs/Web/Progressive_web...
| Touche wrote:
| Which specific feature of PWAs is non-standard?
| monocasa wrote:
| PWA is a Google term, but the whole web app thing is older
| than even the native app store on iPhone.
| BadInformatics wrote:
| Google may have coined the term (not sure about this),
| but it's far from their own thing [1]. PWA should've been
| a blanket term for a set of standards and guidelines for
| developing web apps. Those include progressive
| enhancement, which I don't think most people would
| expect.
|
| Unfortunately, the term has been co-opted to mean
| "website I can install/pin as an app". Again, Google is
| probably to blame for this, but AFAICT it was never meant
| to be the meaning of the term. What it does do is create
| misunderstandings, like a sibling thread claiming that
| (desktop) Firefox doesn't support PWAs because you can't
| install anything.
|
| [1] https://developer.mozilla.org/en-
| US/docs/Web/Progressive_web...
| arthurcolle wrote:
| I wish these bookmarks extended to other browsers on iOS.
| It's already crazy they can't ship custom rendering engines,
| but basically this in and of itself could be considered
| anticompetitive (only Safari bookmarks can be exempted from
| this GC process) lol!
| ricardobeat wrote:
| I think all home screen apps run on the system webview,
| there are no "safari bookmarks" in this context.
| ciex wrote:
| But doesn't this 7 day limit only apply if you load a third
| party script that uses IndexedDB into your site?
|
| Webkit says in your source:
|
| > It is not the intention of Intelligent Tracking Prevention to
| delete website data for first parties in web applications.
| mschuster91 wrote:
| So _that_ is why a website I work on has complaints of Apple
| users who keep getting nagged by cookie banners - they store
| their settings in LocalStorage since cookies tend to have
| issues when the content is too long. Thank you so very much.
| vlunkr wrote:
| This is why I haven't used LocalStorage or any other browser
| storage solution for a long time. They seem really cool, but if
| they will just get wiped by some browsers, then what is the
| point? As of a couple of years ago, localStorage didn't work at
| all in a private windows in safari. Unless you have some very
| specific needs, I don't know how you would every use IndexedDB
| tarsinge wrote:
| As a Safari user I'm really happy with that behavior, it avoids
| the hassle of manually purging it regularly.
|
| 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.
| jlongster wrote:
| 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 wrote:
| 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 wrote:
| People are already trying to get me to hook up
| https://litestream.io/ to it
| adam12 wrote:
| 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.
| da_chicken wrote:
| I mean, he's correct in the same sense that a Buick can't be
| made to haul a fifth wheel trailer. It's because a modern
| Buick's design doesn't include the bed required to install
| the fifth wheel on.
|
| Nevermind that there's nothing strictly stopping GM from
| changing the designs for Buicks to include a vehicle capable
| of a fifth wheel mount.
| enkrs wrote:
| If you add the website to homescreen (make it an "app") then
| the 7 day storage limit does not apply.
|
| This makes total sense to me, and I'm happy Facebook can't
| store tracking data in my Safari for longer than 7 days.
| conductr wrote:
| > I'm happy Facebook can't store tracking data in my Safari
| for longer than 7 days.
|
| They're storing it somewhere, no? I feel like the benefit
| here is more so the browser file system doesn't get bloated
| andrewcl wrote:
| I'd assume Tim has a different definition of what an app is
| than what you're thinking of.
| tshaddox wrote:
| But it's totally a valid option to have an app that is a web
| app and does not have push notifications. For the vast
| majority of apps regardless of platform, I absolutely do not
| want push notifications. It seems bizarre to consider that
| feature as a litmus test for whether the web is a usable
| platform on iPhones. Would you also require web apps to
| appear in the App Store in order to consider a web as a
| usable platform on iPhones?
| enumjorge wrote:
| That's a fair point, but I'd argue that for cases where the
| app really needs notifications (for example an alerting
| system) or when the users want them, building a web app is
| not actually an alternative even if Apple considers it one.
| tshaddox wrote:
| I don't think Apple considers the web as an alternative
| platform for apps that are designed to alert you at any
| time of day for any reason, including when you are not
| actively using your devices. I think this is a very, very
| good thing, and doesn't mean that web apps are not a
| viable alternative to the App Store.
| z3t4 wrote:
| Apple did plan to make web apps first class on iPhones. But
| changed their minds when developers complained about not
| getting hard metal access. Maybe they thought that supporting
| web apps would get them more apps, but developers gave them
| apps for free and it allowed Apple to have a monopoly.
| Microsoft tried the same with their phones but somehow
| failed... Google went the web app route on ChromeOS with
| decent success. Mozilla tried with FirefoxOS but was too
| early - FirefoxOS is now very popular but under another name
| on low end hardware phones.
| papito wrote:
| Makes sense because even my oldish Macbook Pro goes into
| afterburner mode trying to browse the modern Internet.
| cosmie wrote:
| PWA are explicitly exempt from the data storage wipe
| behavior[1]. So there's that, at least. For now.
|
| 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/
|
| [2] https://michaellisboa.com/blog/prompt-ios
| busymom0 wrote:
| That's only half true. PWA are exempt from data storage
| wipe behaviour only if they have been added to the Home
| Screen. And as your second article points out:
|
| > It's important to ensure that our iOS visitor is using
| Safari because iOS doesn't permit other browsers to install
| our awesome PWA's!
|
| So if the user is using Firefox or Chrome etc browser, they
| can't do it. And since all browsers have to use WKWebView,
| the limitation applies to all browsers. Apple has basically
| crippled other browsers with this anti competitive
| behaviour.
| Macha wrote:
| 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.
| PaulHoule wrote:
| Looks like fun.
|
| So far I've found IndexedDB to be outright depressing in it's
| limitations.
| guyromm wrote:
| 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 wrote:
| There's also CouchDB/PouchDB made for this use case.
| guyromm wrote:
| 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.
| OOPMan wrote:
| 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
| eatonphil wrote:
| 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 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
| jlongster wrote:
| Something sounds wrong with your setup. I've had no problems!
| lovasoa wrote:
| 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 wrote:
| 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.
___________________________________________________________________
(page generated 2021-08-12 23:00 UTC)