[HN Gopher] Learn Postgres at the Playground - Postgres compiled...
___________________________________________________________________
Learn Postgres at the Playground - Postgres compiled to WASM
running in browser
Author : samwillis
Score : 452 points
Date : 2022-08-17 16:28 UTC (6 hours ago)
(HTM) web link (www.crunchydata.com)
(TXT) w3m dump (www.crunchydata.com)
| mishraprince wrote:
| 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...
| capableweb wrote:
| Bypassing the idea that this thing is not run via JavaScript
| but WASM, he was definitely right :)
| robocat wrote:
| Use wasm2js to cross-compile WASM to pure JavaScript:
| https://www.google.co.nz/search?q=%22wasm2js%22 and there are
| other ways to not require native WASM!
| prpl wrote:
| maybe we can finally bring back websql
| riston wrote:
| 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.
| craigkerstiens wrote:
| 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:...
| thesausageking wrote:
| 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 wrote:
| This is definitely our plan, we've got some follow on posts
| that go into much more detail on how we built it so stay
| tuned for those.
| ZitchDog wrote:
| 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.
| motleydev wrote:
| 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.
| Mo3 wrote:
| This is a really cool tool. Nice work
| xfalcox wrote:
| > 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.
| MuffinFlavored wrote:
| What were the hardest parts of getting it to compile for WASM?
| stuaxo wrote:
| 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).
| nerpderp82 wrote:
| You are going to need K8S in there to manage all the
| complexity.
| tofuahdude wrote:
| Pretty cool project!
| starik36 wrote:
| Your scientists were so preoccupied with whether they could,
| they didn't stop to think if they should.
|
| Great job!
| xfalcox wrote:
| Broke it trying to load our db schema at
| https://www.crunchydata.com/developers/playground?sql=https:...
|
| Any ideas?
| craigkerstiens wrote:
| 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.
| capableweb wrote:
| You definitely seem to have some issues with memory usage,
| crashes my tab when trying to load that link on a device
| with low amount of memory (4GB).
| data-leon wrote:
| Very cool, I work on https://sqlpad.io which allows people to
| practice sql coding interview questions online.
|
| Do you provide 3rd party hosting? Might consider replacing ours
| to something more flexible like yours in the future.
| craigkerstiens wrote:
| Feel free to drop me a note craig.kerstiens at crunchydata
| and happy to have a conversation to see what options may
| exist.
| pramsey wrote:
| Also, you can `CREATE EXTENSION postgis` on it, and all that
| works too!
| farmin wrote:
| 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 wrote:
| They are really fast with requests!
|
| https://www.crunchydata.com/developers/playground/basics-of-...
| farmin wrote:
| Oh I have no doubt, but I am thinking of niche use cases in a
| offline setting.
| pistoriusp wrote:
| If anyone out there wants to work on an open source version of
| this full-time please reach out to me.
| nro337 wrote:
| This is really neat, great work!
| matrix wrote:
| 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
| samwillis wrote:
| 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!
| motleydev wrote:
| 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
| motleydev wrote:
| Demo part of the premier starting now.
| BMB74 wrote:
| I really liked the playground in action. thanks for sharing.
| neerajdotname2 wrote:
| On a different note we built https://academy.bigbinary.com/learn-
| sql using https://sql.js.org/#/ so that folks could learn SQL in
| the browser without installing anything.
|
| Full disclosure: I worked on it.
| no-reply wrote:
| That is a fine course.
| ijidak wrote:
| 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?
| baq wrote:
| 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.
| candiddevmike wrote:
| 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.
| motleydev wrote:
| We talked about it in this video, about how it's grown the
| way it has. https://www.youtube.com/watch?v=Ryj5c8zLF50
| hombre_fatal wrote:
| > 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 wrote:
| 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.
| hot_gril wrote:
| 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.
| hauxir wrote:
| Incredible. Could this be used to host a local instance using
| webrtc?
| pdntspa wrote:
| > 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 wrote:
| 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 wrote:
| 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 wrote:
| 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.
| hot_gril wrote:
| 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.
| scythmic_waves wrote:
| I was messing with the tutorials in the playground earlier but
| I'm getting a 404 now [1].
|
| [1] https://www.crunchydata.com/playground
| craigkerstiens wrote:
| 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 wrote:
| Seems to be working now! Thanks!
| motleydev wrote:
| Demo is playing here
| https://www.youtube.com/watch?v=Ryj5c8zLF50
| NeutralForest wrote:
| 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 wrote:
| 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.
| k__ wrote:
| 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.
| brap wrote:
| The playground keeps crashing for me (Safari on iOS), can't spend
| more than 2 seconds on the page without a crash
| bluesign wrote:
| Mobile wasm memory management is very hard. I think max is like
| 300-400mb on mobile safari.
| phased20 wrote:
| Looks very cool! Getting a 404 error at the link though (as of
| 10:30am PT anyway):
| https://www.crunchydata.com/developers/tutorials
| wking wrote:
| Should be fixed now! We hit a rate limit a lot sooner than
| expected haha :)
| sprite wrote:
| I'm getting 404 on the Playground link. Do I need to be signed in
| or something?
| craigkerstiens wrote:
| Sorry about that, we hit some rate-limits but should be all
| resolved now.
| mutagen wrote:
| 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.
| nikita wrote:
| 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.
| adam_gyroscope wrote:
| 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 - this lets us use a
| full-featured postgres client (with, eg, cursor support) in the
| browser.
| convolvatron wrote:
| 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 wrote:
| All of Linux is in there, too, I think. Postgres-on-Linux-in-
| WASM
| merb wrote:
| yeah they download v86.wasm which is prolly
| https://github.com/copy/v86/
| 15155 wrote:
| > Were these facilities added to a wasp runtime,
|
| https://github.com/WebAssembly/threads/blob/main/proposals/t...
| simonw wrote:
| 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/
|
| 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...
| Humphrey wrote:
| I love that you did such an audacious thing.
|
| FYI, if you're 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/
| MR4D wrote:
| 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...
| doliveira wrote:
| 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 wrote:
| 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/ - explained
| here: https://leaningtech.com/webvm-server-less-x86-virtual-
| machin...
| ahachete wrote:
| 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
| ogazitt wrote:
| 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.
| panny wrote:
| 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?
| znpy wrote:
| 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 wrote:
| Postgres docs and several recent items here talking about
| "postgres internals" will probably give you a lot of great
| starting points.
| plaur782 wrote:
| A number of good options. Different folks have their favorites:
| https://www.postgresql.org/docs/books/
| hot_gril wrote:
| The basic https://www.postgresql.org/docs/current/index.html is
| where I learned everything.
| samwillis wrote:
| 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.
| WanderPanda wrote:
| Sounds like we are poised to be rewarded with another 10 second
| boot markup for MS Teams
| melony wrote:
| We don't need to wait for CloudFlare to ship their D2! Just
| plonk this on Firecracker and you are good to go.
| sigstoat wrote:
| > 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?
| simonw wrote:
| 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.
| Starlevel001 wrote:
| > now you have to create a database, create a user, grant
| that user access to the database, setup credentials and
| start using them.
|
| CREATE ROLE dumbo WITH LOGIN PASSWORD 'fuck';
|
| CREATE DATABASE dumbo OWNER dumbo;
|
| It's that easy.
| rattray wrote:
| Many developers don't run those commands very frequently,
| so they're easy to forget.
| stuaxo wrote:
| I only have to run these about once every year or two,
| and have to look them up every time.
| Starlevel001 wrote:
| Sorry for being smarter than you I guess.
| groffee wrote:
| > 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.
| michaelmior wrote:
| > That's like complaining you need to break eggs when you
| want to make an omelette.
|
| I have a friend who we discovered didn't like cooking
| eggs because picking the shells out of the bowl was
| tedious. Turns out he was never taught how to crack an
| egg so he would just throw it in the bowl and have bits
| of shell everywhere. Point is that breaking eggs may not
| be so easy for everyone. (Of course, after describing to
| him how eggs are properly cracked, he was excited to try
| it out.)
| josephg wrote:
| 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 wrote:
| 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 wrote:
| 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!
| NeutralForest wrote:
| 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 wrote:
| But presumably none of those issues is fixed by using WASM
| in a browser.
| zachrose wrote:
| Imagine wanting to learn Postgres but not knowing how to use
| a package manager, for example
| konart wrote:
| Is this even a possibility?
| simonw wrote:
| 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/rachelnabo
| rs/status/1558888478955421697
| konart wrote:
| I still kind of lost here.
|
| I know this much about frontend development too but I was
| sure that you still need a package manager to install
| your npm\yarn and other tools.
|
| PS: Obviously you can live without one. Regardless of
| being backend\frontend developer.
|
| PPS: and honestly, how can you be scared of postgresql
| and co. after webpack? If you can actually understand
| this crap postgres setup should be to easy for you.
| powerhour wrote:
| You don't really need webpack or etc for this, you can
| just hit some page that hosts the wasm already.
| josephg wrote:
| > 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.
| simonw wrote:
| 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.
| groffee wrote:
| LOADS of React "developers"
| afavour wrote:
| 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.
| forinti wrote:
| 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.
| [deleted]
| _hl_ wrote:
| 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.
| craigkerstiens wrote:
| I mean we did already ship the ability to run containers from
| inside Postgres so why not tackle every possible approach -
| https://www.crunchydata.com/blog/announcing-postgres-
| contain...
| fermuch wrote:
| > 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 wrote:
| Yeah but can I host that Gitpod in Docker running on
| Kubernetes running on Docker on Linux on Qemu on Wasm as
| well?
| atentaten wrote:
| 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.
| darkarmani wrote:
| 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.
| gfodor wrote:
| Do you have a link pointing out the filesystem API actually
| being developed further? AFAIK that whole API is kind of stable
| and done but I might be wrong.
|
| The other API worth knowing about is the more direct File
| System Access API, which is the one that allows direct access
| now: https://developer.mozilla.org/en-
| US/docs/Web/API/File_System...
|
| wrt SQLite, icmyi: https://github.com/jlongster/absurd-sql
| chrismorgan wrote:
| _[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.)
| samwillis wrote:
| 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 wrote:
| _[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.
| rewq4321 wrote:
| Are you sure you're up to date on the latest additions to
| this API? This wasn't in the MVP:
|
| > The origin private file system provides optional access
| to a special kind of file that is highly optimized for
| performance, for example, by offering in-place and
| exclusive write access to a file's content.
|
| https://web.dev/file-system-access/#accessing-files-
| optimize...
|
| It was originally going to be a separate high-perf
| "Storage Foundation" API, but that was merged with the
| File System Access API.
| chrismorgan wrote:
| Huh. Disregard what I wrote entirely, then. Reading
| through https://github.com/WICG/file-system-
| access/blob/main/AccessH..., I can see how they've
| bypassed most or all of the problems I saw--I was making
| unnecessary assumptions.
|
| Thank you for correcting me. I am now enthusiastic about
| OPFS.
| ankoh wrote:
| This has to be ruled out first: https://github.com/whatwg
| /fs/issues/7#issuecomment-116176851...
|
| ...but then the OPFS will be a quite decent fit. We
| (DuckDB-Wasm) are also looking closely at OPFS.
|
| IMHO the requirement here is not even to get to full
| ACID.
|
| With OPFS, we will get close enough to IndexedDB on
| steroids and bypassing the js heap limits through out-of-
| core operators.
|
| After all, we are still running in a browser.
|
| So I see the value of Wasm-based databases to be a front-
| facing accelerator, not a substitute for robust storage
| solutions.
| tofuahdude wrote:
| $ docker run --name your-db-here -e POSTGRES_PASSWORD=pw -d
| postgres
| afavour wrote:
| Removing docker as a requirement is pretty huge.
| nerpderp82 wrote:
| Removing docker and also, enabling an entire sandbox
| environment by clicking a link in a browser.
| jjwiseman wrote:
| 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/
| ithrow wrote:
| FYI, not all drivers support unix sockets, like the official
| Java one.
| gen220 wrote:
| 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.
| gavinray wrote:
| How does this work if there's no physical storage?
|
| Can WASI use memory as a virtual filesystem?
| williamstein wrote:
| You can use anything you can use from Javascript. E.g.,
| https://www.npmjs.com/package/memfs can be used with
| 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/
|
| 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...).
| e12e wrote:
| 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).
| shadowofneptune wrote:
| There is no WASI in browsers, but yes you can use filesystems
| in main memory. It's called a 'RAM disk.'
| cstrahan wrote:
| 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-webas...
| robomartin wrote:
| 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/
|
| A good first step before jumping into CrunchyData?
| emptysea wrote:
| Interesting, only about 36MB for the wasm postgres
| nathancahill wrote:
| That's amazing.
| wkoszek wrote:
| Doesn't work. Page not found
___________________________________________________________________
(page generated 2022-08-17 23:00 UTC)