Impossible to run platform services on a shared database

[andrea-gioia]

Bug Report

Description

Flyway is configured in each platform service to look for migrations in a table stored in the public schema/database. This setup works well when each service has its own dedicated database instance. However, when services share the same database instance, a conflict arises because each service overrides the migration of the previously run service. As a result, you can run all services in sequence, but if one service is stopped, it cannot be restarted. After the first run, the Flyway table is modified, so the service attempts to recreate the database. Since the database already exists, this causes errors.

Expected behavior

Each service should save the history table that tracks all flyway migration in it own database/schema to avoid conflicts with other services when they share the same database instance

Possible solution

in the application profile of each service add the following flyway configuration parameter: schemas: ${FLYWAY_SCHEMA}

Steps to Reproduce

Build code...

git clone git@github.com:opendatamesh-initiative/odm-platform.git
cd ./odm-platform
mvn clean install -DskipTests

Run postgres container...

docker network create odmp-network
docker run --name odmp-postgres -d --network odmp-network -p 5432:5432 \
  -e POSTGRES_DB=odmpdb \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  postgres:11-alpine

Run registry container...

docker build -t odmp-postgres-registry . -f product-plane-services/registry-server/Dockerfile \
   --build-arg DATABASE_URL=jdbc:postgresql://odmp-postgres:5432/odmpdb \
   --build-arg DATABASE_USERNAME=postgres \
   --build-arg DATABASE_PASSWORD=postgres \
   --build-arg FLYWAY_SCRIPTS_DIR=postgresql


docker run --name odmp-postgres-registry  -d --network odmp-network -p 8001:8001 odmp-postgres-registry

Run notification container...

  docker build -t odmp-postgres-notification . -f product-plane-services/notification-server/Dockerfile\
   --build-arg DATABASE_URL=jdbc:postgresql://odmp-postgres:5432/odmpdb \
   --build-arg DATABASE_USERNAME=postgres \
   --build-arg DATABASE_PASSWORD=postgres \
   --build-arg FLYWAY_SCRIPTS_DIR=postgresql


docker run --name odmp-postgres-notification  -d --network odmp-network -p 8006:8006 odmp-postgres-notification

Restart containers...

docker stop odmp-postgres-registry
docker stop odmp-postgres-notification
docker start odmp-postgres-registry
docker start odmp-postgres-notification

The restart of services should fail.