This is so awesome for two reasons, Postgress isn't exactly easy to setup, this makes it so much easer for tutorials, anything that makes teaching it quicker is good!
It's also an amazing demonstration of the power of WASM, I am utterly convinced that DBs running in browser via WASM on top of the "coming soon" filesystem/block store api [0] will be the future of all offline first apps. I think it's going to be SQLite that really sines here (it will be a smaller package), but if you can run Postgres in browser, you potentially have a close alignment between your server and browser implementations.
I love the idea of running Django in browser via PyScript with Postgres!
0: https://developer.mozilla.org/en-US/docs/Web/API/FileSystem - This API doesn't grant access to the users file system, but a local virtual one just for the website. It will operate at the block level so can be used to provide efficient persistent storage for WASM DBs.
—
Edit rather than replying individually:
It’s so important when teaching people a new thing, like sql, to hit the ground running. A lot of these people will have no experience with Docker, package managers or compiling from source. It’s so important to make technology accessible to all. This does that! Imagine high school kids having a lesson on SQL and running Postgres’s just by opening a webpage!
We shouldn’t assume that someone learning Postgres, or just SQL via it, is a developer with experience of other areas of development or system administration.
> 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?
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.
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
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.
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
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.
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.
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.
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.
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.
[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.
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!
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.
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.
Craig here from Crunchy. Pretty excited to ship this. It started with one of our engineers showing up in slack 6 weeks ago "So I did something crazy over the weekend..." from there it evolved into much more.
The post explains a lot of the high level, but we're going to being doing some deeper dives as well including the build process, but also some of how the tutorials are powered by an internal notion doc which allows us to easily iterate and collaborate on the tutorials themselves.
Would love to see this! I could see a wasm version of postgres being useful for all sorts of things, especially an easy dev instance of pg runing inside a node app.
Maybe give it another shot or two, it's definitely a slow process to import. We have some improvements coming there in time but it's ideal at moment for smaller schemas and datasets.
This is great, I can't wait for someone to get python in the browser talking to postgres in the browser (and hopefully very soon afterward Django in the browser).
Maybe I missed it, but is the licensing info available anywhere? The playground seems to be using v86.js I don't see the required "Redistributions in binary form must reproduce the above copyright notice" -- https://github.com/copy/v86/blob/master/LICENSE
This is great. Have you done any benchmarking? I am currently looking to find/create good Wasm benchmarks, and it seems like this might be a good workload.
Any chance you can give us access to the PSQL sandbox without it being attached to a tutorial. Would both be a great way to learn and also function as a productivity tool if one could fool around with PSQL "scratch pad"
@Craig, I wonder if you've thought of making this available (would it work??) for test case construction. Today (working in Go) I start a postgres in a docker container for testing database code. Could I instead use a go wasm runtime, and start postgres inside of it? That would potentially free me from the docker dependency in these tests.
The communication in and out is a bit of extra work at the moment. We've got some thoughts on improving this in the future, but it's a bit unclear on timeline and roadmap. We're excited to hear what people want from it and how they use it and that'll inform a lot of how we move forward.
WASI is just a set of standardized interfaces that any WASM host can implement. So while there’s no WASI out of the box on browsers, there’s nothing preventing you as a developer from bringing your own implementation (in which, for example, writes to stdout could sent to console.log, and files could be backed by an in memory VFS).
> Due to browser sandboxing there is no way to connect directly to the Postgres instance beyond the embedded psql interface that we establish for you. The current configuration allocates 512MB of memory for your Postgres instance, we may make this more configurable in the future. It’s in the browser, hence if you refresh you’re going to get a fresh instance, we haven’t created any persistence layers (yet).
I'm creating tutorials where I try to let people learn as much as possible without the need to install anything and software in that spirit always makes me happy.
I don't think thats really Postgres running in the browser, it's more a "compatible" reimplementation of Postgres in TypeScript. Obviously impressive in its own right!
Would be nice to get Alpine or Debian compiled to WASM, that way it would just be another architecture to be run through Docker and we could get a lot of easy-to-run Docker images right in the browser. Is there any project working on this?
The problem is that it would be an actual new architecture (just as much as ARM or MIPS or whatever), which means you wouldn't have compatibility with any existing docker images.
Both? So for the tutorials they use a SQL initialization file stored in a GIST (our solution, but can be stored at any public storage url) and it loads that data and any other SQL setup commands in that file on startup to the database that exists in your local browser session only.
PostgreSQL's fame is kind of a paradox: it's so good because everyone uses PostgreSQL because it's so good. With the variety and scale of applications PostgreSQL is used for, it has organically evolved to be a swiss army knife of a database.
Basically PostgreSQL is to databases as Linux is to an OS.
Wasn't always this way...even not that long ago. Back when Heroku was getting serious about building up the Postgres service (I helped some...this is circa 2010) the common question was "that's weird, why not MySQL, isn't that how most Rails applications are run?" RDS only had a MySQL product for a very long time, and that followed from market demand. Tutorials for Rails were generally written with MySQL.
Hot standby (streaming replication) was a key feature to arrive around then. Also, I'd like to think a lot of the work Heroku did to engineer Heroku Postgres, and market it, contributed much better Postgres support in web frameworks and their affiliated ORMs in those critical years from 2010-2014, where encountering headwinds in defects in Postgres support for Rails, Node, etc was common.
When RDS came around with their Postgres offering, I'd say at that point, it could be said that Postgres entered a new stage: it was no longer reflexive for engineers to shrug their shoulders at shaky Postgres support in drivers/ORMs/etc like they did before that.
MySQL was the M in the LAMP stack (I’m this old, crap) but it had issues with downright brain dead behaviors and defaults which were only fixed multiple major versions later. In the meantime if you wanted to store what you asked to be stored without silent truncation etc you chose Postgres.
From the user's perspective, it's the little things. The feature set is overall bigger and better than comparable RDBMSs. Not having to deal with Oracle or Microsoft is also nice. But there's not a huge difference in usage vs MySQL.
Those familiar with the internals might say a lot more about how it's faster or something. IDK, wouldn't surprise me if it were.
I've been looking at wasm some. its totally unclear to me how this would support Postgres - which requires a posix filesystem interface, and the ability to fork children that operate on a shared memory segment. Were these facilities added to a wasp runtime, or was there major surgery on the Postgres codebase. I feel like I must be missing a simple answer.
Here's my similar project, which uses Python and SQLite compiled to WebAssembly in the browser so that it can run my Datasette server-side application entirely client-side:
The tutorials and interactive learning at SQL Tutorial are pretty good. Not Postgres (well, don't know anything about their implementation).
I've been having my kids go through it. They are learning a lot. I would recommend this site to anyone who needs to start with SQL from the ground-up and get a reasonable understanding of the language with practical hands-on usage.
172 comments
[ 0.16 ms ] story [ 230 ms ] threadIt's also an amazing demonstration of the power of WASM, I am utterly convinced that DBs running in browser via WASM on top of the "coming soon" filesystem/block store api [0] will be the future of all offline first apps. I think it's going to be SQLite that really sines here (it will be a smaller package), but if you can run Postgres in browser, you potentially have a close alignment between your server and browser implementations.
I love the idea of running Django in browser via PyScript with Postgres!
0: https://developer.mozilla.org/en-US/docs/Web/API/FileSystem - This API doesn't grant access to the users file system, but a local virtual one just for the website. It will operate at the block level so can be used to provide efficient persistent storage for WASM DBs.
—
Edit rather than replying individually:
It’s so important when teaching people a new thing, like sql, to hit the ground running. A lot of these people will have no experience with Docker, package managers or compiling from source. It’s so important to make technology accessible to all. This does that! Imagine high school kids having a lesson on SQL and running Postgres’s just by opening a webpage!
We shouldn’t assume that someone learning Postgres, or just SQL via it, is a developer with experience of other areas of development or system administration.
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?
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 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.
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.
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.
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.
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.
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.
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.
CREATE ROLE dumbo WITH LOGIN PASSWORD 'fuck';
CREATE DATABASE dumbo OWNER dumbo;
It's that easy.
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.
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.
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
> 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.)
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.
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.
> 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.
Thank you for correcting me. I am now enthusiastic about OPFS.
...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.
You can run vscode on docker on kubernetes on the browser with gitpod!
The post explains a lot of the high level, but we're going to being doing some deeper dives as well including the build process, but also some of how the tutorials are powered by an internal notion doc which allows us to easily iterate and collaborate on the tutorials themselves.
Perhaps our favorite easter egg is that you can bring your own SQL into it for example: https://www.crunchydata.com/developers/playground?sql=https:...
That is super awesome! I plan to use it to allow people to peruse our table structure easily.
Great job!
Any ideas?
https://www.guidingtech.com/fix-google-chrome-out-of-memory-...
Do you provide 3rd party hosting? Might consider replacing ours to something more flexible like yours in the future.
This is very cool but I wouldn't call it an easter egg, I'd just call it a feature!
Can WASI use memory as a virtual filesystem?
See this article, where the mention (and link to) polyfill on browsers: https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webas...
It's also possible (on Google Chrome) to use browser API's to directly access your host computer's filesystem (see https://developer.mozilla.org/en-US/docs/Web/API/File_System...).
> Due to browser sandboxing there is no way to connect directly to the Postgres instance beyond the embedded psql interface that we establish for you. The current configuration allocates 512MB of memory for your Postgres instance, we may make this more configurable in the future. It’s in the browser, hence if you refresh you’re going to get a fresh instance, we haven’t created any persistence layers (yet).
https://www.youtube.com/watch?v=Ryj5c8zLF50
I'm creating tutorials where I try to let people learn as much as possible without the need to install anything and software in that spirit always makes me happy.
Ideally something that goes from installation from the official deb repositories to a knowledgeable (junior?) dba.
This is not the first time someone has gotten PG running in a browser though. Here's another approach:
https://github.com/oguimbal/pg-mem
[1] https://www.crunchydata.com/playground
Well that's embarrassing, looks like one of our underlying APIs hit a rate limit, we're working on a quick fix for it.
Take a look at this project: https://webvm.io/ - explained here: https://leaningtech.com/webvm-server-less-x86-virtual-machin...
I guess for that one would need to implement all Linux syscalls in WASM
I'm partial to MS SQL Server myself.
So many rave about Postgre. Isn't it just another RDBMS?
Basically PostgreSQL is to databases as Linux is to an OS.
Is it really a circular relationship if the starting premise is "people use it because it's good"?
Hot standby (streaming replication) was a key feature to arrive around then. Also, I'd like to think a lot of the work Heroku did to engineer Heroku Postgres, and market it, contributed much better Postgres support in web frameworks and their affiliated ORMs in those critical years from 2010-2014, where encountering headwinds in defects in Postgres support for Rails, Node, etc was common.
When RDS came around with their Postgres offering, I'd say at that point, it could be said that Postgres entered a new stage: it was no longer reflexive for engineers to shrug their shoulders at shaky Postgres support in drivers/ORMs/etc like they did before that.
Those familiar with the internals might say a lot more about how it's faster or something. IDK, wouldn't surprise me if it were.
https://github.com/WebAssembly/threads/blob/main/proposals/t...
Full disclosure: I worked on it.
https://lite.datasette.io/
More about that project here: https://simonwillison.net/series/datasette-lite/
I just added support for installing additional plugins written in Python this morning: https://simonwillison.net/2022/Aug/17/datasette-lite-plugins...
I've been having my kids go through it. They are learning a lot. I would recommend this site to anyone who needs to start with SQL from the ground-up and get a reasonable understanding of the language with practical hands-on usage.
https://www.sqltutorial.org/
A good first step before jumping into CrunchyData?