15 ms·

Learn Postgres at the Playground – Postgres compiled to WASM running in browser

814 points by samwillis 4y ago

brap 4y ago
The playground keeps crashing for me (Safari on iOS), can’t spend more than 2 seconds on the page without a crash
- bluesign 4y ago
  Mobile wasm memory management is very hard. I think max is like 300-400mb on mobile safari.
deleted 4y ago
[deleted]
hauxir 4y ago
Incredible. Could this be used to host a local instance using webrtc?
ogazitt 4y ago
That is super cool! Really curious to hear how it's built, how much runs in wasm, how you got (some portions of) postgres to compile to wasm, etc.
samwillis 4y ago
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 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.
- sigstoat 4y ago
  > 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?
  - zachrose 4y ago
    Imagine wanting to learn Postgres but not knowing how to use a package manager, for example
    - konart 4y ago
      Is this even a possibility?
      - afavour 4y ago
        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.
        l2k34j1l2k34jj1 4y ago
        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.
        konart 4y ago
        You may want to try https://dbeaver.io/ 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 https://www.sqlite.org/quickstart.html) and then connect to it with dbeaver. This is more the enough to get familiar with SQL.
emptysea 4y ago
Interesting, only about 36MB for the wasm postgres
- nathancahill 4y ago
  That's amazing.
craigkerstiens 4y ago
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. 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://gist.githubusercontent.com/eccentric-j/11cce77331a82bcab52563d1f63a9c46/raw/create-births-table.sql https://www.crunchydata.com/developers/playground?sql=https:...
- xfalcox 4y ago
  > Perhaps our favorite easter egg is that you can bring your own SQL into it for example That is super awesome! I plan to use it to allow people to peruse our table structure easily.
- motleydev 4y ago
  That's a heck of an easter egg. It's like the TS playground feature, letting people explore exactly what you mean with a bug, etc. Kudos.
- tofuahdude 4y ago
  Pretty cool project!
- Mo3 4y ago
  This is a really cool tool. Nice work
- thesausageking 4y ago
  This is awesome. Can you talk about how was it implemented? Is it actually a port of the postgres code that's been compiled to WASM?
  - craigkerstiens 4y ago
gavinray 4y ago
How does this work if there's no physical storage? Can WASI use memory as a virtual filesystem?
- shadowofneptune 4y ago
  There is no WASI in browsers, but yes you can use filesystems in main memory. It's called a 'RAM disk.'
  - cstrahan 4y ago
    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). See this article, where the mention (and link to) polyfill on browsers: https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webassembly-system-interface/ https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webas...
- williamstein 4y ago
  You can use anything you can use from Javascript. E.g., https://www.npmjs.com/package/memfs https://www.npmjs.com/package/memfs can be used with https://www.npmjs.com/package/@wapython/wasi https://www.npmjs.com/package/@wapython/wasi to provide a ramdisk, and similarly you can make it persist to indexdb or localStorage. This sort of thing is also built into emscripten. You can see this in action at https://jupyter.org/try-jupyter/lab/ https://jupyter.org/try-jupyter/lab/ 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_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System...).
- e12e 4y ago
  From tfa: > 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).
motleydev 4y ago
I was fortunate enough to get to interview Craig Kerstiens about this launch as we talked all about vanilla Postgres. You can see a demo of that here. https://www.youtube.com/watch?v=Ryj5c8zLF50 https://www.youtube.com/watch?v=Ryj5c8zLF50
- motleydev 4y ago
  Demo part of the premier starting now.
- BMB74 4y ago
  I really liked the playground in action. thanks for sharing.
k__ 4y ago
Pretty awesome. 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.
znpy 4y ago
A bit OT, but since we’re talking postgresql, can anyone recommend a good course (or book) about postgresql server administration? Ideally something that goes from installation from the official deb repositories to a knowledgeable (junior?) dba.
- tomrod 4y ago
  Postgres docs and several recent items here talking about "postgres internals" will probably give you a lot of great starting points.
- plaur782 4y ago
  A number of good options. Different folks have their favorites: https://www.postgresql.org/docs/books/ https://www.postgresql.org/docs/books/
