Build your own SQL fiddle with Docker & VSCode
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 callsinitdb
to create the defaultpostgres
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:
- postgresnetworks:
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:
- This is our directory structure. All Docker CLI commands will be executed from inside the
pg-sql-docker-fiddle
directory. - The
docker-compose.yaml
file created above. - Our actual SQL query.
- The results of our SQL query.
All of the above code is available on Github.