Running PostgreSQL Inside Your Browser
Written by Nikos Vaggalis   
Thursday, 08 September 2022

Yes it is possible thanks to, what else, WebAssembly.  And with it, the emergence of the Postgres playground, brought to us by Crunchy Data, with live-guided SQL exercises for any level.

One of PostgreSQL's engineers had a crazy idea which he has now managed to realize.The idea came from the fact that it was possible to run SQLite inside your browser, so the reasoning was could the same happen for Postgres too?

But compiling an embedded and small in size and scale
DBMS to Webassembly (as sql.js does for instance) is one thing; compiling a full scale client-server one is another. Proof of that is, as the folks at Cruncy Data found out that the web browser simply did not offer the networking features that Postgres needed which was deemed a pretty big obstacle.Or was it ?

But there was a workaround. By building a v86 virtual machine based on an Alpine Linux image and installing Postgres on it,
they could run it inside the browser because v86 emulates an x86-compatible CPU and hardware by translating machine code to WebAssembly at runtime. So Joey Mezzacappa went WebAssembly indirectly documenting the how-to in his blog post.

However there's two limitations with that approach; there's no internet access from inside the VM and there's no data persistence, so changes are lost when leaving or refreshing the page.

That latter disadvantage is one they actually took advantage of,  building on it to create ephemeral playgrounds that let you
practice your SQL inside your browser and then just let go when finished. As such the official Crunchy Data Postgres Playground took off and let's you run SQL inside your ephemeral WASM container containing guided exercises for any level :

The playgrond offers: 

  • psql basics
    Start here with a loaded a sample database
  • Partitioning
    Learn how to create partitions with native Postgres and pg_partman with an IoT sample dataset
  • High level performance analysis
    A quick introduction to some of the most important Postgres performance metrics, including cache hit ratio, index hit, and bloat
  • Joins in Postgres
    Learn about inner and outer joins in this tutorial
  • Basics of PostGIS
    Test some sample spatial queries and functions with PostGIS
  • Indexing (B-Tree Indexes)
    Learn how to create a b-tree index in Postgres.
  • CTEs and Window Functions
    Learn how to query data with US Birth data 

So what's the big deal? I could very well spin a Postgres instance on SQL/DB Fiddle SQL playgrounds and run my queries there, no problem. How does my local Postgres copy offer more?

For one, it is fast as it runs locally without the need to set up a VM lab. There are no restrictions which would be needed for an  internet-based playground; you have control of a full instance and the full spectrum of SQL and functionality. And of course privacy since you can use your own data at your own machine.

Finally there's an extra bonus.With the news of Heroku dropping its free plans, which include the Postgres instances, having an option to run such services locally is going to be a great advantage.I don't know whether it's related to crypto mining, but other SaaS providers like CI webapp.io, GitLab, TravisCI, and Shippable are all limiting or shutting down their free tiers due to cryptocurrency mining attacks.So again, having a service running locally could prove a great alternative.

It's still early days for Postgres and WASM so there's some polishing to do, but the people at Crunchy Data are working on it.

 

More Information

Crazy Idea to Postgres in the Browser

Postgres Playground

Related Articles

The PostgresSQL Transition Guide Helps You Make The Switch

pg_ivm - Materialised Views On Steroids

 Heroku Announces End Of Free Service

In Praise Of SQLite

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Code On Coin Cracked By 14 Year Old!
04/09/2022

A 14-year-old boy was the first to crack four levels of encryption in code imprinted on a commemorative coin released by the Australian Signals Directorate, the country's foreign intelligenc [ ... ]



Java Or Python For Android - Why Not Both!
12/09/2022

Should you choose Java or Python for your next Android project? You don't have to with Chaquopy, the Python SDK that lets you write Android applications in Python. Thanks to support from Anaconda, it  [ ... ]


More News

pythondata

 



 

Comments




or email your comment to: comments@i-programmer.info

Last Updated ( Thursday, 08 September 2022 )