- hot_gril 4y ago
  The basic https://www.postgresql.org/docs/current/index.html https://www.postgresql.org/docs/current/index.html is where I learned everything.
pramsey 4y ago
Also, you can `CREATE EXTENSION postgis` on it, and all that works too!
matrix 4y ago
Hats off to the engineers who pulled this off. 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 https://github.com/oguimbal/pg-mem
- samwillis 4y ago
  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!
scythmic_waves 4y ago
I was messing with the tutorials in the playground earlier but I'm getting a 404 now [1]. [1] https://www.crunchydata.com/playground https://www.crunchydata.com/playground
- craigkerstiens 4y ago
  Edit: Should be all better now. Well that's embarrassing, looks like one of our underlying APIs hit a rate limit, we're working on a quick fix for it.
  - scythmic_waves 4y ago
    Seems to be working now! Thanks!
- motleydev 4y ago
  Demo is playing here https://www.youtube.com/watch?v=Ryj5c8zLF50 https://www.youtube.com/watch?v=Ryj5c8zLF50
phased20 4y ago
Looks very cool! Getting a 404 error at the link though (as of 10:30am PT anyway): https://www.crunchydata.com/developers/tutorials https://www.crunchydata.com/developers/tutorials
- wking 4y ago
  Should be fixed now! We hit a rate limit a lot sooner than expected haha :)
sprite 4y ago
I'm getting 404 on the Playground link. Do I need to be signed in or something?
- craigkerstiens 4y ago
  Sorry about that, we hit some rate-limits but should be all resolved now.
doliveira 4y ago
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?
- simonw 4y ago
  That already exists. In fact the Crunchy Data PostgreSQL playground loads a full Linux VM as part of how it works. Take a look at this project: https://webvm.io/ https://webvm.io/ - explained here: https://leaningtech.com/webvm-server-less-x86-virtual-machines-in-the-browser/ https://leaningtech.com/webvm-server-less-x86-virtual-machin...
  - doliveira 4y ago
    But you wouldn't need to emulate x86 itself, the binaries would just compile to WASM like they do for ARM. I guess for that one would need to implement all Linux syscalls in WASM
- yjftsjthsd-h 4y ago
  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.
NeutralForest 4y ago
That's pretty insane, what does that mean in terms of data storage? Does everyone get a copy of the data locally or is it fetched on the fly?
- wking 4y ago
  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.
mutagen 4y ago
Supabase launch week just got sniped. j/k I have no idea what they have for the rest of the week and someone already referenced Django in the browser.
prpl 4y ago
maybe we can finally bring back websql
ijidak 4y ago
Question. Why are people so positive on Postgre versus MySQL? I'm partial to MS SQL Server myself. So many rave about Postgre. Isn't it just another RDBMS?
- candiddevmike 4y ago
  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.
  - hombre_fatal 4y ago
    > it's so good because everyone uses PostgreSQL because it's so good Is it really a circular relationship if the starting premise is "people use it because it's good"?
  - fdr 4y ago
    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.
  - motleydev 4y ago
    We talked about it in this video, about how it's grown the way it has. https://www.youtube.com/watch?v=Ryj5c8zLF50 https://www.youtube.com/watch?v=Ryj5c8zLF50
convolvatron 4y ago
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.
- fdr 4y ago
  All of Linux is in there, too, I think. Postgres-on-Linux-in-WASM
  - merb 4y ago
    yeah they download v86.wasm which is prolly https://github.com/copy/v86/ https://github.com/copy/v86/
- 15155 4y ago
  > Were these facilities added to a wasp runtime, https://github.com/WebAssembly/threads/blob/main/proposals/threads/Overview.md https://github.com/WebAssembly/threads/blob/main/proposals/t...
neerajdotname2 4y ago
On a different note we built https://academy.bigbinary.com/learn-sql https://academy.bigbinary.com/learn-sql using https://sql.js.org/#/ https://sql.js.org/#/ so that folks could learn SQL in the browser without installing anything. Full disclosure: I worked on it.
- no-reply 4y ago
  That is a fine course.
