asc-schema

ASC Schema

A simple container to run migrations on the ASC audit database

DB Users

hof      | User for the ASC hof application to post audit data
reports  | User for save-return-lookup-ui, save-return-api, save-return-email-alerts services to get and post session data

Environment variables

You'll need to set the following env vars:

HOF_USER_PASS       | Password for the hof USER
NODE_ENV            | So knex knows what config to run against
DB_HOST             | The postgres db host
DB_USER             | The postgres db username
DB_PASS             | The postgres db password
DB_NAME             | The postgres db name

Local Setup

The migrations and seeds folders are used by knex to setup a local DB with dummy information for testing the service. These are not used in production where it is assumed a separate DB is setup for knex to connect to that is already setup.

Run the following commands to setup a test DB:

brew install postgres
brew services start postgresql
psql postgres
CREATE ROLE knex WITH LOGIN PASSWORD 'knex';
ALTER ROLE knex WITH SUPERUSER;
CREATE DATABASE knex_session;
\q

If you download Postico for Mac (https://eggerapps.at/postico/), you can then inspect your postgres DB for example and look at the test entries inserted into the test table 'Reports'.

You then need to use a knexfile with migrations and seeds folders to populate your database. The asc-schema repo which is used for migrations in the ASC service (https://github.com/UKHomeOffice/asc-schema) can be used as a test example and is included in this project. You can run

yarn run db:setup

from that repo to setup your database.

Adding new migrations

Add a new migration running

knex migrate:make <migration_name>

Also ensure to add new seed data which can be used for local DB testing in the seed folder.

Then update the config.js file to specify the latest migration file to run to. Then document this in the audit log below.

When new work is merged to master, update the digest for the kube job in the asc service which should be the commit SHA. The digest is also logged in the Drone console when the image is published to Quay. E.g.:

8b74079b6dbfce20c0dbd683b554ec6c7c0ddfbb: digest:

Finally remember to update the package.json version, tag the repo with the same version, i.e. v1.2.0, create a release note and publish a new npm module:

npm publish

Which then should be updated in save-return-email-alerts, save-return-lookup-ui and save-return-api. This is so they can test the latest migrations on a local DB if needs be.

Migrations Audit