Hacker News Reader

[I retract this comment entirely—see downthread where rewq4321 corrects me.]

> on top of the "coming soon" filesystem/block store api

I’m completely unimpressed by Origin Private File Systems, which I believe is what you’re talking about: it’s just a key-value store, just made to look a little like a file system, but is probably exactly equivalent to IndexedDB in capability and usefulness, quite easily perfectly polyfillable atop it. It is certainly completely unsuitable for building a database on top of as far as ACID transactions or such are concerned—you’ll get appalling write performance because you will have to close the file to commit each write.

I wrote more about this in the thread about Safari having implemented OPFS five months ago: https://news.ycombinator.com/item?id=30394737. (My use of “probably” above is explained in there too.)

Amusingly you where replying to me there, hello again!

I’m sure there are rough edges right now, but I’m complete convinced that even if the api isn’t there yet, this use case will win out and we will see it happen.

From memory the teams working on WASM SQLite are working with the File System API working group to ensure their use case is supported.

[I retract this comment entirely, apart from the pleasantries and the preference of being wrong—see downthread where rewq4321 corrects me.]

Heh, didn’t notice the username match there!

I don’t think “rough edges” is the right characterisation. What OPFS provides is just nothing in the direction required. The kind of file system you need to build a database on is a fundamentally completely different beast, with only unimportant surface-level similarities. It would generally require a complete replacement of the backend, with quite possibly literally no code in common.

I’d like something like this, because it’s certainly genuinely useful for cases like this, but I’d honestly be surprised if it ever happens, because it’s just… not webby. To be useful, it just about requires that the whole thing be backed by an actual file system and exposing that, which is something that has been assiduously avoided so far in the design, probably in significant part because it discloses quite a lot about the host system (fingerprinting; disk performance characteristics, quite possibly even file system identification by various nuances in behaviour; and surprisingly large side-channel attack possibilities, mildly similar to the fuss over high-resolution timers), but also because it tends to be a security hazard, just another moving part where things can go wrong more easily than you imagine.

I strongly suspect it will end up a bit like Web SQL: a nice idea that pretty much everyone agrees is a nice idea, but which is also a non-starter for other reasons.

But I wouldn’t mind being wrong. I do want to be able to deploy a robust, high-performing SQLite in the browser.

Are you sure you're up to date on the latest additions to this API? This wasn't in the MVP:

> The origin private file system provides optional access to a special kind of file that is highly optimized for performance, for example, by offering in-place and exclusive write access to a file's content.

https://web.dev/file-system-access/#accessing-files-optimize...

It was originally going to be a separate high-perf "Storage Foundation" API, but that was merged with the File System Access API.

Huh. Disregard what I wrote entirely, then. Reading through https://github.com/WICG/file-system-access/blob/main/AccessH..., I can see how they’ve bypassed most or all of the problems I saw—I was making unnecessary assumptions.

Thank you for correcting me. I am now enthusiastic about OPFS.

This has to be ruled out first: https://github.com/whatwg/fs/issues/7#issuecomment-116176851...

...but then the OPFS will be a quite decent fit. We (DuckDB-Wasm) are also looking closely at OPFS.

IMHO the requirement here is not even to get to full ACID.

With OPFS, we will get close enough to IndexedDB on steroids and bypassing the js heap limits through out-of-core operators.

After all, we are still running in a browser.

So I see the value of Wasm-based databases to be a front-facing accelerator, not a substitute for robust storage solutions.

Great to see this kind of pleasant discourse on HN!

Ah yes, can't wait to run my offline-first app on a kubernetes-on-docker-on-linux-on-qemu-on-wasm stack in the browser to have closer alignment between my server and browser implementations.

> Ah yes, can't wait to run my offline-first app on a kubernetes-on-docker-on-linux-on-qemu-on-wasm stack in the browser to have closer alignment between my server and browser implementations.