simonw 4y ago
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: https://lite.datasette.io/ https://lite.datasette.io/ More about that project here: https://simonwillison.net/series/datasette-lite/ 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/ https://simonwillison.net/2022/Aug/17/datasette-lite-plugins...
robomartin 4y ago
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. https://www.sqltutorial.org/ https://www.sqltutorial.org/ A good first step before jumping into CrunchyData?
wkoszek 4y ago
Doesn't work. Page not found
nro337 4y ago
This is really neat, great work!
pistoriusp 4y ago
If anyone out there wants to work on an open source version of this full-time please reach out to me.
mishraprince 4y ago
Gary Bernhardt wasn't all that wrong when he mentioned anything that can be written in JavaScript will be written in JavaScript. This post indeed reminded me of his talk https://www.destroyallsoftware.com/talks/the-birth-and-death-of-javascript https://www.destroyallsoftware.com/talks/the-birth-and-death...
- capableweb 4y ago
  Bypassing the idea that this thing is not run via JavaScript but WASM, he was definitely right :)
  - robocat 4y ago
    Use wasm2js to cross-compile WASM to pure JavaScript: https://www.google.co.nz/search?q=%22wasm2js%22 https://www.google.co.nz/search?q=%22wasm2js%22 and there are other ways to not require native WASM!
- kretaceous 4y ago
  I think it was by Jeff Atwood and hence is called Atwood's Law.
  - mishraprince 4y ago
    Aah, yes. My bad.
- jillesvangurp 4y ago
  The only thing he missed is that javascript as a compilation target makes less sense than a byte code format designed for such a thing, which is what WASM is. WASM is more similar to JVM bytecode than it is to Javascript. It seems we have a few implementations of WASM that for historical reasons share a code base with a javascript interpreter. But technically that interpreter is not really needed. You can actually use WASM to run your own javascript interpreter and people are already doing that to not be dependent on the interpreter that comes with the browser or wasm runtime (outside the browser). If you are going to run node.js in a wasm runtime, that's what you might want to do. Likewise, if you want to offer a browser IDE for a node.js project, you might want to run node.js in a browser and this is probably what you'd be doing rather than passing through the javascript to the browser javascript interpreter, which lacks most of the node.js API. Just easier that way. Likewise if you want to run some old internet explorer 10 javascript, packaging up on old version of that as wasm might allow you to do that. The hard part of course would be getting your hands on the source code. But MS might help us out here or somebody might implement something compatible. Very much like is being done with flash.
pdntspa 4y ago
> Postgres in a browser?!?! I cannot wait for the novelty of this to wear off, because the wow factor of "do it, but in a brower" should have a worn off long-ass time ago.
- capableweb 4y ago
  In the spirit of hacking, I hope it doesn't. Running A on B when B wasn't intended to run on A definitely piques my interest. I'm continuously impressed by such things, like Doom running on calculators.
  - pdntspa 4y ago
    I get that, but these are runtime platforms designed specifically to get general-purpose apps running in the browser. That is their sole reason for existence. Yes, WASM is awesome like that. But we've been messing with stuff like that since enscripten, if not earlier. Besides, postgres already runs on pretty much everything.
    - bastawhiz 4y ago
      Could I brew install Postgres to play with some SQL and clean up after myself when I'm done? Yes. I could also just load this, perhaps in multiple tabs, and not think about it.
      - pdntspa 4y ago
        I'd be impressed if they fit it into a dll with the databases as one file, a la SQLite Now that would be an achievement!
- hot_gril 4y ago
  There are still a lot of things that can't be done in browsers but should be doable, so I'm ok with this. Like imagine that instead of having to make a native iPhone app for every little thing, which ironically is probably JS under React Native anyway, Apple supported most of those native features via the browser. Of course Apple has much more to lose than to gain from that. They deliberately don't even support push notifications in PWAs. Also, Postgres in browser is actually useful.
