Working with the database

Introduction

The xCoLab's database is managed through Flyway database migrations. It is split into separate logical sections (within the same logical database), which are each managed by one of the microservices. The schema for each service is automatically set up the first time you compile it with maven or run it. Future changes to will be automatically verified and executed when you compile or run the service.

Structure

Each service manages its own database entities. These entities are prefixed with the service's name followed by two underscores, e.g. activity__activity_entry.

Note that there is the option to separate the tracking-service's database into a different database. This has several reasons, the gist of which is that the service contains a lot of extra information and its data is not required for operations.

Database migrations

Database migrations are managed using Flyway. Flyway keeps track of migrations that have been run in the flyway_*_schema_version table, where * is the name of the service (e.g. flyways_activity_schema_version).

Applying migrations

The migrations are applied automatically by Spring when the service starts or using the flyway-maven-plugin when compiling with maven.

Note: When entities changed, you may need to run mvn compile to update the generated database classes from jOOQ.

Resolving errors

There are two main causes for errors in the flyway migration:

Failed migrations

If a migration fails, you will need to do one of two things:

1. manually undo the changes and re-run the migration (you will need to delete the migration's entry from the flyway_*_schema_version table to rerun the migration).
2. manually apply the remaining changes and set success to 1.

Changes to already applied migrations

Flyway saves a checksum for each migration and verifies them on each startup. It will throw an error if an already applied migration changed since the time it was applied. Generally, you should avoid this happening by not changing already applied migrations. If it does happen, you can modify the checksum in the flyway_*_schema_version table to silence this warning (make sure you apply any changes to the script manually if you do this).

Creating migrations

If you are making a change to the structure of the database or a change that should apply to all xCoLab instances, you will need to create a new database migration. If, on the other hand, your change is specific to a colab, e.g. making a certain user an admin on this instance, you should directly execute SQL statements in your instance only.

Migrations are created in the db/migration folder of each service's resource folder. They are prefixed with VX__, where X is the next version number for this service. To create a new migration, just create a new file in this folder using the next version number for this service. The migration will be run exactly once on each xCoLab instance, so it doesn't need to be repeatable (but it doesn't hurt, in case you need to change and re-apply it during development).

Note: if two branches are both applying a new migration to the same service's database, the second one to be merged to develop needs to increment its version number to avoid a conflict.