You can run vscode on docker on kubernetes on the browser with gitpod!

Yeah but can I host that Gitpod in Docker running on Kubernetes running on Docker on Linux on Qemu on Wasm as well?

I mean we did already ship the ability to run containers from inside Postgres so why not tackle every possible approach - https://www.crunchydata.com/blog/announcing-postgres-contain...

Speaking of making Postgres easy to setup and use, here are instructions for setting up per-project Postgres ("So I worked out how to set up a Postgres database that’s almost as convenient as SQLite."): https://jamey.thesharps.us/2019/05/29/per-project-postgres/

I think most people who use postgres in their personal projects have a snippet like one that they copy from project to project, I know I do. Curious to see others' scripts!

Mine logs in as `postgres`, creates the database and extensions I normally use, then creates a user and grants the appropriate privileges to that application user for the relevant db/schemas. Incidentally, it's the same script I use for "prod" deployments on my homelab.

FYI, not all drivers support unix sockets, like the official Java one.

> This is so awesome for two reasons, Postgress isn't exactly easy to setup

for tutorial level usage, "apt-get install postgresql-X" or installing Postgres.app has always worked perfectly. what sort of troubles do you run into?

Imagine wanting to learn Postgres but not knowing how to use a package manager, for example

Is this even a possibility?

1000%. Lots of people want to do data analysis, learn SQL, all that kind of stuff, without having to learn a load of other unrelated development crap. Think of all the people out there using Excel who want to take the next step.

After browsing this site for a couple of years, I made an account to reply to your comment and say, "This is exactly right" lol. I'm a teacher who uses Excel for basic stuff (power query, pivot tables), but I'm also using Excel as a "database", which is obviously not a good idea.

So here I am trying to take the next step and learn SQL along with good database design, but learning these things through Postgres is really not appropriate for someone like me. I think I have to swallow my pride and start with Access or something.

You may want to try https://dbeaver.io/ + SQLLight for the start.