MR4D 4y ago
There are so many things wrong with running an enterprise class database in a browser that I don't even know where to begin! BUT IT'S FREAKING AWESOME! Seriously, do you ever imagine Oracle or DB2 running in a browser? Crazy, right? Congrats to the team. To me this is one of the great things about the times we are living in - tons of computing horsepower for cheap, open source software, new-ish technology (WASM), and one crazy idea. Well, at least I know where all my spare time is going to be spent...
ahachete 4y ago
This is a fantastic idea, truly innovating what you can do with Postgres. Kudos! Postgres, possibly surprising to many, is very "simple": it has essentially no dependencies other than a few OS system calls (open, read, write files) and some optional dependencies (e.g. libssl). Therefore, it is very portable and "easy" to compile on many environments. This includes new environments or ideas like compiling it to WASM. But you need to come up with the idea. This is a great one and opens the door to other use cases. I hope this serves to push the mindset that Postgres can also be used in lighter-weight environments where SQLite (another fantastic database, don't get me wrong) is often considered as the only viable choice. edit: typo
farmin 4y ago
Great achievement! Now please PostGIS in the browser would make this incredible. Turfjs is a bit limited for Ionic and electron apps, imagine PostGIS in the browser. When I see 'in the browser' I read 'in an easy build and ship app'.
- nerpderp82 4y ago
  They are really fast with requests! https://www.crunchydata.com/developers/playground/basics-of-postgis https://www.crunchydata.com/developers/playground/basics-of-...
  - farmin 4y ago
    Oh I have no doubt, but I am thinking of niche use cases in a offline setting.
riston 4y ago
Interesting news, I hope to see the WASM based postgres for dev machine integration tests definitely would make things easier then the current setup through Docker based containers.
nikita 4y ago
Can this become an embedded database? I noticed that the load time is quite high. That's prob due to the size of the binary. I think the size can be dramatically reduced if you remove stuff that is not practical in the browser.
Humphrey 4y ago
I love that you did such an audacious thing. FYI, if you're on MacOS, someone has packaged Postgres into a standard "just works" self contained MacOS app. With a GUI & system tray menu to control it. It's so good that I use it instead of a Docker image for PG. All the psql and pg_restore commands are contained in the .app package and can be called from the terminal. https://postgresapp.com/ https://postgresapp.com/
panny 4y ago
Will this work with manifest v3? (Does it need a worker thread like absurd-sql and thus doesn't work with mv3?) If it does work, is there a repo or writeup on how to DIY this?
adam_gyroscope 4y ago
Love this! I think it's time for the database to move closer to the browser. We spend some time getting the node-postgres library working with websockets so we could go browser->websockify->postgres: https://github.com/bitdotioinc/node-postgres https://github.com/bitdotioinc/node-postgres - this lets us use a full-featured postgres client (with, eg, cursor support) in the browser.
gelatocar 4y ago
This is really cool, one nitpick on the basics tutorial though. It could be confusing for beginners to write `select distinct(event_type)` because it makes it look like `distinct` is a function, and makes it looks like distinct only applies to certain columns. I've seen postgres beginners confused by syntax like this writing queries like `select distinct(event_type), magnitude` and expecting the query to only do a distinct on `event_type`.
nl 4y ago
See also https://github.com/simonw/datasette-lite https://github.com/simonw/datasette-lite: Datasette running in your browser using WebAssembly and Pyodide
teaearlgraycold 4y ago
Waiting for this to be posted in response to noobs asking how to connect to their database from their web page.
etaioinshrdlu 4y ago
I unironically want to use Postgres WASM and much other software in production. I want the whole stack to be WASM. It would provide a lot of interesting advantages for deployment. Just like the JVM, WASM being relatively limited allows you to assume more about the program you are deploying.
jppope 4y ago
really an amazing project. Things like this are the reason I come to HN
fulafel 4y ago
I seem to remember that Postgres uses SysV shared memory between the master and connection processes - how does this end up working with WebAssembly, is there a build option to use threads for shared memory instead or...?
- fulafel 4y ago
  Answering myself: this is using a x86 emulator apparently, not runnign PG natively in WebAssembly (from https://news.ycombinator.com/item?id=32502781 https://news.ycombinator.com/item?id=32502781)
kespindler 4y ago
Do you have plans to open source any of the work? Would love to use this for a course/training platform.
d0100 4y ago
Could this be compiled into a C lib and then we get in-memory postgres for testing?
ThinkBeat 4y ago
What we need is Kubernetes running in the browser to orchestrate web (WASM) applications and infrastructure it takes to render web 4.0 sites and all the different blockchains that it will require.

814 points by samwillis 4y ago

brap 4y ago

The playground keeps crashing for me (Safari on iOS), can’t spend more than 2 seconds on the page without a crash

bluesign 4y ago
Mobile wasm memory management is very hard. I think max is like 300-400mb on mobile safari.

deleted 4y ago

[deleted]

hauxir 4y ago

Incredible. Could this be used to host a local instance using webrtc?

ogazitt 4y ago

That is super cool! Really curious to hear how it's built, how much runs in wasm, how you got (some portions of) postgres to compile to wasm, etc.

samwillis 4y ago

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 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.

sigstoat 4y ago
> 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?
- zachrose 4y ago
  Imagine wanting to learn Postgres but not knowing how to use a package manager, for example
  - konart 4y ago
    Is this even a possibility?
    - afavour 4y ago
      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.
      - l2k34j1l2k34jj1 4y ago
        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.
        konart 4y ago
        You may want to try https://dbeaver.io/ 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 https://www.sqlite.org/quickstart.html) and then connect to it with dbeaver. This is more the enough to get familiar with SQL.

emptysea 4y ago

Interesting, only about 36MB for the wasm postgres

nathancahill 4y ago
That's amazing.

craigkerstiens 4y ago

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. 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://gist.githubusercontent.com/eccentric-j/11cce77331a82bcab52563d1f63a9c46/raw/create-births-table.sql https://www.crunchydata.com/developers/playground?sql=https:...

xfalcox 4y ago
> Perhaps our favorite easter egg is that you can bring your own SQL into it for example That is super awesome! I plan to use it to allow people to peruse our table structure easily.
motleydev 4y ago
That's a heck of an easter egg. It's like the TS playground feature, letting people explore exactly what you mean with a bug, etc. Kudos.
tofuahdude 4y ago
Pretty cool project!
Mo3 4y ago
This is a really cool tool. Nice work
thesausageking 4y ago
This is awesome. Can you talk about how was it implemented? Is it actually a port of the postgres code that's been compiled to WASM?
- craigkerstiens 4y ago

gavinray 4y ago

How does this work if there's no physical storage? Can WASI use memory as a virtual filesystem?

shadowofneptune 4y ago
There is no WASI in browsers, but yes you can use filesystems in main memory. It's called a 'RAM disk.'
- cstrahan 4y ago
  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). See this article, where the mention (and link to) polyfill on browsers: https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webassembly-system-interface/ https://hacks.mozilla.org/2019/03/standardizing-wasi-a-webas...
williamstein 4y ago
You can use anything you can use from Javascript. E.g., https://www.npmjs.com/package/memfs https://www.npmjs.com/package/memfs can be used with https://www.npmjs.com/package/@wapython/wasi https://www.npmjs.com/package/@wapython/wasi to provide a ramdisk, and similarly you can make it persist to indexdb or localStorage. This sort of thing is also built into emscripten. You can see this in action at https://jupyter.org/try-jupyter/lab/ https://jupyter.org/try-jupyter/lab/ 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_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System...).
e12e 4y ago
From tfa: > 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).

motleydev 4y ago

I was fortunate enough to get to interview Craig Kerstiens about this launch as we talked all about vanilla Postgres. You can see a demo of that here. https://www.youtube.com/watch?v=Ryj5c8zLF50 https://www.youtube.com/watch?v=Ryj5c8zLF50

motleydev 4y ago
Demo part of the premier starting now.
BMB74 4y ago
I really liked the playground in action. thanks for sharing.

k__ 4y ago

Pretty awesome. 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.

znpy 4y ago

A bit OT, but since we’re talking postgresql, can anyone recommend a good course (or book) about postgresql server administration? Ideally something that goes from installation from the official deb repositories to a knowledgeable (junior?) dba.

tomrod 4y ago
Postgres docs and several recent items here talking about "postgres internals" will probably give you a lot of great starting points.
plaur782 4y ago
A number of good options. Different folks have their favorites: https://www.postgresql.org/docs/books/ https://www.postgresql.org/docs/books/
hot_gril 4y ago
The basic https://www.postgresql.org/docs/current/index.html https://www.postgresql.org/docs/current/index.html is where I learned everything.

pramsey 4y ago

Also, you can `CREATE EXTENSION postgis` on it, and all that works too!

matrix 4y ago

Hats off to the engineers who pulled this off. 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 https://github.com/oguimbal/pg-mem

samwillis 4y ago
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!

scythmic_waves 4y ago

I was messing with the tutorials in the playground earlier but I'm getting a 404 now [1]. [1] https://www.crunchydata.com/playground https://www.crunchydata.com/playground

craigkerstiens 4y ago
Edit: Should be all better now. Well that's embarrassing, looks like one of our underlying APIs hit a rate limit, we're working on a quick fix for it.
- scythmic_waves 4y ago
  Seems to be working now! Thanks!
motleydev 4y ago
Demo is playing here https://www.youtube.com/watch?v=Ryj5c8zLF50 https://www.youtube.com/watch?v=Ryj5c8zLF50

phased20 4y ago

Looks very cool! Getting a 404 error at the link though (as of 10:30am PT anyway): https://www.crunchydata.com/developers/tutorials https://www.crunchydata.com/developers/tutorials

wking 4y ago
Should be fixed now! We hit a rate limit a lot sooner than expected haha :)

