HazardCo Database 🐘

This repository contains the source code for working with the Postgres database which we use at HazardCo.

Tech

The HazardCo Database repository uses the following technology at it's core:

• Postgres - The database we use
• RDS - The AWS service we use for hosting our database in the cloud
• TypeORM - The ORM we use for modelling Postgres tables and for defining controllers for database interactions
• Docker - For running containerised unit tests
• SQL - For documenting and versioning the state of the database at any given time
• PGAdmin4 - GUI for working with the database

Getting started

Developing

Clone the repository to your local machine:

$ git clone [email protected]:hazardco-ltd/hazardco-database.git

Install the dependencies

$ npm i

Connecting to the Database

We don't run a insance of Postgres locally when we're working on the database. We just use the version in RDS for the relevant environment. Here are some steps on getting set up:

1. Download the AWS Session manager plugin (you'll only have to do this once):

$ sudo curl "https://s3.amazonaws.com/session-manager-downloads/plugin/latest/ubuntu_64bit/session-manager-plugin.deb" -o "session-manager-plugin.deb"

2. Install the plugin:

$ sudo dpkg -i session-manager-plugin.deb

3. Setup the connection:

$ nohup timeout 1800 aws ssm start-session --region ap-southeast-2 --target "i-0d7ce6daeb4b759ca" --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters host="<HOST_NAME>",portNumber="5432",localPortNumber="5436" >> session_id.txt

4. You can also use the following command to do setup a connection:

$ aws ssm start-session --region ap-southeast-2 --target "i-0d7ce6daeb4b759ca" --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters host="<HOST_NAME>",portNumber="5432",localPortNumber="5436"

Every time you want to connect to the database, you'll just need to run either step 3 or 4 (it doesn't really matter which one).

Whichever command you use, make sure to swap out <HOST_NAME> with the remote host address for your environment. This will be one of the following: | Environment | Host | | --- | --- | | dev | db-441606397117-dev.cubavn9prmt5.ap-southeast-2.rds.amazonaws.com | | hc1 | db-441606397117-hc1.cubavn9prmt5.ap-southeast-2.rds.amazonaws.com | | uat | db-441606397117-uat.cubavn9prmt5.ap-southeast-2.rds.amazonaws.com | | production | db-441606397117-prod.cubavn9prmt5.ap-southeast-2.rds.amazonaws.com |

For more information, check out the full documentation on Confluence.

Folder Structure

All the main database code lives in the src/postgres/ directory. There are subfolders here which contain files for different purposes:

• controllers/ - Files with TypeORM classes for performing queries on the database. These follow the naming convention [MODEL_NAME]Db.ts
• controllers/__test__/ - Test files for controllers. These follow the naming convention [FILE_TO_TEST].spec.ts
• entities/ - Files with TypeORM classes for modelling a database table and the relationships between multiple tables. These follow the naming convention of simply the name of the corresponding database table, but in PascalCase
• helpers/ - General reusable helper functions for use within controllers and other files
• versions/ - SQL files used to maintain a history of the database schema over time

src/
├─ postgres/
│  ├─ controllers/
│  │  ├─ __test__/
│  │  │  ├─ ArtefactDb.spec.ts
│  │  ├─ ArtefactDb.ts
│  │  ├─ index.ts
│  ├─ entities/
│  │  ├─ index.ts
│  │  ├─ Artefact.ts
│  ├─ helpers/
│  │  ├─ __test__/
│  │  │  ├─ Contractor.spec.ts
│  │  ├─ Contractor.ts
│  │  ├─ index.ts
│  ├─ versions/
│  │  ├─ v0.sql
│  │  ├─ v1.sql
│  │  ├─ unreleased.sql

Versioning

The version files (such as v0.sql and v1.sql) files are used to record the schema of the database at each production release. The version files can be run one after another to completely rebuild the shape of the database as needed.

Each version is considered frozen when it gets to production, so you cannot edit any of these files after they're been released. If you want to modify a table that was created in a previous version, you'll need to created another version file with a statement to ALTER the table.

When you're developing, make any SQL changes to the unreleased.sql file. This makes sure that there is only one new version file per release and conflicts can easily be sorted out in the single unreleased.sql file.

Once we're ready for a UAT release, a new v[next].sql file should be created with the number of the next version (so if the latest version file is v2.sql, the new file will be called v3.sql). The code in unreleased.sql is then moved to the new version file.

IMPORTANT: the last statement in a version file should be the following statement:

-- VERSION is the number of the version file
UPDATE configuration SET value = VERSION WHERE key = 'version';

Refer to the diagram below for the steps:

Testing

Unit testing for the repo is done with Jest and uses Docker to create a test database that we can perform queries and make assertions on. The point of unit testing in this repo is to make sure that a method on particular controller class (whether its a simple query or a complex one) returns the data we expect.

To run the tests:

$ npm test

Supporting resources for testing

• test-helpers/factories - A directory for Factories. Factories are used for creating dummy objects to represent data we'd save in Postgres tables
• docker-compose.yml - A YML file is used by docker to create the containers we need for testing