3 ms·

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

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.
        afavour 4y ago
        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.
- NeutralForest 4y ago
  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.
  - mike_hearn 4y ago
    But presumably none of those issues is fixed by using WASM in a browser.
    - NeutralForest 4y ago
      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.
- simonw 4y ago
  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.
  - groffee 4y ago
    > 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.
    - josephg 4y ago
      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.
      - dotancohen 4y ago
        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?
        josephg 4y ago
        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!
deleted 4y ago
[deleted]
tofuahdude 4y ago
$ docker run --name your-db-here -e POSTGRES_PASSWORD=pw -d postgres
- afavour 4y ago
  Removing docker as a requirement is pretty huge.
  - nerpderp82 4y ago
    Removing docker and also, enabling an entire sandbox environment by clicking a link in a browser.
jjwiseman 4y ago
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/ https://jamey.thesharps.us/2019/05/29/per-project-postgres/
- gen220 4y ago
  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.
- ithrow 4y ago
  FYI, not all drivers support unix sockets, like the official Java one.
WanderPanda 4y ago
Sounds like we are poised to be rewarded with another 10 second boot markup for MS Teams
melony 4y ago
We don't need to wait for CloudFlare to ship their D2! Just plonk this on Firecracker and you are good to go.
forinti 4y ago
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.
gfodor 4y ago
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_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System... wrt SQLite, icmyi: https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql
chrismorgan 4y ago
[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 https://news.ycombinator.com/item?id=30394737. (My use of “probably” above is explained in there too.)
- samwillis 4y ago
  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.
  - chrismorgan 4y ago
    [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.
_hl_ 4y ago
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.
- fermuch 4y ago
  > 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!
  - robertlagrant 4y ago
    Yeah but can I host that Gitpod in Docker running on Kubernetes running on Docker on Linux on Qemu on Wasm as well?
- craigkerstiens 4y ago
  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-container-apps-easy-deploy-postgres-apps https://www.crunchydata.com/blog/announcing-postgres-contain...
darkarmani 4y ago
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.
atentaten 4y ago
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.

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.
      - afavour 4y ago
        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.
NeutralForest 4y ago
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.
- mike_hearn 4y ago
  But presumably none of those issues is fixed by using WASM in a browser.
  - NeutralForest 4y ago
    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.
simonw 4y ago
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.
- groffee 4y ago
  > 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.
  - josephg 4y ago
    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.
    - dotancohen 4y ago
      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?
      - josephg 4y ago
        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!

deleted 4y ago

[deleted]

tofuahdude 4y ago

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

afavour 4y ago
Removing docker as a requirement is pretty huge.
- nerpderp82 4y ago
  Removing docker and also, enabling an entire sandbox environment by clicking a link in a browser.

jjwiseman 4y ago

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/ https://jamey.thesharps.us/2019/05/29/per-project-postgres/

gen220 4y ago
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.
ithrow 4y ago
FYI, not all drivers support unix sockets, like the official Java one.

WanderPanda 4y ago

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

melony 4y ago

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

forinti 4y ago

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.

gfodor 4y ago

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_Access_API https://developer.mozilla.org/en-US/docs/Web/API/File_System... wrt SQLite, icmyi: https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql

chrismorgan 4y ago

[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 https://news.ycombinator.com/item?id=30394737. (My use of “probably” above is explained in there too.)

samwillis 4y ago
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.
- chrismorgan 4y ago
  [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.

_hl_ 4y ago

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.

fermuch 4y ago
> 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!
- robertlagrant 4y ago
  Yeah but can I host that Gitpod in Docker running on Kubernetes running on Docker on Linux on Qemu on Wasm as well?
craigkerstiens 4y ago
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-container-apps-easy-deploy-postgres-apps https://www.crunchydata.com/blog/announcing-postgres-contain...

darkarmani 4y ago

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.

atentaten 4y ago

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.