sprite 4y ago

I'm getting 404 on the Playground link. Do I need to be signed in or something?

craigkerstiens 4y ago
Sorry about that, we hit some rate-limits but should be all resolved now.

doliveira 4y ago

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?

simonw 4y ago
That already exists. In fact the Crunchy Data PostgreSQL playground loads a full Linux VM as part of how it works. Take a look at this project: https://webvm.io/ https://webvm.io/ - explained here: https://leaningtech.com/webvm-server-less-x86-virtual-machines-in-the-browser/ https://leaningtech.com/webvm-server-less-x86-virtual-machin...
- doliveira 4y ago
  But you wouldn't need to emulate x86 itself, the binaries would just compile to WASM like they do for ARM. I guess for that one would need to implement all Linux syscalls in WASM
yjftsjthsd-h 4y ago
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.

NeutralForest 4y ago

That's pretty insane, what does that mean in terms of data storage? Does everyone get a copy of the data locally or is it fetched on the fly?

wking 4y ago
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.

mutagen 4y ago

Supabase launch week just got sniped. j/k I have no idea what they have for the rest of the week and someone already referenced Django in the browser.

prpl 4y ago

maybe we can finally bring back websql

ijidak 4y ago

Question. Why are people so positive on Postgre versus MySQL? I'm partial to MS SQL Server myself. So many rave about Postgre. Isn't it just another RDBMS?

