sqldb-postgresql ships mandatory Flyway migrations that may conflict with project migrations #21

New issue

Open

opened 2026-04-05 08:45:00 +00:00 by mprihoda · 0 comments

mprihoda commented

2026-04-05 08:45:00 +00:00

(Migrated from github.com)

Problem

The sqldb-postgresql module (as of 0.1.11) includes two Flyway migration files on the classpath at db/migration/postgresql/:

V1__create_message_catalogue.sql — message catalogue tables with audit trail
V2__create_permissions.sql — ReBAC permissions table

These migrations run automatically whenever a project uses PostgreSQLTestingLayers.flywayMigrationServiceLayer (which scans classpath:db/migration/postgresql) or configures Flyway with that location.

Issues

Version conflicts: Projects that already have their own V1/V2 migrations at db/migration/postgresql will get FlywayException: Found more than one migration with version 1. We hit this in xml-rozhrani after upgrading to 0.1.11 and had to renumber all project migrations to V100+.
Unwanted tables: Projects that don't use message catalogue or permissions features will still get those tables created in their database. This violates the principle of least surprise — a database infrastructure dependency shouldn't impose schema changes.
Version number reservation: Library-level migrations occupying V1/V2 forces all downstream projects to work around those version numbers, which doesn't scale if more built-in migrations are added in future releases.

Suggested Approaches

Separate migration locations: Ship library migrations under a distinct classpath location (e.g., db/migration/postgresql/iw-support) and let projects opt in by adding that location to their Flyway config.
Provide migrations as code/helpers: Instead of auto-discovered SQL files, provide a programmatic way to include these migrations (e.g., a method that returns additional Flyway locations).
Use a different versioning scheme: If auto-discovery is desired, use a prefix that won't clash (e.g., V0_001__ or repeatable migrations R__).

Context

Discovered while upgrading xml-rozhrani from 0.1.10-SNAPSHOT to 0.1.11. The PostgreSQLTestingLayers was also moved from works.iterative.sqldb.testing to works.iterative.sqldb.postgresql.testing in this version, which is a breaking change worth noting in release notes.

## Problem The `sqldb-postgresql` module (as of 0.1.11) includes two Flyway migration files on the classpath at `db/migration/postgresql/`: - `V1__create_message_catalogue.sql` — message catalogue tables with audit trail - `V2__create_permissions.sql` — ReBAC permissions table These migrations run automatically whenever a project uses `PostgreSQLTestingLayers.flywayMigrationServiceLayer` (which scans `classpath:db/migration/postgresql`) or configures Flyway with that location. ## Issues 1. **Version conflicts**: Projects that already have their own V1/V2 migrations at `db/migration/postgresql` will get `FlywayException: Found more than one migration with version 1`. We hit this in `xml-rozhrani` after upgrading to 0.1.11 and had to renumber all project migrations to V100+. 2. **Unwanted tables**: Projects that don't use message catalogue or permissions features will still get those tables created in their database. This violates the principle of least surprise — a database infrastructure dependency shouldn't impose schema changes. 3. **Version number reservation**: Library-level migrations occupying V1/V2 forces all downstream projects to work around those version numbers, which doesn't scale if more built-in migrations are added in future releases. ## Suggested Approaches - **Separate migration locations**: Ship library migrations under a distinct classpath location (e.g., `db/migration/postgresql/iw-support`) and let projects opt in by adding that location to their Flyway config. - **Provide migrations as code/helpers**: Instead of auto-discovered SQL files, provide a programmatic way to include these migrations (e.g., a method that returns additional Flyway locations). - **Use a different versioning scheme**: If auto-discovery is desired, use a prefix that won't clash (e.g., `V0_001__` or repeatable migrations `R__`). ## Context Discovered while upgrading `xml-rozhrani` from `0.1.10-SNAPSHOT` to `0.1.11`. The `PostgreSQLTestingLayers` was also moved from `works.iterative.sqldb.testing` to `works.iterative.sqldb.postgresql.testing` in this version, which is a breaking change worth noting in release notes.