Persistence: Database
Published: 2021-04-07 13:00:57

## 1. Database Setup
How to setup and use a dockerized PostgreSQL database.

{% youtube 6EFN-tMrHSw %}

#### 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`
```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.
```bash
docker-compose up
```
Stop container (if you are on a different terminal than the one running it).
```bash
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
{% youtube 5bEOI0-uDtA %}

#### Snippets & Commands
##### Docker commands
List all running containers.
```bash
docker ps
```
Enter a running container. Replace <service name> with the name of the service in `docker-compose.yaml`.
```bash
docker-compose exec <service name> /bin/sh
```
In this case the service was called `database`.
```bash
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`.
```bash
psql <database name> <database user>
```
In this case the database was called `demo` and the user was `demo_user`.
```bash
psql demo demo_user
```

[GitHub Repo Tag](https://github.com/softwaredevacademy/spring-web-app-demo/releases/tag/docker-compose)

## 3. Modifying the Database with SQL Queries

{% youtube HHt4EMu0J6E %}

#### 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](https://www.postgresql.org/docs/13/app-psql.html)

##### 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)
```sql
CREATE TABLE people (name varchar(120), age integer);
```

Insert a row (entity) with name 'Person 1' and age '25' into the `people` table.
```sql
INSERT INTO people VALUES('Person 1', 25); 
```

Show all rows in people table.
```sql
SELECT * FROM people;
```

Show the rows where the property `name` is 'Person 2'.
```sql
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'.
```sql
UPDATE people SET name = 'New Name' WHERE name = 'Person 1';
```

Delete rows where property 'name' is 'New Name'
```sql
DELETE FROM people WHERE name = 'New Name';
```

Delete a table and all the entities inside. Replace `<table name>` with name of table.
```sql
DROP TABLE <table name>;
```

[PostgreSQL Documentation – SQL Commands](https://www.postgresql.org/docs/10/sql-commands.html)

<hr>
<p style="align: center;"> <span> <a href="https://dev.to/softwaredevacademy/hello-world-paths-3i8a">Previous article</a> | </span>
<span> <a href="https://dev.to/softwaredevacademy/persistence-part-2-orm-3b7k"> Next article </a> </span>