candiddevmike 4y ago
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.
- hombre_fatal 4y ago
  > it's so good because everyone uses PostgreSQL because it's so good Is it really a circular relationship if the starting premise is "people use it because it's good"?
- fdr 4y ago
  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.
- motleydev 4y ago
  We talked about it in this video, about how it's grown the way it has. https://www.youtube.com/watch?v=Ryj5c8zLF50 https://www.youtube.com/watch?v=Ryj5c8zLF50

convolvatron 4y ago

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.

fdr 4y ago
All of Linux is in there, too, I think. Postgres-on-Linux-in-WASM
- merb 4y ago
  yeah they download v86.wasm which is prolly https://github.com/copy/v86/ https://github.com/copy/v86/
15155 4y ago
> Were these facilities added to a wasp runtime, https://github.com/WebAssembly/threads/blob/main/proposals/threads/Overview.md https://github.com/WebAssembly/threads/blob/main/proposals/t...

neerajdotname2 4y ago

On a different note we built https://academy.bigbinary.com/learn-sql https://academy.bigbinary.com/learn-sql using https://sql.js.org/#/ https://sql.js.org/#/ so that folks could learn SQL in the browser without installing anything. Full disclosure: I worked on it.

no-reply 4y ago
That is a fine course.

simonw 4y ago

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: https://lite.datasette.io/ https://lite.datasette.io/ More about that project here: https://simonwillison.net/series/datasette-lite/ 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/ https://simonwillison.net/2022/Aug/17/datasette-lite-plugins...

