Hello again and welcome to the next entry in my blog! In this episode I will remake my application so that it interacts with a database instead of using a hardcoded dictionary as data entry. This is a step towards applying CRUD (Create Read Update Delete) principle in our application.

But first thing first – we of course need a database itself. The easiest way to setup one is by pulling official Docker image of PostgreSQL and then modify it according to our needs. I’ve chosen to go with version 15.10, but you can choose any stable version to your liking. To pull the image run the following command:

docker pull postgres:15.10

You can verify that the base image is now present on your machine by running:

docker image list | grep postgres

Now that we have an image we can run a PostgreSQL container. Setup is very simple, it all boils down to this single command:

docker run -p 5455:5432 --name postgres -e POSTGRES_PASSWORD=mypassword -d postgres:15.10

Once again we need to use port forwarding since we are working on a local machine. The default PosgreSQL port is 5432 and that is the one that container is listening on; but we will forward all of our database connection requests to port 5455 instead. In the run command we also specify name of the container, variable of default admin password and image which should be used. Before we head to actual work you should confirm if the container is running properly:

docker container list

Now that container is running we can connect to it in order to create a database and database user. We can do it by executing this command:

docker exec -it <container id> bash

Afterwards you will be presented with command prompt window logged in as root user, just as the one below:

Change context to user „postgres” utilising „su” command:

su postgres

Now you can run the postgres command line utility:

psql

From postgres we can create the database we need. Do so by running command:

create database rpgapp;

We also require a dedicated user for this database. You can create it this way:

create user rpgapp_user with encrypted password 'PASSWORD';

Of course you should replace 'PASSWORD’ string with actual strong password. Let’s also grant the rpgapp_user rights to database we’ve created:

grant all privileges on database rpgapp to rpgapp_user;

Last thing to do is to verify if the connection works and our user indeed has rights to database. Quit the context of psql with \q command and on container shell run the following:

psql -U rpgapp_user -h 127.0.0.1 -d rpgapp

That should log you back in psql context as rpgapp user. To confirm that run:

\conninfo

You will receive output like the one below:

Let’s take a break for now; in my next entry I will cover adjusting our application to interact with database.