The Postgres Playground: BYOS (Bring Your Own SQL)

The Postgres playground started with a fun idea of "I wonder if I can put Postgres in a web browser". It evolved to a full set of guided tutorials that you can follow along step by step with. We actually use Notion to power all of our tutorials and an in-house API. This means a new tutorial is as simple as some SQL and some markdown. But, did you know you can bring your own setup to the playground?

Just Postgres in a browser

All of our curated tutorials exist directly on the main Crunchy Data Developer area. However, you can visit an empty Postgres playground directly at: https://www.crunchydata.com/developers/playground. This is a blank slate Postgres instance, utilizing compute and memory directly from your web browser. It is not persistent and not connected to a remote session.

Creating your own SQL files

If you pass in a parameter for a sql file it, the playground will automatically run that once the Postgres session is started.

We can take a very basic set of SQL, something like this:

CREATE TABLE example (
  id serial,
  data text);

INSERT INTO example (data) VALUES ('hello');
INSERT INTO example (data) VALUES ('world');

You can build longer SQL files for full data inputs. If you run a pg_dump from an existing database, that file will also work.

Like a restore file, you can also list SET statements for the session

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

Load your SQL file into a url

The easiest way to host a sql file online is to use the GitHub gist feature. You’ll create a new gist, give it a name. Then view the raw link. The raw view link is the URL we want for working with the playground.

My example my file is:

 https://gist.githubusercontent.com/craigkerstiens/62e00c80df31700302726f77595b7661/raw/3aa83426c148ac77bbed0f80ef31f433cf7e5053/example.sql

Build your own playground URLs

Now we can pass that into the playground and build a longer url with:

1 - The base playground URL: https://www.crunchydata.com/developers/playground

2 - The sql parameter ?sql=

3 - Your full gist URL https://gist.githubusercontent.com/craigkerstiens/62e00c80df31700302726f77595b7661/raw/3aa83426c148ac77bbed0f80ef31f433cf7e5053/example.sql

Resulting in this:

https://www.crunchydata.com/developers/playground?sql=https://gist.githubusercontent.com/craigkerstiens/62e00c80df31700302726f77595b7661/raw/3aa83426c148ac77bbed0f80ef31f433cf7e5053/example.sql

I’ve been really excited to see how many people are using the Postgres playground for education and training. Let us know how you’re using the playground on X @crunchydata.