robomartin 4y ago

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. https://www.sqltutorial.org/ https://www.sqltutorial.org/ A good first step before jumping into CrunchyData?

wkoszek 4y ago

Doesn't work. Page not found

nro337 4y ago

This is really neat, great work!

pistoriusp 4y ago

If anyone out there wants to work on an open source version of this full-time please reach out to me.

mishraprince 4y ago

Gary Bernhardt wasn't all that wrong when he mentioned anything that can be written in JavaScript will be written in JavaScript. This post indeed reminded me of his talk https://www.destroyallsoftware.com/talks/the-birth-and-death-of-javascript https://www.destroyallsoftware.com/talks/the-birth-and-death...

capableweb 4y ago
Bypassing the idea that this thing is not run via JavaScript but WASM, he was definitely right :)
- robocat 4y ago
  Use wasm2js to cross-compile WASM to pure JavaScript: https://www.google.co.nz/search?q=%22wasm2js%22 https://www.google.co.nz/search?q=%22wasm2js%22 and there are other ways to not require native WASM!
kretaceous 4y ago
I think it was by Jeff Atwood and hence is called Atwood's Law.
- mishraprince 4y ago
  Aah, yes. My bad.
jillesvangurp 4y ago
The only thing he missed is that javascript as a compilation target makes less sense than a byte code format designed for such a thing, which is what WASM is. WASM is more similar to JVM bytecode than it is to Javascript. It seems we have a few implementations of WASM that for historical reasons share a code base with a javascript interpreter. But technically that interpreter is not really needed. You can actually use WASM to run your own javascript interpreter and people are already doing that to not be dependent on the interpreter that comes with the browser or wasm runtime (outside the browser). If you are going to run node.js in a wasm runtime, that's what you might want to do. Likewise, if you want to offer a browser IDE for a node.js project, you might want to run node.js in a browser and this is probably what you'd be doing rather than passing through the javascript to the browser javascript interpreter, which lacks most of the node.js API. Just easier that way. Likewise if you want to run some old internet explorer 10 javascript, packaging up on old version of that as wasm might allow you to do that. The hard part of course would be getting your hands on the source code. But MS might help us out here or somebody might implement something compatible. Very much like is being done with flash.

pdntspa 4y ago

> Postgres in a browser?!?! I cannot wait for the novelty of this to wear off, because the wow factor of "do it, but in a brower" should have a worn off long-ass time ago.

capableweb 4y ago
In the spirit of hacking, I hope it doesn't. Running A on B when B wasn't intended to run on A definitely piques my interest. I'm continuously impressed by such things, like Doom running on calculators.
- pdntspa 4y ago
  I get that, but these are runtime platforms designed specifically to get general-purpose apps running in the browser. That is their sole reason for existence. Yes, WASM is awesome like that. But we've been messing with stuff like that since enscripten, if not earlier. Besides, postgres already runs on pretty much everything.
  - bastawhiz 4y ago
    Could I brew install Postgres to play with some SQL and clean up after myself when I'm done? Yes. I could also just load this, perhaps in multiple tabs, and not think about it.
    - pdntspa 4y ago
      I'd be impressed if they fit it into a dll with the databases as one file, a la SQLite Now that would be an achievement!
hot_gril 4y ago
There are still a lot of things that can't be done in browsers but should be doable, so I'm ok with this. Like imagine that instead of having to make a native iPhone app for every little thing, which ironically is probably JS under React Native anyway, Apple supported most of those native features via the browser. Of course Apple has much more to lose than to gain from that. They deliberately don't even support push notifications in PWAs. Also, Postgres in browser is actually useful.

MR4D 4y ago

There are so many things wrong with running an enterprise class database in a browser that I don't even know where to begin! BUT IT'S FREAKING AWESOME! Seriously, do you ever imagine Oracle or DB2 running in a browser? Crazy, right? Congrats to the team. To me this is one of the great things about the times we are living in - tons of computing horsepower for cheap, open source software, new-ish technology (WASM), and one crazy idea. Well, at least I know where all my spare time is going to be spent...

ahachete 4y ago

