setup PostgreSQL database with docker in 5 minutes

We use docker at Telixia to develop our backend APIs for our transport and logistic applications. Docker greatly improves our productivity by offering a consistent development environment for your entire team; support for multiple stacks, and easy management of development dependencies, and not to mention, ease of deployment.

We develop backend with Java, Rust, Javascript, Elixir, Go, and C++ programming languages together with various database technologies such as Postgresql, MySql, MongoDB, and Noe4j. A typical task we do during development is setting up the development environment (with the database). Docker takes away these repetitive, mundane configuration tasks.

In this article, we will see how to install docker and launch PostgreSQL in five minutes.

1. Go to the official website and download the right version for your machine.

Personally, I use Ubuntu and Snapcraft works for me:

# snapd can be installed from the command line

$ sudo apt update
$ sudo apt install snapd

# use snap to install docker

$ sudo snap install docker

2. Verify that Docker Engine is installed correctly by running the hello-world image.

 sudo docker run hello-world

This command downloads a test image and runs it in a container. When the container runs, it prints an informational message and exits.

3. (download and) launch the docker images. PostgreSQL 13 in my case

docker run -d --name pg13 -p 5432:5432 -e POSTGRES_PASSWORD=talent postgres:13

4. Connect to the docker container

docker exec -it  pg13   /bin/bash

psql -h localhost -U postgres

5. Now that you are in the database shell, you can execute SQL commands.

PostgreSQL Tutorial: Creating an Academy Database

1. Getting Started with PostgreSQL

Access the PostgreSQL prompt:
- Open your terminal or command prompt.
- Type psql and press Enter.

2. Creating the Academy Database

Run the CREATE DATABASE command:
```
 CREATE DATABASE Academy;
```
Connect to the database:
```
 \c Academy
```

3. Creating Tables

- Student table:

   CREATE TABLE student (
       student_id SERIAL PRIMARY KEY,
       name VARCHAR(50) NOT NULL,
       email VARCHAR(100) UNIQUE,
       enrollment_date DATE
   );

- Teacher table:

   CREATE TABLE teacher (
       teacher_id SERIAL PRIMARY KEY,
       name VARCHAR(50) NOT NULL,
       subject VARCHAR(50),
       email VARCHAR(100) UNIQUE
   );

- Course table:

   CREATE TABLE course (
       course_id SERIAL PRIMARY KEY,
       course_name VARCHAR(50) NOT NULL,
       teacher_id INTEGER REFERENCES teacher(teacher_id)
   );

- Marksheet table:

   CREATE TABLE marksheet (
       student_id INTEGER REFERENCES student(student_id),
       course_id INTEGER REFERENCES course(course_id),
       marks INTEGER NOT NULL,
       PRIMARY KEY (student_id, course_id)
   );

4. Working with Data Types

PostgreSQL supports various data types:
- SERIAL: Auto-incrementing integer
- VARCHAR: Variable-length character string
- DATE: Date values
- INTEGER: Integer numbers
- BOOLEAN: True or false values
- And more!

5. Writing Queries to Retrieve Data

Select all students:
```
 SELECT * FROM student;
```

Select teachers with a specific subject:

 SELECT * FROM teacher WHERE subject = 'Math';

Join tables to see student marks:

 SELECT student.name, course.course_name, marksheet.marks
 FROM student
 JOIN marksheet ON student.student_id = marksheet.student_id
 JOIN course ON marksheet.course_id = course.course_id;

6. Using Functions and Operators

PostgreSQL offers many built-in functions for data manipulation:
- COUNT(): Counts rows
- SUM(): Sums values
- AVG(): Calculates averages
- MAX(): Finds the maximum value
- MIN(): Finds the minimum value
- And more!

7. Managing User Access and Security

Creating users:

 CREATE USER new_user WITH PASSWORD 'password';

Granting permissions:

 GRANT SELECT, INSERT, UPDATE ON student TO new_user;

Additional Tips

Use meaningful table and column names.
Normalize your database to reduce redundancy.
Use constraints to enforce data integrity.
Back up your database regularly.
Explore PostgreSQL's documentation for more advanced features.

6. Let’s stop the instance

docker stop pg13

7. And let’s clear any data generated and stored by our instance

docker rm pg13

8. To clean or prune unused (dangling) images

docker image prune

To remove all images which are not in use containers, add - a

docker image prune -a

To Purne your entire system

docker system prune

Download a Docker cheatsheet for more commands at your fingertip.

Docker Compose

Install docker-compose

Compose is a tool for defining and running multi-container Docker applications. With Compose, you use a YAML file to configure your application’s services. Then, with a single command, you create and start all the services from your configuration. To learn more about all the features of Compose, see the list of features.

Using Compose is basically a three-step process:

Define your app’s environment with a Dockerfile so it can be reproduced anywhere.
Define the services that make up your app in docker-compose.yml so they can be run together in an isolated environment.
Run docker-compose up and the Docker compose command starts and runs your entire app. You can alternatively run docker-compose up using the docker-compose binary.


# project/docker-compose.yml

version: '3.9'
services:
  postgres:
    image: postgres:13
    container_name: postgres
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=telixia
    volumes:
      - postgres:/var/lib/postgresql/data
    ports:
      - '5432:5432'
volumes:
  postgres:

Run

$ docker-compose up -d $ ./run_tests $ docker-compose down

Thanks for reading and happy coding!

Up and running with docker in 5 minute