# DB Setup

Creates tables for the initial database design. This should NOT be run on production any more.  Instead, use db_migrate.ts in the admin_api folder.

# Postgres Installation Local

For local dev, install Postgres15 on a WSL server running Ubuntu 22.04 (installing on windows directly may work too, but these instructions apply to Ubuntu 22.04 on WSL):
```
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
sudo apt-get update
sudo apt install -y postgresql postgresql-contrib postgresql-client
sudo service postgresql start
sudo -i -u postgres
psql
```
You should see a prompt:
```
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
Type "help" for help.

postgres=#
```

# Setup databases

Now we need to run some postgres commands to set up the database:

1. Create a new user:
```
postgres=# CREATE USER admin WITH PASSWORD 'admin';
CREATE ROLE
```

2. Create the fabricator databases:
```
postgres=# CREATE DATABASE devlocalfabricatorv1;
CREATE DATABASE
```

3. Create the cloud databases:
```
postgres=# CREATE DATABASE dev_local_cloud_v1;
CREATE DATABASE
```

3. Grant privileges to the user for both databases:
```
postgres=# GRANT ALL PRIVILEGES ON DATABASE devlocalfabricatorv1 TO admin;
GRANT
```
```
postgres=# GRANT ALL PRIVILEGES ON DATABASE dev_local_cloud_v1 TO admin;
GRANT
```

4. Postgres 15 bullshit for public schema:
```
postgres=# GRANT USAGE, CREATE ON SCHEMA public TO admin;
```

# Creating and updating database tables 

Set the values in the local .env files:
```
# Fabricator database postgres
FABRICATOR_DB_POSTGRES_HOST="127.0.0.1"
FABRICATOR_DB_POSTGRES_DATABASE="devlocalfabricatorv1"
FABRICATOR_DB_POSTGRES_USER="admin"
FABRICATOR_DB_POSTGRES_PASSWORD="admin"
```

Run db_setup.ts:
```
cd apps/db_setup
ts-node fabricator_db_setup.ts
ts-node cloud_db_setup.ts
```

# Migration
```
cd apps/admin_api
ts-node db_migrate.ts
```


# Note on the .pem file in this folder

The .pem file is a publicly available certificate bundle provided by AWS here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html