Persistence: Database

1. Database Setup

How to setup and use a dockerized PostgreSQL database.

Clarifications

• The docker-compose.yaml is manually created. If you're using IntelliJ you can simply right click on your root folder and select New -> File and name it docker-compose.yaml. Alternatively you can use the terminal and use the command touch docker-compose.yaml to create the file. (Just make sure the terminal is in the root folder.)

Snippets & Commands

docker-compose.yaml

version: "3"
services:
  database:
    image: postgres:13-alpine
    environment:
      - POSTGRES_DB=demo
      - POSTGRES_USER=demo_user
      - POSTGRES_PASSWORD=demo_pass
    ports:
      - "5431:5432"
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

Docker commands

Start container.

docker-compose up

Stop container (if you are on a different terminal than the one running it).

docker-compose down

If you are in the terminal where the container is running, you can use control + C to stop it.

Note that docker-compose commands should be run in the root folder where the docker-compose.yaml is.

2. Connecting to the Database

Snippets & Commands

Docker commands

List all running containers.

docker ps

Enter a running container. Replace with the name of the service in docker-compose.yaml.

docker-compose exec <service name> /bin/sh

In this case the service was called database.

docker-compose exec database /bin/sh

psql Commands

While inside a postgres container, enter the postgres database. Replace <database name> and <database user> with name of database and name of user as written in the docker-compose-yaml.

psql <database name> <database user>

In this case the database was called demo and the user was demo_user.

psql demo demo_user

GitHub Repo Tag

3. Modifying the Database with SQL Queries

Snippets & Commands

psql Commands (when inside the database)

Connect to a database. When used without parameters, it shows the current database that we are connected to.

\c

Show the tables in the database.

\dt

PostgreSQL Documentation – psql

SQL Queries (when inside the database)

Create a table with name people that contain entities with a name property (varchar(120), String with maxlength 120) and an age property (integer)

CREATE TABLE people (name varchar(120), age integer);

Insert a row (entity) with name 'Person 1' and age '25' into the people table.

INSERT INTO people VALUES('Person 1', 25); 

Show all rows in people table.

SELECT * FROM people;

Show the rows where the property name is 'Person 2'.

SELECT * FROM people WHERE name = 'Person 2';

Change value of property 'name' to 'New Name' for rows where value of the property 'name' is 'Person 1'.

UPDATE people SET name = 'New Name' WHERE name = 'Person 1';

Delete rows where property 'name' is 'New Name'

DELETE FROM people WHERE name = 'New Name';

Delete a table and all the entities inside. Replace <table name> with name of table.

DROP TABLE <table name>;

PostgreSQL Documentation – SQL Commands

---

Previous article |
Next article