You just download binary for your OS, create a db as described here (https://www.sqlite.org/quickstart.html) and then connect to it with dbeaver.

This is more the enough to get familiar with SQL.

No shame in using Access! It's actually part of how I got my start in tech. Started with an Access database, started writing macros for it in VBA, then started writing VB.NET, then convinced a higher up to give me access to a SQL Server database.

I was in an industry with an absurd number of boilerplate forms that needed to be printed and the ability to create Access forms that automated all the manual filling out coworkers were doing felt like magic. Postgres doesn't really have an equivalent of that.

Yes. See this survey on Twitter from the other day - LOADS of "React" developers (aka front-end developers) are deterred from learning more about backend programming due to friction like this. https://twitter.com/rachelnabors/status/1558888478955421697

LOADS of React "developers"

I still kind of lost here.

I know this much about frontend development too but I was sure that you still need a package manager to install your npm\yarn and other tools.

PS: Obviously you can live without one. Regardless of being backend\frontend developer.

PPS: and honestly, how can you be scared of postgresql and co. after webpack? If you can actually understand this crap postgres setup should be to easy for you.

> If you can actually understand this crap postgres setup should be to easy for you.

You can figure almost anything out if you spend enough time reading documentation and noodling with it. I'd still rather spend that time solving my actual problem.

Yeah I personally find Webpack a whole lot scarier than PostgreSQL / package managers, but that's down to my previous career experience.

Really the key thing here is that learning new things is hard, and anything that can be done to remove potential roadblocks is worthwhile. I've talked to so many people who were put off learning Python because they couldn't get to a working development environment on their own.

You don't really need webpack or etc for this, you can just hit some page that hosts the wasm already.

Just to be clear: I was not talking about the project at crunchydata.com. I merely say that in my opinion modern js development routine is much more complicated that postgres setup

People who come from Windows + MSSQL world might not have touched a package manager before and want to try out Postgres.

Honestly not my experience, if you have postgresql running locally on your laptop, I find upgrading versions is a pain, even with the automated scripts provided and I often run in socket issues: psql is already connected, not able to connect to this or that socket,...

So yeah, installing is easy and on a clean install it runs fine but it's not unusual to get pretty annoying issues over time in my experience.

But presumably none of those issues is fixed by using WASM in a browser.

They aren't but they probably won't appear user-side, the companies will most likely have to deal with their servers, fix those issues and the users can enjoy not thinking about that when they're trying PostgreSQL online.

I run into problems with the next steps: now you have to create a database, create a user, grant that user access to the database, setup credentials and start using them.

This isn't impossible - I can do it if I consult my notes - but it has enough steps where something might go wrong that it's a pretty high friction process for newcomers.

> I run into problems with the next steps: now you have to create a database, create a user, grant that user access to the database, setup credentials and start using them.

That's like complaining you need to break eggs when you want to make an omelette.

Being necessary and being exhausting aren't mutually exclusive qualities.

But - why are the steps needed to create a database and setup credentials such a complex, manual process anyway? Why can't all those steps just be automated by a helper script that ships with postgres?

I reach for sqlite whenever I introduce SQL to people because its so much easier to get started with sqlite.

The reason that I reach for MySQL when introducing people to SQL is the formatted output, with nice tables and column names. Is there a simple way to get similar output with SQLite?

Yep! Just set sqlite into table mode:

    sqlite> .mode table
    sqlite> select * from example;
    +-----+----------+
    | id  |   foo    |
    +-----+----------+
    | 123 | afsd     |
    +-----+----------+

SQLite also has CSV, JSON, HTML and markdown modes - which is pretty neat!

Thank you!

> That's like complaining you need to break eggs when you want to make an omelette.

I have a friend who we discovered didn't like cooking eggs because picking the shells out of the bowl was tedious. Turns out he was never taught how to crack an egg so he would just throw it in the bowl and have bits of shell everywhere. Point is that breaking eggs may not be so easy for everyone. (Of course, after describing to him how eggs are properly cracked, he was excited to try it out.)

> now you have to create a database, create a user, grant that user access to the database, setup credentials and start using them.

CREATE ROLE dumbo WITH LOGIN PASSWORD 'fuck';

CREATE DATABASE dumbo OWNER dumbo;

It's that easy.

Many developers don't run those commands very frequently, so they're easy to forget.

I only have to run these about once every year or two, and have to look them up every time.

You can do all this pretty easily using `pgAdmin` without having to remember any commands. If you do this infrequently, then great use the GUI. If you do it frequently then learn the commands I guess :P `pgAdmin` can help with that too though as it usually shows you the queries it is running.

$ docker run --name your-db-here -e POSTGRES_PASSWORD=pw -d postgres

Removing docker as a requirement is pretty huge.

Removing docker and also, enabling an entire sandbox environment by clicking a link in a browser.

Do you have a link pointing out the filesystem API actually being developed further? AFAIK that whole API is kind of stable and done but I might be wrong.

The other API worth knowing about is the more direct File System Access API, which is the one that allows direct access now: https://developer.mozilla.org/en-US/docs/Web/API/File_System...

wrt SQLite, icmyi: https://github.com/jlongster/absurd-sql

Since I use conda for everything anyway, I just end up doing a `conda install -p ./test postgres` when i want to install a throwaway postgres and then delete the environment later.

Sounds like we are poised to be rewarded with another 10 second boot markup for MS Teams

We don't need to wait for CloudFlare to ship their D2! Just plonk this on Firecracker and you are good to go.

For a database, it is pretty easy. I even compile it from source so that I can put it in my directory structure and have multiple concurrent versions.

This is a 15 minute job with coffee break while it compiles. And I don't even remember the whole thing by heart; I have to consult the docs.

I think WASM is very interesting and running Postgres in the browser is interesting, but for learning one could readily use something like db-fiddle, which offers Postgres.