Build your own SQL fiddle with Docker & VSCode

Querying a Dockerized Postgres instance from the comfort of your text editor (scroll to bottom for annotations).

This blog post is meant as a written accompaniment to the below video series on YouTube. Prerequisites are Docker, Visual Studio Code (along with its PostgreSQL and SQLTools extensions), and basic familiarity with shell commands. Code for this project is available here.

• PART 1: Build your own SQL fiddle with Docker & Visual Studio Code
• PART 2: Adding authentication to your Dockerized PostgreSQL database
• PART 3: Loading the AdventureWorks dataset with docker-compose

Every now and then I get asked “which DMBS should I download and install to practice (or learn) SQL?” The question is usually accompanied by the usual suspects of PostgreSQL vs. MySQL vs. SQLite, and so on.

Usually, I immediately dissuade them from downloading anything, and instead, point them to Google Cloud’s BigQuery, which provides a browser-based UI and plenty of public datasets for practice. (For one-off cases where you don’t plan on saving your work, one of the many fiddles available can also work.)

Recently though I came across an interesting statistic that made me rethink the above advice, and opt for getting started via Docker instead:

Docker has become a lucrative skill in the tech industry, with the share of jobs containing Docker as a skill on Indeed increasing by 9,538% since 2014, the report found. Since that same year, the share of job searches that contain Docker increased by 1,366%.

I have no doubt this trend will continue and that a prerequisite for Docker knowhow will be as commonplace for data-adjacent careers as is familiarity with other everyday tools like Github (if it isn’t already).

The goal of this post is to provide an example of how to create a simple, lightweight, SQL fiddle while:

• Maximizing the mileage of your editor (we’ll be using Visual Studio Code)
• Minimizing the context switching typically accompanied by the need to balance between your text editor, PGAdmin, cloud provider console, etc.

Finding a Postgres image

We’ll be using the official Docker PostgreSQL image from DockerHub, available here. Pay special attention to the note about “Initialization scripts”:

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

We’ll be using this later to stage some data into our database at build time.

Writing our compose file

A docker-compose.yaml file allows you to string together several containers into a single app; nonetheless, it’s equally useful for single-container setups as well. For our purposes, the bare minimum we’ll need is:

version: '3'
services:
  postgres:
    container_name: pg-fiddle
    image: postgres:latest
    environment:
      POSTGRES_HOST_AUTH_METHOD: "trust"
    ports:
      - "5432:5432"
    expose:
      - "5432"
    networks:
      - postgres
networks:
  postgres:
    driver: bridge

Adding in our DDL

Let’s say we want our database to come pre-loaded with some sample data that we can start querying right away; but first, we’ll need to create a user, database, and schema. I’ve copied the sample psql command in the “Initialization script” section of the above docs resulting in the below:

psql -v --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    create user docker;
    create database test;
    grant connect on database test to docker;
    create schema if not exists fiddle;
    grant all on schema fiddle to docker;
    set search_path to fiddle,public;
EOSQL

The only notable addition is the search_path customization, which you can read move about here. Meanwhile for our table, we can use the below employees table:

create table if not exists employee (id int, salary int);
truncate table employee;
insert into employee (id, salary) values ('1', '100');
insert into employee (id, salary) values ('2', '200');
insert into employee (id, salary) values ('3', '300');

Now, the only remaining step is to copy our custom scripts unto the container. Let’s say I have the above two DDL scripts in a script directory:

script/
- init-user-db.sql
- create_table.sql 

Now I can can use volumes to load them unto the Docker container during initialization with:

volumes:
  - ./script/init-user-db.sh:/docker-entrypoint-initdb.d/init-user-db.sh
  - ./script/create_table.sql:/script/create_table.sql

All this says is that:

• The init-user-db.sh file will be moved from my host file system to my container’s /docker-entrypoint-initdb.d directory.
• The create_table.sql file will be moved from my host file system to my container’s /script directory.

Putting it all together

Now we can go to our terminal and run the below to bring up our service:

docker-compose up -d

Then, having downloaded the PostgreSQL VSCode extension, we can follow the instructions in the “Quickstart” guide to query our activity table.

The end result should look something like the below, which I’ve further annotated into four sections:

1. This is our directory structure. All Docker CLI commands will be executed from inside the pg-sql-docker-fiddle directory.
2. The docker-compose.yaml file created above.
3. Our actual SQL query.
4. The results of our SQL query.

All of the above code is available on Github.

What our end result will look like from inside Visual Studio Code.