Creating a PostgreSQL gitpod demo

In this blog I will demo how to create a GitPod workspace containing a PostgreSQL database and database tools in VSCode. Lucas Jellema recently created a GitPod workspace with an Oracle Database, and as we’re also increasingly interested in PostgreSQL I wanted to recreate this experiment but now with PostgreSQL.

First, I started with an empty repository and created a README.md file.

Finding the docker image

My next step was to search the Docker Hub for a convenient ready to use image of a PostgreSQL database:

First hit seems reasonable, looking at the information we can see the latest version maps to 15.1. The Gitpod should start with downloading this docker image version. After download the container should be started, possibly be configured and VSCode needs configuration. To start this, I created a basic .gitpod.yml file containing a small set of statements. The first step is to pull the image, then start the container. As I want to access this database in the gitpod environment, I add a port forwarding option to the docker command line. Additionally a password is given (this is required for this image). No persistent volume is given, this is a throwaway environment.

In my experiments I wanted to add a test user and test database when initializing the database but I found this not working. The issue was that right after starting the container the database takes a couple of seconds to initialize. This meant the command to add users to the database hit a not started database and wouldn’t continue its steps. A small wait of 5 seconds was enough for the database to be fully started. The command ‘docker exec -i -u postgres postgresDB15 psql‘ executes the program sql as user postgres inside the container. As the postgres user is the owner of the postgres software, it is permitted to connect as the database’s superuser without authentication.

After I started the environment a terminal is made available to connect as the superuser to the database. Additionally some extensions are installed in the VSCode environment to allow VSCode to interact with the database. In the pre-supplies VSCode settings file, a database connection is pre-setup. Only the database password needs to be supplied.

tasks:
  - init: |
      docker pull postgres:15.1
      # open the readme.md document in VS code; note: thanks to the configuration in .vscode/settings.json, the document opens in markdown preview mode
      gp open README.md
      docker run -d -p 5432:5432 --name postgresDB15 -e POSTGRES_PASSWORD=welkom01 postgres:15.1 &&
      sleep 5s &&
      docker exec -i -u postgres postgresDB15 psql < scripts/create_user_db.sql &&
      gp sync-done database-running
  - name: start container and run a session as postgres owner
    init: gp sync-await database-running
    command: |
      docker start postgresDB15 &&
      sleep 1s &&
      echo "Postgres is running at localhost port 5432, starting session" && 
      docker exec -it -u postgres postgresDB15 psql 
ports:
  - port: 5432
    onOpen: ignore
    visibility: private

vscode:
  extensions:
    - mtxr.sqltools
    - mtxr.sqltools-driver-pg

After some tries (most tries failed because of the lack of sleep between creation of the database and the first statements against it) the yaml file above is the working implementation. In the scripts directory of the repository is a small demo file created.

Demo time

When I run this small demo it creates 2 tables in the testdb database, as demonstrated in the terminal window. Using the SQL tools extension we can navigate to the database and see the contents of our table:

This was a small introduction, next steps could be to install a PostgreSQL capable tool locally and access it through the forwarded port

Use this link to view the gitpod working: https://gitpod.io/#https://github.com/martijnpronkAMIS/gitpod-postgres-database