This is a fantastic idea, truly innovating what you can do with Postgres. Kudos! Postgres, possibly surprising to many, is very "simple": it has essentially no dependencies other than a few OS system calls (open, read, write files) and some optional dependencies (e.g. libssl). Therefore, it is very portable and "easy" to compile on many environments. This includes new environments or ideas like compiling it to WASM. But you need to come up with the idea. This is a great one and opens the door to other use cases. I hope this serves to push the mindset that Postgres can also be used in lighter-weight environments where SQLite (another fantastic database, don't get me wrong) is often considered as the only viable choice. edit: typo

farmin 4y ago

Great achievement! Now please PostGIS in the browser would make this incredible. Turfjs is a bit limited for Ionic and electron apps, imagine PostGIS in the browser. When I see 'in the browser' I read 'in an easy build and ship app'.

nerpderp82 4y ago
They are really fast with requests! https://www.crunchydata.com/developers/playground/basics-of-postgis https://www.crunchydata.com/developers/playground/basics-of-...
- farmin 4y ago
  Oh I have no doubt, but I am thinking of niche use cases in a offline setting.

riston 4y ago

Interesting news, I hope to see the WASM based postgres for dev machine integration tests definitely would make things easier then the current setup through Docker based containers.

nikita 4y ago

Can this become an embedded database? I noticed that the load time is quite high. That's prob due to the size of the binary. I think the size can be dramatically reduced if you remove stuff that is not practical in the browser.

Humphrey 4y ago

I love that you did such an audacious thing. FYI, if you're on MacOS, someone has packaged Postgres into a standard "just works" self contained MacOS app. With a GUI & system tray menu to control it. It's so good that I use it instead of a Docker image for PG. All the psql and pg_restore commands are contained in the .app package and can be called from the terminal. https://postgresapp.com/ https://postgresapp.com/

panny 4y ago

Will this work with manifest v3? (Does it need a worker thread like absurd-sql and thus doesn't work with mv3?) If it does work, is there a repo or writeup on how to DIY this?

adam_gyroscope 4y ago

Love this! I think it's time for the database to move closer to the browser. We spend some time getting the node-postgres library working with websockets so we could go browser->websockify->postgres: https://github.com/bitdotioinc/node-postgres https://github.com/bitdotioinc/node-postgres - this lets us use a full-featured postgres client (with, eg, cursor support) in the browser.

gelatocar 4y ago

This is really cool, one nitpick on the basics tutorial though. It could be confusing for beginners to write `select distinct(event_type)` because it makes it look like `distinct` is a function, and makes it looks like distinct only applies to certain columns. I've seen postgres beginners confused by syntax like this writing queries like `select distinct(event_type), magnitude` and expecting the query to only do a distinct on `event_type`.

nl 4y ago

See also https://github.com/simonw/datasette-lite https://github.com/simonw/datasette-lite: Datasette running in your browser using WebAssembly and Pyodide

teaearlgraycold 4y ago

Waiting for this to be posted in response to noobs asking how to connect to their database from their web page.

etaioinshrdlu 4y ago

I unironically want to use Postgres WASM and much other software in production. I want the whole stack to be WASM. It would provide a lot of interesting advantages for deployment. Just like the JVM, WASM being relatively limited allows you to assume more about the program you are deploying.

jppope 4y ago

really an amazing project. Things like this are the reason I come to HN

fulafel 4y ago

I seem to remember that Postgres uses SysV shared memory between the master and connection processes - how does this end up working with WebAssembly, is there a build option to use threads for shared memory instead or...?

fulafel 4y ago
Answering myself: this is using a x86 emulator apparently, not runnign PG natively in WebAssembly (from https://news.ycombinator.com/item?id=32502781 https://news.ycombinator.com/item?id=32502781)

kespindler 4y ago

Do you have plans to open source any of the work? Would love to use this for a course/training platform.

d0100 4y ago

Could this be compiled into a C lib and then we get in-memory postgres for testing?

ThinkBeat 4y ago

What we need is Kubernetes running in the browser to orchestrate web (WASM) applications and infrastructure it takes to render web 4.0 sites and all the different blockchains that it will require.