Database

Crunchy Data

Crunchy was chosen as an improvement over the default PostgreSQL option in OpenShift. It facilitates database backups, horizontal scaling, and traffic balancing. This adds to the overall resiliency of the application.

It also offers built-in PostGIS options for spatial query needs.

See what Red Hat has to say about Crunchy Data.

PostgreSQL

PostgreSQL was chosen at the beginning of the PIMS modernization project as a relational database that offered high compatibility with the Node ecosystem of the revised API.

The option of expanding its capability with the PostGIS plugin was seen as beneficial given the requirements for spatial data.

For local development, PostgreSQL is still used in the form of a Docker container.

Migrations

All migrations are handled through TypeORM. TypeORM generates migration files based on the database entities defined in the API. These migrations are recorded in the database and outstanding migrations are automatically run when the API starts.

These manual commands are also available when in the /express-api folder:

• npm run migration run -> Runs outstanding migrations.
• npm run migration revert -> Undo the last run migration. Can be run several times to keep undoing migrations.
• npm run migration generate <name> -> Generates a migration file based on the difference between entity files and the connected database. The argument name is optional but recommended. The name migration will be used if omitted.
• npm run migration create <name> -> Generates a blank migration file. The argument name is optional but recommended. The name migration will be used if omitted.

Database Diagram

This diagram was created using dbdiagram.io

For the purpose of simplifying the diagram, the relations for created_by_id, updated_by_id, and deleted_by_id were removed. Please note that they all refer back to the user.id value.

Click the image below for a larger version.