Skip to content

Latest commit

 

History

History

db

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
 
 
 
 
 
 

DB Setup

Local DB Server

For development purposes it is beneficial to setup a local db service.

The SQL database of choice for COMPASS is PostgreSQL. It is a open source relational database system and is easy to install.

There are different options for setting up a Postgres instance locally. Two are described in the following sections.

For any variant of installation please make sure to use the correct version of Postgres. The db schema for compass doesn’t have special requirements on the RDBMS, but it is tested and know to be working with Postgres in version 12.

Docker

To start an instance with docker, follow the documentation on Postgres image documentation. Please pay special attention to the section Where to Store Data.

Native Install

Follow the OS specific documentation on PostgreSQL Downloads to install Postgres onto your local machine.

Remote DB Server

The setup of a remote db service is out of scope, as it heavily depends on the environment.

DB scripts

Put your db migration scripts here and comply with the naming rules of Flyway. Copy the questionnaire here and paste it to SQL-command.

Steps to Set Up the Schema

Connect to Database

Preconditions
  • oc login was called and the correct project is selected

  • DB runs on default port (5432)

  • DB deployment name is database

Identify the pod that runs the DB: oc get pod -l name=database -o name

Run following command (replace the pod name) to create a port forwarding to the remote database:

oc port-forward <pod-name> <local-port>:<remote-port>

E.g. oc port-forward database-1-ptdq5 15432:5432

To check that everything is working run npx flyway -c db/flyway.js info from the project root folder.

Apply Schema changes to DB

From the project root folder run npx flyway -c db/flyway.js migrate (When you run Flyway the first time, it downloads some binaries and exits. Rerun the command to start the migration.) User and password can be found in the secret database-creds.

The output should look like this (flyway an postgres versions might differ):

$ npx flyway -c db/flyway.js migrate
Database user: MY_DB_USER
Database password:

Flyway Community Edition 7.5.0 by Redgate
Database: jdbc:database://localhost:15432/compass (PostgreSQL 12.1)
Successfully validated 6 migrations (execution time 00:00.038s)
Current version of schema "public": 001
Migrating schema "public" to version "002 - Initial Data"
Migrating schema "public" to version "003 - Queue Data"
Migrating schema "public" to version "004 - Api User"
Migrating schema "public" to version "005 - Store Device Token"
Migrating schema "public" to version "006 - Questionnaire Version History"
Successfully applied 6 migrations to schema "public", now at version v006 (execution time 00:00.153s)

The scripts will setup:

  • 6 tables

  • 1 api user (API_ID = "test", API_KEY = "gKdKLYG2g0-Y1EllI0-W")

  • 2 study participants

  • 17 exemplary queue items (you won’t be able to decrypt those with your key, they are just for reference)

It will not setup an exemplary questionnaire. Therefore, before you can connect the mobile application to the backend, ensure that you add a questionnaire to the table called 'questionnaires'. You can find an exemplary questionnaire here.

Instead of inserting questionnaires into the database using sql, you can use REST calls. Those endpoints are described in the openapi documentation, reachable under http://{your-backend-url}/docs.

Access DB with Admin Tool

Preconditions
  • pgAdmin 4 is installed on your local machine

  • Port forwarding is established. See Connect to Database

Connect
  1. Open PGAdmin

  2. Click Add new Server

  3. Fill in a Server Name

  4. Open tab Connection

  5. 127.0.0.1:15432 as address

  6. Enter Username and Password (can be found in the secret database-creds)

  7. Click Save

Access DB with CLI

Preconditions
  • You identified the pod that runs the DB: oc get pod -l name=database -o name

Connect

The database name and user can be found in the secret database-creds.

$ oc rsh <pod-name>
sh-4.2$ psql <name_of_db> <name_of_db_user>
sampledb=>

You can now execute regular queries.