Hacker News Reader

I don’t see how this is fundamentally any different from IndexedDB: both are asynchronous transactional key-value databases. One is byte-oriented and the other object-oriented, but it looks to me like they’re essentially completely equivalent in what’s possible with them—except insofar as File System Access may allow you to avoid loading the entire thing into memory. (I say may because you can’t control the browser’s implementation; my feeling is that it’s not unlikely that the browser may keep the full thing in memory in FSA where I wouldn’t expect it to with IndexedDB, which if so would return them to basically the same position.)

Implementing SQLite on top of this would require that a commit write the changes, close the file, and then reopen it, since writes are only performed when you close the file. That could perform tolerably or terribly (there’s no way of knowing), but certainly won’t be as good as what you get natively, where you can truly write only part of a file, especially once you get to large databases where it will certainly be drastically slower. If you want performance out of any of these sorts of things, you’re going to need to stop putting everything in one “file” and split it into many, so that you can avoid touching most of them on most writes.

I haven’t had a chance to dig into the details of what part/version of the spec Safari have implemented so far, and I’m aware the spec is somewhat a moving target. But I do know the developers working on the type of projects I’m talking about are talking to the working group behind the spec to ensure the spec meets these type of needs.

The point of this over “abusing” IndexedDB, jlongster who created Absurd SQL had to perform some pretty unholy hacks to get it to work. When porting these db engines to WASM they expect a FS to look and behave like a FS, that’s what this does that IndexedDB doesn’t.

Quite true you could build a new SQL/DB engin on top of IndexedDB with a storage architecture designed for it. But that’s not what the existing engines are expecting.

This FS doesn’t much look like an FS either—certainly it’s way off what SQLite needs. For mutating a file, you get the options write, seek, and truncate, but they don’t do anything until you close the file. It’s essentially equivalent to the write-to-a-temporary-file-and-then-atomic-rename-overwrite pattern.

You can implement exactly the same thing on top of an ArrayBuffer that you’ll put in an IndexedDB with no fuss—and if you were doing it that way, then you could even decide whether to go for something like a rope for intermediate editing, or mutating the ArrayBuffer directly, whereas if you use FSA you have no control over which of the two approaches it might use, or something else altogether.

I'm not sure that SQL in the browser is such a good idea in the long run. Database migrations are hard enough when there's just one instance of the DB; managing and performing migrations in each individual user's browser is going to be incredibly painful. Performance is also likely to be an issue.

your phone already has some tens or hundreds of SQLite databases on it. it requires some care, but many already go through the effort in their apps, so i can imagine web developers going to the same effort if they need it.

though i can also imagine them going through that effort when they don’t, so…

I would take the hassle of carefully constructed database migrations over a no-sql database with records in multiple unknown states from different versions requiring handling routines scattered throughout my codebase.

SQL will always win, as will the web, the potential of SQL in the browser for PWAs is incredible. It also allows alignment of mobile and web apps for local storage.

It was also the correct decision to drop WebSQL, it would have limited the api and held it back. With WASM SQL we can all use whatever sql db we want with whatever extensions we want. Row/Column oriented? Our decision (SQL.js or DuckDB right now). Need a specific full text search extension, or a custom one? Just compile it in. Have an idea for a funky CRDT based replication system? Write an extension and add it.

> I would take the hassle of carefully constructed database migrations over a no-sql database with records in multiple unknown states from different versions requiring handling routines scattered throughout my codebase.

Couldn't you also carefully construct the data migrations for a no-sql store like IndexedDB? In fact they provide a mechanism for it vs. having to bolt one into your SQL system?

    managing and performing migrations in each 
    individual user's browser is going to be 
    incredibly painful

What's the alternative? Typical NoSQL slop where we basically just wind up doing the "painful" SQL stuff anyway in what ultimately winds up being an even more painful process?

What's the alternative if you want to efficiently store data in the browser? SQL isn't the problem, every database requires migrations.

I think this is an ok permissionless fallback if you dont want to bother the user, but tbis feels so radically useless compared to local filesystem access.

It would be be so much more powerful & useful a capability if your users could see the sqlite db, of whatever other store you have. It feels like such a Safari-ism that this great capability does nothing for the user, does not help the web export & share itself. It keeps the web & the system boxes fully isolated. Safari's murderous intent.

This is incorrect. The File System Access API 100% does contain a showOpenFilePicker API. As it says in the blog post, they’ve just begun this process by beginning with the private origin portion of the API, but they’ll be adding the other portions later.

It seems this is up to the implementation as the spec leaves it open. Safari is not mapping this to the actual file system, but what about other browsers?

For Origin Private File Systems, I would not expect any user agent to map things to the actual file system: there are notable functional problems especially on Windows, and it’s a very significant security hazard, all of which is neatly avoided by putting it all in the likes of a SQLite database.

First, the functional problems: file names are going to be a bit of a bother, to the point where if you back it by the actual file system you will require some kind of escaping mechanism, which loses you the exact one-to-one correspondence. Names are sequences of 16-bit code units, which means they need not be valid Unicode (ugh, I wish new additions to the browser platform would just start rejecting malformed Unicode), which will cause trouble on some platforms and will probably not be looked on favourably on others as having the potential to cause trouble in software not written to cope with that (e.g. on Windows I don’t think I’ve ever encountered a name with an unpaired surrogate, though it’s legal; on Linux, paths that aren’t UTF-8 are decidedly more common, though they still have a habit of breaking things). It also allows various file names that are forbidden in Windows at large, though in almost all cases you could with difficulty work around that on a per-process basis by throwing it into POSIX mode and/or using fully qualified (\\.\ or \\?\) paths.

Second, the security hazard: putting files with arbitrary names and contents on the file system is dangerous for a few reasons. The simplest is that there’s various buggy software that automatically reads files that get added to the file system, and more than a few times such software has had bugs in parsers that have led to privileged arbitrary code execution. A key purpose of Origin Private File Systems is that you don’t need to prompt the user for permission, because it’s supposed to be safe. I have no idea if this sort of vector was ever used back in the days when IE persisted its internet cache directly to files on the disk, but I can easily imagine such a thing happening now, and it could be really bad, given the right bug.

Great comment, this expands the issues really well for me.

I didn't notice before that this was supposed to be a safe api usable without permission, that indeed changes things.

Antivirus software often reads all files coming to the system, run with admin / kernel driver privileges, and still has lots of parsing and other vulnerabilities (project zero blog wrote about some of these). They tend to try to unwrap all container formats. I wonder if they'll start diving into SQLite files used by browsers and that way manage to expose their privileged attack surface to malicious web content.